[Tarantool-patches] [PATCH v6 22/22] sql: remove implicit cast from MakeRecord opcode
imeevma at tarantool.org
imeevma at tarantool.org
Thu Jul 16 17:47:20 MSK 2020
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 0c810b73b..18d8ce4b0 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 426247bd7..0de6486b5 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1308,9 +1308,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);
@@ -1730,9 +1734,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;
@@ -3174,9 +3180,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 9b2b18a9a..e8b02a2ac 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -3158,24 +3158,17 @@ type_mismatch:
break;
}
-/* Opcode: MakeRecord P1 P2 P3 P4 P5
+/* Opcode: MakeRecord P1 P2 P3 * P5
* Synopsis: r[P3]=mkrec(r[P1 at 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 */
@@ -3197,7 +3190,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];
@@ -3208,15 +3200,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 7e7f97284..11c479b74 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -2238,19 +2238,13 @@ SELECT false IN (SELECT a1 FROM t6 LIMIT 1);
| ...
SELECT true IN (1, 1.2, 'true', false);
| ---
- | - metadata:
- | - name: true IN (1, 1.2, 'true', false)
- | type: boolean
- | rows:
- | - [false]
+ | - null
+ | - 'Type mismatch: can not convert 1 to boolean'
| ...
SELECT false IN (1, 1.2, 'true', false);
| ---
- | - metadata:
- | - name: false IN (1, 1.2, 'true', false)
- | type: boolean
- | rows:
- | - [true]
+ | - null
+ | - 'Type mismatch: can not convert 1 to boolean'
| ...
SELECT a, a IN (true) FROM t;
@@ -2310,14 +2304,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: a IN (1, 1.2, 'true', false)
- | type: boolean
- | rows:
- | - [false, true]
- | - [true, false]
+ | - null
+ | - 'Type mismatch: can not convert 1 to boolean'
| ...
SELECT true BETWEEN true AND true;
@@ -3842,19 +3830,13 @@ SELECT a2, b, b != a2 FROM t6, t7;
SELECT true IN (0, 1, 2, 3);
| ---
- | - metadata:
- | - name: true IN (0, 1, 2, 3)
- | type: boolean
- | rows:
- | - [false]
+ | - null
+ | - 'Type mismatch: can not convert 0 to boolean'
| ...
SELECT false IN (0, 1, 2, 3);
| ---
- | - metadata:
- | - name: false IN (0, 1, 2, 3)
- | type: boolean
- | rows:
- | - [false]
+ | - null
+ | - 'Type mismatch: can not convert 0 to boolean'
| ...
SELECT true IN (SELECT b FROM t7);
| ---
@@ -3868,14 +3850,8 @@ SELECT false IN (SELECT b FROM t7);
| ...
SELECT a1, a1 IN (0, 1, 2, 3) FROM t6
| ---
- | - metadata:
- | - name: A1
- | type: boolean
- | - name: a1 IN (0, 1, 2, 3)
- | type: boolean
- | rows:
- | - [false, false]
- | - [true, false]
+ | - null
+ | - 'Type mismatch: can not convert 0 to boolean'
| ...
SELECT true BETWEEN 0 and 10;
@@ -4987,35 +4963,23 @@ SELECT a2, c, c != a2 FROM t6, t8;
SELECT true IN (0.1, 1.2, 2.3, 3.4);
| ---
- | - metadata:
- | - name: true IN (0.1, 1.2, 2.3, 3.4)
- | 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: false IN (0.1, 1.2, 2.3, 3.4)
- | 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: a1 IN (0.1, 1.2, 2.3, 3.4)
- | 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: a2 IN (0.1, 1.2, 2.3, 3.4)
- | 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 f9b6cdcf7..866579b86 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -2826,3 +2826,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
More information about the Tarantool-patches
mailing list