From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp57.i.mail.ru (smtp57.i.mail.ru [217.69.128.37]) (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 4DB1D42EF60 for ; Thu, 11 Jun 2020 15:54:16 +0300 (MSK) From: imeevma@tarantool.org Date: Thu, 11 Jun 2020 15:54:15 +0300 Message-Id: In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH v2 5/7] sql: remove implicit cast from string for comparison 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 Thank yo for the patch! My answers, diff and new patch below. On 10.06.2020 01:29, Vladislav Shpilevoy wrote: > Thanks for the patch! > >>>> index 854ed774f..2a18b17be 100755 >>>> --- a/test/sql-tap/tkt-9a8b09f8e6.test.lua >>>> +++ b/test/sql-tap/tkt-9a8b09f8e6.test.lua >>>> @@ -83,23 +83,23 @@ test:do_execsql_test( >>>> -- >>>> }) >>>> -test:do_execsql_test( >>>> +test:do_catchsql_test( >>>> 2.1, >>>> [[ >>>> SELECT x FROM t1 WHERE x IN (1); >>>> ]], { >>>> -- <2.1> >>>> - "1" >>>> + 1,"Type mismatch: can not convert 1 to numeric" >>> 3. Can tests in this file be fixed to return the same results >>> as before? >> I fixed this in most cases, but am not sure if this is correct. >> I think most tests are out of date. > > Yeah, looks like the test file becomes mostly useless. It now > tests rather explicit CAST than implicit cast. The previous > version was better. At least it covered implicit cast errors. > > I would try to rollback to the previous version, remove obviously > duplicated or not needed test cases, and see what is left. > Probably the whole test file is useless. > I moved some tests to "gh-4230-del-impl-cast-str-to-num.test.lua" and removed this test-file. > See 6 comments below. > >> sql: remove implicit cast from string for comparison >> >> This patch removes implicit cast from strings to numbers for >> comparison. >> >> Closes #4230 >> >> @TarantoolBot document >> Title: remove implicit cast between strings and numbers >> >> This patch-set removes implicit cast from string to number and >> from number to string. >> >> Example: >> >> For comparison: >> >> tarantool> box.execute([[SELECT '1' > 0;]]) >> --- >> - null >> - 'Type mismatch: can not convert 1 to numeric' >> ... >> >> tarantool> box.execute([[SELECT "id" FROM "_space" WHERE '1' > "id";]]) >> --- >> - null >> - 'Type mismatch: can not convert text to unsigned' >> ... >> >> For assignment: >> >> tarantool> box.execute([[CREATE TABLE t1(i INT PRIMARY KEY);]]) >> tarantool> box.execute([[INSERT INTO t1 VALUES ('1');]]) >> --- >> - null >> - 'Type mismatch: can not convert text to integer' >> ... >> >> tarantool> box.execute([[CREATE TABLE t2(t text PRIMARY KEY);]]) >> tarantool> box.execute([[INSERT INTO t2 VALUES (1);]]) >> --- >> - null >> - 'Type mismatch: can not convert unsigned to string' >> ... > > 1. I suggest to wrap code samples into ``` to produce more > readable github markdown in the resulting doc issue. > Fixed. >> review fix > > 2. Hm. > Fixed. >> diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c >> index 6d8768865..1d7c76670 100644 >> --- a/src/box/sql/wherecode.c >> +++ b/src/box/sql/wherecode.c >> @@ -335,72 +335,6 @@ disableTerm(WhereLevel * pLevel, WhereTerm * pTerm) >> } >> } >> >> -/** >> - * Code an OP_ApplyType opcode to apply the column type string >> - * @types to the n registers starting at @base. >> - * >> - * As an optimization, SCALAR entries (which are no-ops) at the >> - * beginning and end of @types are ignored. If all entries in >> - * @types are SCALAR, then no code gets generated. >> - * >> - * This routine makes its own copy of @types so that the caller is >> - * free to modify @types after this routine returns. >> - */ >> -static void >> -emit_apply_type(Parse *pParse, int base, int n, enum field_type *types) >> -{ >> - Vdbe *v = pParse->pVdbe; >> - if (types == NULL) { >> - assert(pParse->db->mallocFailed); >> - return; >> - } >> - assert(v != 0); >> - >> - /* >> - * Adjust base and n to skip over SCALAR entries at the >> - * beginning and end of the type sequence. >> - */ >> - while (n > 0 && types[0] == FIELD_TYPE_SCALAR) { >> - n--; >> - base++; >> - types++; >> - } >> - while (n > 1 && types[n - 1] == FIELD_TYPE_SCALAR) { >> - n--; >> - } >> - >> - if (n > 0) { >> - enum field_type *types_dup = field_type_sequence_dup(pParse, >> - types, n); > > 3. This function is now unused and can be deleted. > Removed. >> - sqlVdbeAddOp4(v, OP_ApplyType, base, n, 0, >> - (char *) types_dup, P4_DYNAMIC); >> - sql_expr_type_cache_change(pParse, base, n); >> - } >> -} >> - >> -/** >> - * Expression @rhs, which is the RHS of a comparison operation, is >> - * either a vector of n elements or, if n==1, a scalar expression. >> - * Before the comparison operation, types @types are to be applied >> - * to the @rhs values. This function modifies entries within the >> - * field sequence to SCALAR if either: >> - * >> - * * the comparison will be performed with no type, or >> - * * the type change in @types is guaranteed not to change the value. >> - */ >> -static void >> -expr_cmp_update_rhs_type(struct Expr *rhs, int n, enum field_type *types) >> -{ >> - for (int i = 0; i < n; i++) { >> - Expr *p = sqlVectorFieldSubexpr(rhs, i); >> - enum field_type expr_type = sql_expr_type(p); >> - if (sql_type_result(expr_type, types[i]) == FIELD_TYPE_SCALAR || >> - sql_expr_needs_no_type_change(p, types[i])) { > > 4. Ditto (removed in the next commit, up to you if want to remove > here). > Removed here. >> - types[i] = FIELD_TYPE_SCALAR; >> - } >> - } >> -} >> diff --git a/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua >> new file mode 100755 >> index 000000000..ef4127e0e >> --- /dev/null >> +++ b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua >> @@ -0,0 +1,78 @@ >> +#!/usr/bin/env tarantool >> +test = require("sqltester") >> +test:plan(8) >> + >> +-- >> +-- Make sure that there is no implicit cast between string and >> +-- number. >> +-- >> +test:do_catchsql_test( >> + "gh-4230-1", >> + [[ >> + SELECT '1' > 0; >> + ]], { >> + 1, "Type mismatch: can not convert 1 to numeric" >> + }) >> + >> +test:do_catchsql_test( >> + "gh-4230-2", >> + [[ >> + SELECT 0 > '1'; >> + ]], { >> + 1, "Type mismatch: can not convert 1 to numeric" > > 5. The error messages are really weird. We should probably wrap > string value into quoutes. Or print it as 'text'. Without a > value. Not necessarily here. You can create an issue for that, > and do it later. > Created a new commit. >> diff --git a/test/sql-tap/tkt-f973c7ac31.test.lua b/test/sql-tap/tkt-f973c7ac31.test.lua >> index 82bdb52f8..604a7e6bb 100755 >> --- a/test/sql-tap/tkt-f973c7ac31.test.lua >> +++ b/test/sql-tap/tkt-f973c7ac31.test.lua >> @@ -39,9 +39,8 @@ for tn, sql in ipairs(sqls) do >> test:do_execsql_test( >> "tkt-f973c7ac3-1."..tn..".1", >> [[ >> - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 DESC >> + SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='2' ORDER BY c2 DESC > > 6. On the branch I see trailing whitespaces in this file. > Fixed. >> ]], { >> - >> }) >> Diff: diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c index 8ec685cd9..f51549695 100644 --- a/src/box/sql/expr.c +++ b/src/box/sql/expr.c @@ -137,19 +137,6 @@ sql_expr_type(struct Expr *pExpr) return pExpr->type; } -enum field_type * -field_type_sequence_dup(struct Parse *parse, enum field_type *types, - uint32_t len) -{ - uint32_t sz = (len + 1) * sizeof(enum field_type); - enum field_type *ret_types = sqlDbMallocRaw(parse->db, sz); - if (ret_types == NULL) - return NULL; - memcpy(ret_types, types, sz); - ret_types[len] = field_type_MAX; - return ret_types; -} - /* * Set the collating sequence for expression pExpr to be the collating * sequence named by pToken. Return a pointer to a new Expr node that @@ -2246,36 +2233,6 @@ sqlExprCanBeNull(const Expr * p) } } -bool -sql_expr_needs_no_type_change(const struct Expr *p, enum field_type type) -{ - u8 op; - if (type == FIELD_TYPE_SCALAR) - return true; - while (p->op == TK_UPLUS || p->op == TK_UMINUS) { - p = p->pLeft; - } - op = p->op; - if (op == TK_REGISTER) - op = p->op2; - switch (op) { - case TK_INTEGER: - return type == FIELD_TYPE_INTEGER; - case TK_FLOAT: - return type == FIELD_TYPE_DOUBLE; - case TK_STRING: - return type == FIELD_TYPE_STRING; - case TK_BLOB: - return type == FIELD_TYPE_VARBINARY; - case TK_COLUMN: - /* p cannot be part of a CHECK constraint. */ - assert(p->iTable >= 0); - return p->iColumn < 0 && sql_type_is_numeric(type); - default: - return false; - } -} - /* * pX is the RHS of an IN operator. If pX is a SELECT statement * that can be simplified to a direct table access, then return diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h index 01c32d518..b76c5eab8 100644 --- a/src/box/sql/sqlInt.h +++ b/src/box/sql/sqlInt.h @@ -3229,19 +3229,6 @@ int sqlExprIsTableConstant(Expr *, int); int sqlExprIsInteger(Expr *, int *); int sqlExprCanBeNull(const Expr *); -/** - * Return TRUE if the given expression is a constant which would - * be unchanged by OP_ApplyType with the type given in the second - * argument. - * - * This routine is used to determine if the OP_ApplyType operation - * can be omitted. When in doubt return FALSE. A false negative - * is harmless. A false positive, however, can result in the wrong - * answer. - */ -bool -sql_expr_needs_no_type_change(const struct Expr *expr, enum field_type type); - /** * This routine generates VDBE code that causes a single row of a * single table to be deleted. Both the original table entry and @@ -3907,14 +3894,6 @@ expr_cmp_mutual_type(struct Expr *pExpr); enum field_type sql_expr_type(struct Expr *pExpr); -/** - * This function duplicates first @len entries of types array - * and terminates new array with field_type_MAX member. - */ -enum field_type * -field_type_sequence_dup(struct Parse *parse, enum field_type *types, - uint32_t len); - /** * Convert z to a 64-bit signed or unsigned integer. * z must be decimal. This routine does *not* accept diff --git a/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua index ef4127e0e..b405a11b6 100755 --- a/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua +++ b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(8) +test:plan(32) -- -- Make sure that there is no implicit cast between string and @@ -75,4 +75,207 @@ test:do_catchsql_test( 1, "Type mismatch: can not convert 2 to numeric" }) +test:execsql([[ + CREATE TABLE t1(x TEXT primary key); + INSERT INTO t1 VALUES('1'); + CREATE TABLE t2(x INTEGER primary key); + INSERT INTO t2 VALUES(1); + CREATE TABLE t3(x DOUBLE primary key); + INSERT INTO t3 VALUES(1.0); +]]) + +test:do_catchsql_test( + "gh-4230-9", + [[ + SELECT x FROM t1 WHERE x IN (1); + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + + +test:do_catchsql_test( + "gh-4230-10", + [[ + SELECT x FROM t1 WHERE x IN (1.0); + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + +test:do_execsql_test( + "gh-4230-11", + [[ + SELECT x FROM t1 WHERE x IN ('1'); + ]], { + "1" + }) + +test:do_execsql_test( + "gh-4230-12", + [[ + SELECT x FROM t1 WHERE x IN ('1.0'); + ]], { + }) + +test:do_catchsql_test( + "gh-4230-13", + [[ + SELECT x FROM t1 WHERE 1 IN (x); + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-14", + [[ + SELECT x FROM t1 WHERE 1.0 IN (x); + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + +test:do_execsql_test( + "gh-4230-15", + [[ + SELECT x FROM t1 WHERE '1' IN (x); + ]], { + -- <2.7> + "1" + -- + }) + +test:do_execsql_test( + "gh-4230-16", + [[ + SELECT x FROM t1 WHERE '1.0' IN (x); + ]], { + }) + +test:do_execsql_test( + "gh-4230-17", + [[ + SELECT x FROM t2 WHERE x IN (1); + ]], { + 1 + }) + + +test:do_execsql_test( + "gh-4230-18", + [[ + SELECT x FROM t2 WHERE x IN (1.0); + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-4230-19", + [[ + SELECT x FROM t2 WHERE x IN ('1'); + ]], { + 1, "Type mismatch: can not convert integer to text" + }) + +test:do_catchsql_test( + "gh-4230-20", + [[ + SELECT x FROM t2 WHERE x IN ('1.0'); + ]], { + 1, "Type mismatch: can not convert integer to text" + }) + +test:do_execsql_test( + "gh-4230-21", + [[ + SELECT x FROM t2 WHERE 1 IN (x); + ]], { + 1 + }) + +test:do_execsql_test( + "gh-4230-22", + [[ + SELECT x FROM t2 WHERE 1.0 IN (x); + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-4230-23", + [[ + SELECT x FROM t2 WHERE '1' IN (x); + ]], { + 1, "Type mismatch: can not convert integer to text" + }) + +test:do_catchsql_test( + "gh-4230-24", + [[ + SELECT x FROM t2 WHERE '1.0' IN (x); + ]], { + 1, "Type mismatch: can not convert integer to text" + }) + +test:do_execsql_test( + "gh-4230-25", + [[ + SELECT x FROM t3 WHERE x IN (1); + ]], { + 1 + }) + +test:do_execsql_test( + "gh-4230-26", + [[ + SELECT x FROM t3 WHERE x IN (1.0); + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-4230-27", + [[ + SELECT x FROM t3 WHERE x IN ('1'); + ]], { + 1, "Type mismatch: can not convert double to text" + }) + +test:do_catchsql_test( + "gh-4230-28", + [[ + SELECT x FROM t3 WHERE x IN ('1.0'); + ]], { + 1, "Type mismatch: can not convert double to text" + }) + +test:do_execsql_test( + "gh-4230-29", + [[ + SELECT x FROM t3 WHERE 1 IN (x); + ]], { + 1 + }) + +test:do_execsql_test( + "gh-4230-30", + [[ + SELECT x FROM t3 WHERE 1.0 IN (x); + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-4230-31", + [[ + SELECT x FROM t3 WHERE '1' IN (x); + ]], { + 1, "Type mismatch: can not convert double to text" + }) + +test:do_catchsql_test( + "gh-4230-32", + [[ + SELECT x FROM t3 WHERE '1.0' IN (x); + ]], { + 1, "Type mismatch: can not convert double to text" + }) + test:finish_test() diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua deleted file mode 100755 index 083a87b45..000000000 --- a/test/sql-tap/tkt-9a8b09f8e6.test.lua +++ /dev/null @@ -1,506 +0,0 @@ -#!/usr/bin/env tarantool -test = require("sqltester") -test:plan(47) - ---!./tcltestrunner.lua --- 2014 June 26 --- --- The author disclaims copyright to this source code. In place of --- a legal notice, here is a blessing: --- --- May you do good and not evil. --- May you find forgiveness for yourself and forgive others. --- May you share freely, never taking more than you give. --- -------------------------------------------------------------------------- --- This file implements regression tests for sql library. --- --- This file implements tests to verify that ticket [9a8b09f8e6] has been --- fixed. --- --- ["set","testdir",[["file","dirname",["argv0"]]]] --- ["source",[["testdir"],"\/tester.tcl"]] -testprefix = "tkt-9a8b09f8e6" --- MUST_WORK_TEST -if (0 > 0) - then -end -test:do_execsql_test( - 1.1, - [[ - CREATE TABLE t1(x TEXT primary key); - INSERT INTO t1 VALUES('1'); - ]], { - -- <1.1> - - -- - }) - -test:do_execsql_test( - 1.2, - [[ - CREATE TABLE t2(x INTEGER primary key); - INSERT INTO t2 VALUES(1); - ]], { - -- <1.2> - - -- - }) - -test:do_execsql_test( - 1.3, - [[ - CREATE TABLE t3(x NUMBER primary key); - INSERT INTO t3 VALUES(1.0); - ]], { - -- <1.3> - - -- - }) - -test:do_execsql_test( - 1.4, - [[ - CREATE TABLE t4(x NUMBER primary key); - INSERT INTO t4 VALUES(1.11); - ]], { - -- <1.4> - - -- - }) - -test:do_execsql_test( - 1.5, - [[ - CREATE TABLE t5(id INT primary key, x INT , y TEXT); - INSERT INTO t5 VALUES(1, 1, 'one'); - INSERT INTO t5 VALUES(2, 1, 'two'); - INSERT INTO t5 VALUES(3, 1.0, 'three'); - INSERT INTO t5 VALUES(4, 1.0, 'four'); - ]], { - -- <1.5> - - -- - }) - -test:do_execsql_test( - 2.1, - [[ - SELECT x FROM t1 WHERE CAST(x AS INTEGER) IN (1); - ]], { - -- <2.1> - "1" - -- - }) - -test:do_execsql_test( - 2.2, - [[ - SELECT x FROM t1 WHERE CAST(x AS DOUBLE) IN (1); - ]], { - -- <2.2> - "1" - -- - }) - -test:do_execsql_test( - 2.3, - [[ - SELECT x FROM t1 WHERE x IN ('1'); - ]], { - -- <2.3> - "1" - -- - }) - -test:do_execsql_test( - 2.4, - [[ - SELECT x FROM t1 WHERE x IN ('1.0'); - ]], { - -- <2.4> - - -- - }) - -test:do_execsql_test( - 2.5, - [[ - SELECT x FROM t1 WHERE CAST(1 AS STRING) IN (x); - ]], { - -- <2.5> - "1" - -- - }) - -test:do_execsql_test( - 2.6, - [[ - SELECT x FROM t1 WHERE CAST(1.0 AS STRING) IN (x); - ]], { - -- <2.6> - -- - }) - -test:do_execsql_test( - 2.7, - [[ - SELECT x FROM t1 WHERE '1' IN (x); - ]], { - -- <2.7> - "1" - -- - }) - -test:do_execsql_test( - 2.8, - [[ - SELECT x FROM t1 WHERE '1.0' IN (x); - ]], { - -- <2.8> - - -- - }) - -test:do_execsql_test( - 3.1, - [[ - SELECT x FROM t2 WHERE x IN (1); - ]], { - -- <3.1> - 1 - -- - }) - -test:do_execsql_test( - 3.2, - [[ - SELECT x FROM t2 WHERE x IN (1.0); - ]], { - -- <3.2> - 1 - -- - }) - -test:do_execsql_test( - 3.3, - [[ - SELECT x FROM t2 WHERE CAST(x AS STRING) IN ('1'); - ]], { - -- <3.3> - 1 - -- - }) - -test:do_execsql_test( - 3.5, - [[ - SELECT x FROM t2 WHERE 1 IN (x); - ]], { - -- <3.5> - 1 - -- - }) - -test:do_execsql_test( - 3.6, - [[ - SELECT x FROM t2 WHERE 1.0 IN (x); - ]], { - -- <3.6> - 1 - -- - }) - -test:do_execsql_test( - 3.7, - [[ - SELECT x FROM t2 WHERE CAST('1' AS INTEGER) IN (x); - ]], { - -- <3.7> - 1 - -- - }) - -test:do_execsql_test( - 4.1, - [[ - SELECT x FROM t3 WHERE x IN (1); - ]], { - -- <4.1> - 1.0 - -- - }) - -test:do_execsql_test( - 4.2, - [[ - SELECT x FROM t3 WHERE x IN (1.0); - ]], { - -- <4.2> - 1.0 - -- - }) - -test:do_execsql_test( - 4.3, - [[ - SELECT x FROM t3 WHERE CAST(x AS STRING) IN ('1'); - ]], { - -- <4.3> - -- - }) - -test:do_execsql_test( - 4.4, - [[ - SELECT x FROM t3 WHERE CAST(x AS STRING) IN ('1.0'); - ]], { - -- <4.4> - 1.0 - -- - }) - -test:do_execsql_test( - 4.5, - [[ - SELECT x FROM t3 WHERE 1 IN (x); - ]], { - -- <4.5> - 1.0 - -- - }) - -test:do_execsql_test( - 4.6, - [[ - SELECT x FROM t3 WHERE 1.0 IN (x); - ]], { - -- <4.6> - 1.0 - -- - }) - -test:do_execsql_test( - 4.7, - [[ - SELECT x FROM t3 WHERE CAST('1' AS DOUBLE) IN (x); - ]], { - -- <4.7> - 1.0 - -- - }) - -test:do_execsql_test( - 4.8, - [[ - SELECT x FROM t3 WHERE CAST('1.0' AS DOUBLE) IN (x); - ]], { - -- <4.8> - 1.0 - -- - }) - -test:do_execsql_test( - 5.1, - [[ - SELECT x FROM t4 WHERE x IN (1); - ]], { - -- <5.1> - - -- - }) - -test:do_execsql_test( - 5.2, - [[ - SELECT x FROM t4 WHERE x IN (1.0); - ]], { - -- <5.2> - - -- - }) - -test:do_execsql_test( - 5.3, - [[ - SELECT x FROM t4 WHERE CAST(x AS STRING) IN ('1'); - ]], { - -- <5.3> - - -- - }) - -test:do_execsql_test( - 5.4, - [[ - SELECT x FROM t4 WHERE CAST(x AS STRING) IN ('1.0'); - ]], { - -- <5.4> - - -- - }) - -test:do_execsql_test( - 5.5, - [[ - SELECT x FROM t4 WHERE x IN (1.11); - ]], { - -- <5.5> - 1.11 - -- - }) - -test:do_execsql_test( - 5.6, - [[ - SELECT x FROM t4 WHERE CAST(x AS STRING) IN ('1.11'); - ]], { - -- <5.6> - 1.11 - -- - }) - -test:do_execsql_test( - 5.7, - [[ - SELECT x FROM t4 WHERE 1 IN (x); - ]], { - -- <5.7> - - -- - }) - -test:do_execsql_test( - 5.8, - [[ - SELECT x FROM t4 WHERE 1.0 IN (x); - ]], { - -- <5.8> - - -- - }) - -test:do_execsql_test( - 5.9, - [[ - SELECT x FROM t4 WHERE CAST('1' AS DOUBLE) IN (x); - ]], { - -- <5.9> - - -- - }) - -test:do_execsql_test( - 5.10, - [[ - SELECT x FROM t4 WHERE CAST('1.0' AS DOUBLE) IN (x); - ]], { - -- <5.10> - - -- - }) - -test:do_execsql_test( - 5.11, - [[ - SELECT x FROM t4 WHERE 1.11 IN (x); - ]], { - -- <5.11> - 1.11 - -- - }) - -test:do_execsql_test( - 5.12, - [[ - SELECT x FROM t4 WHERE CAST('1.11' AS DOUBLE) IN (x); - ]], { - -- <5.12> - 1.11 - -- - }) - -test:do_execsql_test( - 6.1, - [[ - SELECT x, y FROM t5 WHERE x IN (1); - ]], { - -- <6.1> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.2, - [[ - SELECT x, y FROM t5 WHERE x IN (1.0); - ]], { - -- <6.2> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.3, - [[ - SELECT x, y FROM t5 WHERE CAST(x AS STRING) IN ('1'); - ]], { - -- <6.3> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.4, - [[ - SELECT x, y FROM t5 WHERE CAST(x AS STRING) IN ('1.0'); - ]], { - -- <6.4> - - -- - }) - -test:do_execsql_test( - 6.5, - [[ - SELECT x, y FROM t5 WHERE 1 IN (x); - ]], { - -- <6.5> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.6, - [[ - SELECT x, y FROM t5 WHERE 1.0 IN (x); - ]], { - -- <6.6> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.7, - [[ - SELECT x, y FROM t5 WHERE CAST('1' AS INTEGER) IN (x); - ]], { - -- <6.7> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.8, - [[ - SELECT x, y FROM t5 WHERE CAST('1.0' AS DOUBLE) IN (x); - ]], { - -- <6.8> - 1, "one", 1, "two", 1, "three", 1, "four" - -- - }) - - - -test:finish_test() diff --git a/test/sql-tap/tkt-f973c7ac31.test.lua b/test/sql-tap/tkt-f973c7ac31.test.lua index 604a7e6bb..9be5d3c53 100755 --- a/test/sql-tap/tkt-f973c7ac31.test.lua +++ b/test/sql-tap/tkt-f973c7ac31.test.lua @@ -39,7 +39,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".1", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='2' ORDER BY c2 DESC + SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='2' ORDER BY c2 DESC ]], { }) @@ -54,7 +54,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".3", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='5' ORDER BY c2 DESC + SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='5' ORDER BY c2 DESC ]], { 5, 5, 5, 4 }) @@ -62,7 +62,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".4", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND c2<=5 ORDER BY c2 DESC + SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND c2<=5 ORDER BY c2 DESC ]], { 5, 5, 5, 4 }) @@ -70,7 +70,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".5", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND CAST(c2 AS STRING)<='5' ORDER BY c2 DESC + SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND CAST(c2 AS STRING)<='5' ORDER BY c2 DESC ]], { 5, 5, 5, 4 }) @@ -78,7 +78,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".6", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='2' ORDER BY c2 ASC + SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='2' ORDER BY c2 ASC ]], { }) @@ -93,7 +93,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".8", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='5' ORDER BY c2 ASC + SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='5' ORDER BY c2 ASC ]], { 5, 4, 5, 5 }) New patch: >From c53921e221266ed1cea89e2c1491b3bd16e33860 Mon Sep 17 00:00:00 2001 From: Mergen Imeev Date: Thu, 16 Apr 2020 12:52:59 +0300 Subject: [PATCH] sql: remove implicit cast from string for comparison This patch removes implicit cast from strings to numbers for comparison. Closes #4230 @TarantoolBot document Title: remove implicit cast between strings and numbers This patch-set removes implicit cast from string to number and from number to string. Example: For comparison: ``` tarantool> box.execute([[SELECT '1' > 0;]]) --- - null - 'Type mismatch: can not convert 1 to numeric' ... tarantool> box.execute([[SELECT "id" FROM "_space" WHERE '1' > "id";]]) --- - null - 'Type mismatch: can not convert text to unsigned' ... ``` For assignment: ``` tarantool> box.execute([[CREATE TABLE t1(i INT PRIMARY KEY);]]) tarantool> box.execute([[INSERT INTO t1 VALUES ('1');]]) --- - null - 'Type mismatch: can not convert text to integer' ... tarantool> box.execute([[CREATE TABLE t2(t text PRIMARY KEY);]]) tarantool> box.execute([[INSERT INTO t2 VALUES (1);]]) --- - null - 'Type mismatch: can not convert unsigned to string' ... ``` diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c index 8ec685cd9..f51549695 100644 --- a/src/box/sql/expr.c +++ b/src/box/sql/expr.c @@ -137,19 +137,6 @@ sql_expr_type(struct Expr *pExpr) return pExpr->type; } -enum field_type * -field_type_sequence_dup(struct Parse *parse, enum field_type *types, - uint32_t len) -{ - uint32_t sz = (len + 1) * sizeof(enum field_type); - enum field_type *ret_types = sqlDbMallocRaw(parse->db, sz); - if (ret_types == NULL) - return NULL; - memcpy(ret_types, types, sz); - ret_types[len] = field_type_MAX; - return ret_types; -} - /* * Set the collating sequence for expression pExpr to be the collating * sequence named by pToken. Return a pointer to a new Expr node that @@ -2246,36 +2233,6 @@ sqlExprCanBeNull(const Expr * p) } } -bool -sql_expr_needs_no_type_change(const struct Expr *p, enum field_type type) -{ - u8 op; - if (type == FIELD_TYPE_SCALAR) - return true; - while (p->op == TK_UPLUS || p->op == TK_UMINUS) { - p = p->pLeft; - } - op = p->op; - if (op == TK_REGISTER) - op = p->op2; - switch (op) { - case TK_INTEGER: - return type == FIELD_TYPE_INTEGER; - case TK_FLOAT: - return type == FIELD_TYPE_DOUBLE; - case TK_STRING: - return type == FIELD_TYPE_STRING; - case TK_BLOB: - return type == FIELD_TYPE_VARBINARY; - case TK_COLUMN: - /* p cannot be part of a CHECK constraint. */ - assert(p->iTable >= 0); - return p->iColumn < 0 && sql_type_is_numeric(type); - default: - return false; - } -} - /* * pX is the RHS of an IN operator. If pX is a SELECT statement * that can be simplified to a direct table access, then return diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c index 253304ba3..7b9cda504 100644 --- a/src/box/sql/insert.c +++ b/src/box/sql/insert.c @@ -41,20 +41,6 @@ #include "box/box.h" #include "box/schema.h" -enum field_type * -sql_index_type_str(struct sql *db, const struct index_def *idx_def) -{ - uint32_t column_count = idx_def->key_def->part_count; - uint32_t sz = (column_count + 1) * sizeof(enum field_type); - enum field_type *types = (enum field_type *) sqlDbMallocRaw(db, sz); - if (types == NULL) - return NULL; - for (uint32_t i = 0; i < column_count; i++) - types[i] = idx_def->key_def->parts[i].type; - types[column_count] = field_type_MAX; - return types; -} - void sql_emit_table_types(struct Vdbe *v, struct space_def *def, int reg) { diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h index 37283e506..b76c5eab8 100644 --- a/src/box/sql/sqlInt.h +++ b/src/box/sql/sqlInt.h @@ -3229,19 +3229,6 @@ int sqlExprIsTableConstant(Expr *, int); int sqlExprIsInteger(Expr *, int *); int sqlExprCanBeNull(const Expr *); -/** - * Return TRUE if the given expression is a constant which would - * be unchanged by OP_ApplyType with the type given in the second - * argument. - * - * This routine is used to determine if the OP_ApplyType operation - * can be omitted. When in doubt return FALSE. A false negative - * is harmless. A false positive, however, can result in the wrong - * answer. - */ -bool -sql_expr_needs_no_type_change(const struct Expr *expr, enum field_type type); - /** * This routine generates VDBE code that causes a single row of a * single table to be deleted. Both the original table entry and @@ -3866,10 +3853,6 @@ int sqlVarintLen(u64 v); #define getVarint sqlGetVarint #define putVarint sqlPutVarint -/** Return string consisting of fields types of given index. */ -enum field_type * -sql_index_type_str(struct sql *db, const struct index_def *idx_def); - /** * Code an OP_ApplyType opcode that will force types * for given range of register starting from @reg. @@ -3911,14 +3894,6 @@ expr_cmp_mutual_type(struct Expr *pExpr); enum field_type sql_expr_type(struct Expr *pExpr); -/** - * This function duplicates first @len entries of types array - * and terminates new array with field_type_MAX member. - */ -enum field_type * -field_type_sequence_dup(struct Parse *parse, enum field_type *types, - uint32_t len); - /** * Convert z to a 64-bit signed or unsigned integer. * z must be decimal. This routine does *not* accept diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index 77b758833..a92c9f60f 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -2260,8 +2260,6 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */ pIn3 = &aMem[pOp->p3]; flags1 = pIn1->flags; flags3 = pIn3->flags; - enum field_type ft_p1 = pIn1->field_type; - enum field_type ft_p3 = pIn3->field_type; if ((flags1 | flags3)&MEM_Null) { /* One or both operands are NULL */ if (pOp->p5 & SQL_NULLEQ) { @@ -2320,22 +2318,17 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */ } else { enum field_type type = pOp->p5 & FIELD_TYPE_MASK; if (sql_type_is_numeric(type)) { - if ((flags1 | flags3)&MEM_Str) { - if ((flags1 & MEM_Str) == MEM_Str) { - mem_apply_numeric_type(pIn1); - testcase( flags3!=pIn3->flags); /* Possible if pIn1==pIn3 */ - flags3 = pIn3->flags; - } - if ((flags3 & MEM_Str) == MEM_Str) { - if (mem_apply_numeric_type(pIn3) != 0) { - diag_set(ClientError, - ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(pIn3), - "numeric"); - goto abort_due_to_error; - } - - } + if ((flags1 & MEM_Str) == MEM_Str) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(pIn1), + "numeric"); + goto abort_due_to_error; + } + if ((flags3 & MEM_Str) == MEM_Str) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(pIn3), + "numeric"); + goto abort_due_to_error; } /* Handle the common case of integer comparison here, as an * optimization, to avoid a call to sqlMemCompare() @@ -2368,22 +2361,17 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */ goto compare_op; } } else if (type == FIELD_TYPE_STRING) { - if ((flags1 & MEM_Str) == 0 && - (flags1 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) { - testcase( pIn1->flags & MEM_Int); - testcase( pIn1->flags & MEM_Real); - sqlVdbeMemStringify(pIn1); - testcase( (flags1&MEM_Dyn) != (pIn1->flags&MEM_Dyn)); - flags1 = (pIn1->flags & ~MEM_TypeMask) | (flags1 & MEM_TypeMask); - assert(pIn1!=pIn3); + if ((flags1 & MEM_Str) == 0) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + mem_type_to_str(pIn3), + mem_type_to_str(pIn1)); + goto abort_due_to_error; } - if ((flags3 & MEM_Str) == 0 && - (flags3 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) { - testcase( pIn3->flags & MEM_Int); - testcase( pIn3->flags & MEM_Real); - sqlVdbeMemStringify(pIn3); - testcase( (flags3&MEM_Dyn) != (pIn3->flags&MEM_Dyn)); - flags3 = (pIn3->flags & ~MEM_TypeMask) | (flags3 & MEM_TypeMask); + if ((flags3 & MEM_Str) == 0) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + mem_type_to_str(pIn1), + mem_type_to_str(pIn3)); + goto abort_due_to_error; } } assert(pOp->p4type==P4_COLLSEQ || pOp->p4.pColl==0); @@ -2399,14 +2387,6 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */ default: res2 = res>=0; break; } - /* Undo any changes made by mem_apply_type() to the input registers. */ - assert((pIn1->flags & MEM_Dyn) == (flags1 & MEM_Dyn)); - pIn1->flags = flags1; - pIn1->field_type = ft_p1; - assert((pIn3->flags & MEM_Dyn) == (flags3 & MEM_Dyn)); - pIn3->flags = flags3; - pIn3->field_type = ft_p3; - if (pOp->p5 & SQL_STOREP2) { iCompare = res; res2 = res2!=0; /* For this path res2 must be exactly 0 or 1 */ @@ -3496,8 +3476,6 @@ case OP_SeekGT: { /* jump, in3 */ pIn3 = &aMem[int_field]; if ((pIn3->flags & MEM_Null) != 0) goto skip_truncate; - if ((pIn3->flags & MEM_Str) != 0) - mem_apply_numeric_type(pIn3); int64_t i; if ((pIn3->flags & MEM_Int) == MEM_Int) { i = pIn3->u.i; @@ -3590,6 +3568,26 @@ skip_truncate: assert(oc!=OP_SeekLT || r.default_rc==+1); r.aMem = &aMem[pOp->p3]; + for (int i = 0; i < r.nField; ++i) { + enum field_type type = r.key_def->parts[i].type; + struct Mem *mem = &r.aMem[i]; + if ((mem->flags & MEM_Str) != 0 && sql_type_is_numeric(type)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + field_type_strs[type], mem_type_to_str(mem)); + goto abort_due_to_error; + } + if (mem_check_types(mem, type) == 0) + continue; + if ((mem->flags & MEM_Real) != 0 && + (type == FIELD_TYPE_INTEGER || + type == FIELD_TYPE_UNSIGNED)) { + res = 1; + goto seek_not_found; + } + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + field_type_strs[type], mem_type_to_str(mem)); + goto abort_due_to_error; + } #ifdef SQL_DEBUG { int i; for(i=0; ip3]; + for (int i = 0; i < r.nField; ++i) { + struct Mem *mem = &r.aMem[i]; + enum mp_type mp_type = sql_value_type(mem); + enum field_type field_type = r.key_def->parts[i].type; + if (field_type == FIELD_TYPE_SCALAR || + mem->field_type == FIELD_TYPE_SCALAR) + continue; + bool is_nullable = r.key_def->parts[i].nullable_action == + ON_CONFLICT_ACTION_NONE; + if (field_mp_plain_type_is_compatible(field_type, mp_type, + is_nullable)) + continue; + if (!sql_type_is_numeric(field_type) || + !(mp_type == MP_INT || mp_type == MP_UINT || + mp_type == MP_DOUBLE || mp_type == MP_FLOAT)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + mem_type_to_str(mem), + field_type_strs[field_type]); + goto abort_due_to_error; + } + } #ifdef SQL_DEBUG { int i; for(i=0; ipVdbe; - if (types == NULL) { - assert(pParse->db->mallocFailed); - return; - } - assert(v != 0); - - /* - * Adjust base and n to skip over SCALAR entries at the - * beginning and end of the type sequence. - */ - while (n > 0 && types[0] == FIELD_TYPE_SCALAR) { - n--; - base++; - types++; - } - while (n > 1 && types[n - 1] == FIELD_TYPE_SCALAR) { - n--; - } - - if (n > 0) { - enum field_type *types_dup = field_type_sequence_dup(pParse, - types, n); - sqlVdbeAddOp4(v, OP_ApplyType, base, n, 0, - (char *) types_dup, P4_DYNAMIC); - sql_expr_type_cache_change(pParse, base, n); - } -} - -/** - * Expression @rhs, which is the RHS of a comparison operation, is - * either a vector of n elements or, if n==1, a scalar expression. - * Before the comparison operation, types @types are to be applied - * to the @rhs values. This function modifies entries within the - * field sequence to SCALAR if either: - * - * * the comparison will be performed with no type, or - * * the type change in @types is guaranteed not to change the value. - */ -static void -expr_cmp_update_rhs_type(struct Expr *rhs, int n, enum field_type *types) -{ - for (int i = 0; i < n; i++) { - Expr *p = sqlVectorFieldSubexpr(rhs, i); - enum field_type expr_type = sql_expr_type(p); - if (sql_type_result(expr_type, types[i]) == FIELD_TYPE_SCALAR || - sql_expr_needs_no_type_change(p, types[i])) { - types[i] = FIELD_TYPE_SCALAR; - } - } -} - /* * Generate code for a single equality term of the WHERE clause. An equality * term can be either X=expr or X IN (...). pTerm is the term to be @@ -644,8 +578,7 @@ static int codeAllEqualityTerms(Parse * pParse, /* Parsing context */ WhereLevel * pLevel, /* Which nested loop of the FROM we are coding */ int bRev, /* Reverse the order of IN operators */ - int nExtraReg, /* Number of extra registers to allocate */ - enum field_type **res_type) + int nExtraReg) /* Number of extra registers to allocate */ { u16 nEq; /* The number of == or IN constraints to code */ u16 nSkip; /* Number of left-most columns to skip */ @@ -669,9 +602,6 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */ nReg = pLoop->nEq + nExtraReg; pParse->nMem += nReg; - enum field_type *type = sql_index_type_str(pParse->db, idx_def); - assert(type != NULL || pParse->db->mallocFailed); - if (nSkip) { int iIdxCur = pLevel->iIdxCur; sqlVdbeAddOp1(v, (bRev ? OP_Last : OP_Rewind), iIdxCur); @@ -714,17 +644,7 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */ sqlVdbeAddOp2(v, OP_SCopy, r1, regBase + j); } } - if (pTerm->eOperator & WO_IN) { - if (pTerm->pExpr->flags & EP_xIsSelect) { - /* No type ever needs to be (or should be) applied to a value - * from the RHS of an "? IN (SELECT ...)" expression. The - * sqlFindInIndex() routine has already ensured that the - * type of the comparison has been applied to the value. - */ - if (type != NULL) - type[j] = FIELD_TYPE_SCALAR; - } - } else if ((pTerm->eOperator & WO_ISNULL) == 0) { + if ((pTerm->eOperator & (WO_IN | WO_ISNULL)) == 0) { Expr *pRight = pTerm->pExpr->pRight; if (sqlExprCanBeNull(pRight)) { sqlVdbeAddOp2(v, OP_IsNull, regBase + j, @@ -733,7 +653,6 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */ } } } - *res_type = type; return regBase; } @@ -904,10 +823,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W int iIdxCur; /* The VDBE cursor for the index */ int nExtraReg = 0; /* Number of extra registers needed */ int op; /* Instruction opcode */ - /* Types for start of range constraint. */ - enum field_type *start_types; - /* Types for end of range constraint */ - enum field_type *end_types = NULL; u8 bSeekPastNull = 0; /* True to seek past initial nulls */ u8 bStopAtNull = 0; /* Add condition to terminate at NULLs */ int force_integer_reg = -1; /* If non-negative: number of @@ -994,17 +909,7 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W * and store the values of those terms in an array of registers * starting at regBase. */ - regBase = - codeAllEqualityTerms(pParse, pLevel, bRev, nExtraReg, - &start_types); - if (start_types != NULL && nTop) { - uint32_t len = 0; - for (enum field_type *tmp = &start_types[nEq]; - *tmp != field_type_MAX; tmp++, len++); - uint32_t sz = len * sizeof(enum field_type); - end_types = sqlDbMallocRaw(db, sz); - memcpy(end_types, &start_types[nEq], sz); - } + regBase = codeAllEqualityTerms(pParse, pLevel, bRev, nExtraReg); addrNxt = pLevel->addrNxt; testcase(pRangeStart && (pRangeStart->eOperator & WO_LE) != 0); @@ -1029,10 +934,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W VdbeCoverage(v); } - if (start_types) { - expr_cmp_update_rhs_type(pRight, nBtm, - &start_types[nEq]); - } nConstraint += nBtm; testcase(pRangeStart->wtFlags & TERM_VIRTUAL); if (sqlExprIsVector(pRight) == 0) { @@ -1049,94 +950,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W } struct index_def *idx_pk = space->index[0]->def; uint32_t pk_part_count = idx_pk->key_def->part_count; - /* - * Tarantool's iterator over integer fields doesn't - * tolerate floating point values. Hence, if term - * is equality comparison and value of operand is - * not integer, we can skip it since it always - * results in false: INT a == 0.5 -> false; - * It is done using OP_MustBeInt facilities. - * In case term is greater comparison (a > ?), we - * should notify OP_SeekGT to process truncation of - * floating point value: a > 0.5 -> a >= 1; - * It is done by setting P5 flag for OP_Seek*. - * It is worth mentioning that we do not need - * this step when it comes for less (<) comparison - * of nullable field. Key is NULL in this case: - * values are ordered as NULL, ... NULL, min_value, - * so to fetch min value we pass NULL to GT iterator. - * The only exception is less comparison in - * conjunction with ORDER BY DESC clause: - * in such situation we use LE iterator and - * truncated value to compare. But then - * pRangeStart == NULL. - * This procedure is correct for compound index: - * only one comparison of less/greater type can be - * used at the same time. For instance, - * a < 1.5 AND b > 0.5 is handled by SeekGT using - * column a and fetching column b from tuple and - * OP_Le comparison. - * - * Note that OP_ApplyType, which is emitted before - * OP_Seek** doesn't truncate floating point to - * integer. That's why we need this routine. - * Also, note that terms are separated by OR - * predicates, so we consider term as sequence - * of AND'ed predicates. - */ - size_t addrs_sz; - int *seek_addrs = region_alloc_array(&pParse->region, - typeof(seek_addrs[0]), nEq, - &addrs_sz); - if (seek_addrs == NULL) { - diag_set(OutOfMemory, addrs_sz, "region_alloc_array", - "seek_addrs"); - pParse->is_aborted = true; - return 0; - } - memset(seek_addrs, 0, addrs_sz); - for (int i = 0; i < nEq; i++) { - enum field_type type = idx_def->key_def->parts[i].type; - if (type == FIELD_TYPE_INTEGER || - type == FIELD_TYPE_UNSIGNED) { - /* - * OP_MustBeInt consider NULLs as - * non-integer values, so firstly - * check whether value is NULL or not. - */ - seek_addrs[i] = sqlVdbeAddOp1(v, OP_IsNull, - regBase); - sqlVdbeAddOp2(v, OP_MustBeInt, regBase + i, - addrNxt); - start_types[i] = FIELD_TYPE_SCALAR; - /* - * We need to notify column cache - * that type of value may change - * so we should fetch value from - * tuple again rather then copy - * from register. - */ - sql_expr_type_cache_change(pParse, regBase + i, - 1); - } - } - /* Inequality constraint comes always at the end of list. */ - part_count = idx_def->key_def->part_count; - if (pRangeStart != NULL) { - /* - * nEq == 0 means that filter condition - * contains only inequality. - */ - uint32_t ineq_idx = nEq == 0 ? 0 : nEq - 1; - assert(ineq_idx < part_count); - enum field_type ineq_type = - idx_def->key_def->parts[ineq_idx].type; - if (ineq_type == FIELD_TYPE_INTEGER || - ineq_type == FIELD_TYPE_UNSIGNED) - force_integer_reg = regBase + nEq; - } - emit_apply_type(pParse, regBase, nConstraint - bSeekPastNull, - start_types); if (pLoop->nSkip > 0 && nConstraint == pLoop->nSkip) { /* The skip-scan logic inside the call to codeAllEqualityConstraints() * above has already left the cursor sitting on the correct row, @@ -1146,10 +959,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W op = aStartOp[(start_constraints << 2) + (startEq << 1) + bRev]; assert(op != 0); - for (uint32_t i = 0; i < nEq; ++i) { - if (seek_addrs[i] != 0) - sqlVdbeJumpHere(v, seek_addrs[i]); - } sqlVdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint); /* If this is Seek* opcode, and IPK is detected in the @@ -1189,13 +998,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W addrNxt); VdbeCoverage(v); } - if (end_types) { - expr_cmp_update_rhs_type(pRight, nTop, end_types); - emit_apply_type(pParse, regBase + nEq, nTop, - end_types); - } else { - assert(pParse->db->mallocFailed); - } nConstraint += nTop; testcase(pRangeEnd->wtFlags & TERM_VIRTUAL); @@ -1209,8 +1011,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W endEq = 0; nConstraint++; } - sqlDbFree(db, start_types); - sqlDbFree(db, end_types); /* Top of the loop body */ pLevel->p2 = sqlVdbeCurrentAddr(v); diff --git a/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua new file mode 100755 index 000000000..b405a11b6 --- /dev/null +++ b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua @@ -0,0 +1,281 @@ +#!/usr/bin/env tarantool +test = require("sqltester") +test:plan(32) + +-- +-- Make sure that there is no implicit cast between string and +-- number. +-- +test:do_catchsql_test( + "gh-4230-1", + [[ + SELECT '1' > 0; + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-2", + [[ + SELECT 0 > '1'; + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + +test:execsql([[ + CREATE TABLE t (i INT PRIMARY KEY, d DOUBLE, n NUMBER, s STRING); + INSERT INTO t VALUES (1, 1.0, 1, '2'), (2, 2.0, 2.0, '2'); + ]]) + +test:do_catchsql_test( + "gh-4230-3", + [[ + SELECT * from t where i > s; + ]], { + 1, "Type mismatch: can not convert 2 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-4", + [[ + SELECT * from t WHERE s > i; + ]], { + 1, "Type mismatch: can not convert 2 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-5", + [[ + SELECT * from t WHERE d > s; + ]], { + 1, "Type mismatch: can not convert 2 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-6", + [[ + SELECT * from t WHERE s > d; + ]], { + 1, "Type mismatch: can not convert 2 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-7", + [[ + SELECT * from t WHERE i = 1 and n > s; + ]], { + 1, "Type mismatch: can not convert 2 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-8", + [[ + SELECT * from t WHERE i = 2 and s > n; + ]], { + 1, "Type mismatch: can not convert 2 to numeric" + }) + +test:execsql([[ + CREATE TABLE t1(x TEXT primary key); + INSERT INTO t1 VALUES('1'); + CREATE TABLE t2(x INTEGER primary key); + INSERT INTO t2 VALUES(1); + CREATE TABLE t3(x DOUBLE primary key); + INSERT INTO t3 VALUES(1.0); +]]) + +test:do_catchsql_test( + "gh-4230-9", + [[ + SELECT x FROM t1 WHERE x IN (1); + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + + +test:do_catchsql_test( + "gh-4230-10", + [[ + SELECT x FROM t1 WHERE x IN (1.0); + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + +test:do_execsql_test( + "gh-4230-11", + [[ + SELECT x FROM t1 WHERE x IN ('1'); + ]], { + "1" + }) + +test:do_execsql_test( + "gh-4230-12", + [[ + SELECT x FROM t1 WHERE x IN ('1.0'); + ]], { + }) + +test:do_catchsql_test( + "gh-4230-13", + [[ + SELECT x FROM t1 WHERE 1 IN (x); + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-14", + [[ + SELECT x FROM t1 WHERE 1.0 IN (x); + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + +test:do_execsql_test( + "gh-4230-15", + [[ + SELECT x FROM t1 WHERE '1' IN (x); + ]], { + -- <2.7> + "1" + -- + }) + +test:do_execsql_test( + "gh-4230-16", + [[ + SELECT x FROM t1 WHERE '1.0' IN (x); + ]], { + }) + +test:do_execsql_test( + "gh-4230-17", + [[ + SELECT x FROM t2 WHERE x IN (1); + ]], { + 1 + }) + + +test:do_execsql_test( + "gh-4230-18", + [[ + SELECT x FROM t2 WHERE x IN (1.0); + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-4230-19", + [[ + SELECT x FROM t2 WHERE x IN ('1'); + ]], { + 1, "Type mismatch: can not convert integer to text" + }) + +test:do_catchsql_test( + "gh-4230-20", + [[ + SELECT x FROM t2 WHERE x IN ('1.0'); + ]], { + 1, "Type mismatch: can not convert integer to text" + }) + +test:do_execsql_test( + "gh-4230-21", + [[ + SELECT x FROM t2 WHERE 1 IN (x); + ]], { + 1 + }) + +test:do_execsql_test( + "gh-4230-22", + [[ + SELECT x FROM t2 WHERE 1.0 IN (x); + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-4230-23", + [[ + SELECT x FROM t2 WHERE '1' IN (x); + ]], { + 1, "Type mismatch: can not convert integer to text" + }) + +test:do_catchsql_test( + "gh-4230-24", + [[ + SELECT x FROM t2 WHERE '1.0' IN (x); + ]], { + 1, "Type mismatch: can not convert integer to text" + }) + +test:do_execsql_test( + "gh-4230-25", + [[ + SELECT x FROM t3 WHERE x IN (1); + ]], { + 1 + }) + +test:do_execsql_test( + "gh-4230-26", + [[ + SELECT x FROM t3 WHERE x IN (1.0); + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-4230-27", + [[ + SELECT x FROM t3 WHERE x IN ('1'); + ]], { + 1, "Type mismatch: can not convert double to text" + }) + +test:do_catchsql_test( + "gh-4230-28", + [[ + SELECT x FROM t3 WHERE x IN ('1.0'); + ]], { + 1, "Type mismatch: can not convert double to text" + }) + +test:do_execsql_test( + "gh-4230-29", + [[ + SELECT x FROM t3 WHERE 1 IN (x); + ]], { + 1 + }) + +test:do_execsql_test( + "gh-4230-30", + [[ + SELECT x FROM t3 WHERE 1.0 IN (x); + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-4230-31", + [[ + SELECT x FROM t3 WHERE '1' IN (x); + ]], { + 1, "Type mismatch: can not convert double to text" + }) + +test:do_catchsql_test( + "gh-4230-32", + [[ + SELECT x FROM t3 WHERE '1.0' IN (x); + ]], { + 1, "Type mismatch: can not convert double to text" + }) + +test:finish_test() diff --git a/test/sql-tap/identifier_case.test.lua b/test/sql-tap/identifier_case.test.lua index 2a00626fc..1d56ffb44 100755 --- a/test/sql-tap/identifier_case.test.lua +++ b/test/sql-tap/identifier_case.test.lua @@ -242,11 +242,11 @@ data = { { 2, [[ 'a' < 'b' collate "binary" ]], {0, {true}}}, { 3, [[ 'a' < 'b' collate 'binary' ]], {1, [[Syntax error at line 1 near ''binary'']]}}, { 4, [[ 'a' < 'b' collate "unicode" ]], {0, {true}}}, - { 5, [[ 5 < 'b' collate "unicode" ]], {0, {true}}}, + { 5, [[ 5 < 'b' collate "unicode" ]], {1, "Type mismatch: can not convert b to numeric"}}, { 6, [[ 5 < 'b' collate unicode ]], {1,"Collation 'UNICODE' does not exist"}}, - { 7, [[ 5 < 'b' collate "unicode_ci" ]], {0, {true}}}, + { 7, [[ 5 < 'b' collate "unicode_ci" ]], {1, "Type mismatch: can not convert b to numeric"}}, { 8, [[ 5 < 'b' collate NONE ]], {1, "Collation 'NONE' does not exist"}}, - { 9, [[ 5 < 'b' collate "none" ]], {0, {true}}}, + { 9, [[ 5 < 'b' collate "none" ]], {1, "Type mismatch: can not convert b to numeric"}}, } for _, row in ipairs(data) do diff --git a/test/sql-tap/in1.test.lua b/test/sql-tap/in1.test.lua index 570cc1779..e2f498889 100755 --- a/test/sql-tap/in1.test.lua +++ b/test/sql-tap/in1.test.lua @@ -637,12 +637,12 @@ test:do_test( "in-11.2", function() -- The '2' should be coerced into 2 because t6.b is NUMERIC - return test:execsql [[ + return test:catchsql [[ SELECT * FROM t6 WHERE b IN ('2'); ]] end, { -- - 1, 2 + 1, "Type mismatch: can not convert 2 to numeric" -- }) diff --git a/test/sql-tap/in4.test.lua b/test/sql-tap/in4.test.lua index 33947d0ab..a494e846f 100755 --- a/test/sql-tap/in4.test.lua +++ b/test/sql-tap/in4.test.lua @@ -147,12 +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') ]], { -- + 1, "Type mismatch: can not convert integer to text" -- }) diff --git a/test/sql-tap/insert3.test.lua b/test/sql-tap/insert3.test.lua index b92bc508e..3276f0db2 100755 --- a/test/sql-tap/insert3.test.lua +++ b/test/sql-tap/insert3.test.lua @@ -59,7 +59,7 @@ test:do_execsql_test( [[ CREATE TABLE log2(rowid INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT UNIQUE,y INT ); CREATE TRIGGER r2 BEFORE INSERT ON t1 FOR EACH ROW BEGIN - UPDATE log2 SET y=y+1 WHERE x=new.b; + UPDATE log2 SET y=y+1 WHERE x=CAST(new.b AS STRING); INSERT OR IGNORE INTO log2(x, y) VALUES(CAST(new.b AS STRING),1); END; INSERT INTO t1(a, b) VALUES('hi', 453); diff --git a/test/sql-tap/intpkey.test.lua b/test/sql-tap/intpkey.test.lua index 0db18ba91..bc3d701a7 100755 --- a/test/sql-tap/intpkey.test.lua +++ b/test/sql-tap/intpkey.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(40) +test:plan(39) --!./tcltestrunner.lua -- 2001 September 15 @@ -854,43 +854,33 @@ test:do_execsql_test( -- }) -test:do_execsql_test( - "intpkey-14.3", - [[ - SELECT * FROM t3 WHERE a>'2'; - ]], { - -- - 3, 3, "3" - -- - }) - -test:do_execsql_test( +test:do_catchsql_test( "intpkey-14.4", [[ SELECT * FROM t3 WHERE a<'2'; ]], { -- - 1, 1, "one" + 1, "Type mismatch: can not convert text to integer" -- }) -test:do_execsql_test( +test:do_catchsql_test( "intpkey-14.5", [[ SELECT * FROM t3 WHERE a - 1, 1, "one" + 1, "Type mismatch: can not convert one to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "intpkey-14.6", [[ SELECT * FROM t3 WHERE a=c; ]], { -- - 2, 2, "2", 3, 3, "3" + 1, "Type mismatch: can not convert one to numeric" -- }) diff --git a/test/sql-tap/join.test.lua b/test/sql-tap/join.test.lua index 51e0ecb79..792302ab5 100755 --- a/test/sql-tap/join.test.lua +++ b/test/sql-tap/join.test.lua @@ -1028,22 +1028,23 @@ test:do_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "join-11.9", [[ SELECT * FROM t1 NATURAL JOIN t2 ]], { -- + 1, "Type mismatch: can not convert integer to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( "join-11.10", [[ SELECT * FROM t2 NATURAL JOIN t1 ]], { -- - 1, "one", 2, "two" + 1, "Type mismatch: can not convert 1 to numeric" -- }) diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua index e0fe50bbe..3cef617f4 100755 --- a/test/sql-tap/misc1.test.lua +++ b/test/sql-tap/misc1.test.lua @@ -88,7 +88,7 @@ test:do_execsql_test( test:do_execsql_test( "misc1-1.4", [[ - SELECT x75 FROM manycol WHERE x50=350 + SELECT x75 FROM manycol WHERE x50='350' ]], { -- "375" @@ -98,7 +98,7 @@ test:do_execsql_test( test:do_execsql_test( "misc1-1.5", [[ - SELECT x50 FROM manycol WHERE x99=599 + SELECT x50 FROM manycol WHERE x99='599' ]], { -- "550" @@ -109,7 +109,7 @@ test:do_test( "misc1-1.6", function() test:execsql("CREATE INDEX manycol_idx1 ON manycol(x99)") - return test:execsql("SELECT x50 FROM manycol WHERE x99=899") + return test:execsql("SELECT x50 FROM manycol WHERE x99='899'") end, { -- "850" @@ -129,7 +129,7 @@ test:do_execsql_test( test:do_test( "misc1-1.8", function() - test:execsql("DELETE FROM manycol WHERE x98=1234") + test:execsql("DELETE FROM manycol WHERE x98='1234'") return test:execsql("SELECT count(*) FROM manycol") end, { -- @@ -140,7 +140,7 @@ test:do_test( test:do_test( "misc1-1.9", function() - test:execsql("DELETE FROM manycol WHERE x98=998") + test:execsql("DELETE FROM manycol WHERE x98='998'") return test:execsql("SELECT count(*) FROM manycol") end, { -- @@ -151,7 +151,7 @@ test:do_test( test:do_test( "misc1-1.10", function() - test:execsql("DELETE FROM manycol WHERE x99=500") + test:execsql("DELETE FROM manycol WHERE x99='500'") return test:execsql("SELECT count(*) FROM manycol") end, { -- @@ -162,7 +162,7 @@ test:do_test( test:do_test( "misc1-1.11", function() - test:execsql("DELETE FROM manycol WHERE x99=599") + test:execsql("DELETE FROM manycol WHERE x99='599'") return test:execsql("SELECT count(*) FROM manycol") end, { -- @@ -479,9 +479,9 @@ local where = "" test:do_test( "misc1-10.1", function() - where = "WHERE x0>=0" + where = "WHERE x0>='0'" for i = 1, 99, 1 do - where = where .. " AND x"..i.."<>0" + where = where .. " AND x"..i.."<>'0'" end return test:catchsql("SELECT count(*) FROM manycol "..where.."") end, { @@ -496,7 +496,7 @@ test:do_test( test:do_test( "misc1-10.3", function() - where = string.gsub(where,"x0>=0", "x0=0") + where = string.gsub(where,"x0>='0'", "x0='0'") return test:catchsql("DELETE FROM manycol "..where.."") end, { -- @@ -520,7 +520,7 @@ test:do_execsql_test( test:do_execsql_test( "misc1-10.6", [[ - SELECT x1 FROM manycol WHERE x0=100 + SELECT x1 FROM manycol WHERE x0='100' ]], { -- "101" @@ -530,7 +530,7 @@ test:do_execsql_test( test:do_test( "misc1-10.7", function() - where = string.gsub(where, "x0=0", "x0=100") + where = string.gsub(where, "x0='0'", "x0='100'") return test:catchsql("UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where.."") end, { -- @@ -541,7 +541,7 @@ test:do_test( test:do_execsql_test( "misc1-10.8", [[ - SELECT x1 FROM manycol WHERE x0=100 + SELECT x1 FROM manycol WHERE x0='100' ]], { -- "102" @@ -563,7 +563,7 @@ test:do_execsql_test( test:do_execsql_test( "misc1-10.10", [[ - SELECT x1 FROM manycol WHERE x0=100 + SELECT x1 FROM manycol WHERE x0='100' ]], { -- "103" @@ -619,13 +619,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "misc1-12.2", [[ SELECT '0'==0.0 ]], { -- - true + 1, "Type mismatch: can not convert 0 to numeric" -- }) diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua index 9a969bf3c..f5a9b63fe 100755 --- a/test/sql-tap/select1.test.lua +++ b/test/sql-tap/select1.test.lua @@ -1912,7 +1912,7 @@ test:do_execsql_test( test:do_execsql_test( "select1-12.7", [[ - SELECT * FROM t3 WHERE a=(SELECT 1); + SELECT * FROM t3 WHERE a=(SELECT '1'); ]], { -- 0, "1", "2" @@ -1922,7 +1922,7 @@ test:do_execsql_test( test:do_execsql_test( "select1-12.8", [[ - SELECT * FROM t3 WHERE a=(SELECT 2); + SELECT * FROM t3 WHERE a=(SELECT '2'); ]], { -- diff --git a/test/sql-tap/select7.test.lua b/test/sql-tap/select7.test.lua index e1e43c557..0d1390fd6 100755 --- a/test/sql-tap/select7.test.lua +++ b/test/sql-tap/select7.test.lua @@ -256,7 +256,7 @@ test:do_execsql_test( DROP TABLE IF EXISTS t5; CREATE TABLE t5(a TEXT primary key, b INT); INSERT INTO t5 VALUES('123', 456); - SELECT typeof(a), a FROM t5 GROUP BY a HAVING a "string", "123" diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua index e0771825e..bad702de9 100755 --- a/test/sql-tap/subquery.test.lua +++ b/test/sql-tap/subquery.test.lua @@ -284,13 +284,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "subquery-2.3.2", [[ SELECT a IN (10.0, 20) FROM t3; ]], { -- - false + 1, "Type mismatch: can not convert text to real" -- }) diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua deleted file mode 100755 index ac89c7df2..000000000 --- a/test/sql-tap/tkt-9a8b09f8e6.test.lua +++ /dev/null @@ -1,506 +0,0 @@ -#!/usr/bin/env tarantool -test = require("sqltester") -test:plan(47) - ---!./tcltestrunner.lua --- 2014 June 26 --- --- The author disclaims copyright to this source code. In place of --- a legal notice, here is a blessing: --- --- May you do good and not evil. --- May you find forgiveness for yourself and forgive others. --- May you share freely, never taking more than you give. --- -------------------------------------------------------------------------- --- This file implements regression tests for sql library. --- --- This file implements tests to verify that ticket [9a8b09f8e6] has been --- fixed. --- --- ["set","testdir",[["file","dirname",["argv0"]]]] --- ["source",[["testdir"],"\/tester.tcl"]] -testprefix = "tkt-9a8b09f8e6" --- MUST_WORK_TEST -if (0 > 0) - then -end -test:do_execsql_test( - 1.1, - [[ - CREATE TABLE t1(x TEXT primary key); - INSERT INTO t1 VALUES('1'); - ]], { - -- <1.1> - - -- - }) - -test:do_execsql_test( - 1.2, - [[ - CREATE TABLE t2(x INTEGER primary key); - INSERT INTO t2 VALUES(1); - ]], { - -- <1.2> - - -- - }) - -test:do_execsql_test( - 1.3, - [[ - CREATE TABLE t3(x NUMBER primary key); - INSERT INTO t3 VALUES(1.0); - ]], { - -- <1.3> - - -- - }) - -test:do_execsql_test( - 1.4, - [[ - CREATE TABLE t4(x NUMBER primary key); - INSERT INTO t4 VALUES(1.11); - ]], { - -- <1.4> - - -- - }) - -test:do_execsql_test( - 1.5, - [[ - CREATE TABLE t5(id INT primary key, x INT , y TEXT); - INSERT INTO t5 VALUES(1, 1, 'one'); - INSERT INTO t5 VALUES(2, 1, 'two'); - INSERT INTO t5 VALUES(3, 1.0, 'three'); - INSERT INTO t5 VALUES(4, 1.0, 'four'); - ]], { - -- <1.5> - - -- - }) - -test:do_execsql_test( - 2.1, - [[ - SELECT x FROM t1 WHERE x IN (1); - ]], { - -- <2.1> - "1" - -- - }) - -test:do_execsql_test( - 2.2, - [[ - SELECT x FROM t1 WHERE x IN (1.0); - ]], { - -- <2.2> - "1" - -- - }) - -test:do_execsql_test( - 2.3, - [[ - SELECT x FROM t1 WHERE x IN ('1'); - ]], { - -- <2.3> - "1" - -- - }) - -test:do_execsql_test( - 2.4, - [[ - SELECT x FROM t1 WHERE x IN ('1.0'); - ]], { - -- <2.4> - - -- - }) - -test:do_execsql_test( - 2.5, - [[ - SELECT x FROM t1 WHERE 1 IN (x); - ]], { - -- <2.5> - "1" - -- - }) - -test:do_execsql_test( - 2.6, - [[ - SELECT x FROM t1 WHERE 1.0 IN (x); - ]], { - -- <2.6> - "1" - -- - }) - -test:do_execsql_test( - 2.7, - [[ - SELECT x FROM t1 WHERE '1' IN (x); - ]], { - -- <2.7> - "1" - -- - }) - -test:do_execsql_test( - 2.8, - [[ - SELECT x FROM t1 WHERE '1.0' IN (x); - ]], { - -- <2.8> - - -- - }) - -test:do_execsql_test( - 3.1, - [[ - SELECT x FROM t2 WHERE x IN (1); - ]], { - -- <3.1> - 1 - -- - }) - -test:do_execsql_test( - 3.2, - [[ - SELECT x FROM t2 WHERE x IN (1.0); - ]], { - -- <3.2> - 1 - -- - }) - -test:do_execsql_test( - 3.3, - [[ - SELECT x FROM t2 WHERE x IN ('1'); - ]], { - -- <3.3> - -- - }) - -test:do_execsql_test( - 3.5, - [[ - SELECT x FROM t2 WHERE 1 IN (x); - ]], { - -- <3.5> - 1 - -- - }) - -test:do_execsql_test( - 3.6, - [[ - SELECT x FROM t2 WHERE 1.0 IN (x); - ]], { - -- <3.6> - 1 - -- - }) - -test:do_execsql_test( - 3.7, - [[ - SELECT x FROM t2 WHERE '1' IN (x); - ]], { - -- <3.7> - -- - }) - -test:do_execsql_test( - 4.1, - [[ - SELECT x FROM t3 WHERE x IN (1); - ]], { - -- <4.1> - 1.0 - -- - }) - -test:do_execsql_test( - 4.2, - [[ - SELECT x FROM t3 WHERE x IN (1.0); - ]], { - -- <4.2> - 1.0 - -- - }) - -test:do_execsql_test( - 4.3, - [[ - SELECT x FROM t3 WHERE x IN ('1'); - ]], { - -- <4.3> - 1.0 - -- - }) - -test:do_execsql_test( - 4.4, - [[ - SELECT x FROM t3 WHERE x IN ('1.0'); - ]], { - -- <4.4> - 1.0 - -- - }) - -test:do_execsql_test( - 4.5, - [[ - SELECT x FROM t3 WHERE 1 IN (x); - ]], { - -- <4.5> - 1.0 - -- - }) - -test:do_execsql_test( - 4.6, - [[ - SELECT x FROM t3 WHERE 1.0 IN (x); - ]], { - -- <4.6> - 1.0 - -- - }) - -test:do_execsql_test( - 4.7, - [[ - SELECT x FROM t3 WHERE '1' IN (x); - ]], { - -- <4.7> - 1 - -- - }) - -test:do_execsql_test( - 4.8, - [[ - SELECT x FROM t3 WHERE '1.0' IN (x); - ]], { - -- <4.8> - 1 - -- - }) - -test:do_execsql_test( - 5.1, - [[ - SELECT x FROM t4 WHERE x IN (1); - ]], { - -- <5.1> - - -- - }) - -test:do_execsql_test( - 5.2, - [[ - SELECT x FROM t4 WHERE x IN (1.0); - ]], { - -- <5.2> - - -- - }) - -test:do_execsql_test( - 5.3, - [[ - SELECT x FROM t4 WHERE x IN ('1'); - ]], { - -- <5.3> - - -- - }) - -test:do_execsql_test( - 5.4, - [[ - SELECT x FROM t4 WHERE x IN ('1.0'); - ]], { - -- <5.4> - - -- - }) - -test:do_execsql_test( - 5.5, - [[ - SELECT x FROM t4 WHERE x IN (1.11); - ]], { - -- <5.5> - 1.11 - -- - }) - -test:do_execsql_test( - 5.6, - [[ - SELECT x FROM t4 WHERE x IN ('1.11'); - ]], { - -- <5.6> - 1.11 - -- - }) - -test:do_execsql_test( - 5.7, - [[ - SELECT x FROM t4 WHERE 1 IN (x); - ]], { - -- <5.7> - - -- - }) - -test:do_execsql_test( - 5.8, - [[ - SELECT x FROM t4 WHERE 1.0 IN (x); - ]], { - -- <5.8> - - -- - }) - -test:do_execsql_test( - 5.9, - [[ - SELECT x FROM t4 WHERE '1' IN (x); - ]], { - -- <5.9> - - -- - }) - -test:do_execsql_test( - 5.10, - [[ - SELECT x FROM t4 WHERE '1.0' IN (x); - ]], { - -- <5.10> - - -- - }) - -test:do_execsql_test( - 5.11, - [[ - SELECT x FROM t4 WHERE 1.11 IN (x); - ]], { - -- <5.11> - 1.11 - -- - }) - -test:do_execsql_test( - 5.12, - [[ - SELECT x FROM t4 WHERE '1.11' IN (x); - ]], { - -- <5.12> - 1.11 - -- - }) - -test:do_execsql_test( - 6.1, - [[ - SELECT x, y FROM t5 WHERE x IN (1); - ]], { - -- <6.1> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.2, - [[ - SELECT x, y FROM t5 WHERE x IN (1.0); - ]], { - -- <6.2> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.3, - [[ - SELECT x, y FROM t5 WHERE x IN ('1'); - ]], { - -- <6.3> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.4, - [[ - SELECT x, y FROM t5 WHERE x IN ('1.0'); - ]], { - -- <6.4> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.5, - [[ - SELECT x, y FROM t5 WHERE 1 IN (x); - ]], { - -- <6.5> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.6, - [[ - SELECT x, y FROM t5 WHERE 1.0 IN (x); - ]], { - -- <6.6> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.7, - [[ - SELECT x, y FROM t5 WHERE '1' IN (x); - ]], { - -- <6.7> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.8, - [[ - SELECT x, y FROM t5 WHERE '1.0' IN (x); - ]], { - -- <6.8> - 1, "one", 1, "two", 1, "three", 1, "four" - -- - }) - - - -test:finish_test() diff --git a/test/sql-tap/tkt-f973c7ac31.test.lua b/test/sql-tap/tkt-f973c7ac31.test.lua index 82bdb52f8..9be5d3c53 100755 --- a/test/sql-tap/tkt-f973c7ac31.test.lua +++ b/test/sql-tap/tkt-f973c7ac31.test.lua @@ -39,9 +39,8 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".1", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 DESC + SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='2' ORDER BY c2 DESC ]], { - }) test:do_execsql_test( @@ -55,7 +54,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".3", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 DESC + SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='5' ORDER BY c2 DESC ]], { 5, 5, 5, 4 }) @@ -63,7 +62,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".4", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 DESC + SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND c2<=5 ORDER BY c2 DESC ]], { 5, 5, 5, 4 }) @@ -71,7 +70,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".5", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 DESC + SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND CAST(c2 AS STRING)<='5' ORDER BY c2 DESC ]], { 5, 5, 5, 4 }) @@ -79,9 +78,8 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".6", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 ASC + SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='2' ORDER BY c2 ASC ]], { - }) test:do_execsql_test( @@ -95,7 +93,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".8", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 ASC + SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='5' ORDER BY c2 ASC ]], { 5, 4, 5, 5 }) @@ -103,7 +101,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".9", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 ASC + SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND c2<=5 ORDER BY c2 ASC ]], { 5, 4, 5, 5 }) @@ -111,7 +109,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".10", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 ASC + SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND CAST(c2 AS STRING)<='5' ORDER BY c2 ASC ]], { 5, 4, 5, 5 }) diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua index de77e61e9..82ba828d0 100755 --- a/test/sql-tap/tkt3493.test.lua +++ b/test/sql-tap/tkt3493.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(26) +test:plan(25) --!./tcltestrunner.lua -- 2008 October 13 @@ -45,7 +45,7 @@ test:do_execsql_test( [[ SELECT CASE - WHEN B.val = 1 THEN 'XYZ' + WHEN B.val = '1' THEN 'XYZ' ELSE A.val END AS Col1 FROM B @@ -63,7 +63,7 @@ test:do_execsql_test( [[ SELECT DISTINCT CASE - WHEN B.val = 1 THEN 'XYZ' + WHEN B.val = '1' THEN 'XYZ' ELSE A.val END AS Col1 FROM B @@ -79,7 +79,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-1.4", [[ - SELECT b.val, CASE WHEN b.val = 1 THEN 'xyz' ELSE b.val END AS col1 FROM b; + SELECT b.val, CASE WHEN b.val = '1' THEN 'xyz' ELSE b.val END AS col1 FROM b; ]], { -- "1", "xyz", "2", "2" @@ -91,7 +91,7 @@ test:do_execsql_test( [[ SELECT DISTINCT b.val, - CASE WHEN b.val = 1 THEN 'xyz' ELSE b.val END AS col1 + CASE WHEN b.val = '1' THEN 'xyz' ELSE b.val END AS col1 FROM b; ]], { -- @@ -126,23 +126,13 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.2.1", [[ - SELECT a=123 FROM t1 GROUP BY a + SELECT a='123' FROM t1 GROUP BY a ]], { -- true -- }) -test:do_execsql_test( - "tkt3493-2.2.2", - [[ - SELECT a=123 FROM t1 - ]], { - -- - true - -- - }) - test:do_execsql_test( "tkt3493-2.2.3", [[ @@ -156,7 +146,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.2.4", [[ - SELECT count(*), a=123 FROM t1 + SELECT count(*), a='123' FROM t1 ]], { -- 1, true @@ -166,7 +156,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.2.5", [[ - SELECT count(*), +a=123 FROM t1 + SELECT count(*), +a='123' FROM t1 ]], { -- 1, true @@ -176,7 +166,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.3.3", [[ - SELECT b='456' FROM t1 GROUP BY a + SELECT b = 456 FROM t1 GROUP BY a ]], { -- true @@ -186,7 +176,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.3.1", [[ - SELECT b='456' FROM t1 GROUP BY b + SELECT b = 456 FROM t1 GROUP BY b ]], { -- true @@ -196,7 +186,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.3.2", [[ - SELECT b='456' FROM t1 + SELECT b = 456 FROM t1 ]], { -- true @@ -206,7 +196,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.4.1", [[ - SELECT typeof(a), a FROM t1 GROUP BY a HAVING a=123 + SELECT typeof(a), a FROM t1 GROUP BY a HAVING a='123' ]], { -- "string", "123" @@ -216,7 +206,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.4.2", [[ - SELECT typeof(a), a FROM t1 GROUP BY b HAVING a=123 + SELECT typeof(a), a FROM t1 GROUP BY b HAVING a='123' ]], { -- "string", "123" @@ -226,7 +216,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.5.1", [[ - SELECT typeof(b), b FROM t1 GROUP BY a HAVING b='456' + SELECT typeof(b), b FROM t1 GROUP BY a HAVING b=456 ]], { -- "integer", 456 @@ -236,7 +226,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.5.2", [[ - SELECT typeof(b), b FROM t1 GROUP BY b HAVING b='456' + SELECT typeof(b), b FROM t1 GROUP BY b HAVING b=456 ]], { -- "integer", 456 diff --git a/test/sql-tap/transitive1.test.lua b/test/sql-tap/transitive1.test.lua index 96895b4a7..cc7e066bf 100755 --- a/test/sql-tap/transitive1.test.lua +++ b/test/sql-tap/transitive1.test.lua @@ -63,7 +63,7 @@ test:do_execsql_test( INSERT INTO t2 VALUES(2, 20,20,'20'); INSERT INTO t2 VALUES(3, 3,3,'3'); - SELECT a,b,c FROM t2 WHERE a=b AND c=b AND c=20; + SELECT a,b,c FROM t2 WHERE a=b AND c=CAST(b AS STRING) AND c='20'; ]], { -- 20, 20, "20" @@ -73,7 +73,7 @@ test:do_execsql_test( test:do_execsql_test( "transitive1-210", [[ - SELECT a,b,c FROM t2 WHERE a=b AND c=b AND c>='20' ORDER BY +a; + SELECT a,b,c FROM t2 WHERE a=b AND c=CAST(b AS STRING) AND c>='20' ORDER BY +a; ]], { -- 3, 3, "3", 20, 20, "20" @@ -83,7 +83,7 @@ test:do_execsql_test( test:do_execsql_test( "transitive1-220", [[ - SELECT a,b,c FROM t2 WHERE a=b AND c=b AND c<='20' ORDER BY +a; + SELECT a,b,c FROM t2 WHERE a=b AND c=CAST(b AS STRING) AND c<='20' ORDER BY +a; ]], { -- 20, 20, "20", 100, 100, "100" @@ -402,7 +402,7 @@ test:do_execsql_test( [[ CREATE TABLE x(i INTEGER PRIMARY KEY, y TEXT); INSERT INTO x VALUES(10, '10'); - SELECT * FROM x WHERE x.y>='1' AND x.y<'2' AND x.i=x.y; + SELECT * FROM x WHERE x.y>='1' AND x.y<'2' AND CAST(x.i AS STRING)=x.y; ]], { -- 10, "10" @@ -430,7 +430,7 @@ test:do_execsql_test( [[ CREATE TABLE t3(i INTEGER PRIMARY KEY, t TEXT); INSERT INTO t3 VALUES(10, '10'); - SELECT * FROM t3 WHERE i=t AND t = '10 '; + SELECT * FROM t3 WHERE CAST(i AS STRING)=t AND t = '10 '; ]], { -- @@ -443,7 +443,7 @@ test:do_execsql_test( CREATE TABLE u1(x TEXT PRIMARY KEY, y INTEGER, z TEXT); CREATE INDEX i1 ON u1(x); INSERT INTO u1 VALUES('00013', 13, '013'); - SELECT * FROM u1 WHERE x=y AND y=z AND z='013'; + SELECT * FROM u1 WHERE CAST(x AS INTEGER)=y AND y=CAST(z AS INTEGER) AND z='013'; ]], { -- "00013",13,"013" diff --git a/test/sql-tap/where2.test.lua b/test/sql-tap/where2.test.lua index f267be8e6..7348a855a 100755 --- a/test/sql-tap/where2.test.lua +++ b/test/sql-tap/where2.test.lua @@ -4,7 +4,7 @@ yaml = require("yaml") fio = require("fio") ffi = require("ffi") -test:plan(74) +test:plan(62) ffi.cdef[[ int dup(int oldfd); @@ -622,181 +622,12 @@ test:do_test( -- }) --- if X(356, "X!cmd", [=[["expr","[permutation] != \"no_optimization\""]]=]) --- then - -- Ticket #2249. Make sure the OR optimization is not attempted if - -- comparisons between columns of different affinities are needed. - -- - test:do_test( - "where2-6.7", - function() - test:execsql [[ - CREATE TABLE t2249a(a TEXT PRIMARY KEY, x VARCHAR(100)); - CREATE TABLE t2249b(b INTEGER PRIMARY KEY); - INSERT INTO t2249a(a) VALUES('0123'); - INSERT INTO t2249b VALUES(123); - ]] - return queryplan([[ - -- Because a is type TEXT and b is type INTEGER, both a and b - -- will attempt to convert to NUMERIC before the comparison. - -- They will thus compare equal. - -- - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b; - ]]) - end, { - -- - 123, '0123', "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.9", - function() - return queryplan([[ - -- The + operator doesn't affect RHS. - -- - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b; - ]]) - end, { - -- - 123, "0123", "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.9.2", - function() - -- The same thing but with the expression flipped around. - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a - ]]) - end, { - -- - 123, "0123","nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.10", - function() - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +a=+b; - ]]) - end, { - -- - 123, "0123", "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.11", - function() - -- This will not attempt the OR optimization because of the a=b - -- comparison. - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello'; - ]]) - end, { - -- - 123, '0123', "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.11.2", - function() - -- Permutations of the expression terms. - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello'; - ]]) - end, { - -- - 123, '0123', "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.11.3", - function() - -- Permutations of the expression terms. - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a; - ]]) - end, { - -- - 123, '0123', "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.11.4", - function() - -- Permutations of the expression terms. - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a; - ]]) - end, { - -- - 123, '0123', "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - -- These tests are not run if subquery support is not included in the - -- build. This is because these tests test the "a = 1 OR a = 2" to - -- "a IN (1, 2)" optimisation transformation, which is not enabled if - -- subqueries and the IN operator is not available. - -- - test:do_test( - "where2-6.12", - function() - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello'; - ]]) - end, { - -- - 123, "0123", "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.12.2", - function() - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a; - ]]) - end, { - -- - 123, "0123", "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.12.3", - function() - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello'; - ]]) - end, { - -- - 123, "0123", "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.13", - function() - -- The addition of +a on the second term disabled the OR optimization. - -- But we should still get the same empty-set result as in where2-6.9. - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello'; - ]]) - end, { - -- - 123, "0123", "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - + test:execsql [[ + CREATE TABLE t2249a(a TEXT PRIMARY KEY, x VARCHAR(100)); + CREATE TABLE t2249b(b INTEGER PRIMARY KEY); + INSERT INTO t2249a(a) VALUES('0123'); + INSERT INTO t2249b VALUES(123); + ]] -- Variations on the order of terms in a WHERE clause in order -- to make sure the OR optimizer can recognize them all. diff --git a/test/sql-tap/where5.test.lua b/test/sql-tap/where5.test.lua index 3aefcaca5..a93ba7854 100755 --- a/test/sql-tap/where5.test.lua +++ b/test/sql-tap/where5.test.lua @@ -34,7 +34,7 @@ test:do_test("where5-1.0", function() INSERT INTO t3 SELECT CAST(x AS INTEGER) FROM t1; ]] return test:execsql [[ - SELECT * FROM t1 WHERE x<0 + SELECT * FROM t1 WHERE CAST(x AS INTEGER)<0 ]] end, { -- @@ -43,7 +43,7 @@ end, { }) test:do_execsql_test("where5-1.1", [[ - SELECT * FROM t1 WHERE x<=0 + SELECT * FROM t1 WHERE CAST(x AS INTEGER)<=0 ]], { -- '-1', '0' @@ -51,7 +51,7 @@ test:do_execsql_test("where5-1.1", [[ }) test:do_execsql_test("where5-1.2", [[ - SELECT * FROM t1 WHERE x=0 + SELECT * FROM t1 WHERE CAST(x AS INTEGER)=0 ]], { -- '0' @@ -59,7 +59,7 @@ test:do_execsql_test("where5-1.2", [[ }) test:do_execsql_test("where5-1.3", [[ - SELECT * FROM t1 WHERE x>=0 + SELECT * FROM t1 WHERE CAST(x AS INTEGER)>=0 ]], { -- '0', '1' @@ -67,7 +67,7 @@ test:do_execsql_test("where5-1.3", [[ }) test:do_execsql_test("where5-1.4", [[ - SELECT * FROM t1 WHERE x>0 + SELECT * FROM t1 WHERE CAST(x AS INTEGER)>0 ]], { -- '1' @@ -75,7 +75,7 @@ test:do_execsql_test("where5-1.4", [[ }) test:do_execsql_test("where5-1.5", [[ - SELECT * FROM t1 WHERE x<>0 + SELECT * FROM t1 WHERE CAST(x AS INTEGER)<>0 ]], { -- '-1', '1' diff --git a/test/sql/types.result b/test/sql/types.result index 6b0f7a651..9099bcea4 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -339,12 +339,12 @@ box.execute("INSERT INTO tboolean VALUES (TRUE);") box.execute("SELECT * FROM tboolean WHERE s1 = x'44';") --- - null -- 'Type mismatch: can not convert varbinary to boolean' +- 'Type mismatch: can not convert boolean to varbinary' ... box.execute("SELECT * FROM tboolean WHERE s1 = 'abc';") --- - null -- 'Type mismatch: can not convert abc to boolean' +- 'Type mismatch: can not convert boolean to text' ... box.execute("SELECT * FROM tboolean WHERE s1 = 1;") --- @@ -606,14 +606,6 @@ box.execute("SELECT 18446744073709551615.0 > 18446744073709551615") rows: - [true] ... -box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)") ---- -- metadata: - - name: 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0) - type: boolean - rows: - - [true] -... box.execute("SELECT 1 LIMIT 18446744073709551615;") --- - metadata: diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua index bd14b342d..f9603c60d 100644 --- a/test/sql/types.test.lua +++ b/test/sql/types.test.lua @@ -151,7 +151,6 @@ box.execute("SELECT 18446744073709551610 - 18446744073709551615;") box.execute("SELECT 18446744073709551615 = null;") box.execute("SELECT 18446744073709551615 = 18446744073709551615.0;") box.execute("SELECT 18446744073709551615.0 > 18446744073709551615") -box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)") box.execute("SELECT 1 LIMIT 18446744073709551615;") box.execute("SELECT 1 LIMIT 1 OFFSET 18446744073709551614;") box.execute("SELECT CAST('18446744073' || '709551616' AS INTEGER);")