[Tarantool-patches] [PATCH v1 1/2] sql: fix comparison in IN with list of values
imeevma at tarantool.org
imeevma at tarantool.org
Wed Apr 15 14:47:46 MSK 2020
After this patch, the IN statement checks the compatibility of the
list of values on the right with the value on the left. If the
list contains a value that is not comparable with the left value,
it throws an error.
Part of #4692
---
src/box/sql/expr.c | 16 ++++-
.../gh-4692-comparison-in-IN-operator.test.lua | 32 +++++++++
test/sql-tap/in4.test.lua | 4 +-
test/sql/boolean.result | 76 ++++++----------------
4 files changed, 68 insertions(+), 60 deletions(-)
create mode 100755 test/sql-tap/gh-4692-comparison-in-IN-operator.test.lua
diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index bc21824..4fe4f83 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -2847,6 +2847,7 @@ sqlCodeSubselect(Parse * pParse, /* Parsing context */
}
}
} else if (ALWAYS(pExpr->x.pList != 0)) {
+ assert(nVal == 1);
/* Case 2: expr IN (exprlist)
*
* For each expression, build an index key from the evaluation and
@@ -2861,6 +2862,9 @@ sqlCodeSubselect(Parse * pParse, /* Parsing context */
enum field_type lhs_type =
sql_expr_type(pLeft);
+ enum field_type field_type =
+ sql_type_is_numeric(lhs_type) ?
+ FIELD_TYPE_NUMBER : lhs_type;
bool unused;
struct coll *unused_coll;
if (sql_expr_coll(pParse, pExpr->pLeft, &unused,
@@ -2886,8 +2890,16 @@ sqlCodeSubselect(Parse * pParse, /* Parsing context */
jmpIfDynamic = -1;
}
r3 = sqlExprCodeTarget(pParse, pE2, r1);
- enum field_type types[2] =
- { lhs_type, field_type_MAX };
+ size_t sz = 2 * sizeof(enum field_type);
+ enum field_type *types =
+ sqlDbMallocRaw(sql_get(), sz);
+ if (types == NULL)
+ return 0;
+ types[0] = field_type;
+ types[1] = field_type_MAX;
+ sqlVdbeAddOp4(v, OP_ApplyType, r3, 1, 0,
+ (char *)types,
+ P4_DYNAMIC);
sqlVdbeAddOp4(v, OP_MakeRecord, r3,
1, r2, (char *)types,
sizeof(types));
diff --git a/test/sql-tap/gh-4692-comparison-in-IN-operator.test.lua b/test/sql-tap/gh-4692-comparison-in-IN-operator.test.lua
new file mode 100755
index 0000000..14ba7ad
--- /dev/null
+++ b/test/sql-tap/gh-4692-comparison-in-IN-operator.test.lua
@@ -0,0 +1,32 @@
+#!/usr/bin/env tarantool
+test = require("sqltester")
+test:plan(2)
+
+--
+-- If left value of IN and NOT IN operators is not a vector with
+-- length more that one, make sure that it cannot be compared to
+-- right values in case they are not comparable.
+--
+
+test:do_catchsql_test(
+ "gh-3692-1.1",
+ [[
+ SELECT true in (true, false, 1);
+ ]], {
+ -- <in4-1.1>
+ 1, "Type mismatch: can not convert 1 to boolean"
+ -- </in4-1.1>
+ })
+
+test:do_catchsql_test(
+ "gh-3692-1.2",
+ [[
+ SELECT X'3132' in (X'31', X'32', 3);
+ ]], {
+ -- <in4-1.1>
+ 1, "Type mismatch: can not convert 3 to varbinary"
+ -- </in4-1.1>
+ })
+
+test:finish_test()
+
diff --git a/test/sql-tap/in4.test.lua b/test/sql-tap/in4.test.lua
index 8c69173..2dd9b13 100755
--- a/test/sql-tap/in4.test.lua
+++ b/test/sql-tap/in4.test.lua
@@ -147,13 +147,13 @@ test:do_execsql_test(
-- </in4-2.7>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"in4-2.8",
[[
SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2')
]], {
-- <in4-2.8>
- "two"
+ 1, "Type mismatch: can not convert to number"
-- </in4-2.8>
})
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index 112e41a..b893f2a 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: 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;
@@ -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: 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;
@@ -3860,19 +3848,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);
| ---
@@ -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: 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;
@@ -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: 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);
| ---
--
2.7.4
More information about the Tarantool-patches
mailing list