From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp55.i.mail.ru (smtp55.i.mail.ru [217.69.128.35]) (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 32626469710 for ; Thu, 28 May 2020 17:17:39 +0300 (MSK) From: Mergen Imeev Date: Thu, 28 May 2020 17:17:37 +0300 Message-Id: <49b7078dd5f787e2dcde37554522afbe63f1d994.1590671266.git.imeevma@gmail.com> In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH 5/6] 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 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' ... --- src/box/sql/vdbe.c | 105 ++++---- src/box/sql/wherecode.c | 203 +-------------- .../gh-4230-del-impl-cast-str-to-num.test.lua | 78 ++++++ test/sql-tap/identifier_case.test.lua | 6 +- test/sql-tap/in1.test.lua | 4 +- test/sql-tap/in4.test.lua | 3 +- test/sql-tap/insert3.test.lua | 2 +- test/sql-tap/intpkey.test.lua | 24 +- test/sql-tap/join.test.lua | 7 +- test/sql-tap/misc1.test.lua | 32 +-- test/sql-tap/select1.test.lua | 4 +- test/sql-tap/select7.test.lua | 2 +- test/sql-tap/subquery.test.lua | 4 +- test/sql-tap/tkt-9a8b09f8e6.test.lua | 84 ++++--- test/sql-tap/tkt-f973c7ac31.test.lua | 32 +-- test/sql-tap/tkt-fc7bd6358f.test.lua | 6 +- test/sql-tap/tkt3493.test.lua | 40 ++- test/sql-tap/transitive1.test.lua | 12 +- test/sql-tap/where2.test.lua | 183 +------------- test/sql-tap/where5.test.lua | 12 +- test/sql-tap/whereB.test.lua | 238 ++++++++++-------- test/sql/types.result | 12 +- test/sql/types.test.lua | 1 - 23 files changed, 418 insertions(+), 676 deletions(-) create mode 100755 test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index 7add67ae7..021e09d1e 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -2237,8 +2237,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) { @@ -2297,22 +2295,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() @@ -2345,22 +2338,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_Int | MEM_UInt | MEM_Real)) != 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_Int | MEM_UInt | MEM_Real)) != 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); @@ -2376,14 +2364,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 */ @@ -3479,8 +3459,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; @@ -3573,6 +3551,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,91 +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 = sizeof(int) * nEq; - int *seek_addrs = region_alloc(&pParse->region, addrs_sz); - if (seek_addrs == NULL) { - diag_set(OutOfMemory, addrs_sz, "region", "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, @@ -1143,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 @@ -1186,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); @@ -1206,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..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" + }) + +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: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 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" -- }) -test:do_execsql_test( +test:do_catchsql_test( 2.2, [[ SELECT x FROM t1 WHERE x IN (1.0); ]], { -- <2.2> - "1" + 1,"Type mismatch: can not convert 1 to numeric" -- }) @@ -123,23 +123,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 2.5, [[ SELECT x FROM t1 WHERE 1 IN (x); ]], { -- <2.5> - "1" + 1,"Type mismatch: can not convert 1 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( 2.6, [[ SELECT x FROM t1 WHERE 1.0 IN (x); ]], { -- <2.6> - "1" + 1,"Type mismatch: can not convert 1 to numeric" -- }) @@ -183,21 +183,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 3.3, [[ SELECT x FROM t2 WHERE x IN ('1'); ]], { -- <3.3> + 1, "Type mismatch: can not convert integer to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( 3.4, [[ SELECT x FROM t2 WHERE x IN ('1'); ]], { -- <3.4> + 1, "Type mismatch: can not convert integer to text" -- }) @@ -221,21 +223,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 3.7, [[ SELECT x FROM t2 WHERE '1' IN (x); ]], { -- <3.7> + 1, "Type mismatch: can not convert integer to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( 3.8, [[ SELECT x FROM t2 WHERE '1' IN (x); ]], { -- <3.8> + 1, "Type mismatch: can not convert integer to text" -- }) @@ -259,23 +263,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 4.3, [[ SELECT x FROM t3 WHERE x IN ('1'); ]], { -- <4.3> - 1.0 + 1, "Type mismatch: can not convert number to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( 4.4, [[ SELECT x FROM t3 WHERE x IN ('1.0'); ]], { -- <4.4> - 1.0 + 1, "Type mismatch: can not convert number to text" -- }) @@ -299,23 +303,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 4.7, [[ SELECT x FROM t3 WHERE '1' IN (x); ]], { -- <4.7> - 1 + 1, "Type mismatch: can not convert number to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( 4.8, [[ SELECT x FROM t3 WHERE '1.0' IN (x); ]], { -- <4.8> - 1 + 1, "Type mismatch: can not convert number to text" -- }) @@ -339,23 +343,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.3, [[ SELECT x FROM t4 WHERE x IN ('1'); ]], { -- <5.3> - + 1, "Type mismatch: can not convert number to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.4, [[ SELECT x FROM t4 WHERE x IN ('1.0'); ]], { -- <5.4> - + 1, "Type mismatch: can not convert number to text" -- }) @@ -369,13 +373,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.6, [[ SELECT x FROM t4 WHERE x IN ('1.11'); ]], { -- <5.6> - 1.11 + 1, "Type mismatch: can not convert number to text" -- }) @@ -399,23 +403,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.9, [[ SELECT x FROM t4 WHERE '1' IN (x); ]], { -- <5.9> - + 1, "Type mismatch: can not convert number to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.10, [[ SELECT x FROM t4 WHERE '1.0' IN (x); ]], { -- <5.10> - + 1, "Type mismatch: can not convert number to text" -- }) @@ -429,13 +433,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.12, [[ SELECT x FROM t4 WHERE '1.11' IN (x); ]], { -- <5.12> - 1.11 + 1, "Type mismatch: can not convert number to text" -- }) @@ -459,23 +463,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 6.3, [[ SELECT x, y FROM t5 WHERE x IN ('1'); ]], { -- <6.3> - 1, "one", 1, "two", 1, "three", 1.0, "four" + 1, "Type mismatch: can not convert 1 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( 6.4, [[ SELECT x, y FROM t5 WHERE x IN ('1.0'); ]], { -- <6.4> - 1, "one", 1, "two", 1, "three", 1.0, "four" + 1, "Type mismatch: can not convert 1.0 to numeric" -- }) @@ -499,23 +503,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 6.7, [[ SELECT x, y FROM t5 WHERE '1' IN (x); ]], { -- <6.7> - 1, "one", 1, "two", 1, "three", 1.0, "four" + 1, "Type mismatch: can not convert 1 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( 6.8, [[ SELECT x, y FROM t5 WHERE '1.0' IN (x); ]], { -- <6.8> - 1, "one", 1, "two", 1, "three", 1, "four" + 1, "Type mismatch: can not convert 1.0 to numeric" -- }) diff --git a/test/sql-tap/tkt-f973c7ac31.test.lua b/test/sql-tap/tkt-f973c7ac31.test.lua index 82bdb52f8..5239a7785 100755 --- a/test/sql-tap/tkt-f973c7ac31.test.lua +++ b/test/sql-tap/tkt-f973c7ac31.test.lua @@ -36,12 +36,12 @@ local sqls = { for tn, sql in ipairs(sqls) do test:execsql(sql) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".1", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 DESC ]], { - + 1, "/Type mismatch: can not convert/" }) test:do_execsql_test( @@ -52,36 +52,36 @@ for tn, sql in ipairs(sqls) do 5, 5, 5, 4 }) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".3", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 DESC ]], { - 5, 5, 5, 4 + 1, "/Type mismatch: can not convert/" }) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".4", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 DESC ]], { - 5, 5, 5, 4 + 1, "/Type mismatch: can not convert/" }) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".5", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 DESC ]], { - 5, 5, 5, 4 + 1, "/Type mismatch: can not convert/" }) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".6", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 ASC ]], { - + 1, "/Type mismatch: can not convert/" }) test:do_execsql_test( @@ -92,28 +92,28 @@ for tn, sql in ipairs(sqls) do 5, 4, 5, 5 }) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".8", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 ASC ]], { - 5, 4, 5, 5 + 1, "/Type mismatch: can not convert/" }) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".9", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 ASC ]], { - 5, 4, 5, 5 + 1, "/Type mismatch: can not convert/" }) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".10", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 ASC ]], { - 5, 4, 5, 5 + 1, "/Type mismatch: can not convert/" }) end diff --git a/test/sql-tap/tkt-fc7bd6358f.test.lua b/test/sql-tap/tkt-fc7bd6358f.test.lua index f38ffa3d6..f77e4bea8 100755 --- a/test/sql-tap/tkt-fc7bd6358f.test.lua +++ b/test/sql-tap/tkt-fc7bd6358f.test.lua @@ -78,15 +78,17 @@ for a, from in ipairs(froms) do test:do_test( string.format("tkt-fc7bd6358f.110.%s.%s.1", a, b), function() - return test:execsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where)) + return test:catchsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where)) end, { + 1, "Type mismatch: can not convert integer to text" }) test:do_test( string.format("tkt-fc7bd6358f.110.%s.%s.2", a, b), function() - return test:execsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where)) + return test:catchsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where)) end, { + 1, "Type mismatch: can not convert integer to text" }) end diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua index de77e61e9..ec12a4492 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-tap/whereB.test.lua b/test/sql-tap/whereB.test.lua index 970ff1dec..9e99ea41c 100755 --- a/test/sql-tap/whereB.test.lua +++ b/test/sql-tap/whereB.test.lua @@ -27,8 +27,7 @@ test:plan(63) -- These values are not equal and because neither affinity is NUMERIC -- no type conversion occurs. -- -test:do_execsql_test( - "whereB-1.1", +test:execsql( [[ CREATE TABLE t1(x INT primary key,y INT ); -- affinity of t1.y is NONE INSERT INTO t1 VALUES(1,99); @@ -36,49 +35,54 @@ test:do_execsql_test( CREATE TABLE t2(a INT primary key, b TEXT); -- affinity of t2.b is TEXT CREATE INDEX t2b ON t2(b); INSERT INTO t2 VALUES(2,'99'); + ]] +) +test:do_catchsql_test( + "whereB-1.1", + [[ SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-1.2", [[ SELECT x, a, y=b FROM t1, t2 WHERE y=b; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-1.3", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-1.4", [[ SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-1.100", [[ DROP INDEX t2b ON t2; @@ -86,29 +90,29 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-1.101", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-1.102", [[ SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) @@ -304,8 +308,7 @@ test:do_execsql_test( -- Because t2.b has a numeric affinity, type conversion should occur -- and the two fields should be equal. -- -test:do_execsql_test( - "whereB-4.1", +test:execsql( [[ DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; @@ -316,38 +319,44 @@ test:do_execsql_test( CREATE TABLE t2(a INT primary key, b NUMBER); -- affinity of t2.b is NUMERIC CREATE INDEX t2b ON t2(b); INSERT INTO t2 VALUES(2,99); + ]] +) + +test:do_catchsql_test( + "whereB-4.1", + [[ SELECT x, a, y=b FROM t1, t2; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-4.2", [[ SELECT x, a, y=b FROM t1, t2 WHERE y=b; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert number to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-4.3", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert number to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-4.4", -- In this case the unary "+" operator shouldn't -- affect result set of query. @@ -356,11 +365,11 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-4.100", [[ DROP INDEX t2b ON t2; @@ -368,22 +377,22 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-4.101", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-4.102", -- In this case the unary "+" operator shouldn't -- affect result set of query. @@ -392,7 +401,7 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) @@ -404,8 +413,7 @@ test:do_execsql_test( -- Because t2.b has a numeric affinity, type conversion should occur -- and the two fields should be equal. -- -test:do_execsql_test( - "whereB-5.1", +test:execsql( [[ DROP TABLE t1; DROP TABLE t2; @@ -416,36 +424,43 @@ test:do_execsql_test( CREATE TABLE t2(a INT primary key, b INT); -- affinity of t2.b is INTEGER CREATE INDEX t2b ON t2(b); INSERT INTO t2 VALUES(2,99); + ]] +) +test:do_catchsql_test( + "whereB-5.1", + [[ SELECT x, a, y=b FROM t1, t2; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-5.2", [[ SELECT x, a, y=b FROM t1, t2 WHERE y=b; ]], { -- + 1, "Type mismatch: can not convert integer to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-5.3", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- + 1, "Type mismatch: can not convert integer to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-5.4", -- In this case the unary "+" operator shouldn't -- affect result set of query. @@ -454,11 +469,11 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-5.100", [[ DROP INDEX t2b ON t2; @@ -466,22 +481,22 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-5.101", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-5.102", -- In this case the unary "+" operator shouldn't -- affect result set of query. @@ -490,7 +505,7 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) @@ -502,8 +517,8 @@ test:do_execsql_test( -- Because t2.b has a numeric affinity, type conversion should occur -- and the two fields should be equal. -- -test:do_execsql_test( - "whereB-6.1", + +test:execsql( [[ DROP TABLE t1; DROP TABLE t2; @@ -514,38 +529,43 @@ test:do_execsql_test( CREATE TABLE t2(a INT primary key, b NUMBER); -- affinity of t2.b is REAL CREATE INDEX t2b ON t2(b); INSERT INTO t2 VALUES(2,99.0); + ]] +) +test:do_catchsql_test( + "whereB-6.1", + [[ SELECT x, a, y=b FROM t1, t2; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-6.2", [[ SELECT x, a, y=b FROM t1, t2 WHERE y=b; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert number to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-6.3", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert number to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-6.4", -- In this case the unary "+" operator shouldn't -- affect result set of query. @@ -554,11 +574,11 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-6.100", [[ DROP INDEX t2b ON t2; @@ -566,22 +586,22 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-6.101", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-6.102", -- In this case the unary "+" operator shouldn't -- affect result set of query. @@ -590,7 +610,7 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) @@ -602,8 +622,7 @@ test:do_execsql_test( -- Because t1.y has a numeric affinity, type conversion should occur -- and the two fields should be equal. -- -test:do_execsql_test( - "whereB-7.1", +test:execsql( [[ DROP TABLE t1; DROP TABLE t2; @@ -614,38 +633,43 @@ test:do_execsql_test( CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE CREATE INDEX t2b ON t2(b); INSERT INTO t2 VALUES(2,'99'); + ]] +) +test:do_catchsql_test( + "whereB-7.1", + [[ SELECT x, a, y=b FROM t1, t2; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-7.2", [[ SELECT x, a, y=b FROM t1, t2 WHERE y=b; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-7.3", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-7.4", -- In this case the unary "+" operator shouldn't -- affect result set of query. @@ -654,11 +678,11 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-7.100", [[ DROP INDEX t2b ON t2; @@ -666,22 +690,22 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-7.101", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-7.102", -- In this case the unary "+" operator shouldn't -- affect result set of query. @@ -690,7 +714,7 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) @@ -702,8 +726,7 @@ test:do_execsql_test( -- Because t1.y has a numeric affinity, type conversion should occur -- and the two fields should be equal. -- -test:do_execsql_test( - "whereB-8.1", +test:execsql( [[ DROP TABLE t1; DROP TABLE t2; @@ -714,38 +737,43 @@ test:do_execsql_test( CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE CREATE INDEX t2b ON t2(b); INSERT INTO t2 VALUES(2,'99'); + ]] +) +test:do_catchsql_test( + "whereB-8.1", + [[ SELECT x, a, y=b FROM t1, t2; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-8.2", [[ SELECT x, a, y=b FROM t1, t2 WHERE y=b; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-8.3", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-8.4", -- In this case the unary "+" operator shouldn't -- affect result set of query. @@ -754,11 +782,11 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-8.100", [[ DROP INDEX t2b ON t2; @@ -766,22 +794,22 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-8.101", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-8.102", -- In this case the unary "+" operator shouldn't -- affect result set of query. @@ -790,7 +818,7 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) @@ -802,8 +830,7 @@ test:do_execsql_test( -- Because t1.y has a numeric affinity, type conversion should occur -- and the two fields should be equal. -- -test:do_execsql_test( - "whereB-9.1", +test:execsql( [[ DROP TABLE t1; DROP TABLE t2; @@ -814,38 +841,43 @@ test:do_execsql_test( CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE CREATE INDEX t2b ON t2(b); INSERT INTO t2 VALUES(2,'99'); + ]] +) +test:do_catchsql_test( + "whereB-9.1", + [[ SELECT x, a, y=b FROM t1, t2; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-9.2", [[ SELECT x, a, y=b FROM t1, t2 WHERE y=b; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-9.3", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-9.4", -- In this case the unary "+" operator shouldn't -- affect result set of query. @@ -854,11 +886,11 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-9.100", [[ DROP INDEX t2b ON t2; @@ -866,22 +898,22 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-9.101", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-9.102", -- In this case the unary "+" operator shouldn't -- affect result set of query. @@ -890,7 +922,7 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert 99 to numeric" -- }) 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);") -- 2.25.1