From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp51.i.mail.ru (smtp51.i.mail.ru [94.100.177.111]) (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 AD371469719 for ; Fri, 20 Mar 2020 15:35:00 +0300 (MSK) From: imeevma@tarantool.org Date: Fri, 20 Mar 2020 15:34:58 +0300 Message-Id: <9a402676dbab6bdc4b1cbbdfc1f5069214fdfa06.1584707598.git.imeevma@gmail.com> Subject: [Tarantool-patches] [PATCH v1 1/1] sql: remove implicit cast for COMPARISON List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: v.shpilevoy@tarantool.org, tsafin@tarantool.org, tarantool-patches@dev.tarantool.org This patch removes implicit cast for comparison. Also, it make search using an index work the same way as in case of fullscan in most cases. Closes #4230 Closes #4783 --- https://github.com/tarantool/tarantool/issues/4230 https://github.com/tarantool/tarantool/issues/4783 https://github.com/tarantool/tarantool/tree/imeevma/gh-4230-remove-implicit-cast-for-index-search-second @ChangeLog Remove implicit cast for comparison gh-4230 src/box/sql/sqlInt.h | 3 + src/box/sql/vdbe.c | 638 +++++++++++++++++++--------------- src/box/sql/vdbemem.c | 1 + src/box/sql/where.c | 4 +- src/box/sql/wherecode.c | 204 +---------- src/box/tuple_compare.cc | 6 + src/box/tuple_compare.h | 9 + test/sql-tap/identifier_case.test.lua | 6 +- test/sql-tap/in1.test.lua | 14 +- test/sql-tap/in4.test.lua | 40 +-- test/sql-tap/index1.test.lua | 4 +- test/sql-tap/insert3.test.lua | 2 +- test/sql-tap/intpkey.test.lua | 12 +- test/sql-tap/join.test.lua | 8 +- test/sql-tap/misc1.test.lua | 32 +- test/sql-tap/select1.test.lua | 10 +- test/sql-tap/select7.test.lua | 2 +- test/sql-tap/sql-errors.test.lua | 4 +- test/sql-tap/subquery.test.lua | 4 +- test/sql-tap/tkt-9a8b09f8e6.test.lua | 88 ++--- test/sql-tap/tkt-f973c7ac31.test.lua | 34 +- test/sql-tap/tkt-fc7bd6358f.test.lua | 8 +- test/sql-tap/tkt3493.test.lua | 30 +- test/sql-tap/transitive1.test.lua | 16 +- test/sql-tap/where2.test.lua | 24 +- test/sql-tap/where5.test.lua | 12 +- test/sql-tap/whereB.test.lua | 80 ++--- test/sql-tap/whereC.test.lua | 10 +- test/sql/boolean.result | 230 ++++++------ test/sql/types.result | 7 +- test/sql/types.test.lua | 2 +- 31 files changed, 702 insertions(+), 842 deletions(-) diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h index 1579cc9..cd626bd 100644 --- a/src/box/sql/sqlInt.h +++ b/src/box/sql/sqlInt.h @@ -1304,6 +1304,9 @@ enum trim_side_mask { (X) == FIELD_TYPE_UNSIGNED || \ (X) == FIELD_TYPE_DOUBLE) +#define sql_mp_type_is_numeric(X) ((X) == MP_INT || (X) == MP_UINT || \ + (X) == MP_FLOAT || (X) == MP_DOUBLE) + /* * Additional bit values that can be ORed with an type without * changing the type. diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index e8a029a..8c95fd4 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -660,6 +660,216 @@ vdbe_field_ref_fetch_field(struct vdbe_field_ref *field_ref, uint32_t fieldno) } /** + * Prepare mem to use for comparison with value with type UNSIGNED + * from index. + * + * @param[out] mem Mem to prepare for comparison. + * @param[in][out] op Operation that was before preperation and + * operation after. + * @param is_eq True if operation was EQ. + * @param[out] is_none True if nothing will be found. + * @retval 0 on success. + * @retval -1 on error. + */ +static int +sql_mem_to_unsigned_for_comp(struct Mem *mem, uint32_t *op, int is_eq, + bool *is_none) +{ + assert(*is_none == false); + uint32_t orig_op = *op; + enum mp_type mp_type = sql_value_type(mem); + assert(mp_type == MP_DOUBLE || mp_type == MP_INT); + if (mp_type == MP_INT || mem->u.r < 0) { + if (orig_op == OP_SeekGE || orig_op == OP_SeekGT) { + mem_set_u64(mem, 0); + *op = OP_SeekGE; + return 0; + } + *is_none = true; + return 0; + } + double d = mem->u.r; + if (d >= UINT64_MAX) { + if (orig_op == OP_SeekLE || orig_op == OP_SeekLT) { + mem_set_u64(mem, UINT64_MAX); + *op = OP_SeekLE; + return 0; + } + *is_none = true; + return 0; + } + uint64_t u = (uint64_t)d; + if (d != u && is_eq) { + *is_none = true; + return 0; + } + mem_set_u64(mem, u); + if (d == u) + return 0; + if (orig_op == OP_SeekGE) + *op = OP_SeekGT; + else if (orig_op == OP_SeekLT) + *op = OP_SeekLE; + return 0; +} + +/** + * Prepare mem to use for comparison with value with type INTEGER + * from index. + * + * @param[out] mem Mem to prepare for comparison. + * @param[in][out] op Operation that was before preperation and + * operation after. + * @param is_eq True if operation was EQ. + * @param[out] is_none True if nothing will be found. + * @retval 0 on success. + * @retval -1 on error. + */ +static int +sql_mem_to_integer_for_comp(struct Mem *mem, uint32_t *op, int is_eq, + bool *is_none) +{ + assert(*is_none == false); + uint32_t orig_op = *op; + assert(sql_value_type(mem) == MP_DOUBLE); + double d = mem->u.r; + if (d < INT64_MIN) { + if (orig_op == OP_SeekGE || orig_op == OP_SeekGT) { + mem_set_i64(mem, INT64_MIN); + *op = OP_SeekGE; + return 0; + } + *is_none = true; + return 0; + } + if (d >= UINT64_MAX) { + if (orig_op == OP_SeekLE || orig_op == OP_SeekLT) { + mem_set_u64(mem, UINT64_MAX); + *op = OP_SeekLE; + return 0; + } + *is_none = true; + return 0; + } + int64_t i = (int64_t)d; + uint64_t u = (uint64_t)d; + if (d != i && d != u && is_eq) { + *is_none = true; + return 0; + } + if (d > 0) + mem_set_u64(mem, u); + else + mem_set_i64(mem, i); + if (d == u || d == i) + return 0; + if (d >= 0) { + if (orig_op == OP_SeekGE) + *op = OP_SeekGT; + else if (orig_op == OP_SeekLT) + *op = OP_SeekLE; + return 0; + } + if (orig_op == OP_SeekLE) + *op = OP_SeekLT; + else if (orig_op == OP_SeekGT) + *op = OP_SeekGE; + return 0; +} + +/** + * Prepare mem to use for comparison with value with type DOUBLE + * from index. + * + * @param[out] mem Mem to prepare for comparison. + * @param[in][out] op Operation that was before preperation and + * operation after. + * @param is_eq True if operation was EQ. + * @param[out] is_none True if nothing will be found. + * @retval 0 on success. + * @retval -1 on error. + */ +static int +sql_mem_to_double_for_comp(struct Mem *mem, uint32_t *op, int is_eq, + bool *is_none) +{ + assert(*is_none == false); + uint32_t orig_op = *op; + enum mp_type mp_type = sql_value_type(mem); + assert(mp_type == MP_INT || mp_type == MP_UINT); + int64_t i = mem->u.i; + uint64_t u = mem->u.u; + /* 2^53 == 9007199254740992 */ + if (mp_type == MP_INT && i > -9007199254740992) { + sqlVdbeMemSetDouble(mem, i); + return 0; + } else if (mp_type == MP_UINT && u < 9007199254740992) { + sqlVdbeMemSetDouble(mem, u); + return 0; + } + + double d = mp_type == MP_INT ? (double)i : (double)u; + if (is_eq) { + if (i != d && u != d) + *is_none = true; + else + sqlVdbeMemSetDouble(mem, d); + return 0; + } + + sqlVdbeMemSetDouble(mem, d); + if (mp_type == MP_INT) { + if (orig_op == OP_SeekGT && d > i) + *op = OP_SeekGE; + else if (orig_op == OP_SeekGE && d < i) + *op = OP_SeekGT; + else if (orig_op == OP_SeekLT && d < i) + *op = OP_SeekLE; + else if (orig_op == OP_SeekLE && d > i) + *op = OP_SeekLT; + return 0; + } + if (orig_op == OP_SeekGT && d > u) + *op = OP_SeekGE; + else if (orig_op == OP_SeekGE && d < u) + *op = OP_SeekGT; + else if (orig_op == OP_SeekLT && d < u) + *op = OP_SeekLE; + else if (orig_op == OP_SeekLE && d > u) + *op = OP_SeekLT; + return 0; +} + +/** + * Prepare mem to use for comparison with value with numeric type + * from index. + * + * @param[in][out] r Unpacked tuple that should be prepared for + * comparison. + * @param i field of unpacked tuple that should be prepared. + * @param is_eq True if operation was EQ. + * @param[out] is_none True if nothing will be found. + * @retval 0 on success. + * @retval -1 on error. + */ +static int +sql_prepare_rec_for_comp(struct UnpackedRecord *r, int i, int is_eq, + bool *is_none) +{ + struct Mem *mem = &r->aMem[i]; + enum field_type field_type = r->key_def->parts[i].type; + uint32_t opcode = r->opcode; + if (field_type == FIELD_TYPE_UNSIGNED) + sql_mem_to_unsigned_for_comp(mem, &opcode, is_eq, is_none); + else if (field_type == FIELD_TYPE_INTEGER) + sql_mem_to_integer_for_comp(mem, &opcode, is_eq, is_none); + else + sql_mem_to_double_for_comp(mem, &opcode, is_eq, is_none); + r->opcode = opcode; + return 0; +} + +/** * Find the left closest field for a given fieldno in field_ref's * slot_bitmask. The fieldno is expected to be greater than 0. * @param field_ref The vdbe_field_ref instance to use. @@ -2050,15 +2260,15 @@ case OP_Cast: { /* in1 */ * jump to address P2. Or if the SQL_STOREP2 flag is set in P5, then * store the result of comparison in register P2. * - * Once any conversions have taken place, and neither value is NULL, - * the values are compared. If both values are blobs then memcmp() is - * used to determine the results of the comparison. If both values - * are text, then the appropriate collating function specified in - * P4 is used to do the comparison. If P4 is not specified then - * memcmp() is used to compare text string. If both values are - * numeric, then a numeric comparison is used. If the two values - * are of different types, then numbers are considered less than - * strings and strings are considered less than blobs. + * If neither value is NULL, the values are compared. If both + * values are blobs then memcmp() is used to determine the results + * of the comparison. If both values are booleans, TRUE if more + * than FALSE. If both values are text, then the appropriate + * collating function specified in P4 is used to do the + * comparison. If P4 is not specified then memcmp() is used to + * compare text string. If both values are numeric, then a numeric + * comparison is used. If both values are not NULL and the above + * rules cannot be applied to them, an error is generated. * * If SQL_NULLEQ is set in P5 then the result of comparison is always either * true or false and is never NULL. If both operands are NULL then the result @@ -2096,15 +2306,8 @@ case OP_Cast: { /* in1 */ * reg(P3) is NULL then the take the jump. If the SQL_JUMPIFNULL * bit is clear then fall through if either operand is NULL. * - * Once any conversions have taken place, and neither value is NULL, - * the values are compared. If both values are blobs then memcmp() is - * used to determine the results of the comparison. If both values - * are text, then the appropriate collating function specified in - * P4 is used to do the comparison. If P4 is not specified then - * memcmp() is used to compare text string. If both values are - * numeric, then a numeric comparison is used. If the two values - * are of different types, then numbers are considered less than - * strings and strings are considered less than blobs. + * See the Eq opcode for additional information about comparison + * rules. */ /* Opcode: Le P1 P2 P3 P4 P5 * Synopsis: IF r[P3]<=r[P1] @@ -2133,189 +2336,109 @@ case OP_Lt: /* same as TK_LT, jump, in1, in3 */ case OP_Le: /* same as TK_LE, jump, in1, in3 */ case OP_Gt: /* same as TK_GT, jump, in1, in3 */ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */ - int res, res2; /* Result of the comparison of pIn1 against pIn3 */ - u32 flags1; /* Copy of initial value of pIn1->flags */ - u32 flags3; /* Copy of initial value of pIn3->flags */ - + /* + * Result of comparison: more than 0 if l > r, + * 0 if l == r, less than 0 if l < r. + */ + int cmp_res; + /* Result of the operation. */ + bool result; pIn1 = &aMem[pOp->p1]; 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) { - /* If SQL_NULLEQ is set (which will only happen if the operator is - * OP_Eq or OP_Ne) then take the jump or not depending on whether - * or not both operands are null. - */ - assert(pOp->opcode==OP_Eq || pOp->opcode==OP_Ne); - assert((flags1 & MEM_Cleared)==0); - assert((pOp->p5 & SQL_JUMPIFNULL)==0); - if ((flags1&flags3&MEM_Null)!=0 - && (flags3&MEM_Cleared)==0 - ) { - res = 0; /* Operands are equal */ - } else { - res = 1; /* Operands are not equal */ - } - } else { - /* SQL_NULLEQ is clear and at least one operand is NULL, - * then the result is always NULL. - * The jump is taken if the SQL_JUMPIFNULL bit is set. + + bool has_scalar = (pIn1->field_type == FIELD_TYPE_SCALAR) || + (pIn3->field_type == FIELD_TYPE_SCALAR); + enum mp_type ltype = sql_value_type(pIn1); + enum mp_type rtype = sql_value_type(pIn3); + assert(ltype != MP_MAP && rtype != MP_MAP); + assert(ltype != MP_ARRAY && rtype != MP_ARRAY); + int mp_classes_comp = mp_type_classes_comp(rtype, ltype); + + if (ltype == MP_NIL || rtype == MP_NIL) { + if ((pOp->p5 & SQL_NULLEQ) == 0) { + /* + * SQL_NULLEQ is clear and at least one + * operand is NULL, then the result is + * always NULL. The jump is taken if the + * SQL_JUMPIFNULL bit is set. */ if (pOp->p5 & SQL_STOREP2) { pOut = vdbe_prepare_null_out(p, pOp->p2); - iCompare = 1; /* Operands are not equal */ + iCompare = 1; REGISTER_TRACE(p, pOp->p2, pOut); } else { VdbeBranchTaken(2,3); - if (pOp->p5 & SQL_JUMPIFNULL) { + if (pOp->p5 & SQL_JUMPIFNULL) goto jump_to_p2; - } } break; } - } else if (((flags1 | flags3) & MEM_Bool) != 0 || - ((flags1 | flags3) & MEM_Blob) != 0) { /* - * If one of values is of type BOOLEAN or VARBINARY, - * then the second one must be of the same type as - * well. Otherwise an error is raised. + * If SQL_NULLEQ is set (which will only happen if + * the operator is OP_Eq or OP_Ne) then take the + * jump or not depending on whether or not both + * operands are null. */ - int type_arg1 = flags1 & (MEM_Bool | MEM_Blob); - int type_arg3 = flags3 & (MEM_Bool | MEM_Blob); - if (type_arg1 != type_arg3) { - char *inconsistent_type = type_arg1 != 0 ? - mem_type_to_str(pIn3) : - mem_type_to_str(pIn1); - char *expected_type = type_arg1 != 0 ? - mem_type_to_str(pIn1) : - mem_type_to_str(pIn3); - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - inconsistent_type, expected_type); - goto abort_due_to_error; - } - res = sqlMemCompare(pIn3, pIn1, NULL); + assert(pOp->opcode == OP_Eq || pOp->opcode == OP_Ne); + assert((pOp->p5 & SQL_JUMPIFNULL) == 0); + cmp_res = !(ltype == MP_NIL && rtype == MP_NIL); + } else if (mp_classes_comp == 0) { + cmp_res = sqlMemCompare(pIn3, pIn1, pOp->p4.pColl); + } else if (has_scalar) { + cmp_res = mp_classes_comp; } 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; - } + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + mem_type_to_str(pIn1), mem_type_to_str(pIn3)); + goto abort_due_to_error; + } - } - } - /* Handle the common case of integer comparison here, as an - * optimization, to avoid a call to sqlMemCompare() - */ - if ((pIn1->flags & pIn3->flags & (MEM_Int | MEM_UInt)) != 0) { - if ((pIn1->flags & pIn3->flags & MEM_Int) != 0) { - if (pIn3->u.i > pIn1->u.i) - res = +1; - else if (pIn3->u.i < pIn1->u.i) - res = -1; - else - res = 0; - goto compare_op; - } - if ((pIn1->flags & pIn3->flags & MEM_UInt) != 0) { - if (pIn3->u.u > pIn1->u.u) - res = +1; - else if (pIn3->u.u < pIn1->u.u) - res = -1; - else - res = 0; - goto compare_op; - } - if ((pIn1->flags & MEM_UInt) != 0 && - (pIn3->flags & MEM_Int) != 0) { - res = -1; - goto compare_op; - } - res = 1; - 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 ((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); - } - } - assert(pOp->p4type==P4_COLLSEQ || pOp->p4.pColl==0); - res = sqlMemCompare(pIn3, pIn1, pOp->p4.pColl); - } - compare_op: - switch( pOp->opcode) { - case OP_Eq: res2 = res==0; break; - case OP_Ne: res2 = res; break; - case OP_Lt: res2 = res<0; break; - case OP_Le: res2 = res<=0; break; - case OP_Gt: res2 = res>0; break; - 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; + switch (pOp->opcode) { + case OP_Eq: + result = cmp_res == 0; + break; + case OP_Ne: + result = cmp_res != 0; + break; + case OP_Lt: + result = cmp_res < 0; + break; + case OP_Le: + result = cmp_res <= 0; + break; + case OP_Gt: + result = cmp_res > 0; + break; + case OP_Ge: + result = cmp_res >= 0; + break; + default: + unreachable(); + } if (pOp->p5 & SQL_STOREP2) { - iCompare = res; - res2 = res2!=0; /* For this path res2 must be exactly 0 or 1 */ - if ((pOp->p5 & SQL_KEEPNULL)!=0) { - /* The KEEPNULL flag prevents OP_Eq from overwriting a NULL with true - * and prevents OP_Ne from overwriting NULL with false. This flag - * is only used in contexts where either: - * (1) op==OP_Eq && (r[P2]==NULL || r[P2]==0) - * (2) op==OP_Ne && (r[P2]==NULL || r[P2]==1) - * Therefore it is not necessary to check the content of r[P2] for - * NULL. - */ - assert(pOp->opcode==OP_Ne || pOp->opcode==OP_Eq); - assert(res2==0 || res2==1); - testcase( res2==0 && pOp->opcode==OP_Eq); - testcase( res2==1 && pOp->opcode==OP_Eq); - testcase( res2==0 && pOp->opcode==OP_Ne); - testcase( res2==1 && pOp->opcode==OP_Ne); - if ((pOp->opcode==OP_Eq)==res2) break; + iCompare = cmp_res; + /* + * The KEEPNULL flag prevents OP_Eq from + * overwriting a NULL with true and prevents OP_Ne + * from overwriting NULL with false. This flag + * is only used in contexts where either: + * (1) op==OP_Eq && (r[P2]==NULL || r[P2]==0) + * (2) op==OP_Ne && (r[P2]==NULL || r[P2]==1) + * Therefore it is not necessary to check the + * content of r[P2] for NULL. + */ + if ((pOp->p5 & SQL_KEEPNULL) != 0) { + assert(pOp->opcode == OP_Ne || pOp->opcode == OP_Eq); + if ((pOp->opcode == OP_Eq) == result) + break; } pOut = vdbe_prepare_null_out(p, pOp->p2); - mem_set_bool(pOut, res2); + mem_set_bool(pOut, result); REGISTER_TRACE(p, pOp->p2, pOut); } else { - VdbeBranchTaken(res!=0, (pOp->p5 & SQL_NULLEQ)?2:3); - if (res2) { + VdbeBranchTaken(cmp_res != 0, (pOp->p5 & SQL_NULLEQ) ? 2 : 3); + if (result) goto jump_to_p2; - } } break; } @@ -3346,7 +3469,6 @@ case OP_SeekGT: { /* jump, in3 */ VdbeCursor *pC; /* The cursor to seek */ UnpackedRecord r; /* The key to seek for */ int nField; /* Number of columns or fields in the key */ - i64 iKey; /* The id we are to seek to */ int eqOnly; /* Only interested in == results */ assert(pOp->p1>=0 && pOp->p1nCursor); @@ -3364,86 +3486,6 @@ case OP_SeekGT: { /* jump, in3 */ #ifdef SQL_DEBUG pC->seekOp = pOp->opcode; #endif - iKey = 0; - /* - * In case floating value is intended to be passed to - * iterator over integer field, we must truncate it to - * integer value and change type of iterator: - * a > 1.5 -> a >= 2 - */ - int int_field = pOp->p5; - bool is_neg = false; - - if (int_field > 0) { - /* The input value in P3 might be of any type: integer, real, string, - * blob, or NULL. But it needs to be an integer before we can do - * the seek, so convert it. - */ - 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; - is_neg = true; - } else if ((pIn3->flags & MEM_UInt) == MEM_UInt) { - i = pIn3->u.u; - is_neg = false; - } else if ((pIn3->flags & MEM_Real) == MEM_Real) { - if (pIn3->u.r > INT64_MAX) - i = INT64_MAX; - else if (pIn3->u.r < INT64_MIN) - i = INT64_MIN; - else - i = pIn3->u.r; - is_neg = i < 0; - } else { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(pIn3), "integer"); - goto abort_due_to_error; - } - iKey = i; - - /* If the P3 value could not be converted into an integer without - * loss of information, then special processing is required... - */ - if ((pIn3->flags & (MEM_Int | MEM_UInt)) == 0) { - if ((pIn3->flags & MEM_Real)==0) { - /* If the P3 value cannot be converted into any kind of a number, - * then the seek is not possible, so jump to P2 - */ - VdbeBranchTaken(1,2); goto jump_to_p2; - break; - } - - /* If the approximation iKey is larger than the actual real search - * term, substitute >= for > and < for <=. e.g. if the search term - * is 4.9 and the integer approximation 5: - * - * (x > 4.9) -> (x >= 5) - * (x <= 4.9) -> (x < 5) - */ - if (pIn3->u.r<(double)iKey) { - assert(OP_SeekGE==(OP_SeekGT-1)); - assert(OP_SeekLT==(OP_SeekLE-1)); - assert((OP_SeekLE & 0x0001)==(OP_SeekGT & 0x0001)); - if ((oc & 0x0001)==(OP_SeekGT & 0x0001)) oc--; - } - - /* If the approximation iKey is smaller than the actual real search - * term, substitute <= for < and > for >=. - */ - else if (pIn3->u.r>(double)iKey) { - assert(OP_SeekLE==(OP_SeekLT+1)); - assert(OP_SeekGT==(OP_SeekGE+1)); - assert((OP_SeekLT & 0x0001)==(OP_SeekGE & 0x0001)); - if ((oc & 0x0001)==(OP_SeekLT & 0x0001)) oc++; - } - } - } -skip_truncate: /* * For a cursor with the OPFLAG_SEEKEQ hint, only the * OP_SeekGE and OP_SeekLE opcodes are allowed, and these @@ -3465,22 +3507,38 @@ skip_truncate: assert(nField>0); r.key_def = pC->key_def; r.nField = (u16)nField; - - if (int_field > 0) - mem_set_int(&aMem[int_field], iKey, is_neg); - - r.default_rc = ((1 & (oc - OP_SeekLT)) ? -1 : +1); - assert(oc!=OP_SeekGT || r.default_rc==-1); - assert(oc!=OP_SeekLE || r.default_rc==-1); - assert(oc!=OP_SeekGE || r.default_rc==+1); - assert(oc!=OP_SeekLT || r.default_rc==+1); - r.aMem = &aMem[pOp->p3]; #ifdef SQL_DEBUG { int i; for(i=0; iparts[i].type; + 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) || + !sql_mp_type_is_numeric(mp_type)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + mem_type_to_str(mem), + field_type_strs[field_type]); + goto abort_due_to_error; + } + bool is_none = false; + if (sql_prepare_rec_for_comp(&r, i, eqOnly, &is_none) != 0) + goto abort_due_to_error; + oc = r.opcode; + if (is_none) { + res = 1; + goto seek_not_found; + } + } + if (sqlCursorMovetoUnpacked(pC->uc.pCursor, &r, &res) != 0) goto abort_due_to_error; if (eqOnly && r.eqSeen==0) { @@ -4595,26 +4653,36 @@ case OP_IdxGE: { /* jump */ assert(pOp->p4type==P4_INT32); r.key_def = pC->key_def; r.nField = (u16)pOp->p4.i; - if (pOp->opcodeopcode==OP_IdxLE || pOp->opcode==OP_IdxGT); + 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; + if (!sql_type_is_numeric(field_type) || + !sql_mp_type_is_numeric(mp_type)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + mem_type_to_str(mem), + field_type_strs[field_type]); + goto abort_due_to_error; + } + } + if (pOp->opcode == OP_IdxLE || pOp->opcode == OP_IdxGT) r.default_rc = -1; - } else { - assert(pOp->opcode==OP_IdxGE || pOp->opcode==OP_IdxLT); + else r.default_rc = 0; - } - r.aMem = &aMem[pOp->p3]; -#ifdef SQL_DEBUG - { int i; for(i=0; iuc.pCursor, &r); - assert((OP_IdxLE&1)==(OP_IdxLT&1) && (OP_IdxGE&1)==(OP_IdxGT&1)); - if ((pOp->opcode&1)==(OP_IdxLT&1)) { - assert(pOp->opcode==OP_IdxLE || pOp->opcode==OP_IdxLT); + int res = tarantoolsqlIdxKeyCompare(pC->uc.pCursor, &r); + if (pOp->opcode == OP_IdxLE || pOp->opcode == OP_IdxLT) res = -res; - } else { - assert(pOp->opcode==OP_IdxGE || pOp->opcode==OP_IdxGT); + else res++; - } VdbeBranchTaken(res>0,2); if (res>0) goto jump_to_p2; break; diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c index aad030d..2d10c21 100644 --- a/src/box/sql/vdbemem.c +++ b/src/box/sql/vdbemem.c @@ -670,6 +670,7 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type) return 0; switch (type) { case FIELD_TYPE_SCALAR: + pMem->field_type = FIELD_TYPE_SCALAR; return 0; case FIELD_TYPE_BOOLEAN: if ((pMem->flags & MEM_Int) != 0) { diff --git a/src/box/sql/where.c b/src/box/sql/where.c index 7ec43e1..993b078 100644 --- a/src/box/sql/where.c +++ b/src/box/sql/where.c @@ -299,7 +299,9 @@ whereScanNext(WhereScan * pScan) pX = pTerm->pExpr; enum field_type expr_type = expr_cmp_mutual_type(pX); - if (!field_type1_contains_type2(expr_type, pScan->idx_type)) + if (!field_type1_contains_type2(expr_type, pScan->idx_type) && + !(sql_type_is_numeric(expr_type) && + sql_type_is_numeric(pScan->idx_type))) continue; if (pScan->is_column_seen) { Parse *pParse = diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c index 5bc27f1..725e2af 100644 --- a/src/box/sql/wherecode.c +++ b/src/box/sql/wherecode.c @@ -335,72 +335,6 @@ disableTerm(WhereLevel * pLevel, WhereTerm * pTerm) } } -/** - * Code an OP_ApplyType opcode to apply the column type string - * @types to the n registers starting at @base. - * - * As an optimization, SCALAR entries (which are no-ops) at the - * beginning and end of @types are ignored. If all entries in - * @types are SCALAR, then no code gets generated. - * - * This routine makes its own copy of @types so that the caller is - * free to modify @types after this routine returns. - */ -static void -emit_apply_type(Parse *pParse, int base, int n, enum field_type *types) -{ - Vdbe *v = pParse->pVdbe; - if (types == NULL) { - assert(pParse->db->mallocFailed); - return; - } - assert(v != 0); - - /* - * Adjust base and n to skip over SCALAR entries at the - * beginning and end of the type sequence. - */ - while (n > 0 && types[0] == FIELD_TYPE_SCALAR) { - n--; - base++; - types++; - } - while (n > 1 && types[n - 1] == FIELD_TYPE_SCALAR) { - n--; - } - - if (n > 0) { - enum field_type *types_dup = field_type_sequence_dup(pParse, - types, n); - 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,8 @@ 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) && + (pTerm->eOperator & WO_ISNULL) == 0) { Expr *pRight = pTerm->pExpr->pRight; if (sqlExprCanBeNull(pRight)) { sqlVdbeAddOp2(v, OP_IsNull, regBase + j, @@ -733,7 +654,6 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */ } } } - *res_type = type; return regBase; } @@ -904,10 +824,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 +910,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 +935,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W VdbeCoverage(v); } - if (start_types) { - expr_cmp_update_rhs_type(pRight, nBtm, - &start_types[nEq]); - } nConstraint += nBtm; testcase(pRangeStart->wtFlags & TERM_VIRTUAL); if (sqlExprIsVector(pRight) == 0) { @@ -1049,91 +951,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W } struct index_def *idx_pk = space->index[0]->def; uint32_t pk_part_count = idx_pk->key_def->part_count; - /* - * Tarantool's iterator over integer fields doesn't - * tolerate floating point values. Hence, if term - * is equality comparison and value of operand is - * not integer, we can skip it since it always - * results in false: INT a == 0.5 -> false; - * It is done using OP_MustBeInt facilities. - * In case term is greater comparison (a > ?), we - * should notify OP_SeekGT to process truncation of - * floating point value: a > 0.5 -> a >= 1; - * It is done by setting P5 flag for OP_Seek*. - * It is worth mentioning that we do not need - * this step when it comes for less (<) comparison - * of nullable field. Key is NULL in this case: - * values are ordered as NULL, ... NULL, min_value, - * so to fetch min value we pass NULL to GT iterator. - * The only exception is less comparison in - * conjunction with ORDER BY DESC clause: - * in such situation we use LE iterator and - * truncated value to compare. But then - * pRangeStart == NULL. - * This procedure is correct for compound index: - * only one comparison of less/greater type can be - * used at the same time. For instance, - * a < 1.5 AND b > 0.5 is handled by SeekGT using - * column a and fetching column b from tuple and - * OP_Le comparison. - * - * Note that OP_ApplyType, which is emitted before - * OP_Seek** doesn't truncate floating point to - * integer. That's why we need this routine. - * Also, note that terms are separated by OR - * predicates, so we consider term as sequence - * of AND'ed predicates. - */ - size_t addrs_sz = sizeof(int) * nEq; - int *seek_addrs = region_alloc(&pParse->region, addrs_sz); - if (seek_addrs == NULL) { - diag_set(OutOfMemory, addrs_sz, "region", "seek_addrs"); - pParse->is_aborted = true; - return 0; - } - memset(seek_addrs, 0, addrs_sz); - for (int i = 0; i < nEq; i++) { - enum field_type type = idx_def->key_def->parts[i].type; - if (type == FIELD_TYPE_INTEGER || - type == FIELD_TYPE_UNSIGNED) { - /* - * OP_MustBeInt consider NULLs as - * non-integer values, so firstly - * check whether value is NULL or not. - */ - seek_addrs[i] = sqlVdbeAddOp1(v, OP_IsNull, - regBase); - sqlVdbeAddOp2(v, OP_MustBeInt, regBase + i, - addrNxt); - start_types[i] = FIELD_TYPE_SCALAR; - /* - * We need to notify column cache - * that type of value may change - * so we should fetch value from - * tuple again rather then copy - * from register. - */ - sql_expr_type_cache_change(pParse, regBase + i, - 1); - } - } - /* Inequality constraint comes always at the end of list. */ - part_count = idx_def->key_def->part_count; - if (pRangeStart != NULL) { - /* - * nEq == 0 means that filter condition - * contains only inequality. - */ - uint32_t ineq_idx = nEq == 0 ? 0 : nEq - 1; - assert(ineq_idx < part_count); - enum field_type ineq_type = - idx_def->key_def->parts[ineq_idx].type; - if (ineq_type == FIELD_TYPE_INTEGER || - ineq_type == FIELD_TYPE_UNSIGNED) - force_integer_reg = regBase + nEq; - } - emit_apply_type(pParse, regBase, nConstraint - bSeekPastNull, - start_types); if (pLoop->nSkip > 0 && nConstraint == pLoop->nSkip) { /* The skip-scan logic inside the call to codeAllEqualityConstraints() * above has already left the cursor sitting on the correct row, @@ -1143,10 +960,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W op = aStartOp[(start_constraints << 2) + (startEq << 1) + bRev]; assert(op != 0); - for (uint32_t i = 0; i < nEq; ++i) { - if (seek_addrs[i] != 0) - sqlVdbeJumpHere(v, seek_addrs[i]); - } sqlVdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint); /* If this is Seek* opcode, and IPK is detected in the @@ -1186,13 +999,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W addrNxt); VdbeCoverage(v); } - if (end_types) { - expr_cmp_update_rhs_type(pRight, nTop, end_types); - emit_apply_type(pParse, regBase + nEq, nTop, - end_types); - } else { - assert(pParse->db->mallocFailed); - } nConstraint += nTop; testcase(pRangeEnd->wtFlags & TERM_VIRTUAL); @@ -1206,8 +1012,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/src/box/tuple_compare.cc b/src/box/tuple_compare.cc index 3f8a0ce..6cbf555 100644 --- a/src/box/tuple_compare.cc +++ b/src/box/tuple_compare.cc @@ -104,6 +104,12 @@ mp_classof(enum mp_type type) return mp_classes[type]; } +int +mp_type_classes_comp(enum mp_type ltype, enum mp_type rtype) +{ + return mp_classof(ltype) - mp_classof(rtype); +} + static enum mp_class mp_extension_class(const char *data) { diff --git a/src/box/tuple_compare.h b/src/box/tuple_compare.h index 8614f23..0c1e4c6 100644 --- a/src/box/tuple_compare.h +++ b/src/box/tuple_compare.h @@ -31,6 +31,7 @@ * SUCH DAMAGE. */ #include +#include #if defined(__cplusplus) extern "C" { @@ -76,6 +77,14 @@ typedef uint64_t hint_t; void key_def_set_compare_func(struct key_def *def); +/** + * Return an integer less than, equal to, or greater than zero if + * mp_class of ltype is found, respectively, to be less than, to + * match, or be greater than mp_class of rtype. + */ +int +mp_type_classes_comp(enum mp_type ltype, enum mp_type rtype); + #if defined(__cplusplus) } /* extern "C" */ #endif /* defined(__cplusplus) */ diff --git a/test/sql-tap/identifier_case.test.lua b/test/sql-tap/identifier_case.test.lua index 2a00626..242645f 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 text to unsigned"}}, { 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 text to unsigned"}}, { 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 text to unsigned"}}, } for _, row in ipairs(data) do diff --git a/test/sql-tap/in1.test.lua b/test/sql-tap/in1.test.lua index 570cc17..710dd80 100755 --- a/test/sql-tap/in1.test.lua +++ b/test/sql-tap/in1.test.lua @@ -633,16 +633,12 @@ test:do_execsql_test( -- }) -test:do_test( - "in-11.2", - function() - -- The '2' should be coerced into 2 because t6.b is NUMERIC - return test:execsql [[ - SELECT * FROM t6 WHERE b IN ('2'); - ]] - end, { +test:do_catchsql_test( + "in-11.2",[[ + SELECT * FROM t6 WHERE b IN ('2'); + ]], { -- - 1, 2 + 1, "Type mismatch: can not convert text to unsigned" -- }) diff --git a/test/sql-tap/in4.test.lua b/test/sql-tap/in4.test.lua index 8c69173..4d400ce 100755 --- a/test/sql-tap/in4.test.lua +++ b/test/sql-tap/in4.test.lua @@ -147,13 +147,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "in4-2.8", [[ SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2') ]], { -- - "two" + 1, "Type mismatch: can not convert text to integer" -- }) @@ -585,7 +585,7 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "in4-4.11", [[ CREATE TABLE t4b(a TEXT, b NUMBER, c INT PRIMARY KEY); @@ -593,87 +593,87 @@ test:do_execsql_test( SELECT c FROM t4b WHERE a=b; ]], { -- - 4 + 1, "Type mismatch: can not convert unsigned to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( "in4-4.12", [[ SELECT c FROM t4b WHERE b=a; ]], { -- - 4 + 1, "Type mismatch: can not convert text to unsigned" -- }) -test:do_execsql_test( +test:do_catchsql_test( "in4-4.13", [[ SELECT c FROM t4b WHERE +a=b; ]], { -- - 4 + 1, "Type mismatch: can not convert text to unsigned" -- }) -test:do_execsql_test( +test:do_catchsql_test( "in4-4.14", [[ SELECT c FROM t4b WHERE a=+b; ]], { -- - 4 + 1, "Type mismatch: can not convert unsigned to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( "in4-4.15", [[ SELECT c FROM t4b WHERE +b=a; ]], { -- - 4 + 1, "Type mismatch: can not convert unsigned to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( "in4-4.16", [[ SELECT c FROM t4b WHERE b=+a; ]], { -- - 4 + 1, "Type mismatch: can not convert text to unsigned" -- }) -test:do_execsql_test( +test:do_catchsql_test( "in4-4.17", [[ SELECT c FROM t4b WHERE a IN (b); ]], { -- - 4 + 1, "Type mismatch: can not convert unsigned to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( "in4-4.18", [[ SELECT c FROM t4b WHERE b IN (a); ]], { -- - 4 + 1, "Type mismatch: can not convert text to unsigned" -- }) -test:do_execsql_test( +test:do_catchsql_test( "in4-4.19", [[ SELECT c FROM t4b WHERE +b IN (a); ]], { -- - 4 + 1, "Type mismatch: can not convert unsigned to text" -- }) diff --git a/test/sql-tap/index1.test.lua b/test/sql-tap/index1.test.lua index e173e68..85988c9 100755 --- a/test/sql-tap/index1.test.lua +++ b/test/sql-tap/index1.test.lua @@ -785,7 +785,7 @@ test:do_catchsql_test( SELECT c FROM t6 WHERE a>123; ]], { -- - 1, "Type mismatch: can not convert to numeric" + 1, "Type mismatch: can not convert unsigned to text" -- }) @@ -795,7 +795,7 @@ test:do_catchsql_test( SELECT c FROM t6 WHERE a>=123; ]], { -- - 1, "Type mismatch: can not convert to numeric" + 1, "Type mismatch: can not convert unsigned to text" -- }) diff --git a/test/sql-tap/insert3.test.lua b/test/sql-tap/insert3.test.lua index 43bb066..ff8f6de 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(new.b,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 bec2670..71739f7 100755 --- a/test/sql-tap/intpkey.test.lua +++ b/test/sql-tap/intpkey.test.lua @@ -859,30 +859,30 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "intpkey-14.3", [[ SELECT * FROM t3 WHERE a>'2'; ]], { -- - 3, 3, "3" + 1, "Type mismatch: can not convert text to integer" -- }) -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( "intpkey-14.5", [[ - SELECT * FROM t3 WHERE a 1, 1, "one" @@ -892,7 +892,7 @@ test:do_execsql_test( test:do_execsql_test( "intpkey-14.6", [[ - SELECT * FROM t3 WHERE a=c; + SELECT * FROM t3 WHERE CAST(a AS TEXT)=c; ]], { -- 2, 2, "2", 3, 3, "3" diff --git a/test/sql-tap/join.test.lua b/test/sql-tap/join.test.lua index 4f014e0..577db7a 100755 --- a/test/sql-tap/join.test.lua +++ b/test/sql-tap/join.test.lua @@ -1028,23 +1028,23 @@ test:do_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "join-11.9", [[ SELECT * FROM t1 NATURAL JOIN t2 ]], { -- - "one", "1.0", "two", "2" + 1, "Type mismatch: can not convert text to integer" -- }) -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 text to unsigned" -- }) diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua index 32f38cc..4f83bc1 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=x1+1 "..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 real to text" -- }) diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua index fbebfab..ed01c26 100755 --- a/test/sql-tap/select1.test.lua +++ b/test/sql-tap/select1.test.lua @@ -320,7 +320,7 @@ test:do_catchsql_test( SELECT count(*),count(a),count(b) FROM t4 WHERE b=5 ]], { -- - 1, "Type mismatch: can not convert This is a string that is too big to fit inside a NBFS buffer to numeric" + 1, "Type mismatch: can not convert unsigned to text" -- }) @@ -1909,23 +1909,23 @@ test:do_execsql_test( -- ifcapable compound -test:do_execsql_test( +test:do_catchsql_test( "select1-12.7", [[ SELECT * FROM t3 WHERE a=(SELECT 1); ]], { -- - 0, "1", "2" + 1, "Type mismatch: can not convert unsigned to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( "select1-12.8", [[ SELECT * FROM t3 WHERE a=(SELECT 2); ]], { -- - + 1, "Type mismatch: can not convert unsigned to text" -- }) diff --git a/test/sql-tap/select7.test.lua b/test/sql-tap/select7.test.lua index fec5d7a..167ce05 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/sql-errors.test.lua b/test/sql-tap/sql-errors.test.lua index 5ec94bf..8ea6f8b 100755 --- a/test/sql-tap/sql-errors.test.lua +++ b/test/sql-tap/sql-errors.test.lua @@ -765,7 +765,7 @@ test:do_catchsql_test( SELECT X'ff' >= false; ]], { -- - 1, "Type mismatch: can not convert varbinary to boolean" + 1, "Type mismatch: can not convert boolean to varbinary" -- }) @@ -775,7 +775,7 @@ test:do_catchsql_test( SELECT X'ff' <= false; ]], { -- - 1, "Type mismatch: can not convert varbinary to boolean" + 1, "Type mismatch: can not convert boolean to varbinary" -- }) diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua index 6bedf58..4a59b01 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 db0881c..e80e7c6 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 unsigned to text" -- }) -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 real to text" -- }) @@ -123,23 +123,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 2.5, [[ SELECT x FROM t1 WHERE 1 IN (x); ]], { -- <2.5> - "1" + 1, "Type mismatch: can not convert unsigned to text" -- }) -test:do_execsql_test( +test:do_catchsql_test( 2.6, [[ SELECT x FROM t1 WHERE 1.0 IN (x); ]], { -- <2.6> - "1" + 1, "Type mismatch: can not convert real to text" -- }) @@ -183,23 +183,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 3.3, [[ SELECT x FROM t2 WHERE x IN ('1'); ]], { -- <3.3> - 1 + 1, "Type mismatch: can not convert text to integer" -- }) -test:do_execsql_test( +test:do_catchsql_test( 3.4, [[ SELECT x FROM t2 WHERE x IN ('1.0'); ]], { -- <3.4> - 1 + 1, "Type mismatch: can not convert text to integer" -- }) @@ -223,23 +223,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 3.7, [[ SELECT x FROM t2 WHERE '1' IN (x); ]], { -- <3.7> - 1 + 1, "Type mismatch: can not convert text to integer" -- }) -test:do_execsql_test( +test:do_catchsql_test( 3.8, [[ SELECT x FROM t2 WHERE '1.0' IN (x); ]], { -- <3.8> - 1 + 1, "Type mismatch: can not convert text to integer" -- }) @@ -263,23 +263,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 4.3, [[ SELECT x FROM t3 WHERE x IN ('1'); ]], { -- <4.3> - 1.0 + 1, "Type mismatch: can not convert text to number" -- }) -test:do_execsql_test( +test:do_catchsql_test( 4.4, [[ SELECT x FROM t3 WHERE x IN ('1.0'); ]], { -- <4.4> - 1.0 + 1, "Type mismatch: can not convert text to number" -- }) @@ -303,23 +303,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 4.7, [[ SELECT x FROM t3 WHERE '1' IN (x); ]], { -- <4.7> - 1 + 1, "Type mismatch: can not convert text to number" -- }) -test:do_execsql_test( +test:do_catchsql_test( 4.8, [[ SELECT x FROM t3 WHERE '1.0' IN (x); ]], { -- <4.8> - 1 + 1, "Type mismatch: can not convert text to number" -- }) @@ -343,23 +343,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.3, [[ SELECT x FROM t4 WHERE x IN ('1'); ]], { -- <5.3> - + 1, "Type mismatch: can not convert text to number" -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.4, [[ SELECT x FROM t4 WHERE x IN ('1.0'); ]], { -- <5.4> - + 1, "Type mismatch: can not convert text to number" -- }) @@ -373,13 +373,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.6, [[ SELECT x FROM t4 WHERE x IN ('1.11'); ]], { -- <5.6> - 1.11 + 1, "Type mismatch: can not convert text to number" -- }) @@ -403,23 +403,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.9, [[ SELECT x FROM t4 WHERE '1' IN (x); ]], { -- <5.9> - + 1, "Type mismatch: can not convert text to number" -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.10, [[ SELECT x FROM t4 WHERE '1.0' IN (x); ]], { -- <5.10> - + 1, "Type mismatch: can not convert text to number" -- }) @@ -433,13 +433,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.12, [[ SELECT x FROM t4 WHERE '1.11' IN (x); ]], { -- <5.12> - 1.11 + 1, "Type mismatch: can not convert text to number" -- }) @@ -463,23 +463,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 6.3, [[ SELECT x, y FROM t5 WHERE x IN ('1'); ]], { -- <6.3> - 1, "one", 1, "two", 1, "three", 1.0, "four" + 1, "Type mismatch: can not convert text to unsigned" -- }) -test:do_execsql_test( +test:do_catchsql_test( 6.4, [[ SELECT x, y FROM t5 WHERE x IN ('1.0'); ]], { -- <6.4> - 1, "one", 1, "two", 1, "three", 1.0, "four" + 1, "Type mismatch: can not convert text to unsigned" -- }) @@ -503,23 +503,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 6.7, [[ SELECT x, y FROM t5 WHERE '1' IN (x); ]], { -- <6.7> - 1, "one", 1, "two", 1, "three", 1.0, "four" + 1, "Type mismatch: can not convert text to unsigned" -- }) -test:do_execsql_test( +test:do_catchsql_test( 6.8, [[ SELECT x, y FROM t5 WHERE '1.0' IN (x); ]], { -- <6.8> - 1, "one", 1, "two", 1, "three", 1, "four" + 1, "Type mismatch: can not convert text to unsigned" -- }) diff --git a/test/sql-tap/tkt-f973c7ac31.test.lua b/test/sql-tap/tkt-f973c7ac31.test.lua index 82bdb52..6bf7884 100755 --- a/test/sql-tap/tkt-f973c7ac31.test.lua +++ b/test/sql-tap/tkt-f973c7ac31.test.lua @@ -20,7 +20,7 @@ test:plan(21) test:do_execsql_test( "tkt-f973c7ac3-1.0", [[ - CREATE TABLE t(id INT primary key, c1 INTEGER, c2 INTEGER); + CREATE TABLE t(id INT primary key, c1 UNSIGNED, c2 UNSIGNED); INSERT INTO t VALUES(1, 5, 5); INSERT INTO t VALUES(2, 5, 4); ]], { @@ -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 text to unsigned" }) test:do_execsql_test( @@ -52,36 +52,36 @@ for tn, sql in ipairs(sqls) do 5, 5, 5, 4 }) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".3", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 DESC ]], { - 5, 5, 5, 4 + 1,"Type mismatch: can not convert text to unsigned" }) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".4", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 DESC ]], { - 5, 5, 5, 4 + 1,"Type mismatch: can not convert text to unsigned" }) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".5", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 DESC ]], { - 5, 5, 5, 4 + 1,"Type mismatch: can not convert text to unsigned" }) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".6", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 ASC ]], { - + 1,"Type mismatch: can not convert text to unsigned" }) test:do_execsql_test( @@ -92,28 +92,28 @@ for tn, sql in ipairs(sqls) do 5, 4, 5, 5 }) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".8", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 ASC ]], { - 5, 4, 5, 5 + 1,"Type mismatch: can not convert text to unsigned" }) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".9", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 ASC ]], { - 5, 4, 5, 5 + 1,"Type mismatch: can not convert text to unsigned" }) - test:do_execsql_test( + test:do_catchsql_test( "tkt-f973c7ac3-1."..tn..".10", [[ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 ASC ]], { - 5, 4, 5, 5 + 1,"Type mismatch: can not convert text to unsigned" }) end diff --git a/test/sql-tap/tkt-fc7bd6358f.test.lua b/test/sql-tap/tkt-fc7bd6358f.test.lua index fe5d620..8b99f8e 100755 --- a/test/sql-tap/tkt-fc7bd6358f.test.lua +++ b/test/sql-tap/tkt-fc7bd6358f.test.lua @@ -78,17 +78,17 @@ for a, from in ipairs(froms) do test:do_test( string.format("tkt-fc7bd6358f.110.%s.%s.1", a, b), function() - return test:execsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where)) + return test:catchsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where)) end, { - "12", 12, "12", "34", 34, "34" + 1, "Type mismatch: can not convert text to integer" }) test:do_test( string.format("tkt-fc7bd6358f.110.%s.%s.2", a, b), function() - return test:execsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where)) + return test:catchsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where)) end, { - "12", 12, "12", "34", 34, "34" + 1, "Type mismatch: can not convert text to integer" }) end diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua index 7ceec47..e5d7b2f 100755 --- a/test/sql-tap/tkt3493.test.lua +++ b/test/sql-tap/tkt3493.test.lua @@ -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,7 +126,7 @@ 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 @@ -136,7 +136,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.2.2", [[ - SELECT a=123 FROM t1 + SELECT a = '123' FROM t1; ]], { -- true @@ -156,7 +156,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 +166,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 +176,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 +186,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 +196,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 +206,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 +216,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 +226,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 +236,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 e960565..cc7e066 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" @@ -338,7 +338,7 @@ test:do_execsql_test( ON tvshow.idshow = episode.idshow LEFT JOIN seasons ON seasons.idshow = episode.idshow - AND seasons.season = episode.c12 + AND seasons.season = CAST(episode.c12 AS INTEGER) JOIN path ON files.idpath = path.idpath LEFT JOIN bookmark @@ -378,7 +378,7 @@ test:do_execsql_test( FROM episodeview JOIN tvshowview ON tvshowview.idShow = episodeview.idShow JOIN seasons ON (seasons.idShow = tvshowview.idShow - AND seasons.season = episodeview.c12) + AND seasons.season = CAST(episodeview.c12 AS INTEGER)) JOIN files ON files.idFile = episodeview.idFile JOIN tvshowlinkpath ON tvshowlinkpath.idShow = tvshowview.idShow JOIN path ON path.idPath = tvshowlinkpath.idPath @@ -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 f267be8..ca6b480 100755 --- a/test/sql-tap/where2.test.lua +++ b/test/sql-tap/where2.test.lua @@ -641,7 +641,7 @@ test:do_test( -- 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; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE CAST(a AS INTEGER)=b; ]]) end, { -- @@ -655,7 +655,7 @@ test:do_test( return queryplan([[ -- The + operator doesn't affect RHS. -- - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE CAST(a AS INTEGER)=+b; ]]) end, { -- @@ -668,7 +668,7 @@ test:do_test( function() -- The same thing but with the expression flipped around. return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=CAST(a AS INTEGER) ]]) end, { -- @@ -680,7 +680,7 @@ test:do_test( "where2-6.10", function() return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +a=+b; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +CAST(a AS INTEGER)=+b; ]]) end, { -- @@ -694,7 +694,7 @@ test:do_test( -- 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'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE CAST(a AS INTEGER)=b OR a='hello'; ]]) end, { -- @@ -707,7 +707,7 @@ test:do_test( function() -- Permutations of the expression terms. return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE b=CAST(a AS INTEGER) OR a='hello'; ]]) end, { -- @@ -720,7 +720,7 @@ test:do_test( function() -- Permutations of the expression terms. return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=CAST(a AS INTEGER); ]]) end, { -- @@ -733,7 +733,7 @@ test:do_test( function() -- Permutations of the expression terms. return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=CAST(a AS INTEGER); ]]) end, { -- @@ -750,7 +750,7 @@ test:do_test( "where2-6.12", function() return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE CAST(a AS INTEGER)=+b OR a='hello'; ]]) end, { -- @@ -762,7 +762,7 @@ test:do_test( "where2-6.12.2", function() return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=CAST(a AS INTEGER); ]]) end, { -- @@ -774,7 +774,7 @@ test:do_test( "where2-6.12.3", function() return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=CAST(a AS INTEGER) OR a='hello'; ]]) end, { -- @@ -788,7 +788,7 @@ test:do_test( -- 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'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE CAST(a AS INTEGER)=+b OR +a='hello'; ]]) end, { -- diff --git a/test/sql-tap/where5.test.lua b/test/sql-tap/where5.test.lua index 7492015..fc532c5 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 * FROM t1; ]] return test:execsql [[ - SELECT * FROM t1 WHERE x<0 + SELECT * FROM t1 WHERE CAST(x AS INTEGER)<0 ]] end, { -- @@ -43,7 +43,7 @@ end, { }) test:do_execsql_test("where5-1.1", [[ - SELECT * FROM t1 WHERE x<=0 + SELECT * FROM t1 WHERE CAST(x AS INTEGER)<=0 ]], { -- '-1', '0' @@ -51,7 +51,7 @@ test:do_execsql_test("where5-1.1", [[ }) test:do_execsql_test("where5-1.2", [[ - SELECT * FROM t1 WHERE x=0 + SELECT * FROM t1 WHERE CAST(x AS INTEGER)=0 ]], { -- '0' @@ -59,7 +59,7 @@ test:do_execsql_test("where5-1.2", [[ }) test:do_execsql_test("where5-1.3", [[ - SELECT * FROM t1 WHERE x>=0 + SELECT * FROM t1 WHERE CAST(x AS INTEGER)>=0 ]], { -- '0', '1' @@ -67,7 +67,7 @@ test:do_execsql_test("where5-1.3", [[ }) test:do_execsql_test("where5-1.4", [[ - SELECT * FROM t1 WHERE x>0 + SELECT * FROM t1 WHERE CAST(x AS INTEGER)>0 ]], { -- '1' @@ -75,7 +75,7 @@ test:do_execsql_test("where5-1.4", [[ }) test:do_execsql_test("where5-1.5", [[ - SELECT * FROM t1 WHERE x<>0 + SELECT * FROM t1 WHERE CAST(x AS INTEGER)<>0 ]], { -- '-1', '1' diff --git a/test/sql-tap/whereB.test.lua b/test/sql-tap/whereB.test.lua index d98645f..eb11ba3 100755 --- a/test/sql-tap/whereB.test.lua +++ b/test/sql-tap/whereB.test.lua @@ -37,7 +37,7 @@ test:do_execsql_test( CREATE INDEX t2b ON t2(b); INSERT INTO t2 VALUES(2,'99'); - SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a; + SELECT x, a, y=CAST(b AS INTEGER) FROM t1, t2 ORDER BY +x, +a; ]], { -- @@ -48,7 +48,7 @@ test:do_execsql_test( test:do_execsql_test( "whereB-1.2", [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; + SELECT x, a, y=CAST(b AS INTEGER) FROM t1, t2 WHERE y=CAST(b AS INTEGER); ]], { -- @@ -59,7 +59,7 @@ test:do_execsql_test( test:do_execsql_test( "whereB-1.3", [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; + SELECT x, a, y=CAST(b AS INTEGER) FROM t1, t2 WHERE CAST(b AS INTEGER)=y; ]], { -- @@ -70,7 +70,7 @@ test:do_execsql_test( test:do_execsql_test( "whereB-1.4", [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; + SELECT x, a, y=CAST(b AS INTEGER) FROM t1, t2 WHERE +y=+CAST(b AS INTEGER); ]], { -- @@ -82,7 +82,7 @@ test:do_execsql_test( "whereB-1.100", [[ DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; + SELECT x, a, y=CAST(b AS INTEGER) FROM t1, t2 WHERE y=CAST(b AS INTEGER); ]], { -- @@ -93,7 +93,7 @@ test:do_execsql_test( test:do_execsql_test( "whereB-1.101", [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; + SELECT x, a, y=CAST(b AS INTEGER) FROM t1, t2 WHERE CAST(b AS INTEGER)=y; ]], { -- @@ -104,7 +104,7 @@ test:do_execsql_test( test:do_execsql_test( "whereB-1.102", [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; + SELECT x, a, y=CAST(b AS INTEGER) FROM t1, t2 WHERE +y=+CAST(b AS INTEGER); ]], { -- @@ -329,29 +329,29 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, 2, false -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-4.2", [[ SELECT x, a, y=b FROM t1, t2 WHERE y=b; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert text to number" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-4.3", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert text to number" -- }) @@ -364,7 +364,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -376,7 +375,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -387,7 +385,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -400,7 +397,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -429,29 +425,29 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, 2, false -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-5.2", [[ SELECT x, a, y=b FROM t1, t2 WHERE y=b; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert text to integer" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-5.3", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert text to integer" -- }) @@ -464,7 +460,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -476,7 +471,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -487,7 +481,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -500,7 +493,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -529,29 +521,29 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, 2, false -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-6.2", [[ SELECT x, a, y=b FROM t1, t2 WHERE y=b; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert text to number" -- }) -test:do_execsql_test( +test:do_catchsql_test( "whereB-6.3", [[ SELECT x, a, y=b FROM t1, t2 WHERE b=y; ]], { -- - 1, 2, true + 1, "Type mismatch: can not convert text to number" -- }) @@ -564,7 +556,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -576,7 +567,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -587,7 +577,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -600,7 +589,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -629,7 +617,7 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, 2, false -- }) @@ -640,7 +628,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -651,7 +638,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -664,7 +650,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -676,7 +661,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -687,7 +671,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -700,7 +683,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -729,7 +711,7 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, 2, false -- }) @@ -740,7 +722,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -751,7 +732,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -764,7 +744,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -776,7 +755,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -787,7 +765,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -800,7 +777,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -829,7 +805,7 @@ test:do_execsql_test( ]], { -- - 1, 2, true + 1, 2, false -- }) @@ -840,7 +816,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -851,7 +826,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -864,7 +838,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -876,7 +849,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -887,7 +859,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) @@ -900,7 +871,6 @@ test:do_execsql_test( ]], { -- - 1, 2, true -- }) diff --git a/test/sql-tap/whereC.test.lua b/test/sql-tap/whereC.test.lua index 89459de..2f214ef 100755 --- a/test/sql-tap/whereC.test.lua +++ b/test/sql-tap/whereC.test.lua @@ -1,7 +1,7 @@ #!/usr/bin/env tarantool test = require("sqltester") local yaml = require('yaml') -test:plan(41) +test:plan(29) --!./tcltestrunner.lua -- 2011 November 16 @@ -54,10 +54,6 @@ test:do_execsql_test( --local function main() test:test("main", function() local data = {{"SELECT i FROM t1 WHERE a=1 AND b=2 AND i>3", {4, 5}}, - -- {"SELECT i FROM t1 WHERE rowid='12'", {12}}, - {"SELECT i FROM t1 WHERE a=1 AND b='2'", {3, 4, 5}}, - {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i>'3'", {4, 5}}, - {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i<5", {3, 4}}, {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i<12", {10, 11}}, {"SELECT i FROM t1 WHERE a IN(1, 2) AND b=2 AND i<11", {3, 4, 5, 10}}, {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 12", {10, 11, 12}}, @@ -65,9 +61,7 @@ test:test("main", function() {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 11", {10, 11}}, {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 12 AND 10", {}}, {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i=NULL", {}}, - {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i<4.5", {3, 4}}} - -- {"SELECT i FROM t1 WHERE rowid IS '12'", {12}}} + {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i>=NULL", {}}} for tn, t in ipairs(data) do test:do_execsql_test( diff --git a/test/sql/boolean.result b/test/sql/boolean.result index 112e41a..483873b 100644 --- a/test/sql/boolean.result +++ b/test/sql/boolean.result @@ -137,13 +137,19 @@ INSERT INTO ts(s) VALUES ('abc'), (12.5); | ... SELECT s FROM ts WHERE s = true; | --- - | - null - | - 'Type mismatch: can not convert text to boolean' + | - metadata: + | - name: S + | type: scalar + | rows: + | - [true] | ... SELECT s FROM ts WHERE s < true; | --- - | - null - | - 'Type mismatch: can not convert text to boolean' + | - metadata: + | - name: S + | type: scalar + | rows: + | - [false] | ... SELECT s FROM ts WHERE s IN (true, 1, 'abcd'); | --- @@ -3389,22 +3395,22 @@ SELECT false < 2; SELECT 2 > true; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT 2 > false; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT 2 < true; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT 2 < false; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a1, a1 > 2 FROM t6 @@ -3420,12 +3426,12 @@ SELECT a1, a1 < 2 FROM t6 SELECT a1, 2 > a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a1, 2 < a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a2, a2 > 2 FROM t6 | --- @@ -3440,12 +3446,12 @@ SELECT a2, a2 < 2 FROM t6 SELECT a2, 2 > a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a2, 2 < a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT b, true > b FROM t7; @@ -3471,22 +3477,22 @@ SELECT b, false < b FROM t7; SELECT b, b > true FROM t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT b, b > false FROM t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT b, b < true FROM t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT b, b < false FROM t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a1, b, a1 > b FROM t6, t7; @@ -3502,12 +3508,12 @@ SELECT a1, b, a1 < b FROM t6, t7; SELECT a1, b, b > a1 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a1, b, b < a1 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a2, b, a2 > b FROM t6, t7; | --- @@ -3522,12 +3528,12 @@ SELECT a2, b, a2 < b FROM t6, t7; SELECT a2, b, b > a2 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a2, b, b < a2 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT true >= 2; @@ -3553,22 +3559,22 @@ SELECT false <= 2; SELECT 2 >= true; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT 2 >= false; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT 2 <= true; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT 2 <= false; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a1, a1 >= 2 FROM t6 @@ -3584,12 +3590,12 @@ SELECT a1, a1 <= 2 FROM t6 SELECT a1, 2 >= a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a1, 2 <= a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a2, a2 >= 2 FROM t6 | --- @@ -3604,12 +3610,12 @@ SELECT a2, a2 <= 2 FROM t6 SELECT a2, 2 >= a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a2, 2 <= a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT b, true >= b FROM t7; @@ -3635,22 +3641,22 @@ SELECT b, false <= b FROM t7; SELECT b, b >= true FROM t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT b, b >= false FROM t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT b, b <= true FROM t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT b, b <= false FROM t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a1, b, a1 >= b FROM t6, t7; @@ -3666,12 +3672,12 @@ SELECT a1, b, a1 <= b FROM t6, t7; SELECT a1, b, b >= a1 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a1, b, b <= a1 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a2, b, a2 >= b FROM t6, t7; | --- @@ -3686,12 +3692,12 @@ SELECT a2, b, a2 <= b FROM t6, t7; SELECT a2, b, b >= a2 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a2, b, b <= a2 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT true == 2; @@ -3717,22 +3723,22 @@ SELECT false != 2; SELECT 2 == true; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT 2 == false; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT 2 != true; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT 2 != false; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a1, a1 == 2 FROM t6 @@ -3748,12 +3754,12 @@ SELECT a1, a1 != 2 FROM t6 SELECT a1, 2 == a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a1, 2 != a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a2, a2 == 2 FROM t6 | --- @@ -3768,12 +3774,12 @@ SELECT a2, a2 != 2 FROM t6 SELECT a2, 2 == a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a2, 2 != a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT b, true == b FROM t7; @@ -3799,22 +3805,22 @@ SELECT b, false != b FROM t7; SELECT b, b == true FROM t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT b, b == false FROM t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT b, b != true FROM t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT b, b != false FROM t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a1, b, a1 == b FROM t6, t7; @@ -3830,12 +3836,12 @@ SELECT a1, b, a1 != b FROM t6, t7; SELECT a1, b, b == a1 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a1, b, b != a1 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a2, b, a2 == b FROM t6, t7; | --- @@ -3850,12 +3856,12 @@ SELECT a2, b, a2 != b FROM t6, t7; SELECT a2, b, b == a2 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT a2, b, b != a2 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert unsigned to boolean' + | - 'Type mismatch: can not convert boolean to unsigned' | ... SELECT true IN (0, 1, 2, 3); @@ -4534,22 +4540,22 @@ SELECT false < 2.3; SELECT 2.3 > true; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT 2.3 > false; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT 2.3 < true; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT 2.3 < false; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a1, a1 > 2.3 FROM t6 @@ -4565,12 +4571,12 @@ SELECT a1, a1 < 2.3 FROM t6 SELECT a1, 2.3 > a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a1, 2.3 < a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a2, a2 > 2.3 FROM t6 | --- @@ -4585,12 +4591,12 @@ SELECT a2, a2 < 2.3 FROM t6 SELECT a2, 2.3 > a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a2, 2.3 < a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT c, true > c FROM t8; @@ -4616,22 +4622,22 @@ SELECT c, false < c FROM t8; SELECT c, c > true FROM t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT c, c > false FROM t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT c, c < true FROM t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT c, c < false FROM t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a1, c, a1 > c FROM t6, t8; @@ -4647,12 +4653,12 @@ SELECT a1, c, a1 < c FROM t6, t8; SELECT a1, c, c > a1 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a1, c, c < a1 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a2, c, a2 > c FROM t6, t8; | --- @@ -4667,12 +4673,12 @@ SELECT a2, c, a2 < c FROM t6, t8; SELECT a2, c, c > a2 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a2, c, c < a2 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT true >= 2.3; @@ -4698,22 +4704,22 @@ SELECT false <= 2.3; SELECT 2.3 >= true; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT 2.3 >= false; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT 2.3 <= true; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT 2.3 <= false; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a1, a1 >= 2.3 FROM t6 @@ -4729,12 +4735,12 @@ SELECT a1, a1 <= 2.3 FROM t6 SELECT a1, 2.3 >= a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a1, 2.3 <= a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a2, a2 >= 2.3 FROM t6 | --- @@ -4749,12 +4755,12 @@ SELECT a2, a2 <= 2.3 FROM t6 SELECT a2, 2.3 >= a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a2, 2.3 <= a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT c, true >= c FROM t8; @@ -4780,22 +4786,22 @@ SELECT c, false <= c FROM t8; SELECT c, c >= true FROM t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT c, c >= false FROM t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT c, c <= true FROM t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT c, c <= false FROM t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a1, c, a1 >= c FROM t6, t8; @@ -4811,12 +4817,12 @@ SELECT a1, c, a1 <= c FROM t6, t8; SELECT a1, c, c >= a1 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a1, c, c <= a1 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a2, c, a2 >= c FROM t6, t8; | --- @@ -4831,12 +4837,12 @@ SELECT a2, c, a2 <= c FROM t6, t8; SELECT a2, c, c >= a2 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a2, c, c <= a2 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT true == 2.3; @@ -4862,22 +4868,22 @@ SELECT false != 2.3; SELECT 2.3 == true; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT 2.3 == false; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT 2.3 != true; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT 2.3 != false; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a1, a1 == 2.3 FROM t6 @@ -4893,12 +4899,12 @@ SELECT a1, a1 != 2.3 FROM t6 SELECT a1, 2.3 == a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a1, 2.3 != a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a2, a2 == 2.3 FROM t6 | --- @@ -4913,12 +4919,12 @@ SELECT a2, a2 != 2.3 FROM t6 SELECT a2, 2.3 == a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a2, 2.3 != a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT c, true == c FROM t8; @@ -4944,22 +4950,22 @@ SELECT c, false != c FROM t8; SELECT c, c == true FROM t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT c, c == false FROM t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT c, c != true FROM t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT c, c != false FROM t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a1, c, a1 == c FROM t6, t8; @@ -4975,12 +4981,12 @@ SELECT a1, c, a1 != c FROM t6, t8; SELECT a1, c, c == a1 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a1, c, c != a1 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a2, c, a2 == c FROM t6, t8; | --- @@ -4995,12 +5001,12 @@ SELECT a2, c, a2 != c FROM t6, t8; SELECT a2, c, c == a2 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT a2, c, c != a2 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert real to boolean' + | - 'Type mismatch: can not convert boolean to real' | ... SELECT true IN (0.1, 1.2, 2.3, 3.4); @@ -5290,22 +5296,22 @@ SELECT false < 'abc'; SELECT 'abc' > true; | --- | - null - | - 'Type mismatch: can not convert text to boolean' + | - 'Type mismatch: can not convert boolean to text' | ... SELECT 'abc' > false; | --- | - null - | - 'Type mismatch: can not convert text to boolean' + | - 'Type mismatch: can not convert boolean to text' | ... SELECT 'abc' < true; | --- | - null - | - 'Type mismatch: can not convert text to boolean' + | - 'Type mismatch: can not convert boolean to text' | ... SELECT 'abc' < false; | --- | - null - | - 'Type mismatch: can not convert text to boolean' + | - 'Type mismatch: can not convert boolean to text' | ... SELECT d, true > d FROM t9; @@ -5331,22 +5337,22 @@ SELECT d, false < d FROM t9; SELECT d, d > true FROM t9; | --- | - null - | - 'Type mismatch: can not convert text to boolean' + | - 'Type mismatch: can not convert boolean to text' | ... SELECT d, d > false FROM t9; | --- | - null - | - 'Type mismatch: can not convert text to boolean' + | - 'Type mismatch: can not convert boolean to text' | ... SELECT d, d < true FROM t9; | --- | - null - | - 'Type mismatch: can not convert text to boolean' + | - 'Type mismatch: can not convert boolean to text' | ... SELECT d, d < false FROM t9; | --- | - null - | - 'Type mismatch: can not convert text to boolean' + | - 'Type mismatch: can not convert boolean to text' | ... SELECT a1, d, a1 > d FROM t6, t9; @@ -5362,12 +5368,12 @@ SELECT a1, d, a1 < d FROM t6, t9; SELECT a1, d, d > a1 FROM t6, t9; | --- | - null - | - 'Type mismatch: can not convert text to boolean' + | - 'Type mismatch: can not convert boolean to text' | ... SELECT a1, d, d < a1 FROM t6, t9; | --- | - null - | - 'Type mismatch: can not convert text to boolean' + | - 'Type mismatch: can not convert boolean to text' | ... SELECT a2, d, a2 > d FROM t6, t9; | --- @@ -5382,12 +5388,12 @@ SELECT a2, d, a2 < d FROM t6, t9; SELECT a2, d, d > a2 FROM t6, t9; | --- | - null - | - 'Type mismatch: can not convert text to boolean' + | - 'Type mismatch: can not convert boolean to text' | ... SELECT a2, d, d < a2 FROM t6, t9; | --- | - null - | - 'Type mismatch: can not convert text to boolean' + | - 'Type mismatch: can not convert boolean to text' | ... SELECT true || 'abc'; diff --git a/test/sql/types.result b/test/sql/types.result index 38e4385..97b57a8 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -352,7 +352,7 @@ box.execute("SELECT * FROM tboolean WHERE s1 = x'44';") box.execute("SELECT * FROM tboolean WHERE s1 = 'abc';") --- - null -- 'Type mismatch: can not convert abc to boolean' +- 'Type mismatch: can not convert text to boolean' ... box.execute("SELECT * FROM tboolean WHERE s1 = 1;") --- @@ -614,10 +614,11 @@ box.execute("SELECT 18446744073709551615.0 > 18446744073709551615") rows: - [true] ... -box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)") +box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0, 1)") --- - metadata: - - name: 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0) + - name: 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0, + 1) type: boolean rows: - [true] diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua index bd14b34..f9e2531 100644 --- a/test/sql/types.test.lua +++ b/test/sql/types.test.lua @@ -151,7 +151,7 @@ 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 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0, 1)") box.execute("SELECT 1 LIMIT 18446744073709551615;") box.execute("SELECT 1 LIMIT 1 OFFSET 18446744073709551614;") box.execute("SELECT CAST('18446744073' || '709551616' AS INTEGER);") -- 2.7.4