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
next prev 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