From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp29.i.mail.ru (smtp29.i.mail.ru [94.100.177.89]) (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 AEC61469710 for ; Tue, 9 Jun 2020 14:51:32 +0300 (MSK) References: <49b7078dd5f787e2dcde37554522afbe63f1d994.1590671266.git.imeevma@gmail.com> <379debee-67fc-f870-9e0b-032a5188d8a4@tarantool.org> From: Mergen Imeev Message-ID: Date: Tue, 9 Jun 2020 14:51:26 +0300 MIME-Version: 1.0 In-Reply-To: <379debee-67fc-f870-9e0b-032a5188d8a4@tarantool.org> Content-Type: text/plain; charset="utf-8"; format="flowed" Content-Transfer-Encoding: 8bit Content-Language: en-US Subject: Re: [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: Vladislav Shpilevoy , tsafin@tarantool.org, tarantool-patches@dev.tarantool.org Thanks for the review! My answers and new patch below. On 01.06.2020 20:04, Vladislav Shpilevoy wrote: > Thanks for the patch! > > See 7 comments below. > > On 28/05/2020 16:17, Mergen Imeev wrote: >> 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 >> @@ -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) { > 1. Maybe it would be better to check '(flags1 & MEM_Str) == 0'. Otherwise > every time when we will add a new type, we will need to add it here. The > same below. Thanks, fixed. > >> + 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); >> diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c >> index 5bc27f134..1d7c76670 100644 >> --- a/src/box/sql/wherecode.c >> +++ b/src/box/sql/wherecode.c >> @@ -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); > 2. This function is now unused and can be deleted. Removed. > >> - assert(type != NULL || pParse->db->mallocFailed); >> - >> if (nSkip) { >> int iIdxCur = pLevel->iIdxCur; >> sqlVdbeAddOp1(v, (bRev ? OP_Last : OP_Rewind), iIdxCur);> 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" > 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. > >> -- >> }) >> >> -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" >> -- >> }) >> >> 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/" > 4. The same for this file. Fixed, used CAST(). > >> }) >> >> test:do_execsql_test( >> 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 >> @@ -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 > 5. Trailing whitespace. Removed. > >> ]], { >> -- >> 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 > 6. Trailing whitespace here and in the test above. Removed. > >> ]], { >> -- >> 1, true >> 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 >> @@ -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", > 7. The whole whereB-1 now fails because of type conversions banned. > Is this test still needed at all? I didn't check other whereB tests, > but seems like most of them also fail now. So either almost the entire > test file is not needed, or it should be fixed so as the test still > would work. Removed whereB.test.lua in the previous patch. New patch: From 5c3feffdfd6837438de4b7ce3d728b356bb4be8a 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' ... review fix 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..01c32d518 100644 --- a/src/box/sql/sqlInt.h +++ b/src/box/sql/sqlInt.h @@ -3866,10 +3866,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. 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..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..083a87b45 100755 --- a/test/sql-tap/tkt-9a8b09f8e6.test.lua +++ b/test/sql-tap/tkt-9a8b09f8e6.test.lua @@ -1,6 +1,6 @@  #!/usr/bin/env tarantool  test = require("sqltester") -test:plan(49) +test:plan(47)  --!./tcltestrunner.lua  -- 2014 June 26 @@ -86,7 +86,7 @@ test:do_execsql_test(  test:do_execsql_test(      2.1,      [[ -        SELECT x FROM t1 WHERE x IN (1); +        SELECT x FROM t1 WHERE CAST(x AS INTEGER) IN (1);      ]], {          -- <2.1>          "1" @@ -96,7 +96,7 @@ test:do_execsql_test(  test:do_execsql_test(      2.2,      [[ -        SELECT x FROM t1 WHERE x IN (1.0); +        SELECT x FROM t1 WHERE CAST(x AS DOUBLE) IN (1);      ]], {          -- <2.2>          "1" @@ -126,7 +126,7 @@ test:do_execsql_test(  test:do_execsql_test(      2.5,      [[ -        SELECT x FROM t1 WHERE 1 IN (x); +        SELECT x FROM t1 WHERE CAST(1 AS STRING) IN (x);      ]], {          -- <2.5>          "1" @@ -136,10 +136,9 @@ test:do_execsql_test(  test:do_execsql_test(      2.6,      [[ -        SELECT x FROM t1 WHERE 1.0 IN (x); +        SELECT x FROM t1 WHERE CAST(1.0 AS STRING) IN (x);      ]], {          -- <2.6> -        "1"          --      }) @@ -186,21 +185,13 @@ test:do_execsql_test(  test:do_execsql_test(      3.3,      [[ -        SELECT x FROM t2 WHERE x IN ('1'); +        SELECT x FROM t2 WHERE CAST(x AS STRING) IN ('1');      ]], {          -- <3.3> +        1          --      }) -test:do_execsql_test( -    3.4, -    [[ -        SELECT x FROM t2 WHERE x IN ('1'); -    ]], { -        -- <3.4> -        -- -    }) -  test:do_execsql_test(      3.5,      [[ @@ -224,21 +215,13 @@ test:do_execsql_test(  test:do_execsql_test(      3.7,      [[ -        SELECT x FROM t2 WHERE '1' IN (x); +        SELECT x FROM t2 WHERE CAST('1' AS INTEGER) IN (x);      ]], {          -- <3.7> +        1          --      }) -test:do_execsql_test( -    3.8, -    [[ -        SELECT x FROM t2 WHERE '1' IN (x); -    ]], { -        -- <3.8> -        -- -    }) -  test:do_execsql_test(      4.1,      [[ @@ -262,17 +245,16 @@ test:do_execsql_test(  test:do_execsql_test(      4.3,      [[ -        SELECT x FROM t3 WHERE x IN ('1'); +        SELECT x FROM t3 WHERE CAST(x AS STRING) IN ('1');      ]], {          -- <4.3> -        1.0          --      })  test:do_execsql_test(      4.4,      [[ -        SELECT x FROM t3 WHERE x IN ('1.0'); +        SELECT x FROM t3 WHERE CAST(x AS STRING) IN ('1.0');      ]], {          -- <4.4>          1.0 @@ -302,20 +284,20 @@ test:do_execsql_test(  test:do_execsql_test(      4.7,      [[ -        SELECT x FROM t3 WHERE '1' IN (x); +        SELECT x FROM t3 WHERE CAST('1' AS DOUBLE) IN (x);      ]], {          -- <4.7> -        1 +        1.0          --      })  test:do_execsql_test(      4.8,      [[ -        SELECT x FROM t3 WHERE '1.0' IN (x); +        SELECT x FROM t3 WHERE CAST('1.0' AS DOUBLE) IN (x);      ]], {          -- <4.8> -        1 +        1.0          --      }) @@ -342,20 +324,20 @@ test:do_execsql_test(  test:do_execsql_test(      5.3,      [[ -        SELECT x FROM t4 WHERE x IN ('1'); +        SELECT x FROM t4 WHERE CAST(x AS STRING) IN ('1');      ]], {          -- <5.3> - +          --      })  test:do_execsql_test(      5.4,      [[ -        SELECT x FROM t4 WHERE x IN ('1.0'); +        SELECT x FROM t4 WHERE CAST(x AS STRING) IN ('1.0');      ]], {          -- <5.4> - +          --      }) @@ -372,7 +354,7 @@ test:do_execsql_test(  test:do_execsql_test(      5.6,      [[ -        SELECT x FROM t4 WHERE x IN ('1.11'); +        SELECT x FROM t4 WHERE CAST(x AS STRING) IN ('1.11');      ]], {          -- <5.6>          1.11 @@ -402,20 +384,20 @@ test:do_execsql_test(  test:do_execsql_test(      5.9,      [[ -        SELECT x FROM t4 WHERE '1' IN (x); +        SELECT x FROM t4 WHERE CAST('1' AS DOUBLE) IN (x);      ]], {          -- <5.9> - +          --      })  test:do_execsql_test(      5.10,      [[ -        SELECT x FROM t4 WHERE '1.0' IN (x); +        SELECT x FROM t4 WHERE CAST('1.0' AS DOUBLE) IN (x);      ]], {          -- <5.10> - +          --      }) @@ -432,7 +414,7 @@ test:do_execsql_test(  test:do_execsql_test(      5.12,      [[ -        SELECT x FROM t4 WHERE '1.11' IN (x); +        SELECT x FROM t4 WHERE CAST('1.11' AS DOUBLE) IN (x);      ]], {          -- <5.12>          1.11 @@ -462,7 +444,7 @@ test:do_execsql_test(  test:do_execsql_test(      6.3,      [[ -        SELECT x, y FROM t5 WHERE x IN ('1'); +        SELECT x, y FROM t5 WHERE CAST(x AS STRING) IN ('1');      ]], {          -- <6.3>          1, "one", 1, "two", 1, "three", 1.0, "four" @@ -472,10 +454,10 @@ test:do_execsql_test(  test:do_execsql_test(      6.4,      [[ -        SELECT x, y FROM t5 WHERE x IN ('1.0'); +        SELECT x, y FROM t5 WHERE CAST(x AS STRING) IN ('1.0');      ]], {          -- <6.4> -        1, "one", 1, "two", 1, "three", 1.0, "four" +          --      }) @@ -502,7 +484,7 @@ test:do_execsql_test(  test:do_execsql_test(      6.7,      [[ -        SELECT x, y FROM t5 WHERE '1' IN (x); +        SELECT x, y FROM t5 WHERE CAST('1' AS INTEGER) IN (x);      ]], {          -- <6.7>          1, "one", 1, "two", 1, "three", 1.0, "four" @@ -512,7 +494,7 @@ test:do_execsql_test(  test:do_execsql_test(      6.8,      [[ -        SELECT x, y FROM t5 WHERE '1.0' IN (x); +        SELECT x, y FROM t5 WHERE CAST('1.0' AS DOUBLE) IN (x);      ]], {          -- <6.8>          1, "one", 1, "two", 1, "three", 1, "four" 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          ]], { -          })      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);")