Tarantool development patches archive
 help / color / mirror / Atom feed
From: imeevma@tarantool.org
To: korablev@tarantool.org, tsafin@tarantool.org
Cc: tarantool-patches@dev.tarantool.org
Subject: [Tarantool-patches] [PATCH v5 6/6] sql: remove implicit cast from MakeRecord opcode
Date: Fri, 21 Aug 2020 12:19:58 +0300	[thread overview]
Message-ID: <2446ef040a7f9d235cfc14bccf4dcaa7b6254e73.1598000242.git.imeevma@gmail.com> (raw)
In-Reply-To: <cover.1598000242.git.imeevma@gmail.com>

This patch removes implicit casting from MakeRecord opcode.

Closes #4230

@TarantoolBot document
Title: remove implicit cast for comparison

After this patch-set, there will be no implicit casts for comparison.
This means that the values ​of the field types STRING, BOOLEAN and
VARBINARY can be compared with the values of the same field type.
Any numerical value can be compared with any other numerical value.

Example:

```
tarantool> box.execute([[SELECT '1' > 0;]])
---
- null
- 'Type mismatch: can not convert 1 to numeric'
...

tarantool> box.execute([[SELECT true > X'33';]])
---
- null
- 'Type mismatch: can not convert boolean to varbinary'
...

tarantool> box.execute([[SELECT 1.23 > 123;]])
---
- metadata:
  - name: 1.23 > 123
    type: boolean
  rows:
  - [false]
...
```
---
 src/box/sql/analyze.c       |   6 +-
 src/box/sql/delete.c        |  15 ++-
 src/box/sql/expr.c          |  17 ++-
 src/box/sql/fk_constraint.c |  12 +-
 src/box/sql/select.c        |  26 +++--
 src/box/sql/update.c        |  23 ++--
 src/box/sql/vdbe.c          |  19 +--
 test/sql-tap/in3.test.lua   |  26 +----
 test/sql/boolean.result     |  76 ++++--------
 test/sql/types.result       | 225 ++++++++++++++++++++++++++++++++++++
 test/sql/types.test.lua     |  51 ++++++++
 11 files changed, 363 insertions(+), 133 deletions(-)

diff --git a/src/box/sql/analyze.c b/src/box/sql/analyze.c
index f74f9b358..3efcd041a 100644
--- a/src/box/sql/analyze.c
+++ b/src/box/sql/analyze.c
@@ -969,8 +969,10 @@ vdbe_emit_analyze_space(struct Parse *parse, struct space *space)
 					     FIELD_TYPE_STRING,
 					     FIELD_TYPE_STRING,
 					     field_type_MAX };
-		sqlVdbeAddOp4(v, OP_MakeRecord, tab_name_reg, 4, tmp_reg,
-				  (char *)types, sizeof(types));
+		sqlVdbeAddOp4(v, OP_ApplyType, tab_name_reg, 4, 0,
+			      (char *)types, P4_STATIC);
+		sqlVdbeChangeP5(v, OPFLAG_DO_NOT_CONVERT_NUMBERS);
+		sqlVdbeAddOp3(v, OP_MakeRecord, tab_name_reg, 4, tmp_reg);
 		sqlVdbeAddOp4(v, OP_IdxInsert, tmp_reg, 0, 0,
 				  (char *)stat1, P4_SPACEPTR);
 		/* Add the entries to the stat4 table. */
diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index 68abd1f58..40a52aadc 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -329,11 +329,16 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 			 */
 			key_len = 0;
 			struct index *pk = space_index(space, 0);
-			enum field_type *types = is_view ? NULL :
-						 sql_index_type_str(parse->db,
-								    pk->def);
-			sqlVdbeAddOp4(v, OP_MakeRecord, reg_pk, pk_len,
-					  reg_key, (char *)types, P4_DYNAMIC);
+			if (!is_view) {
+				enum field_type *types =
+					sql_index_type_str(parse->db, pk->def);
+				sqlVdbeAddOp4(v, OP_ApplyType, reg_pk, pk_len,
+					      0, (char *)types, P4_DYNAMIC);
+				sqlVdbeChangeP5(v,
+						OPFLAG_DO_NOT_CONVERT_NUMBERS);
+			}
+			sqlVdbeAddOp3(v, OP_MakeRecord, reg_pk, pk_len,
+				      reg_key);
 			/* Set flag to save memory allocating one
 			 * by malloc.
 			 */
diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index bc2182446..d553b80db 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -2886,11 +2886,18 @@ sqlCodeSubselect(Parse * pParse,	/* Parsing context */
 						jmpIfDynamic = -1;
 					}
 					r3 = sqlExprCodeTarget(pParse, pE2, r1);
-					enum field_type types[2] =
-						{ lhs_type, field_type_MAX };
-	 				sqlVdbeAddOp4(v, OP_MakeRecord, r3,
-							  1, r2, (char *)types,
-							  sizeof(types));
+					uint32_t size =
+						2 * sizeof(enum field_type);
+					enum field_type *types=
+						sqlDbMallocZero(pParse->db,
+								size);
+					types[0] = lhs_type;
+					types[1] = field_type_MAX;
+					sqlVdbeAddOp4(v, OP_ApplyType, r3, 1, 0,
+						      (char *)types, P4_DYNAMIC);
+					sqlVdbeChangeP5(v, OPFLAG_DO_NOT_CONVERT_NUMBERS);
+					sqlVdbeAddOp3(v, OP_MakeRecord, r3, 1,
+						      r2);
 					sql_expr_type_cache_change(pParse,
 								   r3, 1);
 					sqlVdbeAddOp2(v, OP_IdxInsert, r2,
diff --git a/src/box/sql/fk_constraint.c b/src/box/sql/fk_constraint.c
index 482220a95..50f9ebf74 100644
--- a/src/box/sql/fk_constraint.c
+++ b/src/box/sql/fk_constraint.c
@@ -264,11 +264,13 @@ fk_constraint_lookup_parent(struct Parse *parse_context, struct space *parent,
 		}
 		struct index *idx = space_index(parent, referenced_idx);
 		assert(idx != NULL);
-		sqlVdbeAddOp4(v, OP_MakeRecord, temp_regs, field_count,
-				  rec_reg,
-				  (char *) sql_index_type_str(parse_context->db,
-							      idx->def),
-				  P4_DYNAMIC);
+		sqlVdbeAddOp4(v, OP_ApplyType, temp_regs, field_count, 0,
+			      (char *) sql_index_type_str(parse_context->db,
+							  idx->def),
+			      P4_DYNAMIC);
+		sqlVdbeChangeP5(v, OPFLAG_DO_NOT_CONVERT_NUMBERS);
+		sqlVdbeAddOp3(v, OP_MakeRecord, temp_regs, field_count,
+			      rec_reg);
 		sqlVdbeAddOp4Int(v, OP_Found, cursor, ok_label, rec_reg, 0);
 		sqlReleaseTempReg(parse_context, rec_reg);
 		sqlReleaseTempRange(parse_context, temp_regs, field_count);
diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index b0554a172..4c7d8001c 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1274,9 +1274,13 @@ selectInnerLoop(Parse * pParse,		/* The parser context */
 					field_type_sequence_dup(pParse,
 								pDest->dest_type,
 								nResultCol);
-				sqlVdbeAddOp4(v, OP_MakeRecord, regResult,
-						  nResultCol, r1, (char *)types,
-						  P4_DYNAMIC);
+				sqlVdbeAddOp4(v, OP_ApplyType, regResult,
+					      nResultCol, 0, (char *)types,
+					      P4_DYNAMIC);
+				sqlVdbeChangeP5(v,
+						OPFLAG_DO_NOT_CONVERT_NUMBERS);
+				sqlVdbeAddOp3(v, OP_MakeRecord, regResult,
+						  nResultCol, r1);
 				sql_expr_type_cache_change(pParse,
 							   regResult,
 							   nResultCol);
@@ -1696,9 +1700,11 @@ generateSortTail(Parse * pParse,	/* Parsing context */
 			enum field_type *types =
 				field_type_sequence_dup(pParse, pDest->dest_type,
 							nColumn);
-			sqlVdbeAddOp4(v, OP_MakeRecord, regRow, nColumn,
-					  regTupleid, (char *)types,
-					  P4_DYNAMIC);
+			sqlVdbeAddOp4(v, OP_ApplyType, regRow, nColumn, 0,
+				      (char *)types, P4_DYNAMIC);
+			sqlVdbeChangeP5(v, OPFLAG_DO_NOT_CONVERT_NUMBERS);
+			sqlVdbeAddOp3(v, OP_MakeRecord, regRow, nColumn,
+				      regTupleid);
 			sql_expr_type_cache_change(pParse, regRow, nColumn);
 			sqlVdbeAddOp2(v, OP_IdxInsert, regTupleid, pDest->reg_eph);
 			break;
@@ -3138,9 +3144,11 @@ generateOutputSubroutine(struct Parse *parse, struct Select *p,
 			enum field_type *types =
 				field_type_sequence_dup(parse, dest->dest_type,
 							in->nSdst);
-			sqlVdbeAddOp4(v, OP_MakeRecord, in->iSdst,
-					  in->nSdst, r1, (char *)types,
-					  P4_DYNAMIC);
+			sqlVdbeAddOp4(v, OP_ApplyType, in->iSdst, in->nSdst, 0,
+				      (char *)types, P4_DYNAMIC);
+			sqlVdbeChangeP5(v, OPFLAG_DO_NOT_CONVERT_NUMBERS);
+			sqlVdbeAddOp3(v, OP_MakeRecord, in->iSdst, in->nSdst,
+				      r1);
 			sql_expr_type_cache_change(parse, in->iSdst,
 						   in->nSdst);
 			sqlVdbeAddOp2(v, OP_IdxInsert, r1, dest->reg_eph);
diff --git a/src/box/sql/update.c b/src/box/sql/update.c
index 24c7cfa27..7c80dcc4e 100644
--- a/src/box/sql/update.c
+++ b/src/box/sql/update.c
@@ -251,11 +251,14 @@ sqlUpdate(Parse * pParse,		/* The parser context */
 		nKey = pk_part_count;
 		regKey = iPk;
 	} else {
-		enum field_type *types = is_view ? NULL :
-					 sql_index_type_str(pParse->db,
-							    pPk->def);
-		sqlVdbeAddOp4(v, OP_MakeRecord, iPk, pk_part_count,
-				  regKey, (char *) types, P4_DYNAMIC);
+		if (!is_view) {
+			enum field_type *types =
+				sql_index_type_str(pParse->db, pPk->def);
+			sqlVdbeAddOp4(v, OP_ApplyType, iPk, pk_part_count, 0,
+				      (char *)types, P4_DYNAMIC);
+			sqlVdbeChangeP5(v, OPFLAG_DO_NOT_CONVERT_NUMBERS);
+		}
+		sqlVdbeAddOp3(v, OP_MakeRecord, iPk, pk_part_count, regKey);
 		/*
 		 * Set flag to save memory allocating one by
 		 * malloc.
@@ -423,9 +426,13 @@ sqlUpdate(Parse * pParse,		/* The parser context */
 				enum field_type *types =
 					sql_index_type_str(pParse->db,
 							   pPk->def);
-				sqlVdbeAddOp4(v, OP_MakeRecord, iPk,
-						  pk_part_count, key_reg,
-						  (char *) types, P4_DYNAMIC);
+				sqlVdbeAddOp4(v, OP_ApplyType, iPk,
+					      pk_part_count, 0, (char *)types,
+					      P4_DYNAMIC);
+				sqlVdbeChangeP5(v,
+						OPFLAG_DO_NOT_CONVERT_NUMBERS);
+				sqlVdbeAddOp3(v, OP_MakeRecord, iPk,
+					      pk_part_count, key_reg);
 			} else {
 				assert(nKey == 0);
 				key_reg = regKey;
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index a0ddbaf60..544619b03 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -3145,24 +3145,17 @@ type_mismatch:
 	break;
 }
 
-/* Opcode: MakeRecord P1 P2 P3 P4 P5
+/* Opcode: MakeRecord P1 P2 P3 * P5
  * Synopsis: r[P3]=mkrec(r[P1@P2])
  *
  * Convert P2 registers beginning with P1 into the [record format]
  * use as a data record in a database table or as a key
  * in an index.  The OP_Column opcode can decode the record later.
  *
- * P4 may be a string that is P2 characters long.  The nth character of the
- * string indicates the column type that should be used for the nth
- * field of the index key.
- *
- * If P4 is NULL then all index fields have type SCALAR.
- *
  * If P5 is not NULL then record under construction is intended to be inserted
  * into ephemeral space. Thus, sort of memory optimization can be performed.
  */
 case OP_MakeRecord: {
-	Mem *pRec;             /* The new record */
 	Mem *pData0;           /* First field to be combined into the record */
 	Mem MAYBE_UNUSED *pLast;  /* Last field of the record */
 	int nField;            /* Number of fields in the record */
@@ -3184,7 +3177,6 @@ case OP_MakeRecord: {
 	 * of the record to data0.
 	 */
 	nField = pOp->p1;
-	enum field_type *types = pOp->p4.types;
 	bIsEphemeral = pOp->p5;
 	assert(nField>0 && pOp->p2>0 && pOp->p2+nField<=(p->nMem+1 - p->nCursor)+1);
 	pData0 = &aMem[nField];
@@ -3195,15 +3187,6 @@ case OP_MakeRecord: {
 	assert(pOp->p3<pOp->p1 || pOp->p3>=pOp->p1+pOp->p2);
 	pOut = vdbe_prepare_null_out(p, pOp->p3);
 
-	/* Apply the requested types to all inputs */
-	assert(pData0<=pLast);
-	if (types != NULL) {
-		pRec = pData0;
-		do {
-			mem_apply_type(pRec++, *(types++));
-		} while(types[0] != field_type_MAX);
-	}
-
 	struct region *region = &fiber()->gc;
 	size_t used = region_used(region);
 	uint32_t tuple_size;
diff --git a/test/sql-tap/in3.test.lua b/test/sql-tap/in3.test.lua
index a6d842962..7f3abbae0 100755
--- a/test/sql-tap/in3.test.lua
+++ b/test/sql-tap/in3.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
-test:plan(28)
+test:plan(26)
 
 --!./tcltestrunner.lua
 -- 2007 November 29
@@ -334,18 +334,6 @@ test:do_test(
         -- </in3-3.4>
     })
 
-test:do_test(
-    "in3-3.5",
-    function()
-        -- Numeric affinity should be applied to each side before the comparison
-        -- takes place. Therefore we cannot use index t1_i1, which has no affinity.
-        return exec_neph(" SELECT y IN (SELECT a FROM t1) FROM t2 ")
-    end, {
-        -- <in3-3.5>
-        1, true
-        -- </in3-3.5>
-    })
-
 test:do_test(
     "in3-3.6",
     function()
@@ -358,18 +346,6 @@ test:do_test(
         -- </in3-3.6>
     })
 
-test:do_test(
-    "in3-3.7",
-    function()
-        -- Numeric affinity is applied before the comparison takes place. 
-        -- Making it impossible to use index t1_i3.
-        return exec_neph(" SELECT y IN (SELECT c FROM t1) FROM t2 ")
-    end, {
-        -- <in3-3.7>
-        1, true
-        -- </in3-3.7>
-    })
-
 -----------------------------------------------------------------------
 --
 -- Test using a multi-column index.
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index 51ec5820b..d3eca833c 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -2256,19 +2256,13 @@ SELECT false IN (SELECT a1 FROM t6 LIMIT 1);
  | ...
 SELECT true IN (1, 1.2, 'true', false);
  | ---
- | - metadata:
- |   - name: COLUMN_1
- |     type: boolean
- |   rows:
- |   - [false]
+ | - null
+ | - 'Type mismatch: can not convert 1 to boolean'
  | ...
 SELECT false IN (1, 1.2, 'true', false);
  | ---
- | - metadata:
- |   - name: COLUMN_1
- |     type: boolean
- |   rows:
- |   - [true]
+ | - null
+ | - 'Type mismatch: can not convert 1 to boolean'
  | ...
 
 SELECT a, a IN (true) FROM t;
@@ -2328,14 +2322,8 @@ SELECT a, a IN (SELECT a1 FROM t6) FROM t;
  | ...
 SELECT a, a IN (1, 1.2, 'true', false) FROM t;
  | ---
- | - metadata:
- |   - name: A
- |     type: boolean
- |   - name: COLUMN_1
- |     type: boolean
- |   rows:
- |   - [false, true]
- |   - [true, false]
+ | - null
+ | - 'Type mismatch: can not convert 1 to boolean'
  | ...
 
 SELECT true BETWEEN true AND true;
@@ -3860,19 +3848,13 @@ SELECT a2, b, b != a2 FROM t6, t7;
 
 SELECT true IN (0, 1, 2, 3);
  | ---
- | - metadata:
- |   - name: COLUMN_1
- |     type: boolean
- |   rows:
- |   - [false]
+ | - null
+ | - 'Type mismatch: can not convert 0 to boolean'
  | ...
 SELECT false IN (0, 1, 2, 3);
  | ---
- | - metadata:
- |   - name: COLUMN_1
- |     type: boolean
- |   rows:
- |   - [false]
+ | - null
+ | - 'Type mismatch: can not convert 0 to boolean'
  | ...
 SELECT true IN (SELECT b FROM t7);
  | ---
@@ -3886,14 +3868,8 @@ SELECT false IN (SELECT b FROM t7);
  | ...
 SELECT a1, a1 IN (0, 1, 2, 3) FROM t6
  | ---
- | - metadata:
- |   - name: A1
- |     type: boolean
- |   - name: COLUMN_1
- |     type: boolean
- |   rows:
- |   - [false, false]
- |   - [true, false]
+ | - null
+ | - 'Type mismatch: can not convert 0 to boolean'
  | ...
 
 SELECT true BETWEEN 0 and 10;
@@ -5005,35 +4981,23 @@ SELECT a2, c, c != a2 FROM t6, t8;
 
 SELECT true IN (0.1, 1.2, 2.3, 3.4);
  | ---
- | - metadata:
- |   - name: COLUMN_1
- |     type: boolean
- |   rows:
- |   - [false]
+ | - null
+ | - 'Type mismatch: can not convert 0.1 to boolean'
  | ...
 SELECT false IN (0.1, 1.2, 2.3, 3.4);
  | ---
- | - metadata:
- |   - name: COLUMN_1
- |     type: boolean
- |   rows:
- |   - [false]
+ | - null
+ | - 'Type mismatch: can not convert 0.1 to boolean'
  | ...
 SELECT a1 IN (0.1, 1.2, 2.3, 3.4) FROM t6 LIMIT 1;
  | ---
- | - metadata:
- |   - name: COLUMN_1
- |     type: boolean
- |   rows:
- |   - [false]
+ | - null
+ | - 'Type mismatch: can not convert 0.1 to boolean'
  | ...
 SELECT a2 IN (0.1, 1.2, 2.3, 3.4) FROM t6 LIMIT 1;
  | ---
- | - metadata:
- |   - name: COLUMN_1
- |     type: boolean
- |   rows:
- |   - [false]
+ | - null
+ | - 'Type mismatch: can not convert 0.1 to boolean'
  | ...
 SELECT true IN (SELECT c FROM t8);
  | ---
diff --git a/test/sql/types.result b/test/sql/types.result
index b40f45029..29aa90d7b 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -2846,3 +2846,228 @@ box.execute([[DROP TABLE t;]])
 ---
 - row_count: 1
 ...
+--
+-- Make sure that there is no implicit cast between string and
+-- number.
+--
+box.execute([[SELECT '1' > 0;]]);
+---
+- null
+- 'Type mismatch: can not convert 1 to numeric'
+...
+box.execute([[SELECT 1 > '0';]]);
+---
+- null
+- 'Type mismatch: can not convert 0 to numeric'
+...
+box.execute([[CREATE TABLE t (i INT PRIMARY KEY, d DOUBLE, n NUMBER, s STRING);]])
+---
+- row_count: 1
+...
+box.execute([[INSERT INTO t VALUES (1, 1.0, 1, '2'), (2, 2.0, 2.0, '2');]])
+---
+- row_count: 2
+...
+box.execute([[SELECT * from t WHERE i > s;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE s > i;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE d > s;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE s > d;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE i = 1 and n > s;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE i = 2 and s > n;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE i in (1);]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows:
+  - [1]
+...
+box.execute([[SELECT i FROM t WHERE d in (1);]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows:
+  - [1]
+...
+box.execute([[SELECT i FROM t WHERE n in (1);]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows:
+  - [1]
+...
+box.execute([[SELECT i FROM t WHERE s in (1);]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE i in (1.0);]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows:
+  - [1]
+...
+box.execute([[SELECT i FROM t WHERE d in (1.0);]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows:
+  - [1]
+...
+box.execute([[SELECT i FROM t WHERE n in (1.0);]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows:
+  - [1]
+...
+box.execute([[SELECT i FROM t WHERE s in (1.0);]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE i in ('1');]])
+---
+- null
+- 'Type mismatch: can not convert 1 to integer'
+...
+box.execute([[SELECT i FROM t WHERE d in ('1');]])
+---
+- null
+- 'Type mismatch: can not convert 1 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE n in ('1');]])
+---
+- null
+- 'Type mismatch: can not convert 1 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE s in ('1');]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows: []
+...
+box.execute([[SELECT i FROM t WHERE i in ('1.0');]])
+---
+- null
+- 'Type mismatch: can not convert 1.0 to integer'
+...
+box.execute([[SELECT i FROM t WHERE d in ('1.0');]])
+---
+- null
+- 'Type mismatch: can not convert 1.0 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE n in ('1.0');]])
+---
+- null
+- 'Type mismatch: can not convert 1.0 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE s in ('1.0');]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows: []
+...
+box.execute([[DROP TABLE t;]])
+---
+- row_count: 1
+...
+-- Comparison with SCALAR.
+box.execute([[CREATE TABLE t(a SCALAR PRIMARY KEY);]])
+---
+- row_count: 1
+...
+box.execute([[INSERT INTO t VALUES (1), (2.2), ('3');]]);
+---
+- row_count: 3
+...
+box.execute([[SELECT a FROM t WHERE a > 1]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a > 1.0]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a > '1']]);
+---
+- metadata:
+  - name: A
+    type: scalar
+  rows:
+  - ['3']
+...
+box.execute([[SELECT a FROM t WHERE a < 1]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a < 1.0]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a < '1']]);
+---
+- metadata:
+  - name: A
+    type: scalar
+  rows:
+  - [1]
+  - [2.2]
+...
+box.execute([[SELECT a FROM t WHERE a = 1]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a = 1.0]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a = '1']]);
+---
+- metadata:
+  - name: A
+    type: scalar
+  rows: []
+...
+box.execute([[DROP TABLE t;]])
+---
+- row_count: 1
+...
diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua
index a23b12801..09619653b 100644
--- a/test/sql/types.test.lua
+++ b/test/sql/types.test.lua
@@ -635,3 +635,54 @@ box.execute([[SELECT * FROM t WHERE i > ?]], {-2^70})
 box.execute([[SELECT * FROM t WHERE a = ?]], {2ULL^60ULL - 1ULL})
 box.execute([[SELECT * FROM t WHERE a > ?]], {2ULL^60ULL - 1ULL})
 box.execute([[DROP TABLE t;]])
+
+--
+-- Make sure that there is no implicit cast between string and
+-- number.
+--
+box.execute([[SELECT '1' > 0;]]);
+box.execute([[SELECT 1 > '0';]]);
+box.execute([[CREATE TABLE t (i INT PRIMARY KEY, d DOUBLE, n NUMBER, s STRING);]])
+box.execute([[INSERT INTO t VALUES (1, 1.0, 1, '2'), (2, 2.0, 2.0, '2');]])
+box.execute([[SELECT * from t WHERE i > s;]])
+box.execute([[SELECT * from t WHERE s > i;]])
+box.execute([[SELECT * from t WHERE d > s;]])
+box.execute([[SELECT * from t WHERE s > d;]])
+box.execute([[SELECT * from t WHERE i = 1 and n > s;]])
+box.execute([[SELECT * from t WHERE i = 2 and s > n;]])
+
+box.execute([[SELECT i FROM t WHERE i in (1);]])
+box.execute([[SELECT i FROM t WHERE d in (1);]])
+box.execute([[SELECT i FROM t WHERE n in (1);]])
+box.execute([[SELECT i FROM t WHERE s in (1);]])
+
+box.execute([[SELECT i FROM t WHERE i in (1.0);]])
+box.execute([[SELECT i FROM t WHERE d in (1.0);]])
+box.execute([[SELECT i FROM t WHERE n in (1.0);]])
+box.execute([[SELECT i FROM t WHERE s in (1.0);]])
+
+box.execute([[SELECT i FROM t WHERE i in ('1');]])
+box.execute([[SELECT i FROM t WHERE d in ('1');]])
+box.execute([[SELECT i FROM t WHERE n in ('1');]])
+box.execute([[SELECT i FROM t WHERE s in ('1');]])
+
+box.execute([[SELECT i FROM t WHERE i in ('1.0');]])
+box.execute([[SELECT i FROM t WHERE d in ('1.0');]])
+box.execute([[SELECT i FROM t WHERE n in ('1.0');]])
+box.execute([[SELECT i FROM t WHERE s in ('1.0');]])
+
+box.execute([[DROP TABLE t;]])
+
+-- Comparison with SCALAR.
+box.execute([[CREATE TABLE t(a SCALAR PRIMARY KEY);]])
+box.execute([[INSERT INTO t VALUES (1), (2.2), ('3');]]);
+box.execute([[SELECT a FROM t WHERE a > 1]]);
+box.execute([[SELECT a FROM t WHERE a > 1.0]]);
+box.execute([[SELECT a FROM t WHERE a > '1']]);
+box.execute([[SELECT a FROM t WHERE a < 1]]);
+box.execute([[SELECT a FROM t WHERE a < 1.0]]);
+box.execute([[SELECT a FROM t WHERE a < '1']]);
+box.execute([[SELECT a FROM t WHERE a = 1]]);
+box.execute([[SELECT a FROM t WHERE a = 1.0]]);
+box.execute([[SELECT a FROM t WHERE a = '1']]);
+box.execute([[DROP TABLE t;]])
-- 
2.25.1

  parent reply	other threads:[~2020-08-21  9:19 UTC|newest]

Thread overview: 15+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2020-08-21  9:19 [Tarantool-patches] [PATCH v5 0/6] sql; remove implicit cast for comparison imeevma
2020-08-21  9:19 ` [Tarantool-patches] [PATCH v5 1/6] sql: remove unused DOUBLE to INTEGER conversion imeevma
2020-09-17 14:48   ` Nikita Pettik
2020-09-28 15:50     ` Mergen Imeev
2020-08-21  9:19 ` [Tarantool-patches] [PATCH v5 2/6] sql: add implicit cast between numbers in OP_Seek* imeevma
2020-09-17 15:34   ` Nikita Pettik
2020-09-28 15:55     ` Mergen Imeev
2020-08-21  9:19 ` [Tarantool-patches] [PATCH v5 3/6] sql: change comparison between numbers using index imeevma
2020-09-18  8:08   ` Nikita Pettik
2020-09-28 16:10     ` Mergen Imeev
2020-08-21  9:19 ` [Tarantool-patches] [PATCH v5 4/6] sql: remove implicit cast from comparison opcodes imeevma
2020-08-21  9:19 ` [Tarantool-patches] [PATCH v5 5/6] sql: fix implicit cast in opcode MustBeInt imeevma
2020-08-21  9:19 ` imeevma [this message]
2020-09-18 12:28   ` [Tarantool-patches] [PATCH v5 6/6] sql: remove implicit cast from MakeRecord opcode Nikita Pettik
2020-09-28 16:19     ` Mergen Imeev

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=2446ef040a7f9d235cfc14bccf4dcaa7b6254e73.1598000242.git.imeevma@gmail.com \
    --to=imeevma@tarantool.org \
    --cc=korablev@tarantool.org \
    --cc=tarantool-patches@dev.tarantool.org \
    --cc=tsafin@tarantool.org \
    --subject='Re: [Tarantool-patches] [PATCH v5 6/6] sql: remove implicit cast from MakeRecord opcode' \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox