[Tarantool-patches] [PATCH v1 1/1] sql: remove implicit cast for COMPARISON
Mergen Imeev
imeevma at tarantool.org
Tue Apr 21 15:42:50 MSK 2020
Hi! Thank you for review. My answers and new patch below.
In this patch I removed part about SCALAR behaviour and
part that tries to remove difference in behaviour between
cases we use index and not. These both parts looks too
complicated here. And, now I am not sure that they should
be here. Also, I reduced number of changes in the patch.
On Mon, Mar 30, 2020 at 12:29:22AM +0200, Vladislav Shpilevoy wrote:
> Hi! Thanks for the patch!
>
> See 15 comments below.
>
> I don't think I understood all the changed (because I am very out of
> context), so I will revisited many of them when you send v2.
>
> On 20/03/2020 13:34, imeevma at tarantool.org wrote:
> > 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
>
> 1. Worth adding more details. What implicit cast? All of them?
Even it say that all of them should be removed, actually it
removes only case from string to numbers and from numbers
to string.
>
> Do we need a docbot request for this?
>
Fixed, but not sure that I wrote enough.
> > 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)
>
> 2. Better make it static inline function.
>
Removed.
> > 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.
>
> 3. preperation -> preparation. The same in the next functions.
>
Removed.
> > + * @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,
>
> 4. Opcode has type u8 in UnpackedRecord. Better make it the
> same here. The same in other places.
>
Removed.
> 5. is_eq is a flag, and therefore should be a boolean. The
> same in the next functions.
>
Removed.
> > + 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)
>
> 6. >= 0?
>
Removed.
> > + 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);
>
> 7. Is there a ticket to stop conveting MEM_* to MP_*? MP_* now
> contains all MEM_* and even more, right?
>
I created an issue: #4906. I'm sorry, I wanted to fix it
here, but decided to leave it for later since it took me
too long to make new version of this patch.
> > + 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) {
>
> 8. Is it possible to replace this with 'i == (int64)(double)i' ?
> The same below. Because these double vs int are always tricky.
> And I would like to avoid any constant assumptions here. For
> example, you excluded 2^53, but 2^53 can be stored (from what I
> saw and tested). So it would be more correct to write >= -9007199254740992,
> not >. However the 2-cast solution may work too, and does not
> use constants.
>
Removed.
> > + 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
>
> 9. No mem among the arguments.
>
Removed.
> > + * 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;
> > +}
> > @@ -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;
> > - }
>
> 10. I know these places violated our style, but lets keep
> them. Since they are not stictly related to the patch. We will
> have a chance to fix them later.
>
Fixed.
> > }
> > 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);
>
> 11. The compiler is likely to help us here, but still lets apply '!'.
> It will look
>
> ltype != MP_NIL || rtype != MP_NIL
>
Removed.
> > + } 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) {
>
> 12. This condition and the comment clearly could be kept as is, since
> only formatting is changed. Could you please preserve the old lines?
> For the sake of git blame. Also check other places where it is possible
> to keep the old lines for free (without necessity to make new code
> worse).
>
Fixed.
> > + 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;
> > }> 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);
> > +}
>
> 13. Probably better keep it in one single usage place. Since it adds
> unnecessary dependency on msgpuck.h for tuple_compare.h.
>
Removed.
> > +
> > static enum mp_class
> > mp_extension_class(const char *data)
> > {
> > 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'
>
> 14. Leading whitespace.
>
Removed.
> > ELSE A.val
> > END AS Col1
> > FROM B
> 15. I propose to add some tests specific to the tickets we fix.
> Probably remove some existing tests which test the same. With more
> edge cases. gcov should help to check that all conditions in this
> patch are covered, especially about preparations of Mem to be used
> in a comparison.
Done.
New patch:
>From 58f7b0ee9e840c00d3742a59eb6f660b0dbb213e Mon Sep 17 00:00:00 2001
From: Mergen Imeev <imeevma at gmail.com>
Date: Thu, 16 Apr 2020 12:52:59 +0300
Subject: [PATCH] sql: remove implicit cast from string for comparison
This patch removes implicit cast from strings to numbers for
comparison.
Part of #4230
@TarantoolBot document
Title: remove implicit cast from string to number
After this patch implicit cast from string to number and from
number to string is removed for COMPARISON.
Example:
tarantool> box.execute([[SELECT '1' > 0;]])
---
- null
- 'Type mismatch: can not convert 1 to numeric'
...
tarantool> box.execute([[SELECT "id" FROM "_space" WHERE '1' > "id";]])
---
- null
- 'Type mismatch: can not convert text to unsigned'
...
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 724bc18..34bd38d 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -2209,22 +2209,17 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */
} else {
enum field_type type = pOp->p5 & FIELD_TYPE_MASK;
if (sql_type_is_numeric(type)) {
- if ((flags1 | flags3)&MEM_Str) {
- if ((flags1 & MEM_Str) == MEM_Str) {
- mem_apply_numeric_type(pIn1);
- testcase( flags3!=pIn3->flags); /* Possible if pIn1==pIn3 */
- flags3 = pIn3->flags;
- }
- if ((flags3 & MEM_Str) == MEM_Str) {
- if (mem_apply_numeric_type(pIn3) != 0) {
- diag_set(ClientError,
- ER_SQL_TYPE_MISMATCH,
- sql_value_to_diag_str(pIn3),
- "numeric");
- goto abort_due_to_error;
- }
-
- }
+ if ((flags1 & MEM_Str) == MEM_Str) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ sql_value_to_diag_str(pIn1),
+ "numeric");
+ goto abort_due_to_error;
+ }
+ if ((flags3 & MEM_Str) == MEM_Str) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ sql_value_to_diag_str(pIn3),
+ "numeric");
+ goto abort_due_to_error;
}
/* Handle the common case of integer comparison here, as an
* optimization, to avoid a call to sqlMemCompare()
@@ -2257,22 +2252,17 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */
goto compare_op;
}
} else if (type == FIELD_TYPE_STRING) {
- if ((flags1 & MEM_Str) == 0 &&
- (flags1 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) {
- testcase( pIn1->flags & MEM_Int);
- testcase( pIn1->flags & MEM_Real);
- sqlVdbeMemStringify(pIn1);
- testcase( (flags1&MEM_Dyn) != (pIn1->flags&MEM_Dyn));
- flags1 = (pIn1->flags & ~MEM_TypeMask) | (flags1 & MEM_TypeMask);
- assert(pIn1!=pIn3);
+ if ((flags1 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ mem_type_to_str(pIn3),
+ mem_type_to_str(pIn1));
+ goto abort_due_to_error;
}
- if ((flags3 & MEM_Str) == 0 &&
- (flags3 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) {
- testcase( pIn3->flags & MEM_Int);
- testcase( pIn3->flags & MEM_Real);
- sqlVdbeMemStringify(pIn3);
- testcase( (flags3&MEM_Dyn) != (pIn3->flags&MEM_Dyn));
- flags3 = (pIn3->flags & ~MEM_TypeMask) | (flags3 & MEM_TypeMask);
+ if ((flags3 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ mem_type_to_str(pIn1),
+ mem_type_to_str(pIn3));
+ goto abort_due_to_error;
}
}
assert(pOp->p4type==P4_COLLSEQ || pOp->p4.pColl==0);
@@ -3377,8 +3367,6 @@ case OP_SeekGT: { /* jump, in3 */
pIn3 = &aMem[int_field];
if ((pIn3->flags & MEM_Null) != 0)
goto skip_truncate;
- if ((pIn3->flags & MEM_Str) != 0)
- mem_apply_numeric_type(pIn3);
int64_t i;
if ((pIn3->flags & MEM_Int) == MEM_Int) {
i = pIn3->u.i;
@@ -3471,6 +3459,26 @@ skip_truncate:
assert(oc!=OP_SeekLT || r.default_rc==+1);
r.aMem = &aMem[pOp->p3];
+ for (int i = 0; i < r.nField; ++i) {
+ enum field_type type = r.key_def->parts[i].type;
+ struct Mem *mem = &r.aMem[i];
+ if ((mem->flags & MEM_Str) != 0 && sql_type_is_numeric(type)) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ field_type_strs[type], mem_type_to_str(mem));
+ goto abort_due_to_error;
+ }
+ if (mem_apply_type(mem, type) != 0) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ field_type_strs[type], mem_type_to_str(mem));
+ goto abort_due_to_error;
+ }
+ if ((mem->flags & MEM_Real) != 0 &&
+ (type == FIELD_TYPE_INTEGER ||
+ type == FIELD_TYPE_UNSIGNED)) {
+ res = 1;
+ goto seek_not_found;
+ }
+ }
#ifdef SQL_DEBUG
{ int i; for(i=0; i<r.nField; i++) assert(memIsValid(&r.aMem[i])); }
#endif
@@ -4598,6 +4606,27 @@ case OP_IdxGE: { /* jump */
r.default_rc = 0;
}
r.aMem = &aMem[pOp->p3];
+ for (int i = 0; i < r.nField; ++i) {
+ struct Mem *mem = &r.aMem[i];
+ enum mp_type mp_type = sql_value_type(mem);
+ enum field_type field_type = r.key_def->parts[i].type;
+ if (field_type == FIELD_TYPE_SCALAR ||
+ mem->field_type == FIELD_TYPE_SCALAR)
+ continue;
+ bool is_nullable = r.key_def->parts[i].nullable_action ==
+ ON_CONFLICT_ACTION_NONE;
+ if (field_mp_plain_type_is_compatible(field_type, mp_type,
+ is_nullable))
+ continue;
+ if (!sql_type_is_numeric(field_type) ||
+ !(mp_type == MP_INT || mp_type == MP_UINT ||
+ mp_type == MP_DOUBLE || mp_type == MP_FLOAT)) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ mem_type_to_str(mem),
+ field_type_strs[field_type]);
+ goto abort_due_to_error;
+ }
+ }
#ifdef SQL_DEBUG
{ int i; for(i=0; i<r.nField; i++) assert(memIsValid(&r.aMem[i])); }
#endif
diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c
index 5bc27f1..1d7c766 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,7 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */
sqlVdbeAddOp2(v, OP_SCopy, r1, regBase + j);
}
}
- if (pTerm->eOperator & WO_IN) {
- if (pTerm->pExpr->flags & EP_xIsSelect) {
- /* No type ever needs to be (or should be) applied to a value
- * from the RHS of an "? IN (SELECT ...)" expression. The
- * sqlFindInIndex() routine has already ensured that the
- * type of the comparison has been applied to the value.
- */
- if (type != NULL)
- type[j] = FIELD_TYPE_SCALAR;
- }
- } else if ((pTerm->eOperator & WO_ISNULL) == 0) {
+ if ((pTerm->eOperator & (WO_IN | WO_ISNULL)) == 0) {
Expr *pRight = pTerm->pExpr->pRight;
if (sqlExprCanBeNull(pRight)) {
sqlVdbeAddOp2(v, OP_IsNull, regBase + j,
@@ -733,7 +653,6 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */
}
}
}
- *res_type = type;
return regBase;
}
@@ -904,10 +823,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
int iIdxCur; /* The VDBE cursor for the index */
int nExtraReg = 0; /* Number of extra registers needed */
int op; /* Instruction opcode */
- /* Types for start of range constraint. */
- enum field_type *start_types;
- /* Types for end of range constraint */
- enum field_type *end_types = NULL;
u8 bSeekPastNull = 0; /* True to seek past initial nulls */
u8 bStopAtNull = 0; /* Add condition to terminate at NULLs */
int force_integer_reg = -1; /* If non-negative: number of
@@ -994,17 +909,7 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
* and store the values of those terms in an array of registers
* starting at regBase.
*/
- regBase =
- codeAllEqualityTerms(pParse, pLevel, bRev, nExtraReg,
- &start_types);
- if (start_types != NULL && nTop) {
- uint32_t len = 0;
- for (enum field_type *tmp = &start_types[nEq];
- *tmp != field_type_MAX; tmp++, len++);
- uint32_t sz = len * sizeof(enum field_type);
- end_types = sqlDbMallocRaw(db, sz);
- memcpy(end_types, &start_types[nEq], sz);
- }
+ regBase = codeAllEqualityTerms(pParse, pLevel, bRev, nExtraReg);
addrNxt = pLevel->addrNxt;
testcase(pRangeStart && (pRangeStart->eOperator & WO_LE) != 0);
@@ -1029,10 +934,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
VdbeCoverage(v);
}
- if (start_types) {
- expr_cmp_update_rhs_type(pRight, nBtm,
- &start_types[nEq]);
- }
nConstraint += nBtm;
testcase(pRangeStart->wtFlags & TERM_VIRTUAL);
if (sqlExprIsVector(pRight) == 0) {
@@ -1049,91 +950,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
}
struct index_def *idx_pk = space->index[0]->def;
uint32_t pk_part_count = idx_pk->key_def->part_count;
- /*
- * Tarantool's iterator over integer fields doesn't
- * tolerate floating point values. Hence, if term
- * is equality comparison and value of operand is
- * not integer, we can skip it since it always
- * results in false: INT a == 0.5 -> false;
- * It is done using OP_MustBeInt facilities.
- * In case term is greater comparison (a > ?), we
- * should notify OP_SeekGT to process truncation of
- * floating point value: a > 0.5 -> a >= 1;
- * It is done by setting P5 flag for OP_Seek*.
- * It is worth mentioning that we do not need
- * this step when it comes for less (<) comparison
- * of nullable field. Key is NULL in this case:
- * values are ordered as NULL, ... NULL, min_value,
- * so to fetch min value we pass NULL to GT iterator.
- * The only exception is less comparison in
- * conjunction with ORDER BY DESC clause:
- * in such situation we use LE iterator and
- * truncated value to compare. But then
- * pRangeStart == NULL.
- * This procedure is correct for compound index:
- * only one comparison of less/greater type can be
- * used at the same time. For instance,
- * a < 1.5 AND b > 0.5 is handled by SeekGT using
- * column a and fetching column b from tuple and
- * OP_Le comparison.
- *
- * Note that OP_ApplyType, which is emitted before
- * OP_Seek** doesn't truncate floating point to
- * integer. That's why we need this routine.
- * Also, note that terms are separated by OR
- * predicates, so we consider term as sequence
- * of AND'ed predicates.
- */
- size_t addrs_sz = sizeof(int) * nEq;
- int *seek_addrs = region_alloc(&pParse->region, addrs_sz);
- if (seek_addrs == NULL) {
- diag_set(OutOfMemory, addrs_sz, "region", "seek_addrs");
- pParse->is_aborted = true;
- return 0;
- }
- memset(seek_addrs, 0, addrs_sz);
- for (int i = 0; i < nEq; i++) {
- enum field_type type = idx_def->key_def->parts[i].type;
- if (type == FIELD_TYPE_INTEGER ||
- type == FIELD_TYPE_UNSIGNED) {
- /*
- * OP_MustBeInt consider NULLs as
- * non-integer values, so firstly
- * check whether value is NULL or not.
- */
- seek_addrs[i] = sqlVdbeAddOp1(v, OP_IsNull,
- regBase);
- sqlVdbeAddOp2(v, OP_MustBeInt, regBase + i,
- addrNxt);
- start_types[i] = FIELD_TYPE_SCALAR;
- /*
- * We need to notify column cache
- * that type of value may change
- * so we should fetch value from
- * tuple again rather then copy
- * from register.
- */
- sql_expr_type_cache_change(pParse, regBase + i,
- 1);
- }
- }
- /* Inequality constraint comes always at the end of list. */
- part_count = idx_def->key_def->part_count;
- if (pRangeStart != NULL) {
- /*
- * nEq == 0 means that filter condition
- * contains only inequality.
- */
- uint32_t ineq_idx = nEq == 0 ? 0 : nEq - 1;
- assert(ineq_idx < part_count);
- enum field_type ineq_type =
- idx_def->key_def->parts[ineq_idx].type;
- if (ineq_type == FIELD_TYPE_INTEGER ||
- ineq_type == FIELD_TYPE_UNSIGNED)
- force_integer_reg = regBase + nEq;
- }
- emit_apply_type(pParse, regBase, nConstraint - bSeekPastNull,
- start_types);
if (pLoop->nSkip > 0 && nConstraint == pLoop->nSkip) {
/* The skip-scan logic inside the call to codeAllEqualityConstraints()
* above has already left the cursor sitting on the correct row,
@@ -1143,10 +959,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
op = aStartOp[(start_constraints << 2) +
(startEq << 1) + bRev];
assert(op != 0);
- for (uint32_t i = 0; i < nEq; ++i) {
- if (seek_addrs[i] != 0)
- sqlVdbeJumpHere(v, seek_addrs[i]);
- }
sqlVdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase,
nConstraint);
/* If this is Seek* opcode, and IPK is detected in the
@@ -1186,13 +998,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
addrNxt);
VdbeCoverage(v);
}
- if (end_types) {
- expr_cmp_update_rhs_type(pRight, nTop, end_types);
- emit_apply_type(pParse, regBase + nEq, nTop,
- end_types);
- } else {
- assert(pParse->db->mallocFailed);
- }
nConstraint += nTop;
testcase(pRangeEnd->wtFlags & TERM_VIRTUAL);
@@ -1206,8 +1011,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
endEq = 0;
nConstraint++;
}
- sqlDbFree(db, start_types);
- sqlDbFree(db, end_types);
/* Top of the loop body */
pLevel->p2 = sqlVdbeCurrentAddr(v);
diff --git a/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua
new file mode 100755
index 0000000..ef4127e
--- /dev/null
+++ b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua
@@ -0,0 +1,78 @@
+#!/usr/bin/env tarantool
+test = require("sqltester")
+test:plan(8)
+
+--
+-- Make sure that there is no implicit cast between string and
+-- number.
+--
+test:do_catchsql_test(
+ "gh-4230-1",
+ [[
+ SELECT '1' > 0;
+ ]], {
+ 1, "Type mismatch: can not convert 1 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-2",
+ [[
+ SELECT 0 > '1';
+ ]], {
+ 1, "Type mismatch: can not convert 1 to numeric"
+ })
+
+test:execsql([[
+ CREATE TABLE t (i INT PRIMARY KEY, d DOUBLE, n NUMBER, s STRING);
+ INSERT INTO t VALUES (1, 1.0, 1, '2'), (2, 2.0, 2.0, '2');
+ ]])
+
+test:do_catchsql_test(
+ "gh-4230-3",
+ [[
+ SELECT * from t where i > s;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-4",
+ [[
+ SELECT * from t WHERE s > i;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-5",
+ [[
+ SELECT * from t WHERE d > s;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-6",
+ [[
+ SELECT * from t WHERE s > d;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-7",
+ [[
+ SELECT * from t WHERE i = 1 and n > s;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-8",
+ [[
+ SELECT * from t WHERE i = 2 and s > n;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:finish_test()
diff --git a/test/sql-tap/identifier_case.test.lua b/test/sql-tap/identifier_case.test.lua
index 2a00626..1d56ffb 100755
--- a/test/sql-tap/identifier_case.test.lua
+++ b/test/sql-tap/identifier_case.test.lua
@@ -242,11 +242,11 @@ data = {
{ 2, [[ 'a' < 'b' collate "binary" ]], {0, {true}}},
{ 3, [[ 'a' < 'b' collate 'binary' ]], {1, [[Syntax error at line 1 near ''binary'']]}},
{ 4, [[ 'a' < 'b' collate "unicode" ]], {0, {true}}},
- { 5, [[ 5 < 'b' collate "unicode" ]], {0, {true}}},
+ { 5, [[ 5 < 'b' collate "unicode" ]], {1, "Type mismatch: can not convert b to numeric"}},
{ 6, [[ 5 < 'b' collate unicode ]], {1,"Collation 'UNICODE' does not exist"}},
- { 7, [[ 5 < 'b' collate "unicode_ci" ]], {0, {true}}},
+ { 7, [[ 5 < 'b' collate "unicode_ci" ]], {1, "Type mismatch: can not convert b to numeric"}},
{ 8, [[ 5 < 'b' collate NONE ]], {1, "Collation 'NONE' does not exist"}},
- { 9, [[ 5 < 'b' collate "none" ]], {0, {true}}},
+ { 9, [[ 5 < 'b' collate "none" ]], {1, "Type mismatch: can not convert b to numeric"}},
}
for _, row in ipairs(data) do
diff --git a/test/sql-tap/in1.test.lua b/test/sql-tap/in1.test.lua
index 570cc17..e2f4988 100755
--- a/test/sql-tap/in1.test.lua
+++ b/test/sql-tap/in1.test.lua
@@ -637,12 +637,12 @@ test:do_test(
"in-11.2",
function()
-- The '2' should be coerced into 2 because t6.b is NUMERIC
- return test:execsql [[
+ return test:catchsql [[
SELECT * FROM t6 WHERE b IN ('2');
]]
end, {
-- <in-11.2>
- 1, 2
+ 1, "Type mismatch: can not convert 2 to numeric"
-- </in-11.2>
})
diff --git a/test/sql-tap/in4.test.lua b/test/sql-tap/in4.test.lua
index 8c69173..17a9150 100755
--- a/test/sql-tap/in4.test.lua
+++ b/test/sql-tap/in4.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(61)
+test:plan(52)
--!./tcltestrunner.lua
-- 2008 September 1
@@ -147,13 +147,13 @@ test:do_execsql_test(
-- </in4-2.7>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"in4-2.8",
[[
SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2')
]], {
-- <in4-2.8>
- "two"
+ 1, "Type mismatch: can not convert integer to text"
-- </in4-2.8>
})
@@ -585,98 +585,6 @@ test:do_execsql_test(
-- </in4-4.6>
})
-test:do_execsql_test(
- "in4-4.11",
- [[
- CREATE TABLE t4b(a TEXT, b NUMBER, c INT PRIMARY KEY);
- INSERT INTO t4b VALUES('1.0',1,4);
- SELECT c FROM t4b WHERE a=b;
- ]], {
- -- <in4-4.11>
- 4
- -- </in4-4.11>
- })
-
-test:do_execsql_test(
- "in4-4.12",
- [[
- SELECT c FROM t4b WHERE b=a;
- ]], {
- -- <in4-4.12>
- 4
- -- </in4-4.12>
- })
-
-test:do_execsql_test(
- "in4-4.13",
- [[
- SELECT c FROM t4b WHERE +a=b;
- ]], {
- -- <in4-4.13>
- 4
- -- </in4-4.13>
- })
-
-test:do_execsql_test(
- "in4-4.14",
- [[
- SELECT c FROM t4b WHERE a=+b;
- ]], {
- -- <in4-4.14>
- 4
- -- </in4-4.14>
- })
-
-test:do_execsql_test(
- "in4-4.15",
- [[
- SELECT c FROM t4b WHERE +b=a;
- ]], {
- -- <in4-4.15>
- 4
- -- </in4-4.15>
- })
-
-test:do_execsql_test(
- "in4-4.16",
- [[
- SELECT c FROM t4b WHERE b=+a;
- ]], {
- -- <in4-4.16>
- 4
- -- </in4-4.16>
- })
-
-test:do_execsql_test(
- "in4-4.17",
- [[
- SELECT c FROM t4b WHERE a IN (b);
- ]], {
- -- <in4-4.17>
- 4
- -- </in4-4.17>
- })
-
-test:do_execsql_test(
- "in4-4.18",
- [[
- SELECT c FROM t4b WHERE b IN (a);
- ]], {
- -- <in4-4.18>
- 4
- -- </in4-4.18>
- })
-
-test:do_execsql_test(
- "in4-4.19",
- [[
- SELECT c FROM t4b WHERE +b IN (a);
- ]], {
- -- <in4-4.19>
- 4
- -- </in4-4.19>
- })
-
-- MUST_WORK_TEST
-- Tarantool: TBI: Need to support collations. Depends on #2121
-- test:do_execsql_test(
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 b6b1866..1823109 100755
--- a/test/sql-tap/intpkey.test.lua
+++ b/test/sql-tap/intpkey.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(40)
+test:plan(39)
--!./tcltestrunner.lua
-- 2001 September 15
@@ -859,43 +859,33 @@ test:do_execsql_test(
-- </intpkey-14.2>
})
-test:do_execsql_test(
- "intpkey-14.3",
- [[
- SELECT * FROM t3 WHERE a>'2';
- ]], {
- -- <intpkey-14.3>
- 3, 3, "3"
- -- </intpkey-14.3>
- })
-
-test:do_execsql_test(
+test:do_catchsql_test(
"intpkey-14.4",
[[
SELECT * FROM t3 WHERE a<'2';
]], {
-- <intpkey-14.4>
- 1, 1, "one"
+ 1, "Type mismatch: can not convert text to integer"
-- </intpkey-14.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"intpkey-14.5",
[[
SELECT * FROM t3 WHERE a<c;
]], {
-- <intpkey-14.5>
- 1, 1, "one"
+ 1, "Type mismatch: can not convert one to numeric"
-- </intpkey-14.5>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"intpkey-14.6",
[[
SELECT * FROM t3 WHERE a=c;
]], {
-- <intpkey-14.6>
- 2, 2, "2", 3, 3, "3"
+ 1, "Type mismatch: can not convert one to numeric"
-- </intpkey-14.6>
})
diff --git a/test/sql-tap/join.test.lua b/test/sql-tap/join.test.lua
index 840b780..792302a 100755
--- a/test/sql-tap/join.test.lua
+++ b/test/sql-tap/join.test.lua
@@ -1028,23 +1028,23 @@ test:do_test(
-- </join-11.8>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"join-11.9",
[[
SELECT * FROM t1 NATURAL JOIN t2
]], {
-- <join-11.9>
- "one", "1", "two", "2"
+ 1, "Type mismatch: can not convert integer to text"
-- </join-11.9>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"join-11.10",
[[
SELECT * FROM t2 NATURAL JOIN t1
]], {
-- <join-11.10>
- 1, "one", 2, "two"
+ 1, "Type mismatch: can not convert 1 to numeric"
-- </join-11.10>
})
diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua
index 32f38cc..1cf6bd9 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'
]], {
-- <misc1-1.4>
"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'
]], {
-- <misc1-1.5>
"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, {
-- <misc1-1.6>
"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, {
-- <misc1-1.8>
@@ -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, {
-- <misc1-1.9>
@@ -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, {
-- <misc1-1.10>
@@ -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, {
-- <misc1-1.11>
@@ -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, {
-- <misc1-10.3>
@@ -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'
]], {
-- <misc1-10.6>
"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, {
-- <misc1-10.7>
@@ -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'
]], {
-- <misc1-10.8>
"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'
]], {
-- <misc1-10.10>
"103"
@@ -619,13 +619,13 @@ test:do_execsql_test(
-- </misc1-12.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"misc1-12.2",
[[
SELECT '0'==0.0
]], {
-- <misc1-12.2>
- true
+ 1, "Type mismatch: can not convert 0 to numeric"
-- </misc1-12.2>
})
diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua
index fbebfab..cb4bece 100755
--- a/test/sql-tap/select1.test.lua
+++ b/test/sql-tap/select1.test.lua
@@ -1912,7 +1912,7 @@ test:do_execsql_test(
test:do_execsql_test(
"select1-12.7",
[[
- SELECT * FROM t3 WHERE a=(SELECT 1);
+ SELECT * FROM t3 WHERE a=(SELECT '1');
]], {
-- <select1-12.7>
0, "1", "2"
@@ -1922,7 +1922,7 @@ test:do_execsql_test(
test:do_execsql_test(
"select1-12.8",
[[
- SELECT * FROM t3 WHERE a=(SELECT 2);
+ SELECT * FROM t3 WHERE a=(SELECT '2');
]], {
-- <select1-12.8>
diff --git a/test/sql-tap/select7.test.lua b/test/sql-tap/select7.test.lua
index fec5d7a..a5c4e41 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<b;
+ SELECT typeof(a), a FROM t5 GROUP BY a HAVING CAST(a AS INTEGER)<b;
]], {
-- <select7-7.7>
"string", "123"
diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua
index 15c4c82..e3d2cee 100755
--- a/test/sql-tap/subquery.test.lua
+++ b/test/sql-tap/subquery.test.lua
@@ -284,13 +284,13 @@ test:do_execsql_test(
-- </subquery-2.3.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"subquery-2.3.2",
[[
SELECT a IN (10.0, 20) FROM t3;
]], {
-- <subquery-2.3.2>
- false
+ 1, "Type mismatch: can not convert text to real"
-- </subquery-2.3.2>
})
diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua
index cb5348a..2a18b17 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(
-- </1.5>
})
-test:do_execsql_test(
+test:do_catchsql_test(
2.1,
[[
SELECT x FROM t1 WHERE x IN (1);
]], {
-- <2.1>
- "1"
+ 1,"Type mismatch: can not convert 1 to numeric"
-- </2.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
2.2,
[[
SELECT x FROM t1 WHERE x IN (1.0);
]], {
-- <2.2>
- "1"
+ 1,"Type mismatch: can not convert 1 to numeric"
-- </2.2>
})
@@ -123,23 +123,23 @@ test:do_execsql_test(
-- </2.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
2.5,
[[
SELECT x FROM t1 WHERE 1 IN (x);
]], {
-- <2.5>
- "1"
+ 1,"Type mismatch: can not convert 1 to numeric"
-- </2.5>
})
-test:do_execsql_test(
+test:do_catchsql_test(
2.6,
[[
SELECT x FROM t1 WHERE 1.0 IN (x);
]], {
-- <2.6>
- "1"
+ 1,"Type mismatch: can not convert 1 to numeric"
-- </2.6>
})
@@ -183,23 +183,23 @@ test:do_execsql_test(
-- </3.2>
})
-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 integer to text"
-- </3.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
3.4,
[[
SELECT x FROM t2 WHERE x IN ('1');
]], {
-- <3.4>
- 1
+ 1, "Type mismatch: can not convert integer to text"
-- </3.4>
})
@@ -223,23 +223,23 @@ test:do_execsql_test(
-- </3.6>
})
-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 integer to text"
-- </3.7>
})
-test:do_execsql_test(
+test:do_catchsql_test(
3.8,
[[
SELECT x FROM t2 WHERE '1' IN (x);
]], {
-- <3.8>
- 1
+ 1, "Type mismatch: can not convert integer to text"
-- </3.8>
})
@@ -263,23 +263,23 @@ test:do_execsql_test(
-- </4.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
4.3,
[[
SELECT x FROM t3 WHERE x IN ('1');
]], {
-- <4.3>
- 1.0
+ 1, "Type mismatch: can not convert number to text"
-- </4.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
4.4,
[[
SELECT x FROM t3 WHERE x IN ('1.0');
]], {
-- <4.4>
- 1.0
+ 1, "Type mismatch: can not convert number to text"
-- </4.4>
})
@@ -303,23 +303,23 @@ test:do_execsql_test(
-- </4.6>
})
-test:do_execsql_test(
+test:do_catchsql_test(
4.7,
[[
SELECT x FROM t3 WHERE '1' IN (x);
]], {
-- <4.7>
- 1
+ 1, "Type mismatch: can not convert number to text"
-- </4.7>
})
-test:do_execsql_test(
+test:do_catchsql_test(
4.8,
[[
SELECT x FROM t3 WHERE '1.0' IN (x);
]], {
-- <4.8>
- 1
+ 1, "Type mismatch: can not convert number to text"
-- </4.8>
})
@@ -343,23 +343,23 @@ test:do_execsql_test(
-- </5.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
5.3,
[[
SELECT x FROM t4 WHERE x IN ('1');
]], {
-- <5.3>
-
+ 1, "Type mismatch: can not convert number to text"
-- </5.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
5.4,
[[
SELECT x FROM t4 WHERE x IN ('1.0');
]], {
-- <5.4>
-
+ 1, "Type mismatch: can not convert number to text"
-- </5.4>
})
@@ -373,13 +373,13 @@ test:do_execsql_test(
-- </5.5>
})
-test:do_execsql_test(
+test:do_catchsql_test(
5.6,
[[
SELECT x FROM t4 WHERE x IN ('1.11');
]], {
-- <5.6>
- 1.11
+ 1, "Type mismatch: can not convert number to text"
-- </5.6>
})
@@ -403,23 +403,23 @@ test:do_execsql_test(
-- </5.8>
})
-test:do_execsql_test(
+test:do_catchsql_test(
5.9,
[[
SELECT x FROM t4 WHERE '1' IN (x);
]], {
-- <5.9>
-
+ 1, "Type mismatch: can not convert number to text"
-- </5.9>
})
-test:do_execsql_test(
+test:do_catchsql_test(
5.10,
[[
SELECT x FROM t4 WHERE '1.0' IN (x);
]], {
-- <5.10>
-
+ 1, "Type mismatch: can not convert number to text"
-- </5.10>
})
@@ -433,13 +433,13 @@ test:do_execsql_test(
-- </5.11>
})
-test:do_execsql_test(
+test:do_catchsql_test(
5.12,
[[
SELECT x FROM t4 WHERE '1.11' IN (x);
]], {
-- <5.12>
- 1.11
+ 1, "Type mismatch: can not convert number to text"
-- </5.12>
})
@@ -463,23 +463,23 @@ test:do_execsql_test(
-- </6.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
6.3,
[[
SELECT x, y FROM t5 WHERE x IN ('1');
]], {
-- <6.3>
- 1, "one", 1, "two", 1, "three", 1.0, "four"
+ 1, "Type mismatch: can not convert 1 to numeric"
-- </6.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
6.4,
[[
SELECT x, y FROM t5 WHERE x IN ('1.0');
]], {
-- <6.4>
- 1, "one", 1, "two", 1, "three", 1.0, "four"
+ 1, "Type mismatch: can not convert 1.0 to numeric"
-- </6.4>
})
@@ -503,23 +503,23 @@ test:do_execsql_test(
-- </6.6>
})
-test:do_execsql_test(
+test:do_catchsql_test(
6.7,
[[
SELECT x, y FROM t5 WHERE '1' IN (x);
]], {
-- <6.7>
- 1, "one", 1, "two", 1, "three", 1.0, "four"
+ 1, "Type mismatch: can not convert 1 to numeric"
-- </6.7>
})
-test:do_execsql_test(
+test:do_catchsql_test(
6.8,
[[
SELECT x, y FROM t5 WHERE '1.0' IN (x);
]], {
-- <6.8>
- 1, "one", 1, "two", 1, "three", 1, "four"
+ 1, "Type mismatch: can not convert 1.0 to numeric"
-- </6.8>
})
diff --git a/test/sql-tap/tkt-f973c7ac31.test.lua b/test/sql-tap/tkt-f973c7ac31.test.lua
index 82bdb52..5239a77 100755
--- a/test/sql-tap/tkt-f973c7ac31.test.lua
+++ b/test/sql-tap/tkt-f973c7ac31.test.lua
@@ -36,12 +36,12 @@ local sqls = {
for tn, sql in ipairs(sqls) do
test:execsql(sql)
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".1",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 DESC
]], {
-
+ 1, "/Type mismatch: can not convert/"
})
test:do_execsql_test(
@@ -52,36 +52,36 @@ for tn, sql in ipairs(sqls) do
5, 5, 5, 4
})
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".3",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 DESC
]], {
- 5, 5, 5, 4
+ 1, "/Type mismatch: can not convert/"
})
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".4",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 DESC
]], {
- 5, 5, 5, 4
+ 1, "/Type mismatch: can not convert/"
})
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".5",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 DESC
]], {
- 5, 5, 5, 4
+ 1, "/Type mismatch: can not convert/"
})
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".6",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 ASC
]], {
-
+ 1, "/Type mismatch: can not convert/"
})
test:do_execsql_test(
@@ -92,28 +92,28 @@ for tn, sql in ipairs(sqls) do
5, 4, 5, 5
})
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".8",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 ASC
]], {
- 5, 4, 5, 5
+ 1, "/Type mismatch: can not convert/"
})
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".9",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 ASC
]], {
- 5, 4, 5, 5
+ 1, "/Type mismatch: can not convert/"
})
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".10",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 ASC
]], {
- 5, 4, 5, 5
+ 1, "/Type mismatch: can not convert/"
})
end
diff --git a/test/sql-tap/tkt-fc7bd6358f.test.lua b/test/sql-tap/tkt-fc7bd6358f.test.lua
index fe5d620..f77e4be 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 integer to text"
})
test:do_test(
string.format("tkt-fc7bd6358f.110.%s.%s.2", a, b),
function()
- return test:execsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where))
+ return test:catchsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where))
end, {
- "12", 12, "12", "34", 34, "34"
+ 1, "Type mismatch: can not convert integer to text"
})
end
diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua
index 7ceec47..1d31c0c 100755
--- a/test/sql-tap/tkt3493.test.lua
+++ b/test/sql-tap/tkt3493.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(26)
+test:plan(25)
--!./tcltestrunner.lua
-- 2008 October 13
@@ -45,7 +45,7 @@ test:do_execsql_test(
[[
SELECT
CASE
- WHEN B.val = 1 THEN 'XYZ'
+ WHEN B.val = '1' THEN 'XYZ'
ELSE A.val
END AS Col1
FROM B
@@ -63,7 +63,7 @@ test:do_execsql_test(
[[
SELECT DISTINCT
CASE
- WHEN B.val = 1 THEN 'XYZ'
+ WHEN B.val = '1' THEN 'XYZ'
ELSE A.val
END AS Col1
FROM B
@@ -79,7 +79,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-1.4",
[[
- SELECT b.val, CASE WHEN b.val = 1 THEN 'xyz' ELSE b.val END AS col1 FROM b;
+ SELECT b.val, CASE WHEN b.val = '1' THEN 'xyz' ELSE b.val END AS col1 FROM b;
]], {
-- <tkt3493-1.4>
"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;
]], {
-- <tkt3493-1.5>
@@ -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
]], {
-- <tkt3493-2.2.1>
true
@@ -134,16 +134,6 @@ test:do_execsql_test(
})
test:do_execsql_test(
- "tkt3493-2.2.2",
- [[
- SELECT a=123 FROM t1
- ]], {
- -- <tkt3493-2.2.2>
- true
- -- </tkt3493-2.2.2>
- })
-
-test:do_execsql_test(
"tkt3493-2.2.3",
[[
SELECT a='123' FROM t1
@@ -156,7 +146,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.2.4",
[[
- SELECT count(*), a=123 FROM t1
+ SELECT count(*), a='123' FROM t1
]], {
-- <tkt3493-2.2.4>
1, true
@@ -166,7 +156,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.2.5",
[[
- SELECT count(*), +a=123 FROM t1
+ SELECT count(*), +a='123' FROM t1
]], {
-- <tkt3493-2.2.5>
1, true
@@ -176,7 +166,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.3.3",
[[
- SELECT b='456' FROM t1 GROUP BY a
+ SELECT b = 456 FROM t1 GROUP BY a
]], {
-- <tkt3493-2.3.3>
true
@@ -186,7 +176,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.3.1",
[[
- SELECT b='456' FROM t1 GROUP BY b
+ SELECT b = 456 FROM t1 GROUP BY b
]], {
-- <tkt3493-2.3.1>
true
@@ -196,7 +186,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.3.2",
[[
- SELECT b='456' FROM t1
+ SELECT b = 456 FROM t1
]], {
-- <tkt3493-2.3.2>
true
@@ -206,7 +196,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.4.1",
[[
- SELECT typeof(a), a FROM t1 GROUP BY a HAVING a=123
+ SELECT typeof(a), a FROM t1 GROUP BY a HAVING a='123'
]], {
-- <tkt3493-2.4.1>
"string", "123"
@@ -216,7 +206,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.4.2",
[[
- SELECT typeof(a), a FROM t1 GROUP BY b HAVING a=123
+ SELECT typeof(a), a FROM t1 GROUP BY b HAVING a='123'
]], {
-- <tkt3493-2.4.2>
"string", "123"
@@ -226,7 +216,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.5.1",
[[
- SELECT typeof(b), b FROM t1 GROUP BY a HAVING b='456'
+ SELECT typeof(b), b FROM t1 GROUP BY a HAVING b=456
]], {
-- <tkt3493-2.5.1>
"integer", 456
@@ -236,7 +226,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.5.2",
[[
- SELECT typeof(b), b FROM t1 GROUP BY b HAVING b='456'
+ SELECT typeof(b), b FROM t1 GROUP BY b HAVING b=456
]], {
-- <tkt3493-2.5.2>
"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';
]], {
-- <transitive1-200>
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;
]], {
-- <transitive1-210>
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;
]], {
-- <transitive1-220>
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;
]], {
-- <transitive1-500>
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 ';
]], {
-- <transitive1-520>
@@ -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';
]], {
-- <transitive1-530>
"00013",13,"013"
diff --git a/test/sql-tap/where2.test.lua b/test/sql-tap/where2.test.lua
index f267be8..7348a85 100755
--- a/test/sql-tap/where2.test.lua
+++ b/test/sql-tap/where2.test.lua
@@ -4,7 +4,7 @@ yaml = require("yaml")
fio = require("fio")
ffi = require("ffi")
-test:plan(74)
+test:plan(62)
ffi.cdef[[
int dup(int oldfd);
@@ -622,181 +622,12 @@ test:do_test(
-- </where2-6.6>
})
--- if X(356, "X!cmd", [=[["expr","[permutation] != \"no_optimization\""]]=])
--- then
- -- Ticket #2249. Make sure the OR optimization is not attempted if
- -- comparisons between columns of different affinities are needed.
- --
- test:do_test(
- "where2-6.7",
- function()
- test:execsql [[
- CREATE TABLE t2249a(a TEXT PRIMARY KEY, x VARCHAR(100));
- CREATE TABLE t2249b(b INTEGER PRIMARY KEY);
- INSERT INTO t2249a(a) VALUES('0123');
- INSERT INTO t2249b VALUES(123);
- ]]
- return queryplan([[
- -- Because a is type TEXT and b is type INTEGER, both a and b
- -- will attempt to convert to NUMERIC before the comparison.
- -- They will thus compare equal.
- --
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b;
- ]])
- end, {
- -- <where2-6.7>
- 123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.7>
- })
-
- test:do_test(
- "where2-6.9",
- function()
- return queryplan([[
- -- The + operator doesn't affect RHS.
- --
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b;
- ]])
- end, {
- -- <where2-6.9>
- 123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.9>
- })
-
- test:do_test(
- "where2-6.9.2",
- function()
- -- The same thing but with the expression flipped around.
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a
- ]])
- end, {
- -- <where2-6.9.2>
- 123, "0123","nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.9.2>
- })
-
- test:do_test(
- "where2-6.10",
- function()
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +a=+b;
- ]])
- end, {
- -- <where2-6.10>
- 123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.10>
- })
-
- test:do_test(
- "where2-6.11",
- function()
- -- This will not attempt the OR optimization because of the a=b
- -- comparison.
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello';
- ]])
- end, {
- -- <where2-6.11>
- 123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.11>
- })
-
- test:do_test(
- "where2-6.11.2",
- function()
- -- Permutations of the expression terms.
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello';
- ]])
- end, {
- -- <where2-6.11.2>
- 123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.11.2>
- })
-
- test:do_test(
- "where2-6.11.3",
- function()
- -- Permutations of the expression terms.
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a;
- ]])
- end, {
- -- <where2-6.11.3>
- 123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.11.3>
- })
-
- test:do_test(
- "where2-6.11.4",
- function()
- -- Permutations of the expression terms.
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a;
- ]])
- end, {
- -- <where2-6.11.4>
- 123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.11.4>
- })
-
- -- These tests are not run if subquery support is not included in the
- -- build. This is because these tests test the "a = 1 OR a = 2" to
- -- "a IN (1, 2)" optimisation transformation, which is not enabled if
- -- subqueries and the IN operator is not available.
- --
- test:do_test(
- "where2-6.12",
- function()
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello';
- ]])
- end, {
- -- <where2-6.12>
- 123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.12>
- })
-
- test:do_test(
- "where2-6.12.2",
- function()
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a;
- ]])
- end, {
- -- <where2-6.12.2>
- 123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.12.2>
- })
-
- test:do_test(
- "where2-6.12.3",
- function()
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello';
- ]])
- end, {
- -- <where2-6.12.3>
- 123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.12.3>
- })
-
- test:do_test(
- "where2-6.13",
- function()
- -- The addition of +a on the second term disabled the OR optimization.
- -- But we should still get the same empty-set result as in where2-6.9.
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello';
- ]])
- end, {
- -- <where2-6.13>
- 123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.13>
- })
-
-
+ test:execsql [[
+ CREATE TABLE t2249a(a TEXT PRIMARY KEY, x VARCHAR(100));
+ CREATE TABLE t2249b(b INTEGER PRIMARY KEY);
+ INSERT INTO t2249a(a) VALUES('0123');
+ INSERT INTO t2249b VALUES(123);
+ ]]
-- Variations on the order of terms in a WHERE clause in order
-- to make sure the OR optimizer can recognize them all.
diff --git a/test/sql-tap/where5.test.lua b/test/sql-tap/where5.test.lua
index 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, {
-- <where5-1.0>
@@ -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
]], {
-- <where5-1.1>
'-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
]], {
-- <where5-1.2>
'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
]], {
-- <where5-1.3>
'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
]], {
-- <where5-1.4>
'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
]], {
-- <where5-1.5>
'-1', '1'
diff --git a/test/sql-tap/whereB.test.lua b/test/sql-tap/whereB.test.lua
index d98645f..b9ff361 100755
--- a/test/sql-tap/whereB.test.lua
+++ b/test/sql-tap/whereB.test.lua
@@ -27,8 +27,7 @@ test:plan(63)
-- These values are not equal and because neither affinity is NUMERIC
-- no type conversion occurs.
--
-test:do_execsql_test(
- "whereB-1.1",
+test:execsql(
[[
CREATE TABLE t1(x INT primary key,y INT ); -- affinity of t1.y is NONE
INSERT INTO t1 VALUES(1,99);
@@ -36,49 +35,54 @@ test:do_execsql_test(
CREATE TABLE t2(a INT primary key, b TEXT); -- affinity of t2.b is TEXT
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(2,'99');
+ ]]
+)
+test:do_catchsql_test(
+ "whereB-1.1",
+ [[
SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a;
]],
{
-- <whereB-1.1>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-1.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-1.2",
[[
SELECT x, a, y=b FROM t1, t2 WHERE y=b;
]],
{
-- <whereB-1.2>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-1.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-1.3",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-1.3>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-1.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-1.4",
[[
SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
]],
{
-- <whereB-1.4>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-1.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-1.100",
[[
DROP INDEX t2b ON t2;
@@ -86,29 +90,29 @@ test:do_execsql_test(
]],
{
-- <whereB-1.100>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-1.100>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-1.101",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-1.101>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-1.101>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-1.102",
[[
SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
]],
{
-- <whereB-1.102>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-1.102>
})
@@ -312,8 +316,7 @@ test:do_execsql_test(
-- Because t2.b has a numeric affinity, type conversion should occur
-- and the two fields should be equal.
--
-test:do_execsql_test(
- "whereB-4.1",
+test:execsql(
[[
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
@@ -324,38 +327,44 @@ test:do_execsql_test(
CREATE TABLE t2(a INT primary key, b NUMBER); -- affinity of t2.b is NUMERIC
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(2,99);
+ ]]
+)
+
+test:do_catchsql_test(
+ "whereB-4.1",
+ [[
SELECT x, a, y=b FROM t1, t2;
]],
{
-- <whereB-4.1>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-4.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-4.2",
[[
SELECT x, a, y=b FROM t1, t2 WHERE y=b;
]],
{
-- <whereB-4.2>
- 1, 2, true
+ 1, "Type mismatch: can not convert number to text"
-- </whereB-4.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-4.3",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-4.3>
- 1, 2, true
+ 1, "Type mismatch: can not convert number to text"
-- </whereB-4.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-4.4",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -364,11 +373,11 @@ test:do_execsql_test(
]],
{
-- <whereB-4.4>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-4.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-4.100",
[[
DROP INDEX t2b ON t2;
@@ -376,22 +385,22 @@ test:do_execsql_test(
]],
{
-- <whereB-4.100>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-4.100>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-4.101",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-4.101>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-4.101>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-4.102",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -400,7 +409,7 @@ test:do_execsql_test(
]],
{
-- <whereB-4.102>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-4.102>
})
@@ -412,8 +421,7 @@ test:do_execsql_test(
-- Because t2.b has a numeric affinity, type conversion should occur
-- and the two fields should be equal.
--
-test:do_execsql_test(
- "whereB-5.1",
+test:execsql(
[[
DROP TABLE t1;
DROP TABLE t2;
@@ -424,38 +432,43 @@ test:do_execsql_test(
CREATE TABLE t2(a INT primary key, b INT); -- affinity of t2.b is INTEGER
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(2,99);
+ ]]
+)
+test:do_catchsql_test(
+ "whereB-5.1",
+ [[
SELECT x, a, y=b FROM t1, t2;
]],
{
-- <whereB-5.1>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-5.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-5.2",
[[
SELECT x, a, y=b FROM t1, t2 WHERE y=b;
]],
{
-- <whereB-5.2>
- 1, 2, true
+ 1, "Type mismatch: can not convert integer to text"
-- </whereB-5.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-5.3",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-5.3>
- 1, 2, true
+ 1, "Type mismatch: can not convert integer to text"
-- </whereB-5.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-5.4",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -464,11 +477,11 @@ test:do_execsql_test(
]],
{
-- <whereB-5.4>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-5.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-5.100",
[[
DROP INDEX t2b ON t2;
@@ -476,22 +489,22 @@ test:do_execsql_test(
]],
{
-- <whereB-5.100>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-5.100>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-5.101",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-5.101>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-5.101>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-5.102",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -500,7 +513,7 @@ test:do_execsql_test(
]],
{
-- <whereB-5.102>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-5.102>
})
@@ -512,8 +525,8 @@ test:do_execsql_test(
-- Because t2.b has a numeric affinity, type conversion should occur
-- and the two fields should be equal.
--
-test:do_execsql_test(
- "whereB-6.1",
+
+test:execsql(
[[
DROP TABLE t1;
DROP TABLE t2;
@@ -524,38 +537,43 @@ test:do_execsql_test(
CREATE TABLE t2(a INT primary key, b NUMBER); -- affinity of t2.b is REAL
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(2,99.0);
+ ]]
+)
+test:do_catchsql_test(
+ "whereB-6.1",
+ [[
SELECT x, a, y=b FROM t1, t2;
]],
{
-- <whereB-6.1>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-6.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-6.2",
[[
SELECT x, a, y=b FROM t1, t2 WHERE y=b;
]],
{
-- <whereB-6.2>
- 1, 2, true
+ 1, "Type mismatch: can not convert number to text"
-- </whereB-6.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-6.3",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-6.3>
- 1, 2, true
+ 1, "Type mismatch: can not convert number to text"
-- </whereB-6.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-6.4",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -564,11 +582,11 @@ test:do_execsql_test(
]],
{
-- <whereB-6.4>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-6.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-6.100",
[[
DROP INDEX t2b ON t2;
@@ -576,22 +594,22 @@ test:do_execsql_test(
]],
{
-- <whereB-6.100>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-6.100>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-6.101",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-6.101>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-6.101>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-6.102",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -600,7 +618,7 @@ test:do_execsql_test(
]],
{
-- <whereB-6.102>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-6.102>
})
@@ -612,8 +630,7 @@ test:do_execsql_test(
-- Because t1.y has a numeric affinity, type conversion should occur
-- and the two fields should be equal.
--
-test:do_execsql_test(
- "whereB-7.1",
+test:execsql(
[[
DROP TABLE t1;
DROP TABLE t2;
@@ -624,38 +641,43 @@ test:do_execsql_test(
CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(2,'99');
+ ]]
+)
+test:do_catchsql_test(
+ "whereB-7.1",
+ [[
SELECT x, a, y=b FROM t1, t2;
]],
{
-- <whereB-7.1>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-7.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-7.2",
[[
SELECT x, a, y=b FROM t1, t2 WHERE y=b;
]],
{
-- <whereB-7.2>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-7.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-7.3",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-7.3>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-7.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-7.4",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -664,11 +686,11 @@ test:do_execsql_test(
]],
{
-- <whereB-7.4>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-7.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-7.100",
[[
DROP INDEX t2b ON t2;
@@ -676,22 +698,22 @@ test:do_execsql_test(
]],
{
-- <whereB-7.100>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-7.100>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-7.101",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-7.101>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-7.101>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-7.102",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -700,7 +722,7 @@ test:do_execsql_test(
]],
{
-- <whereB-7.102>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-7.102>
})
@@ -712,8 +734,7 @@ test:do_execsql_test(
-- Because t1.y has a numeric affinity, type conversion should occur
-- and the two fields should be equal.
--
-test:do_execsql_test(
- "whereB-8.1",
+test:execsql(
[[
DROP TABLE t1;
DROP TABLE t2;
@@ -724,38 +745,43 @@ test:do_execsql_test(
CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(2,'99');
+ ]]
+)
+test:do_catchsql_test(
+ "whereB-8.1",
+ [[
SELECT x, a, y=b FROM t1, t2;
]],
{
-- <whereB-8.1>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-8.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-8.2",
[[
SELECT x, a, y=b FROM t1, t2 WHERE y=b;
]],
{
-- <whereB-8.2>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-8.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-8.3",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-8.3>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-8.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-8.4",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -764,11 +790,11 @@ test:do_execsql_test(
]],
{
-- <whereB-8.4>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-8.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-8.100",
[[
DROP INDEX t2b ON t2;
@@ -776,22 +802,22 @@ test:do_execsql_test(
]],
{
-- <whereB-8.100>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-8.100>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-8.101",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-8.101>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-8.101>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-8.102",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -800,7 +826,7 @@ test:do_execsql_test(
]],
{
-- <whereB-8.102>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-8.102>
})
@@ -812,8 +838,7 @@ test:do_execsql_test(
-- Because t1.y has a numeric affinity, type conversion should occur
-- and the two fields should be equal.
--
-test:do_execsql_test(
- "whereB-9.1",
+test:execsql(
[[
DROP TABLE t1;
DROP TABLE t2;
@@ -824,38 +849,43 @@ test:do_execsql_test(
CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(2,'99');
+ ]]
+)
+test:do_catchsql_test(
+ "whereB-9.1",
+ [[
SELECT x, a, y=b FROM t1, t2;
]],
{
-- <whereB-9.1>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-9.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-9.2",
[[
SELECT x, a, y=b FROM t1, t2 WHERE y=b;
]],
{
-- <whereB-9.2>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-9.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-9.3",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-9.3>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-9.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-9.4",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -864,11 +894,11 @@ test:do_execsql_test(
]],
{
-- <whereB-9.4>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-9.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-9.100",
[[
DROP INDEX t2b ON t2;
@@ -876,22 +906,22 @@ test:do_execsql_test(
]],
{
-- <whereB-9.100>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-9.100>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-9.101",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-9.101>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-9.101>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-9.102",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -900,7 +930,7 @@ test:do_execsql_test(
]],
{
-- <whereB-9.102>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-9.102>
})
diff --git a/test/sql-tap/whereC.test.lua b/test/sql-tap/whereC.test.lua
index 89459de..58c0495 100755
--- a/test/sql-tap/whereC.test.lua
+++ b/test/sql-tap/whereC.test.lua
@@ -55,9 +55,9 @@ test:do_execsql_test(
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=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}},
@@ -66,7 +66,7 @@ test:test("main", function()
{"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=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 a=1 AND b=2 AND i<4.5", {3, 4}}}
-- {"SELECT i FROM t1 WHERE rowid IS '12'", {12}}}
for tn, t in ipairs(data) do
diff --git a/test/sql/types.result b/test/sql/types.result
index 54aff46..62161cf 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -339,12 +339,12 @@ box.execute("INSERT INTO tboolean VALUES (TRUE);")
box.execute("SELECT * FROM tboolean WHERE s1 = x'44';")
---
- null
-- 'Type mismatch: can not convert varbinary to boolean'
+- 'Type mismatch: can not convert boolean to varbinary'
...
box.execute("SELECT * FROM tboolean WHERE s1 = 'abc';")
---
- null
-- 'Type mismatch: can not convert abc to boolean'
+- 'Type mismatch: can not convert boolean to text'
...
box.execute("SELECT * FROM tboolean WHERE s1 = 1;")
---
@@ -606,14 +606,6 @@ box.execute("SELECT 18446744073709551615.0 > 18446744073709551615")
rows:
- [true]
...
-box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)")
----
-- metadata:
- - name: 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)
- type: boolean
- rows:
- - [true]
-...
box.execute("SELECT 1 LIMIT 18446744073709551615;")
---
- metadata:
diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua
index bd14b34..f9603c6 100644
--- a/test/sql/types.test.lua
+++ b/test/sql/types.test.lua
@@ -151,7 +151,6 @@ box.execute("SELECT 18446744073709551610 - 18446744073709551615;")
box.execute("SELECT 18446744073709551615 = null;")
box.execute("SELECT 18446744073709551615 = 18446744073709551615.0;")
box.execute("SELECT 18446744073709551615.0 > 18446744073709551615")
-box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)")
box.execute("SELECT 1 LIMIT 18446744073709551615;")
box.execute("SELECT 1 LIMIT 1 OFFSET 18446744073709551614;")
box.execute("SELECT CAST('18446744073' || '709551616' AS INTEGER);")
More information about the Tarantool-patches
mailing list