[Tarantool-patches] [PATCH v1 1/2] sql: fix comparison in IN with list of values

[imeevma@tarantool.org]

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