From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp20.mail.ru (smtp20.mail.ru [94.100.179.251]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 8F79C4696C4 for ; Wed, 15 Apr 2020 14:47:47 +0300 (MSK) From: imeevma@tarantool.org Date: Wed, 15 Apr 2020 14:47:46 +0300 Message-Id: <2d865e88da427dd63acdc0e456dd7d032eee5769.1586950754.git.imeevma@gmail.com> In-Reply-To: References: Subject: [Tarantool-patches] [PATCH v1 1/2] sql: fix comparison in IN with list of values List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: v.shpilevoy@tarantool.org, tsafin@tarantool.org, tarantool-patches@dev.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); + ]], { + -- + 1, "Type mismatch: can not convert 1 to boolean" + -- + }) + +test:do_catchsql_test( + "gh-3692-1.2", + [[ + SELECT X'3132' in (X'31', X'32', 3); + ]], { + -- + 1, "Type mismatch: can not convert 3 to varbinary" + -- + }) + +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( -- }) -test:do_execsql_test( +test:do_catchsql_test( "in4-2.8", [[ SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2') ]], { -- - "two" + 1, "Type mismatch: can not convert to number" -- }) 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