From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (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 2AF7442EF5D for ; Thu, 25 Jun 2020 18:17:48 +0300 (MSK) From: imeevma@tarantool.org Date: Thu, 25 Jun 2020 18:17:46 +0300 Message-Id: <64653f49b79f2888243a76c30425d762acd301db.1593096639.git.imeevma@gmail.com> In-Reply-To: References: MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH v3 6/8] sql: remove implicit cast for comparison List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: korablev@tarantool.org, tsafin@tarantool.org, tarantool-patches@dev.tarantool.org Thank you for review! My answers and new patch below. On 22.06.2020 15:25, Nikita Pettik wrote: > On 17 Jun 15:36, imeevma@tarantool.org wrote: >> @@ -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; >> - > > Replace these assertions with relevant ones. > Not sure if this is necessary, since now we have much less flag changes after removal of mem_apply_type(). >> @@ -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; > > Could you substitute routine below with mem_check_types() > (or whatever this function is called)? > Partially substituted. >> @@ -3590,6 +3568,26 @@ skip_truncate: >> assert(oc!=OP_SeekLT || r.default_rc==+1); >> >> r.aMem = &aMem[pOp->p3]; > > Code below definitely lacks some comments. > Fixed. >> + 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)) { > > What if key_def contains scalar/any type? > It will be fine since mem_check_type() process these cases. >> + 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; i> #endif >> @@ -4717,6 +4715,27 @@ case OP_IdxGE: { /* jump */ >> r.default_rc = 0; >> } >> r.aMem = &aMem[pOp->p3]; >> + 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; > > Why this procedure is different from one in Seek* opcode > handling routine? Could you put it in one function and reuse? > Also lacks comments. > At Seek*, we convert numbers to use them in an iterator. This is not so in Idx*. In addition, Seek* is used only once during a selection from space, but Idx* can be used more than once, so we cannot convert here. >> + 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; >> + } >> + } >> diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c >> index 6d8768865..1d7c76670 100644 >> --- a/src/box/sql/wherecode.c >> +++ b/src/box/sql/wherecode.c >> @@ -335,72 +335,6 @@ disableTerm(WhereLevel * pLevel, WhereTerm * pTerm) >> } >> } >> >> -/** >> - 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 >> 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; > > Solid code removal. I won't review it, I hope you verified that all > tests are working as desired. > Sure. >> - 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, >> --- /dev/null >> +++ b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua > > Same note: I'd better use .sql test format and/or amalgamate it with > already existing test file. > I was said that TAP is preferred. >> @@ -0,0 +1,281 @@ >> +#!/usr/bin/env tarantool >> +test = require("sqltester") >> +test:plan(32) >> + >> +-- >> + >> +test:execsql([[ >> + CREATE TABLE t1(x TEXT primary key); >> + INSERT INTO t1 VALUES('1'); >> + CREATE TABLE t2(x INTEGER primary key); >> + INSERT INTO t2 VALUES(1); >> + CREATE TABLE t3(x DOUBLE primary key); >> + INSERT INTO t3 VALUES(1.0); > > What about table with scalar/any fields? > I think this is too big a problem to solve it here. New patch: >From 64653f49b79f2888243a76c30425d762acd301db Mon Sep 17 00:00:00 2001 From: Mergen Imeev Date: Thu, 25 Jun 2020 12:39:19 +0300 Subject: [PATCH] sql: remove implicit cast for comparison MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch removes implicit cast for comparison. Closes #4230 @TarantoolBot document Title: remove implicit cast for comparison After this patch-set, there will be no implicit casts for comparison. This means that the values ​​of the field types STRING, BOOLEAN and VARBINARY can be compared with the values ​​of the same field type. Any numerical value can be compared with any other numerical value. Example: For comparison: ``` tarantool> box.execute([[SELECT '1' > 0;]]) --- - null - "Type mismatch: can not convert '1' (type: text) to numeric" ... tarantool> box.execute([[SELECT true > X'33';]]) --- - null - 'Type mismatch: can not convert ''TRUE'' (type: boolean) to varbinary' ... tarantool> box.execute([[SELECT 1.23 > 123;]]) --- - metadata: - name: 1.23 > 123 type: boolean rows: - [false] ... ``` diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index a609fa985..f531a2241 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -2283,8 +2283,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) { @@ -2343,22 +2341,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() @@ -2391,22 +2384,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); @@ -2422,14 +2410,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 */ @@ -3524,8 +3504,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; @@ -3617,7 +3595,38 @@ skip_truncate: assert(oc!=OP_SeekGE || r.default_rc==+1); assert(oc!=OP_SeekLT || r.default_rc==+1); + /* + * Make sure that the types of all the fields in the tuple + * that will be used in the iterator match the field types + * of the space. + */ 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_check_type(mem, type) == 0 || + mem_convert_numeric(mem, type, true) == 0) + continue; + /* + * If the number was not converted without loss, + * we will not find tuples using the EQ iterator. + */ + if ((mem->flags & MEM_Real) != 0 && + (type == FIELD_TYPE_INTEGER || + type == FIELD_TYPE_UNSIGNED)) { + assert(eqOnly == 1); + res = 1; + goto seek_not_found; + } + if ((mem->flags & (MEM_Int | MEM_UInt)) != 0 && + type == FIELD_TYPE_DOUBLE && eqOnly == 1) { + 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; iopcode==OP_IdxGE || pOp->opcode==OP_IdxLT); r.default_rc = 0; } + + /* + * Make sure that the types of all the fields in the tuple + * that will be used in the iterator comparable with the + * fields of the space. + */ 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_check_type(mem, type) == 0) + continue; + if (sql_type_is_numeric(type) && + (mem->flags & (MEM_Real | MEM_Int | MEM_UInt)) != 0) + continue; + 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; ipVdbe; - if (types == NULL) { - assert(pParse->db->mallocFailed); - return; - } - assert(v != 0); - - /* - * Adjust base and n to skip over SCALAR entries at the - * beginning and end of the type sequence. - */ - while (n > 0 && types[0] == FIELD_TYPE_SCALAR) { - n--; - base++; - types++; - } - while (n > 1 && types[n - 1] == FIELD_TYPE_SCALAR) { - n--; - } - - if (n > 0) { - enum field_type *types_dup = field_type_sequence_dup(pParse, - types, n); - sqlVdbeAddOp4(v, OP_ApplyType, base, n, 0, - (char *) types_dup, P4_DYNAMIC); - sql_expr_type_cache_change(pParse, base, n); - } -} - -/** - * Expression @rhs, which is the RHS of a comparison operation, is - * either a vector of n elements or, if n==1, a scalar expression. - * Before the comparison operation, types @types are to be applied - * to the @rhs values. This function modifies entries within the - * field sequence to SCALAR if either: - * - * * the comparison will be performed with no type, or - * * the type change in @types is guaranteed not to change the value. - */ -static void -expr_cmp_update_rhs_type(struct Expr *rhs, int n, enum field_type *types) -{ - for (int i = 0; i < n; i++) { - Expr *p = sqlVectorFieldSubexpr(rhs, i); - enum field_type expr_type = sql_expr_type(p); - if (sql_type_result(expr_type, types[i]) == FIELD_TYPE_SCALAR || - sql_expr_needs_no_type_change(p, types[i])) { - types[i] = FIELD_TYPE_SCALAR; - } - } -} - /* * Generate code for a single equality term of the WHERE clause. An equality * term can be either X=expr or X IN (...). pTerm is the term to be @@ -644,8 +578,7 @@ static int codeAllEqualityTerms(Parse * pParse, /* Parsing context */ WhereLevel * pLevel, /* Which nested loop of the FROM we are coding */ int bRev, /* Reverse the order of IN operators */ - int nExtraReg, /* Number of extra registers to allocate */ - enum field_type **res_type) + int nExtraReg) /* Number of extra registers to allocate */ { u16 nEq; /* The number of == or IN constraints to code */ u16 nSkip; /* Number of left-most columns to skip */ @@ -669,9 +602,6 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */ nReg = pLoop->nEq + nExtraReg; pParse->nMem += nReg; - enum field_type *type = sql_index_type_str(pParse->db, idx_def); - assert(type != NULL || pParse->db->mallocFailed); - if (nSkip) { int iIdxCur = pLevel->iIdxCur; sqlVdbeAddOp1(v, (bRev ? OP_Last : OP_Rewind), iIdxCur); @@ -714,17 +644,7 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */ sqlVdbeAddOp2(v, OP_SCopy, r1, regBase + j); } } - if (pTerm->eOperator & WO_IN) { - if (pTerm->pExpr->flags & EP_xIsSelect) { - /* No type ever needs to be (or should be) applied to a value - * from the RHS of an "? IN (SELECT ...)" expression. The - * sqlFindInIndex() routine has already ensured that the - * type of the comparison has been applied to the value. - */ - if (type != NULL) - type[j] = FIELD_TYPE_SCALAR; - } - } else if ((pTerm->eOperator & WO_ISNULL) == 0) { + if ((pTerm->eOperator & (WO_IN | WO_ISNULL)) == 0) { Expr *pRight = pTerm->pExpr->pRight; if (sqlExprCanBeNull(pRight)) { sqlVdbeAddOp2(v, OP_IsNull, regBase + j, @@ -733,7 +653,6 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */ } } } - *res_type = type; return regBase; } @@ -904,10 +823,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W int iIdxCur; /* The VDBE cursor for the index */ int nExtraReg = 0; /* Number of extra registers needed */ int op; /* Instruction opcode */ - /* Types for start of range constraint. */ - enum field_type *start_types; - /* Types for end of range constraint */ - enum field_type *end_types = NULL; u8 bSeekPastNull = 0; /* True to seek past initial nulls */ u8 bStopAtNull = 0; /* Add condition to terminate at NULLs */ int force_integer_reg = -1; /* If non-negative: number of @@ -994,17 +909,7 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W * and store the values of those terms in an array of registers * starting at regBase. */ - regBase = - codeAllEqualityTerms(pParse, pLevel, bRev, nExtraReg, - &start_types); - if (start_types != NULL && nTop) { - uint32_t len = 0; - for (enum field_type *tmp = &start_types[nEq]; - *tmp != field_type_MAX; tmp++, len++); - uint32_t sz = len * sizeof(enum field_type); - end_types = sqlDbMallocRaw(db, sz); - memcpy(end_types, &start_types[nEq], sz); - } + regBase = codeAllEqualityTerms(pParse, pLevel, bRev, nExtraReg); addrNxt = pLevel->addrNxt; testcase(pRangeStart && (pRangeStart->eOperator & WO_LE) != 0); @@ -1029,10 +934,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W VdbeCoverage(v); } - if (start_types) { - expr_cmp_update_rhs_type(pRight, nBtm, - &start_types[nEq]); - } nConstraint += nBtm; testcase(pRangeStart->wtFlags & TERM_VIRTUAL); if (sqlExprIsVector(pRight) == 0) { @@ -1049,94 +950,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W } struct index_def *idx_pk = space->index[0]->def; uint32_t pk_part_count = idx_pk->key_def->part_count; - /* - * Tarantool's iterator over integer fields doesn't - * tolerate floating point values. Hence, if term - * is equality comparison and value of operand is - * not integer, we can skip it since it always - * results in false: INT a == 0.5 -> false; - * It is done using OP_MustBeInt facilities. - * In case term is greater comparison (a > ?), we - * should notify OP_SeekGT to process truncation of - * floating point value: a > 0.5 -> a >= 1; - * It is done by setting P5 flag for OP_Seek*. - * It is worth mentioning that we do not need - * this step when it comes for less (<) comparison - * of nullable field. Key is NULL in this case: - * values are ordered as NULL, ... NULL, min_value, - * so to fetch min value we pass NULL to GT iterator. - * The only exception is less comparison in - * conjunction with ORDER BY DESC clause: - * in such situation we use LE iterator and - * truncated value to compare. But then - * pRangeStart == NULL. - * This procedure is correct for compound index: - * only one comparison of less/greater type can be - * used at the same time. For instance, - * a < 1.5 AND b > 0.5 is handled by SeekGT using - * column a and fetching column b from tuple and - * OP_Le comparison. - * - * Note that OP_ApplyType, which is emitted before - * OP_Seek** doesn't truncate floating point to - * integer. That's why we need this routine. - * Also, note that terms are separated by OR - * predicates, so we consider term as sequence - * of AND'ed predicates. - */ - size_t addrs_sz; - int *seek_addrs = region_alloc_array(&pParse->region, - typeof(seek_addrs[0]), nEq, - &addrs_sz); - if (seek_addrs == NULL) { - diag_set(OutOfMemory, addrs_sz, "region_alloc_array", - "seek_addrs"); - pParse->is_aborted = true; - return 0; - } - memset(seek_addrs, 0, addrs_sz); - for (int i = 0; i < nEq; i++) { - enum field_type type = idx_def->key_def->parts[i].type; - if (type == FIELD_TYPE_INTEGER || - type == FIELD_TYPE_UNSIGNED) { - /* - * OP_MustBeInt consider NULLs as - * non-integer values, so firstly - * check whether value is NULL or not. - */ - seek_addrs[i] = sqlVdbeAddOp1(v, OP_IsNull, - regBase); - sqlVdbeAddOp2(v, OP_MustBeInt, regBase + i, - addrNxt); - start_types[i] = FIELD_TYPE_SCALAR; - /* - * We need to notify column cache - * that type of value may change - * so we should fetch value from - * tuple again rather then copy - * from register. - */ - sql_expr_type_cache_change(pParse, regBase + i, - 1); - } - } - /* Inequality constraint comes always at the end of list. */ - part_count = idx_def->key_def->part_count; - if (pRangeStart != NULL) { - /* - * nEq == 0 means that filter condition - * contains only inequality. - */ - uint32_t ineq_idx = nEq == 0 ? 0 : nEq - 1; - assert(ineq_idx < part_count); - enum field_type ineq_type = - idx_def->key_def->parts[ineq_idx].type; - if (ineq_type == FIELD_TYPE_INTEGER || - ineq_type == FIELD_TYPE_UNSIGNED) - force_integer_reg = regBase + nEq; - } - emit_apply_type(pParse, regBase, nConstraint - bSeekPastNull, - start_types); if (pLoop->nSkip > 0 && nConstraint == pLoop->nSkip) { /* The skip-scan logic inside the call to codeAllEqualityConstraints() * above has already left the cursor sitting on the correct row, @@ -1146,10 +959,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W op = aStartOp[(start_constraints << 2) + (startEq << 1) + bRev]; assert(op != 0); - for (uint32_t i = 0; i < nEq; ++i) { - if (seek_addrs[i] != 0) - sqlVdbeJumpHere(v, seek_addrs[i]); - } sqlVdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint); /* If this is Seek* opcode, and IPK is detected in the @@ -1189,13 +998,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W addrNxt); VdbeCoverage(v); } - if (end_types) { - expr_cmp_update_rhs_type(pRight, nTop, end_types); - emit_apply_type(pParse, regBase + nEq, nTop, - end_types); - } else { - assert(pParse->db->mallocFailed); - } nConstraint += nTop; testcase(pRangeEnd->wtFlags & TERM_VIRTUAL); @@ -1209,8 +1011,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W endEq = 0; nConstraint++; } - sqlDbFree(db, start_types); - sqlDbFree(db, end_types); /* Top of the loop body */ pLevel->p2 = sqlVdbeCurrentAddr(v); diff --git a/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua new file mode 100755 index 000000000..b405a11b6 --- /dev/null +++ b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua @@ -0,0 +1,281 @@ +#!/usr/bin/env tarantool +test = require("sqltester") +test:plan(32) + +-- +-- Make sure that there is no implicit cast between string and +-- number. +-- +test:do_catchsql_test( + "gh-4230-1", + [[ + SELECT '1' > 0; + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-2", + [[ + SELECT 0 > '1'; + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + +test:execsql([[ + CREATE TABLE t (i INT PRIMARY KEY, d DOUBLE, n NUMBER, s STRING); + INSERT INTO t VALUES (1, 1.0, 1, '2'), (2, 2.0, 2.0, '2'); + ]]) + +test:do_catchsql_test( + "gh-4230-3", + [[ + SELECT * from t where i > s; + ]], { + 1, "Type mismatch: can not convert 2 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-4", + [[ + SELECT * from t WHERE s > i; + ]], { + 1, "Type mismatch: can not convert 2 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-5", + [[ + SELECT * from t WHERE d > s; + ]], { + 1, "Type mismatch: can not convert 2 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-6", + [[ + SELECT * from t WHERE s > d; + ]], { + 1, "Type mismatch: can not convert 2 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-7", + [[ + SELECT * from t WHERE i = 1 and n > s; + ]], { + 1, "Type mismatch: can not convert 2 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-8", + [[ + SELECT * from t WHERE i = 2 and s > n; + ]], { + 1, "Type mismatch: can not convert 2 to numeric" + }) + +test:execsql([[ + CREATE TABLE t1(x TEXT primary key); + INSERT INTO t1 VALUES('1'); + CREATE TABLE t2(x INTEGER primary key); + INSERT INTO t2 VALUES(1); + CREATE TABLE t3(x DOUBLE primary key); + INSERT INTO t3 VALUES(1.0); +]]) + +test:do_catchsql_test( + "gh-4230-9", + [[ + SELECT x FROM t1 WHERE x IN (1); + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + + +test:do_catchsql_test( + "gh-4230-10", + [[ + SELECT x FROM t1 WHERE x IN (1.0); + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + +test:do_execsql_test( + "gh-4230-11", + [[ + SELECT x FROM t1 WHERE x IN ('1'); + ]], { + "1" + }) + +test:do_execsql_test( + "gh-4230-12", + [[ + SELECT x FROM t1 WHERE x IN ('1.0'); + ]], { + }) + +test:do_catchsql_test( + "gh-4230-13", + [[ + SELECT x FROM t1 WHERE 1 IN (x); + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + +test:do_catchsql_test( + "gh-4230-14", + [[ + SELECT x FROM t1 WHERE 1.0 IN (x); + ]], { + 1, "Type mismatch: can not convert 1 to numeric" + }) + +test:do_execsql_test( + "gh-4230-15", + [[ + SELECT x FROM t1 WHERE '1' IN (x); + ]], { + -- <2.7> + "1" + -- + }) + +test:do_execsql_test( + "gh-4230-16", + [[ + SELECT x FROM t1 WHERE '1.0' IN (x); + ]], { + }) + +test:do_execsql_test( + "gh-4230-17", + [[ + SELECT x FROM t2 WHERE x IN (1); + ]], { + 1 + }) + + +test:do_execsql_test( + "gh-4230-18", + [[ + SELECT x FROM t2 WHERE x IN (1.0); + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-4230-19", + [[ + SELECT x FROM t2 WHERE x IN ('1'); + ]], { + 1, "Type mismatch: can not convert integer to text" + }) + +test:do_catchsql_test( + "gh-4230-20", + [[ + SELECT x FROM t2 WHERE x IN ('1.0'); + ]], { + 1, "Type mismatch: can not convert integer to text" + }) + +test:do_execsql_test( + "gh-4230-21", + [[ + SELECT x FROM t2 WHERE 1 IN (x); + ]], { + 1 + }) + +test:do_execsql_test( + "gh-4230-22", + [[ + SELECT x FROM t2 WHERE 1.0 IN (x); + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-4230-23", + [[ + SELECT x FROM t2 WHERE '1' IN (x); + ]], { + 1, "Type mismatch: can not convert integer to text" + }) + +test:do_catchsql_test( + "gh-4230-24", + [[ + SELECT x FROM t2 WHERE '1.0' IN (x); + ]], { + 1, "Type mismatch: can not convert integer to text" + }) + +test:do_execsql_test( + "gh-4230-25", + [[ + SELECT x FROM t3 WHERE x IN (1); + ]], { + 1 + }) + +test:do_execsql_test( + "gh-4230-26", + [[ + SELECT x FROM t3 WHERE x IN (1.0); + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-4230-27", + [[ + SELECT x FROM t3 WHERE x IN ('1'); + ]], { + 1, "Type mismatch: can not convert double to text" + }) + +test:do_catchsql_test( + "gh-4230-28", + [[ + SELECT x FROM t3 WHERE x IN ('1.0'); + ]], { + 1, "Type mismatch: can not convert double to text" + }) + +test:do_execsql_test( + "gh-4230-29", + [[ + SELECT x FROM t3 WHERE 1 IN (x); + ]], { + 1 + }) + +test:do_execsql_test( + "gh-4230-30", + [[ + SELECT x FROM t3 WHERE 1.0 IN (x); + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-4230-31", + [[ + SELECT x FROM t3 WHERE '1' IN (x); + ]], { + 1, "Type mismatch: can not convert double to text" + }) + +test:do_catchsql_test( + "gh-4230-32", + [[ + SELECT x FROM t3 WHERE '1.0' IN (x); + ]], { + 1, "Type mismatch: can not convert double to text" + }) + +test:finish_test() diff --git a/test/sql-tap/identifier_case.test.lua b/test/sql-tap/identifier_case.test.lua index 2a00626fc..1d56ffb44 100755 --- a/test/sql-tap/identifier_case.test.lua +++ b/test/sql-tap/identifier_case.test.lua @@ -242,11 +242,11 @@ data = { { 2, [[ 'a' < 'b' collate "binary" ]], {0, {true}}}, { 3, [[ 'a' < 'b' collate 'binary' ]], {1, [[Syntax error at line 1 near ''binary'']]}}, { 4, [[ 'a' < 'b' collate "unicode" ]], {0, {true}}}, - { 5, [[ 5 < 'b' collate "unicode" ]], {0, {true}}}, + { 5, [[ 5 < 'b' collate "unicode" ]], {1, "Type mismatch: can not convert b to numeric"}}, { 6, [[ 5 < 'b' collate unicode ]], {1,"Collation 'UNICODE' does not exist"}}, - { 7, [[ 5 < 'b' collate "unicode_ci" ]], {0, {true}}}, + { 7, [[ 5 < 'b' collate "unicode_ci" ]], {1, "Type mismatch: can not convert b to numeric"}}, { 8, [[ 5 < 'b' collate NONE ]], {1, "Collation 'NONE' does not exist"}}, - { 9, [[ 5 < 'b' collate "none" ]], {0, {true}}}, + { 9, [[ 5 < 'b' collate "none" ]], {1, "Type mismatch: can not convert b to numeric"}}, } for _, row in ipairs(data) do diff --git a/test/sql-tap/in1.test.lua b/test/sql-tap/in1.test.lua index 570cc1779..e2f498889 100755 --- a/test/sql-tap/in1.test.lua +++ b/test/sql-tap/in1.test.lua @@ -637,12 +637,12 @@ test:do_test( "in-11.2", function() -- The '2' should be coerced into 2 because t6.b is NUMERIC - return test:execsql [[ + return test:catchsql [[ SELECT * FROM t6 WHERE b IN ('2'); ]] end, { -- - 1, 2 + 1, "Type mismatch: can not convert 2 to numeric" -- }) diff --git a/test/sql-tap/in4.test.lua b/test/sql-tap/in4.test.lua index 33947d0ab..a494e846f 100755 --- a/test/sql-tap/in4.test.lua +++ b/test/sql-tap/in4.test.lua @@ -147,12 +147,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "in4-2.8", [[ SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2') ]], { -- + 1, "Type mismatch: can not convert integer to text" -- }) diff --git a/test/sql-tap/insert3.test.lua b/test/sql-tap/insert3.test.lua index b92bc508e..3276f0db2 100755 --- a/test/sql-tap/insert3.test.lua +++ b/test/sql-tap/insert3.test.lua @@ -59,7 +59,7 @@ test:do_execsql_test( [[ CREATE TABLE log2(rowid INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT UNIQUE,y INT ); CREATE TRIGGER r2 BEFORE INSERT ON t1 FOR EACH ROW BEGIN - UPDATE log2 SET y=y+1 WHERE x=new.b; + UPDATE log2 SET y=y+1 WHERE x=CAST(new.b AS STRING); INSERT OR IGNORE INTO log2(x, y) VALUES(CAST(new.b AS STRING),1); END; INSERT INTO t1(a, b) VALUES('hi', 453); diff --git a/test/sql-tap/join.test.lua b/test/sql-tap/join.test.lua index 51e0ecb79..792302ab5 100755 --- a/test/sql-tap/join.test.lua +++ b/test/sql-tap/join.test.lua @@ -1028,22 +1028,23 @@ test:do_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "join-11.9", [[ SELECT * FROM t1 NATURAL JOIN t2 ]], { -- + 1, "Type mismatch: can not convert integer to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( "join-11.10", [[ SELECT * FROM t2 NATURAL JOIN t1 ]], { -- - 1, "one", 2, "two" + 1, "Type mismatch: can not convert 1 to numeric" -- }) diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua index e0fe50bbe..3cef617f4 100755 --- a/test/sql-tap/misc1.test.lua +++ b/test/sql-tap/misc1.test.lua @@ -88,7 +88,7 @@ test:do_execsql_test( test:do_execsql_test( "misc1-1.4", [[ - SELECT x75 FROM manycol WHERE x50=350 + SELECT x75 FROM manycol WHERE x50='350' ]], { -- "375" @@ -98,7 +98,7 @@ test:do_execsql_test( test:do_execsql_test( "misc1-1.5", [[ - SELECT x50 FROM manycol WHERE x99=599 + SELECT x50 FROM manycol WHERE x99='599' ]], { -- "550" @@ -109,7 +109,7 @@ test:do_test( "misc1-1.6", function() test:execsql("CREATE INDEX manycol_idx1 ON manycol(x99)") - return test:execsql("SELECT x50 FROM manycol WHERE x99=899") + return test:execsql("SELECT x50 FROM manycol WHERE x99='899'") end, { -- "850" @@ -129,7 +129,7 @@ test:do_execsql_test( test:do_test( "misc1-1.8", function() - test:execsql("DELETE FROM manycol WHERE x98=1234") + test:execsql("DELETE FROM manycol WHERE x98='1234'") return test:execsql("SELECT count(*) FROM manycol") end, { -- @@ -140,7 +140,7 @@ test:do_test( test:do_test( "misc1-1.9", function() - test:execsql("DELETE FROM manycol WHERE x98=998") + test:execsql("DELETE FROM manycol WHERE x98='998'") return test:execsql("SELECT count(*) FROM manycol") end, { -- @@ -151,7 +151,7 @@ test:do_test( test:do_test( "misc1-1.10", function() - test:execsql("DELETE FROM manycol WHERE x99=500") + test:execsql("DELETE FROM manycol WHERE x99='500'") return test:execsql("SELECT count(*) FROM manycol") end, { -- @@ -162,7 +162,7 @@ test:do_test( test:do_test( "misc1-1.11", function() - test:execsql("DELETE FROM manycol WHERE x99=599") + test:execsql("DELETE FROM manycol WHERE x99='599'") return test:execsql("SELECT count(*) FROM manycol") end, { -- @@ -479,9 +479,9 @@ local where = "" test:do_test( "misc1-10.1", function() - where = "WHERE x0>=0" + where = "WHERE x0>='0'" for i = 1, 99, 1 do - where = where .. " AND x"..i.."<>0" + where = where .. " AND x"..i.."<>'0'" end return test:catchsql("SELECT count(*) FROM manycol "..where.."") end, { @@ -496,7 +496,7 @@ test:do_test( test:do_test( "misc1-10.3", function() - where = string.gsub(where,"x0>=0", "x0=0") + where = string.gsub(where,"x0>='0'", "x0='0'") return test:catchsql("DELETE FROM manycol "..where.."") end, { -- @@ -520,7 +520,7 @@ test:do_execsql_test( test:do_execsql_test( "misc1-10.6", [[ - SELECT x1 FROM manycol WHERE x0=100 + SELECT x1 FROM manycol WHERE x0='100' ]], { -- "101" @@ -530,7 +530,7 @@ test:do_execsql_test( test:do_test( "misc1-10.7", function() - where = string.gsub(where, "x0=0", "x0=100") + where = string.gsub(where, "x0='0'", "x0='100'") return test:catchsql("UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where.."") end, { -- @@ -541,7 +541,7 @@ test:do_test( test:do_execsql_test( "misc1-10.8", [[ - SELECT x1 FROM manycol WHERE x0=100 + SELECT x1 FROM manycol WHERE x0='100' ]], { -- "102" @@ -563,7 +563,7 @@ test:do_execsql_test( test:do_execsql_test( "misc1-10.10", [[ - SELECT x1 FROM manycol WHERE x0=100 + SELECT x1 FROM manycol WHERE x0='100' ]], { -- "103" @@ -619,13 +619,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "misc1-12.2", [[ SELECT '0'==0.0 ]], { -- - true + 1, "Type mismatch: can not convert 0 to numeric" -- }) diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua index 9a969bf3c..f5a9b63fe 100755 --- a/test/sql-tap/select1.test.lua +++ b/test/sql-tap/select1.test.lua @@ -1912,7 +1912,7 @@ test:do_execsql_test( test:do_execsql_test( "select1-12.7", [[ - SELECT * FROM t3 WHERE a=(SELECT 1); + SELECT * FROM t3 WHERE a=(SELECT '1'); ]], { -- 0, "1", "2" @@ -1922,7 +1922,7 @@ test:do_execsql_test( test:do_execsql_test( "select1-12.8", [[ - SELECT * FROM t3 WHERE a=(SELECT 2); + SELECT * FROM t3 WHERE a=(SELECT '2'); ]], { -- diff --git a/test/sql-tap/select7.test.lua b/test/sql-tap/select7.test.lua index e1e43c557..0d1390fd6 100755 --- a/test/sql-tap/select7.test.lua +++ b/test/sql-tap/select7.test.lua @@ -256,7 +256,7 @@ test:do_execsql_test( DROP TABLE IF EXISTS t5; CREATE TABLE t5(a TEXT primary key, b INT); INSERT INTO t5 VALUES('123', 456); - SELECT typeof(a), a FROM t5 GROUP BY a HAVING a "string", "123" diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua index e0771825e..bad702de9 100755 --- a/test/sql-tap/subquery.test.lua +++ b/test/sql-tap/subquery.test.lua @@ -284,13 +284,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "subquery-2.3.2", [[ SELECT a IN (10.0, 20) FROM t3; ]], { -- - false + 1, "Type mismatch: can not convert text to real" -- }) diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua deleted file mode 100755 index ac89c7df2..000000000 --- a/test/sql-tap/tkt-9a8b09f8e6.test.lua +++ /dev/null @@ -1,506 +0,0 @@ -#!/usr/bin/env tarantool -test = require("sqltester") -test:plan(47) - ---!./tcltestrunner.lua --- 2014 June 26 --- --- The author disclaims copyright to this source code. In place of --- a legal notice, here is a blessing: --- --- May you do good and not evil. --- May you find forgiveness for yourself and forgive others. --- May you share freely, never taking more than you give. --- -------------------------------------------------------------------------- --- This file implements regression tests for sql library. --- --- This file implements tests to verify that ticket [9a8b09f8e6] has been --- fixed. --- --- ["set","testdir",[["file","dirname",["argv0"]]]] --- ["source",[["testdir"],"\/tester.tcl"]] -testprefix = "tkt-9a8b09f8e6" --- MUST_WORK_TEST -if (0 > 0) - then -end -test:do_execsql_test( - 1.1, - [[ - CREATE TABLE t1(x TEXT primary key); - INSERT INTO t1 VALUES('1'); - ]], { - -- <1.1> - - -- - }) - -test:do_execsql_test( - 1.2, - [[ - CREATE TABLE t2(x INTEGER primary key); - INSERT INTO t2 VALUES(1); - ]], { - -- <1.2> - - -- - }) - -test:do_execsql_test( - 1.3, - [[ - CREATE TABLE t3(x NUMBER primary key); - INSERT INTO t3 VALUES(1.0); - ]], { - -- <1.3> - - -- - }) - -test:do_execsql_test( - 1.4, - [[ - CREATE TABLE t4(x NUMBER primary key); - INSERT INTO t4 VALUES(1.11); - ]], { - -- <1.4> - - -- - }) - -test:do_execsql_test( - 1.5, - [[ - CREATE TABLE t5(id INT primary key, x INT , y TEXT); - INSERT INTO t5 VALUES(1, 1, 'one'); - INSERT INTO t5 VALUES(2, 1, 'two'); - INSERT INTO t5 VALUES(3, 1.0, 'three'); - INSERT INTO t5 VALUES(4, 1.0, 'four'); - ]], { - -- <1.5> - - -- - }) - -test:do_execsql_test( - 2.1, - [[ - SELECT x FROM t1 WHERE x IN (1); - ]], { - -- <2.1> - "1" - -- - }) - -test:do_execsql_test( - 2.2, - [[ - SELECT x FROM t1 WHERE x IN (1.0); - ]], { - -- <2.2> - "1" - -- - }) - -test:do_execsql_test( - 2.3, - [[ - SELECT x FROM t1 WHERE x IN ('1'); - ]], { - -- <2.3> - "1" - -- - }) - -test:do_execsql_test( - 2.4, - [[ - SELECT x FROM t1 WHERE x IN ('1.0'); - ]], { - -- <2.4> - - -- - }) - -test:do_execsql_test( - 2.5, - [[ - SELECT x FROM t1 WHERE 1 IN (x); - ]], { - -- <2.5> - "1" - -- - }) - -test:do_execsql_test( - 2.6, - [[ - SELECT x FROM t1 WHERE 1.0 IN (x); - ]], { - -- <2.6> - "1" - -- - }) - -test:do_execsql_test( - 2.7, - [[ - SELECT x FROM t1 WHERE '1' IN (x); - ]], { - -- <2.7> - "1" - -- - }) - -test:do_execsql_test( - 2.8, - [[ - SELECT x FROM t1 WHERE '1.0' IN (x); - ]], { - -- <2.8> - - -- - }) - -test:do_execsql_test( - 3.1, - [[ - SELECT x FROM t2 WHERE x IN (1); - ]], { - -- <3.1> - 1 - -- - }) - -test:do_execsql_test( - 3.2, - [[ - SELECT x FROM t2 WHERE x IN (1.0); - ]], { - -- <3.2> - 1 - -- - }) - -test:do_execsql_test( - 3.3, - [[ - SELECT x FROM t2 WHERE x IN ('1'); - ]], { - -- <3.3> - -- - }) - -test:do_execsql_test( - 3.5, - [[ - SELECT x FROM t2 WHERE 1 IN (x); - ]], { - -- <3.5> - 1 - -- - }) - -test:do_execsql_test( - 3.6, - [[ - SELECT x FROM t2 WHERE 1.0 IN (x); - ]], { - -- <3.6> - 1 - -- - }) - -test:do_execsql_test( - 3.7, - [[ - SELECT x FROM t2 WHERE '1' IN (x); - ]], { - -- <3.7> - -- - }) - -test:do_execsql_test( - 4.1, - [[ - SELECT x FROM t3 WHERE x IN (1); - ]], { - -- <4.1> - 1.0 - -- - }) - -test:do_execsql_test( - 4.2, - [[ - SELECT x FROM t3 WHERE x IN (1.0); - ]], { - -- <4.2> - 1.0 - -- - }) - -test:do_execsql_test( - 4.3, - [[ - SELECT x FROM t3 WHERE x IN ('1'); - ]], { - -- <4.3> - 1.0 - -- - }) - -test:do_execsql_test( - 4.4, - [[ - SELECT x FROM t3 WHERE x IN ('1.0'); - ]], { - -- <4.4> - 1.0 - -- - }) - -test:do_execsql_test( - 4.5, - [[ - SELECT x FROM t3 WHERE 1 IN (x); - ]], { - -- <4.5> - 1.0 - -- - }) - -test:do_execsql_test( - 4.6, - [[ - SELECT x FROM t3 WHERE 1.0 IN (x); - ]], { - -- <4.6> - 1.0 - -- - }) - -test:do_execsql_test( - 4.7, - [[ - SELECT x FROM t3 WHERE '1' IN (x); - ]], { - -- <4.7> - 1 - -- - }) - -test:do_execsql_test( - 4.8, - [[ - SELECT x FROM t3 WHERE '1.0' IN (x); - ]], { - -- <4.8> - 1 - -- - }) - -test:do_execsql_test( - 5.1, - [[ - SELECT x FROM t4 WHERE x IN (1); - ]], { - -- <5.1> - - -- - }) - -test:do_execsql_test( - 5.2, - [[ - SELECT x FROM t4 WHERE x IN (1.0); - ]], { - -- <5.2> - - -- - }) - -test:do_execsql_test( - 5.3, - [[ - SELECT x FROM t4 WHERE x IN ('1'); - ]], { - -- <5.3> - - -- - }) - -test:do_execsql_test( - 5.4, - [[ - SELECT x FROM t4 WHERE x IN ('1.0'); - ]], { - -- <5.4> - - -- - }) - -test:do_execsql_test( - 5.5, - [[ - SELECT x FROM t4 WHERE x IN (1.11); - ]], { - -- <5.5> - 1.11 - -- - }) - -test:do_execsql_test( - 5.6, - [[ - SELECT x FROM t4 WHERE x IN ('1.11'); - ]], { - -- <5.6> - 1.11 - -- - }) - -test:do_execsql_test( - 5.7, - [[ - SELECT x FROM t4 WHERE 1 IN (x); - ]], { - -- <5.7> - - -- - }) - -test:do_execsql_test( - 5.8, - [[ - SELECT x FROM t4 WHERE 1.0 IN (x); - ]], { - -- <5.8> - - -- - }) - -test:do_execsql_test( - 5.9, - [[ - SELECT x FROM t4 WHERE '1' IN (x); - ]], { - -- <5.9> - - -- - }) - -test:do_execsql_test( - 5.10, - [[ - SELECT x FROM t4 WHERE '1.0' IN (x); - ]], { - -- <5.10> - - -- - }) - -test:do_execsql_test( - 5.11, - [[ - SELECT x FROM t4 WHERE 1.11 IN (x); - ]], { - -- <5.11> - 1.11 - -- - }) - -test:do_execsql_test( - 5.12, - [[ - SELECT x FROM t4 WHERE '1.11' IN (x); - ]], { - -- <5.12> - 1.11 - -- - }) - -test:do_execsql_test( - 6.1, - [[ - SELECT x, y FROM t5 WHERE x IN (1); - ]], { - -- <6.1> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.2, - [[ - SELECT x, y FROM t5 WHERE x IN (1.0); - ]], { - -- <6.2> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.3, - [[ - SELECT x, y FROM t5 WHERE x IN ('1'); - ]], { - -- <6.3> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.4, - [[ - SELECT x, y FROM t5 WHERE x IN ('1.0'); - ]], { - -- <6.4> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.5, - [[ - SELECT x, y FROM t5 WHERE 1 IN (x); - ]], { - -- <6.5> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.6, - [[ - SELECT x, y FROM t5 WHERE 1.0 IN (x); - ]], { - -- <6.6> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.7, - [[ - SELECT x, y FROM t5 WHERE '1' IN (x); - ]], { - -- <6.7> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.8, - [[ - SELECT x, y FROM t5 WHERE '1.0' IN (x); - ]], { - -- <6.8> - 1, "one", 1, "two", 1, "three", 1, "four" - -- - }) - - - -test:finish_test() diff --git a/test/sql-tap/tkt-f973c7ac31.test.lua b/test/sql-tap/tkt-f973c7ac31.test.lua index 82bdb52f8..381f29c65 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 54aff460e..62161cfe1 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);")