[Tarantool-patches] [PATCH v1 2/7] sql: remove implicit cast from comparison opcodes
Mergen Imeev
imeevma at tarantool.org
Fri Aug 6 02:33:39 MSK 2021
Thank you for the review! My answers and new patch below.
On Thu, Aug 05, 2021 at 12:24:05AM +0200, Vladislav Shpilevoy wrote:
> Thanks for the patch!
>
> See 5 comments below.
>
> > diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
> > index e804dba67..b04303be2 100644
> > --- a/src/box/sql/mem.c
> > +++ b/src/box/sql/mem.c
> > @@ -1976,25 +2000,21 @@ mem_bit_not(const struct Mem *mem, struct Mem *result)
> > return 0;
> > }
> >
> > -int
> > -mem_cmp_bool(const struct Mem *a, const struct Mem *b, int *result)
> > +static int
> > +mem_cmp_bool(const struct Mem *a, const struct Mem *b)
> > {
> > - if ((a->type & b->type & MEM_TYPE_BOOL) == 0)
> > - return -1;
> > + assert((a->type & b->type & MEM_TYPE_BOOL) != 0);
> > if (a->u.b == b->u.b)
> > - *result = 0;
> > - else if (a->u.b)
> > - *result = 1;
> > - else
> > - *result = -1;
> > - return 0;
> > + return 0;
> > + if (a->u.b)
> > + return 1;
> > + return -1;
>
> 1. Could be simpler:
>
> ====================
> @@ -2004,11 +2004,7 @@ static int
> mem_cmp_bool(const struct Mem *a, const struct Mem *b)
> {
> assert((a->type & b->type & MEM_TYPE_BOOL) != 0);
> - if (a->u.b == b->u.b)
> - return 0;
> - if (a->u.b)
> - return 1;
> - return -1;
> + return a->u.b - b->u.b;
> }
> ====================
>
Thanks! Fixed.
> > @@ -2245,8 +2189,11 @@ mem_cmp_msgpack(const struct Mem *a, const char **b, int *result,
> > if (type == MP_UUID) {
> > assert(len == UUID_LEN);
> > mem.type = MEM_TYPE_UUID;
> > - if (uuid_unpack(b, len, &mem.u.uuid) == NULL)
> > + if (uuid_unpack(b, len, &mem.u.uuid) == NULL) {
> > + diag_set(ClientError, ER_SQL_EXECUTE,
> > + "cannot parse UUID");
>
> 2. Shouldn't this be a separate commit as a bugfix?
>
True. I removed this diff. I will create an issue a bit later, when I think up
of some reproducer.
> > return -1;
> > + }
> > break;
> > }
> > diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
> > index d143ce364..62f58def9 100644
> > --- a/src/box/sql/vdbe.c
> > +++ b/src/box/sql/vdbe.c
> <...>
>
> > - 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;
> > + bool result;
> > + switch(pOp->opcode) {
>
> 3. It does not look good to have a second switch-case here. But I
> can't find a better solution right away. We can't wrap this all
> into a function, because need to make goto abort_due_to_error and
> goto jump_to_p2 in some places. Up to you if you want to find a
> way to fix it.
>
> As a side note, the code now is incomparably simpler than it was.
> It is getting actually understable, nice.
>
I divided these 6 opcode to two grops: EQ & NE and all others. ALso, I actually
can avoid branching in any groups using somethins like:
int op = pOp->opcode;
bool result = (op == OP_Lt && cmp_res < 0) || (op == OP_Gt && cmp_res > 0) || ...
But not sure that it will be easier to read. What do you think?
> > + 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();
> > }
> > diff --git a/test/sql-tap/transitive1.test.lua b/test/sql-tap/transitive1.test.lua
> > index dbc2559fa..2d502f5e8 100755
> > --- a/test/sql-tap/transitive1.test.lua
> > +++ b/test/sql-tap/transitive1.test.lua
> > @@ -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 >= '20' ORDER BY +a;
>
> 4. Might worth adding whitespaces after ',' in the select list.
>
Fixed.
> > ]], {
> > -- <transitive1-210>
> > 3, 3, "3", 20, 20, "20"
> > diff --git a/test/sql/boolean.result b/test/sql/boolean.result
> > index d54de8fe7..81d79ee78 100644
> > --- a/test/sql/boolean.result
> > +++ b/test/sql/boolean.result
> > @@ -4898,12 +4898,12 @@ SELECT a1, a1 != 2.3 FROM t6
> > SELECT a1, 2.3 == a1 FROM t6
> > | ---
> > | - null
> > - | - 'Type mismatch: can not convert double(2.3) to boolean'
> > + | - 'Type mismatch: can not convert boolean(FALSE) to number'
>
> 5. Is it possible to keep the old error messages? For the sake of
> smaller diff.
It is possible but it will be very inconvenient since rules of defining value
and type for the error become quite complicated. Sorry, but I think it is better
to fix these rules now.
New patch:
commit 8bb2223cd9a50b47d4055a357825e4ea3007c0f0
Author: Mergen Imeev <imeevma at gmail.com>
Date: Fri Jul 23 15:31:19 2021 +0300
sql: remove implicit cast from comparison opcodes
After this patch, the new rules will be applied to implicit cast during
comparison where index is not used. Essentially it means that implicit
cast from STRING to number during such comparisons was removed.
Part of #4230
Part of #4470
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index e153db24b..7dd3a4897 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -144,15 +144,7 @@ minmaxFunc(sql_context * context, int argc, sql_value ** argv)
for (i = 1; i < argc; i++) {
if (mem_is_null(argv[i]))
return;
- int res;
- if (mem_cmp_scalar(argv[iBest], argv[i], &res, pColl) != 0) {
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
- mem_str(argv[i]),
- mem_type_to_str(argv[iBest]));
- context->is_aborted = true;
- return;
- }
- if ((res ^ mask) >= 0)
+ if ((mem_cmp_scalar(argv[iBest], argv[i], pColl) ^ mask) >= 0)
iBest = i;
}
sql_result_value(context, argv[iBest]);
@@ -1059,14 +1051,7 @@ nullifFunc(sql_context * context, int NotUsed, sql_value ** argv)
{
struct coll *pColl = sqlGetFuncCollSeq(context);
UNUSED_PARAMETER(NotUsed);
- int res;
- if (mem_cmp_scalar(argv[0], argv[1], &res, pColl) != 0) {
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
- mem_str(argv[1]), mem_type_to_str(argv[0]));
- context->is_aborted = true;
- return;
- }
- if (res != 0)
+ if (mem_cmp_scalar(argv[0], argv[1], pColl) != 0)
sql_result_value(context, argv[0]);
}
@@ -1826,7 +1811,6 @@ minmaxStep(sql_context * context, int NotUsed, sql_value ** argv)
if (!mem_is_null(pBest))
sqlSkipAccumulatorLoad(context);
} else if (!mem_is_null(pBest)) {
- int cmp;
struct coll *pColl = sqlGetFuncCollSeq(context);
/*
* This step function is used for both the min()
@@ -1835,12 +1819,7 @@ minmaxStep(sql_context * context, int NotUsed, sql_value ** argv)
* comparison is inverted.
*/
bool is_max = (func->flags & SQL_FUNC_MAX) != 0;
- if (mem_cmp_scalar(pBest, pArg, &cmp, pColl) != 0) {
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
- mem_str(pArg), mem_type_to_str(pBest));
- context->is_aborted = true;
- return;
- }
+ int cmp = mem_cmp_scalar(pBest, pArg, pColl);
if ((is_max && cmp < 0) || (!is_max && cmp > 0)) {
mem_copy(pBest, pArg);
} else {
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index c0ceb98e9..32fdd9add 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -162,6 +162,30 @@ mem_str(const struct Mem *mem)
}
}
+static const char *
+mem_type_class_to_str(const struct Mem *mem)
+{
+ switch (mem->type) {
+ case MEM_TYPE_NULL:
+ return "NULL";
+ case MEM_TYPE_UINT:
+ case MEM_TYPE_INT:
+ case MEM_TYPE_DOUBLE:
+ return "number";
+ case MEM_TYPE_STR:
+ return "string";
+ case MEM_TYPE_BIN:
+ return "varbinary";
+ case MEM_TYPE_BOOL:
+ return "boolean";
+ case MEM_TYPE_UUID:
+ return "uuid";
+ default:
+ break;
+ }
+ return "unknown";
+}
+
void
mem_create(struct Mem *mem)
{
@@ -1508,56 +1532,6 @@ mem_concat(struct Mem *a, struct Mem *b, struct Mem *result)
return 0;
}
-struct sql_num {
- union {
- int64_t i;
- uint64_t u;
- double d;
- };
- enum mem_type type;
- bool is_neg;
-};
-
-static int
-get_number(const struct Mem *mem, struct sql_num *number)
-{
- if (mem->type == MEM_TYPE_DOUBLE) {
- number->d = mem->u.r;
- number->type = MEM_TYPE_DOUBLE;
- return 0;
- }
- if (mem->type == MEM_TYPE_INT) {
- number->i = mem->u.i;
- number->type = MEM_TYPE_INT;
- number->is_neg = true;
- return 0;
- }
- if (mem->type == MEM_TYPE_UINT) {
- number->u = mem->u.u;
- number->type = MEM_TYPE_UINT;
- number->is_neg = false;
- return 0;
- }
- if ((mem->type & (MEM_TYPE_STR | MEM_TYPE_BIN)) == 0)
- return -1;
- if (sql_atoi64(mem->z, &number->i, &number->is_neg, mem->n) == 0) {
- number->type = number->is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT;
- /*
- * The next line should be removed along with the is_neg field
- * of struct sql_num. The integer type tells us about the sign.
- * However, if it is removed, the behavior of arithmetic
- * operations will change.
- */
- number->is_neg = false;
- return 0;
- }
- if (sqlAtoF(mem->z, &number->d, mem->n) != 0) {
- number->type = MEM_TYPE_DOUBLE;
- return 0;
- }
- return -1;
-}
-
int
mem_add(const struct Mem *left, const struct Mem *right, struct Mem *result)
{
@@ -1855,25 +1829,17 @@ mem_bit_not(const struct Mem *mem, struct Mem *result)
return 0;
}
-int
-mem_cmp_bool(const struct Mem *a, const struct Mem *b, int *result)
+static int
+mem_cmp_bool(const struct Mem *a, const struct Mem *b)
{
- if ((a->type & b->type & MEM_TYPE_BOOL) == 0)
- return -1;
- if (a->u.b == b->u.b)
- *result = 0;
- else if (a->u.b)
- *result = 1;
- else
- *result = -1;
- return 0;
+ assert((a->type & b->type & MEM_TYPE_BOOL) != 0);
+ return a->u.b - b->u.b;
}
-int
-mem_cmp_bin(const struct Mem *a, const struct Mem *b, int *result)
+static int
+mem_cmp_bin(const struct Mem *a, const struct Mem *b)
{
- if ((a->type & b->type & MEM_TYPE_BIN) == 0)
- return -1;
+ assert((a->type & b->type & MEM_TYPE_BIN) != 0);
int an = a->n;
int bn = b->n;
int minlen = MIN(an, bn);
@@ -1887,181 +1853,105 @@ mem_cmp_bin(const struct Mem *a, const struct Mem *b, int *result)
assert((a->flags & MEM_Zero) == 0 || an == 0);
assert((b->flags & MEM_Zero) == 0 || bn == 0);
- if ((a->flags & b->flags & MEM_Zero) != 0) {
- *result = a->u.nZero - b->u.nZero;
- return 0;
- }
+ if ((a->flags & b->flags & MEM_Zero) != 0)
+ return a->u.nZero - b->u.nZero;
if ((a->flags & MEM_Zero) != 0) {
for (int i = 0; i < minlen; ++i) {
- if (b->z[i] != 0) {
- *result = -1;
- return 0;
- }
+ if (b->z[i] != 0)
+ return -1;
}
- *result = a->u.nZero - bn;
- return 0;
+ return a->u.nZero - bn;
}
if ((b->flags & MEM_Zero) != 0) {
for (int i = 0; i < minlen; ++i) {
- if (a->z[i] != 0){
- *result = 1;
- return 0;
- }
+ if (a->z[i] != 0)
+ return 1;
}
- *result = b->u.nZero - an;
- return 0;
+ return b->u.nZero - an;
}
- *result = memcmp(a->z, b->z, minlen);
- if (*result != 0)
- return 0;
- *result = an - bn;
- return 0;
+ int res = memcmp(a->z, b->z, minlen);
+ return res != 0 ? res : an - bn;
}
-int
-mem_cmp_num(const struct Mem *left, const struct Mem *right, int *result)
+static int
+mem_cmp_num(const struct Mem *a, const struct Mem *b)
{
- struct sql_num a, b;
- /* TODO: Here should be check for right value type. */
- if (get_number(right, &b) != 0) {
- *result = -1;
+ assert(mem_is_num(a) && mem_is_num(b));
+ if ((a->type & b->type & MEM_TYPE_DOUBLE) != 0) {
+ if (a->u.r > b->u.r)
+ return 1;
+ if (a->u.r < b->u.r)
+ return -1;
return 0;
}
- if (get_number(left, &a) != 0)
- return -1;
- if (a.type == MEM_TYPE_DOUBLE) {
- if (b.type == MEM_TYPE_DOUBLE) {
- if (a.d > b.d)
- *result = 1;
- else if (a.d < b.d)
- *result = -1;
- else
- *result = 0;
- return 0;
- }
- if (b.type == MEM_TYPE_INT)
- *result = double_compare_nint64(a.d, b.i, 1);
- else
- *result = double_compare_uint64(a.d, b.u, 1);
+ if ((a->type & b->type & MEM_TYPE_INT) != 0) {
+ if (a->u.i > b->u.i)
+ return 1;
+ if (a->u.i < b->u.i)
+ return -1;
return 0;
}
- if (a.type == MEM_TYPE_INT) {
- if (b.type == MEM_TYPE_INT) {
- if (a.i > b.i)
- *result = 1;
- else if (a.i < b.i)
- *result = -1;
- else
- *result = 0;
- return 0;
- }
- if (b.type == MEM_TYPE_UINT)
- *result = -1;
- else
- *result = double_compare_nint64(b.d, a.i, -1);
+ if ((a->type & b->type & MEM_TYPE_UINT) != 0) {
+ if (a->u.u > b->u.u)
+ return 1;
+ if (a->u.u < b->u.u)
+ return -1;
return 0;
}
- assert(a.type == MEM_TYPE_UINT);
- if (b.type == MEM_TYPE_UINT) {
- if (a.u > b.u)
- *result = 1;
- else if (a.u < b.u)
- *result = -1;
- else
- *result = 0;
- return 0;
+ if (a->type == MEM_TYPE_DOUBLE) {
+ if (b->type == MEM_TYPE_INT)
+ return double_compare_nint64(a->u.r, b->u.i, 1);
+ return double_compare_uint64(a->u.r, b->u.u, 1);
}
- if (b.type == MEM_TYPE_INT)
- *result = 1;
- else
- *result = double_compare_uint64(b.d, a.u, -1);
- return 0;
+ if (b->type == MEM_TYPE_DOUBLE) {
+ if (a->type == MEM_TYPE_INT)
+ return double_compare_nint64(b->u.r, a->u.i, -1);
+ return double_compare_uint64(b->u.r, a->u.u, -1);
+ }
+ if (a->type == MEM_TYPE_INT)
+ return -1;
+ assert(a->type == MEM_TYPE_UINT && b->type == MEM_TYPE_INT);
+ return 1;
}
-int
-mem_cmp_str(const struct Mem *left, const struct Mem *right, int *result,
- const struct coll *coll)
-{
- char *a;
- uint32_t an;
- char bufl[BUF_SIZE];
- if (left->type == MEM_TYPE_STR) {
- a = left->z;
- an = left->n;
- } else {
- assert(mem_is_num(left));
- a = &bufl[0];
- if (left->type == MEM_TYPE_INT)
- sql_snprintf(BUF_SIZE, a, "%lld", left->u.i);
- else if (left->type == MEM_TYPE_UINT)
- sql_snprintf(BUF_SIZE, a, "%llu", left->u.u);
- else
- sql_snprintf(BUF_SIZE, a, "%!.15g", left->u.r);
- an = strlen(a);
- }
-
- char *b;
- uint32_t bn;
- char bufr[BUF_SIZE];
- if (right->type == MEM_TYPE_STR) {
- b = right->z;
- bn = right->n;
- } else {
- assert(mem_is_num(right));
- b = &bufr[0];
- if (right->type == MEM_TYPE_INT)
- sql_snprintf(BUF_SIZE, b, "%lld", right->u.i);
- else if (right->type == MEM_TYPE_UINT)
- sql_snprintf(BUF_SIZE, b, "%llu", right->u.u);
- else
- sql_snprintf(BUF_SIZE, b, "%!.15g", right->u.r);
- bn = strlen(b);
- }
- if (coll != NULL) {
- *result = coll->cmp(a, an, b, bn, coll);
- return 0;
- }
- uint32_t minlen = MIN(an, bn);
- *result = memcmp(a, b, minlen);
- if (*result != 0)
- return 0;
- *result = an - bn;
- return 0;
+static int
+mem_cmp_str(const struct Mem *a, const struct Mem *b, const struct coll *coll)
+{
+ assert((a->type & b->type & MEM_TYPE_STR) != 0);
+ if (coll != NULL)
+ return coll->cmp(a->z, a->n, b->z, b->n, coll);
+ int res = memcmp(a->z, b->z, MIN(a->n, b->n));
+ return res != 0 ? res : a->n - b->n;
}
-int
-mem_cmp_uuid(const struct Mem *a, const struct Mem *b, int *result)
+static int
+mem_cmp_uuid(const struct Mem *a, const struct Mem *b)
{
- if ((a->type & b->type & MEM_TYPE_UUID) == 0)
- return -1;
- *result = memcmp(&a->u.uuid, &b->u.uuid, UUID_LEN);
- return 0;
+ assert((a->type & b->type & MEM_TYPE_UUID) != 0);
+ return memcmp(&a->u.uuid, &b->u.uuid, UUID_LEN);
}
int
-mem_cmp_scalar(const struct Mem *a, const struct Mem *b, int *result,
+mem_cmp_scalar(const struct Mem *a, const struct Mem *b,
const struct coll *coll)
{
enum mem_class class_a = mem_type_class(a->type);
enum mem_class class_b = mem_type_class(b->type);
- if (class_a != class_b) {
- *result = class_a - class_b;
- return 0;
- }
+ if (class_a != class_b)
+ return class_a - class_b;
switch (class_a) {
case MEM_CLASS_NULL:
- *result = 0;
return 0;
case MEM_CLASS_BOOL:
- return mem_cmp_bool(a, b, result);
+ return mem_cmp_bool(a, b);
case MEM_CLASS_NUMBER:
- return mem_cmp_num(a, b, result);
+ return mem_cmp_num(a, b);
case MEM_CLASS_STR:
- return mem_cmp_str(a, b, result, coll);
+ return mem_cmp_str(a, b, coll);
case MEM_CLASS_BIN:
- return mem_cmp_bin(a, b, result);
+ return mem_cmp_bin(a, b);
case MEM_CLASS_UUID:
- return mem_cmp_uuid(a, b, result);
+ return mem_cmp_uuid(a, b);
default:
unreachable();
}
@@ -2138,7 +2028,47 @@ mem_cmp_msgpack(const struct Mem *a, const char **b, int *result,
default:
unreachable();
}
- return mem_cmp_scalar(a, &mem, result, coll);
+ *result = mem_cmp_scalar(a, &mem, coll);
+ return 0;
+}
+
+int
+mem_cmp(const struct Mem *a, const struct Mem *b, int *result,
+ const struct coll *coll)
+{
+ enum mem_class class_a = mem_type_class(a->type);
+ enum mem_class class_b = mem_type_class(b->type);
+ if (mem_is_any_null(a, b)) {
+ *result = class_a - class_b;
+ if ((a->flags & b->flags & MEM_Cleared) != 0)
+ *result = 1;
+ return 0;
+ }
+ if (class_a != class_b) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(b),
+ mem_type_class_to_str(a));
+ return -1;
+ }
+ switch (class_a) {
+ case MEM_CLASS_BOOL:
+ *result = mem_cmp_bool(a, b);
+ break;
+ case MEM_CLASS_NUMBER:
+ *result = mem_cmp_num(a, b);
+ break;
+ case MEM_CLASS_STR:
+ *result = mem_cmp_str(a, b, coll);
+ break;
+ case MEM_CLASS_BIN:
+ *result = mem_cmp_bin(a, b);
+ break;
+ case MEM_CLASS_UUID:
+ *result = mem_cmp_uuid(a, b);
+ break;
+ default:
+ unreachable();
+ }
+ return 0;
}
char *
diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h
index 645d0ee27..70bbe557b 100644
--- a/src/box/sql/mem.h
+++ b/src/box/sql/mem.h
@@ -657,53 +657,12 @@ mem_shift_right(const struct Mem *left, const struct Mem *right,
int
mem_bit_not(const struct Mem *mem, struct Mem *result);
-/**
- * Compare two MEMs and return the result of comparison. MEMs should be of
- * BOOLEAN type or their values are converted to VARBINARY according to implicit
- * cast rules. Original MEMs are not changed.
- */
-int
-mem_cmp_bool(const struct Mem *a, const struct Mem *b, int *result);
-
-/**
- * Compare two MEMs and return the result of comparison. MEMs should be of
- * VARBINARY type or their values are converted to VARBINARY according to
- * implicit cast rules. Original MEMs are not changed.
- */
-int
-mem_cmp_bin(const struct Mem *a, const struct Mem *b, int *result);
-
-/**
- * Compare two MEMs and return the result of comparison. MEMs should be of
- * STRING type or their values are converted to VARBINARY according to
- * implicit cast rules. Original MEMs are not changed.
- */
-int
-mem_cmp_str(const struct Mem *left, const struct Mem *right, int *result,
- const struct coll *coll);
-
-/**
- * Compare two MEMs and return the result of comparison. MEMs should be of
- * NUMBER type or their values are converted to NUMBER according to
- * implicit cast rules. Original MEMs are not changed.
- */
-int
-mem_cmp_num(const struct Mem *a, const struct Mem *b, int *result);
-
-/**
- * Compare two MEMs and return the result of comparison. MEMs should be of
- * UUID type or their values are converted to UUID according to
- * implicit cast rules. Original MEMs are not changed.
- */
-int
-mem_cmp_uuid(const struct Mem *left, const struct Mem *right, int *result);
-
/**
* Compare two MEMs using SCALAR rules and return the result of comparison. MEMs
* should be scalars. Original MEMs are not changed.
*/
int
-mem_cmp_scalar(const struct Mem *a, const struct Mem *b, int *result,
+mem_cmp_scalar(const struct Mem *a, const struct Mem *b,
const struct coll *coll);
/**
@@ -716,6 +675,14 @@ int
mem_cmp_msgpack(const struct Mem *a, const char **b, int *result,
const struct coll *coll);
+/**
+ * Compare two MEMs using implicit cast rules and return the result of
+ * comparison. MEMs should be scalars. Original MEMs are not changed.
+ */
+int
+mem_cmp(const struct Mem *a, const struct Mem *b, int *result,
+ const struct coll *coll);
+
/**
* Convert the given MEM to INTEGER. This function and the function below define
* the rules that are used to convert values of all other types to INTEGER. In
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index f05da5051..2e147ec0b 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -1483,231 +1483,134 @@ case OP_Cast: { /* in1 */
/* Opcode: Eq P1 P2 P3 P4 P5
* Synopsis: IF r[P3]==r[P1]
*
- * Compare the values in register P1 and P3. If reg(P3)==reg(P1) then
- * 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 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
- * of comparison is true. If either operand is NULL then the result is false.
- * If neither operand is NULL the result is the same as it would be if
- * the SQL_NULLEQ flag were omitted from P5.
- * P5 also can contain type to be applied to operands. Note that
- * the type conversions are stored back into the input registers
- * P1 and P3. So this opcode can cause persistent changes to
- * registers P1 and P3.
- *
- * If both SQL_STOREP2 and SQL_KEEPNULL flags are set then the
- * content of r[P2] is only changed if the new value is NULL or false.
- * In other words, a prior r[P2] value will not be overwritten by true.
+ * Compare the values in register P1 and P3. If r[P3] == r[P1], then the action
+ * is performed. The action is to jump to address P2 or store the comparison
+ * result in register P2 if the SQL_STOREP2 flag is set in P5. In case both
+ * values are STRINGs and collation is used for comparison, the collation is
+ * specified in P4. If SQL_NULLEQ is set in P5, then the result of the
+ * comparison is always either TRUE or FALSE and will never be NULL.
*/
/* Opcode: Ne P1 P2 P3 P4 P5
* Synopsis: IF r[P3]!=r[P1]
*
- * This works just like the Eq opcode except that the jump is taken if
- * the operands in registers P1 and P3 are not equal. See the Eq opcode for
- * additional information.
- *
- * If both SQL_STOREP2 and SQL_KEEPNULL flags are set then the
- * content of r[P2] is only changed if the new value is NULL or true.
- * In other words, a prior r[P2] value will not be overwritten by false.
+ * This works just like the Eq opcode except that the action is performed if
+ * r[P3] != r[P1]. See the Eq opcode for additional information.
*/
+case OP_Eq: /* same as TK_EQ, jump, in1, in3 */
+case OP_Ne: { /* same as TK_NE, jump, in1, in3 */
+ pIn1 = &aMem[pOp->p1];
+ pIn3 = &aMem[pOp->p3];
+ if (mem_is_any_null(pIn1, pIn3) && (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) != 0) {
+ pOut = vdbe_prepare_null_out(p, pOp->p2);
+ iCompare = 1;
+ REGISTER_TRACE(p, pOp->p2, pOut);
+ break;
+ }
+ VdbeBranchTaken(2, 3);
+ if ((pOp->p5 & SQL_JUMPIFNULL) != 0)
+ goto jump_to_p2;
+ break;
+ }
+ int cmp_res;
+ if (mem_cmp(pIn3, pIn1, &cmp_res, pOp->p4.pColl) != 0)
+ goto abort_due_to_error;
+ bool result = pOp->opcode == OP_Eq ? cmp_res == 0 : cmp_res != 0;
+ if ((pOp->p5 & SQL_STOREP2) != 0) {
+ iCompare = cmp_res;
+ pOut = &aMem[pOp->p2];
+ mem_set_bool(pOut, result);
+ REGISTER_TRACE(p, pOp->p2, pOut);
+ break;
+ }
+ VdbeBranchTaken(result, (pOp->p5 & SQL_NULLEQ) != 0 ? 2 : 3);
+ if (result)
+ goto jump_to_p2;
+ break;
+}
+
/* Opcode: Lt P1 P2 P3 P4 P5
* Synopsis: IF r[P3]<r[P1]
*
- * Compare the values in register P1 and P3. If reg(P3)<reg(P1) then
- * jump to address P2. Or if the SQL_STOREP2 flag is set in P5 store
- * the result of comparison (false or true or NULL) into register P2.
- *
- * If the SQL_JUMPIFNULL bit of P5 is set and either reg(P1) or
- * 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.
+ * Compare the values in register P1 and P3. If r[P3] < r[P1], then the action
+ * is performed. The action is to jump to address P2 or store the comparison
+ * result in register P2 if the SQL_STOREP2 flag is set in P5. In case both
+ * values are STRINGs and collation is used for comparison, the collation is
+ * specified in P4.
*/
/* Opcode: Le P1 P2 P3 P4 P5
* Synopsis: IF r[P3]<=r[P1]
*
- * This works just like the Lt opcode except that the jump is taken if
- * the content of register P3 is less than or equal to the content of
- * register P1. See the Lt opcode for additional information.
+ * This works just like the Lt opcode except that the action is performed if
+ * r[P3] <= r[P1]. See the Lt opcode for additional information.
*/
/* Opcode: Gt P1 P2 P3 P4 P5
* Synopsis: IF r[P3]>r[P1]
*
- * This works just like the Lt opcode except that the jump is taken if
- * the content of register P3 is greater than the content of
- * register P1. See the Lt opcode for additional information.
+ * This works just like the Lt opcode except that the action is performed if
+ * r[P3] > r[P1]. See the Lt opcode for additional information.
*/
/* Opcode: Ge P1 P2 P3 P4 P5
* Synopsis: IF r[P3]>=r[P1]
*
- * This works just like the Lt opcode except that the jump is taken if
- * the content of register P3 is greater than or equal to the content of
- * register P1. See the Lt opcode for additional information.
+ * This works just like the Lt opcode except that the action is performed if
+ * r[P3] >= r[P1]. See the Lt opcode for additional information.
*/
-case OP_Eq: /* same as TK_EQ, jump, in1, in3 */
-case OP_Ne: /* same as TK_NE, jump, in1, in3 */
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 */
-
pIn1 = &aMem[pOp->p1];
pIn3 = &aMem[pOp->p3];
- enum field_type type = pOp->p5 & FIELD_TYPE_MASK;
if (mem_is_any_null(pIn1, pIn3)) {
- /* 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(!mem_is_cleared(pIn1));
- assert((pOp->p5 & SQL_JUMPIFNULL)==0);
- if (mem_is_same_type(pIn1, pIn3) &&
- !mem_is_cleared(pIn3)) {
- 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.
- */
- if (pOp->p5 & SQL_STOREP2) {
- pOut = vdbe_prepare_null_out(p, pOp->p2);
- iCompare = 1; /* Operands are not equal */
- REGISTER_TRACE(p, pOp->p2, pOut);
- } else {
- VdbeBranchTaken(2,3);
- if (pOp->p5 & SQL_JUMPIFNULL) {
- goto jump_to_p2;
- }
- }
+ if ((pOp->p5 & SQL_STOREP2) != 0) {
+ pOut = vdbe_prepare_null_out(p, pOp->p2);
+ iCompare = 1;
+ REGISTER_TRACE(p, pOp->p2, pOut);
break;
}
- } else if (mem_is_bool(pIn3) || mem_is_bool(pIn1)) {
- if (mem_cmp_bool(pIn3, pIn1, &res) != 0) {
- const char *str = !mem_is_bool(pIn3) ?
- mem_str(pIn3) : mem_str(pIn1);
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH, str,
- "boolean");
- goto abort_due_to_error;
- }
- } else if (((pIn3->type | pIn1->type) & MEM_TYPE_UUID) != 0) {
- if (mem_cmp_uuid(pIn3, pIn1, &res) != 0) {
- const char *str = pIn3->type != MEM_TYPE_UUID ?
- mem_str(pIn3) : mem_str(pIn1);
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH, str,
- "uuid");
- goto abort_due_to_error;
- }
- } else if (mem_is_bin(pIn3) || mem_is_bin(pIn1)) {
- if (mem_cmp_bin(pIn3, pIn1, &res) != 0) {
- const char *str = !mem_is_bin(pIn3) ?
- mem_str(pIn3) : mem_str(pIn1);
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH, str,
- "varbinary");
- goto abort_due_to_error;
- }
- } else if (mem_is_map(pIn3) || mem_is_map(pIn1) || mem_is_array(pIn3) ||
- mem_is_array(pIn1)) {
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
- mem_str(pIn3), mem_type_to_str(pIn1));
- goto abort_due_to_error;
- } else if (type == FIELD_TYPE_STRING) {
- if (mem_cmp_str(pIn3, pIn1, &res, pOp->p4.pColl) != 0) {
- const char *str =
- mem_cast_implicit_old(pIn3, type) != 0 ?
- mem_str(pIn3) : mem_str(pIn1);
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH, str,
- "string");
- goto abort_due_to_error;
- }
- } else if (sql_type_is_numeric(type) || mem_is_num(pIn3) ||
- mem_is_num(pIn1)) {
- type = FIELD_TYPE_NUMBER;
- if (mem_cmp_num(pIn3, pIn1, &res) != 0) {
- const char *str =
- mem_cast_implicit_old(pIn3, type) != 0 ?
- mem_str(pIn3) : mem_str(pIn1);
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH, str,
- "number");
- goto abort_due_to_error;
- }
- } else {
- type = FIELD_TYPE_STRING;
- assert(mem_is_str(pIn3) && mem_is_same_type(pIn3, pIn1));
- if (mem_cmp_str(pIn3, pIn1, &res, pOp->p4.pColl) != 0) {
- const char *str =
- mem_cast_implicit_old(pIn3, type) != 0 ?
- mem_str(pIn3) : mem_str(pIn1);
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH, str,
- "string");
- goto abort_due_to_error;
- }
+ VdbeBranchTaken(2,3);
+ if ((pOp->p5 & SQL_JUMPIFNULL) != 0)
+ goto jump_to_p2;
+ break;
}
+ int cmp_res;
+ if (mem_cmp(pIn3, pIn1, &cmp_res, pOp->p4.pColl) != 0)
+ goto abort_due_to_error;
- 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;
+ bool result;
+ switch(pOp->opcode) {
+ 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;
- }
- pOut = vdbe_prepare_null_out(p, pOp->p2);
- mem_set_bool(pOut, res2);
+ if ((pOp->p5 & SQL_STOREP2) != 0) {
+ iCompare = cmp_res;
+ pOut = &aMem[pOp->p2];
+ mem_set_bool(pOut, result);
REGISTER_TRACE(p, pOp->p2, pOut);
- } else {
- VdbeBranchTaken(res!=0, (pOp->p5 & SQL_NULLEQ)?2:3);
- if (res2) {
- goto jump_to_p2;
- }
+ break;
}
+ VdbeBranchTaken(result, 3);
+ if (result)
+ goto jump_to_p2;
break;
}
@@ -1810,11 +1713,7 @@ case OP_Compare: {
bool is_rev = def->parts[i].sort_order == SORT_ORDER_DESC;
struct Mem *a = &aMem[p1+idx];
struct Mem *b = &aMem[p2+idx];
- if (mem_cmp_scalar(a, b, &iCompare, coll) != 0) {
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(b),
- mem_type_to_str(a));
- goto abort_due_to_error;
- }
+ iCompare = mem_cmp_scalar(a, b, coll);
if (iCompare) {
if (is_rev)
iCompare = -iCompare;
diff --git a/src/box/sql/where.c b/src/box/sql/where.c
index e2eb153fb..16766f2f8 100644
--- a/src/box/sql/where.c
+++ b/src/box/sql/where.c
@@ -1272,27 +1272,11 @@ whereRangeSkipScanEst(Parse * pParse, /* Parsing & code generating context */
rc = sql_stat4_column(db, samples[i].sample_key, nEq,
&pVal);
if (rc == 0 && p1 != NULL) {
- int res;
- rc = mem_cmp_scalar(p1, pVal, &res, coll);
- if (rc != 0) {
- diag_set(ClientError,
- ER_SQL_TYPE_MISMATCH,
- mem_str(pVal),
- mem_type_to_str(p1));
- }
- if (rc == 0 && res >= 0)
+ if (mem_cmp_scalar(p1, pVal, coll) >= 0)
nLower++;
}
if (rc == 0 && p2 != NULL) {
- int res;
- rc = mem_cmp_scalar(p2, pVal, &res, coll);
- if (rc != 0) {
- diag_set(ClientError,
- ER_SQL_TYPE_MISMATCH,
- mem_str(pVal),
- mem_type_to_str(p2));
- }
- if (rc == 0 && res >= 0)
+ if (mem_cmp_scalar(p2, pVal, coll) >= 0)
nUpper++;
}
}
diff --git a/test/sql-tap/cast.test.lua b/test/sql-tap/cast.test.lua
index 8af99dbde..b8ad23317 100755
--- a/test/sql-tap/cast.test.lua
+++ b/test/sql-tap/cast.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
-test:plan(103)
+test:plan(107)
--!./tcltestrunner.lua
-- 2005 June 25
@@ -1107,4 +1107,41 @@ test:execsql([[
DROP TABLE t5;
]])
+--
+-- Make sure that implicit cast from STRING to number was removed during
+-- comparison where index is not used.
+--
+
+test:do_catchsql_test(
+ "cast-10.1",
+ [[
+ SELECT 1 < '2';
+ ]], {
+ 1, "Type mismatch: can not convert string('2') to number"
+ })
+
+test:do_catchsql_test(
+ "cast-10.2",
+ [[
+ SELECT '1' < 2;
+ ]], {
+ 1, "Type mismatch: can not convert integer(2) to string"
+ })
+
+test:do_catchsql_test(
+ "cast-10.3",
+ [[
+ SELECT 1.5 < '2';
+ ]], {
+ 1, "Type mismatch: can not convert string('2') to number"
+ })
+
+test:do_catchsql_test(
+ "cast-10.4",
+ [[
+ SELECT '1' < 2.5;
+ ]], {
+ 1, "Type mismatch: can not convert double(2.5) to string"
+ })
+
test:finish_test()
diff --git a/test/sql-tap/func5.test.lua b/test/sql-tap/func5.test.lua
index 9b1526aaf..98a8b9ada 100755
--- a/test/sql-tap/func5.test.lua
+++ b/test/sql-tap/func5.test.lua
@@ -305,11 +305,13 @@ test:do_execsql_test(
SELECT ifnull(null, 'qqq2') = 'qqq2';
]], { true } )
-test:do_execsql_test(
+test:do_catchsql_test(
"func-6.3-ifnull",
[[
SELECT ifnull(null, 1) = 'qqq2';
- ]], { false } )
+ ]], {
+ 1, "Type mismatch: can not convert string('qqq2') to number"
+ })
box.func.COUNTER1:drop()
box.func.COUNTER2:drop()
diff --git a/test/sql-tap/identifier_case.test.lua b/test/sql-tap/identifier_case.test.lua
index 84b6078fd..097fa1b64 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}}},
- { 6, [[ 5 < 'b' collate unicode ]], {1,"Collation 'UNICODE' does not exist"}},
- { 7, [[ 5 < 'b' collate "unicode_ci" ]], {0, {true}}},
- { 8, [[ 5 < 'b' collate NONE ]], {1, "Collation 'NONE' does not exist"}},
- { 9, [[ 5 < 'b' collate "none" ]], {0, {true}}},
+ { 5, [[ '5' < 'b' collate "unicode" ]], {0, {true}}},
+ { 6, [[ '5' < 'b' collate unicode ]], {1,"Collation 'UNICODE' does not exist"}},
+ { 7, [[ '5' < 'b' collate "unicode_ci" ]], {0, {true}}},
+ { 8, [[ '5' < 'b' collate NONE ]], {1, "Collation 'NONE' does not exist"}},
+ { 9, [[ '5' < 'b' collate "none" ]], {0, {true}}},
}
for _, row in ipairs(data) do
diff --git a/test/sql-tap/in1.test.lua b/test/sql-tap/in1.test.lua
index bdf1f207a..14aefd15d 100755
--- a/test/sql-tap/in1.test.lua
+++ b/test/sql-tap/in1.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
-test:plan(80)
+test:plan(79)
--!./tcltestrunner.lua
-- 2001 September 15
@@ -636,19 +636,6 @@ test:do_execsql_test(
-- </in-11.1>
})
-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, {
- -- <in-11.2>
- 1, 2
- -- </in-11.2>
- })
-
test:do_execsql_test(
"in-11.5",
[[
diff --git a/test/sql-tap/index1.test.lua b/test/sql-tap/index1.test.lua
index 6a7450bb7..3230556d1 100755
--- a/test/sql-tap/index1.test.lua
+++ b/test/sql-tap/index1.test.lua
@@ -778,7 +778,7 @@ test:do_catchsql_test(
SELECT c FROM t6 WHERE a>123;
]], {
-- <index-14.6>
- 1, "Type mismatch: can not convert string('') to number"
+ 1, "Type mismatch: can not convert integer(123) to string"
-- </index-14.6>
})
@@ -788,7 +788,7 @@ test:do_catchsql_test(
SELECT c FROM t6 WHERE a>=123;
]], {
-- <index-14.7>
- 1, "Type mismatch: can not convert string('') to number"
+ 1, "Type mismatch: can not convert integer(123) to string"
-- </index-14.7>
})
diff --git a/test/sql-tap/insert3.test.lua b/test/sql-tap/insert3.test.lua
index 5469fe06b..062fb1e33 100755
--- a/test/sql-tap/insert3.test.lua
+++ b/test/sql-tap/insert3.test.lua
@@ -59,7 +59,7 @@ test:do_execsql_test(
[[
CREATE TABLE log2(rowid INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT UNIQUE,y INT );
CREATE TRIGGER r2 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
- UPDATE log2 SET y=y+1 WHERE x=new.b;
+ UPDATE log2 SET y = y+1 WHERE x = CAST(new.b AS STRING);
INSERT OR IGNORE INTO log2(x, y) VALUES(CAST(new.b AS STRING),1);
END;
INSERT INTO t1(a, b) VALUES('hi', 453);
diff --git a/test/sql-tap/join.test.lua b/test/sql-tap/join.test.lua
index 2eb884d32..48639a7b3 100755
--- a/test/sql-tap/join.test.lua
+++ b/test/sql-tap/join.test.lua
@@ -1038,13 +1038,13 @@ test:do_execsql_test(
-- </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 string('1') to number"
-- </join-11.10>
})
diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua
index 52c7945fe..f207d3e92 100755
--- a/test/sql-tap/misc1.test.lua
+++ b/test/sql-tap/misc1.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
-test:plan(59)
+test:plan(58)
--!./tcltestrunner.lua
-- 2001 September 15.
@@ -90,7 +90,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"
@@ -100,7 +100,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"
@@ -111,7 +111,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"
@@ -131,7 +131,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>
@@ -142,7 +142,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>
@@ -153,7 +153,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>
@@ -164,7 +164,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>
@@ -478,13 +478,14 @@ test:do_execsql_test(
-- </misc1-10.0>
})
local where = ""
+local where_part = ""
+for i = 1, 99, 1 do
+ where_part = where_part .. " AND CAST(x"..i.." AS NUMBER) <> 0"
+end
test:do_test(
"misc1-10.1",
function()
- where = "WHERE x0>=0"
- for i = 1, 99, 1 do
- where = where .. " AND x"..i.."<>0"
- end
+ where = "WHERE CAST(x0 AS NUMBER) >= 0" .. where_part
return test:catchsql("SELECT count(*) FROM manycol "..where.."")
end, {
-- <misc1-10.1>
@@ -498,7 +499,7 @@ test:do_test(
test:do_test(
"misc1-10.3",
function()
- where = string.gsub(where,"x0>=0", "x0=0")
+ where = "WHERE CAST(x0 AS NUMBER) = 0" .. where_part
return test:catchsql("DELETE FROM manycol "..where.."")
end, {
-- <misc1-10.3>
@@ -522,7 +523,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"
@@ -533,7 +534,7 @@ local cast = "CAST(CAST(x1 AS INTEGER) + 1 AS STRING)"
test:do_test(
"misc1-10.7",
function()
- where = string.gsub(where, "x0=0", "x0=100")
+ where = "WHERE CAST(x0 AS NUMBER) = 100" .. where_part
return test:catchsql("UPDATE manycol SET x1 = "..cast.." "..where..";")
end, {
-- <misc1-10.7>
@@ -544,7 +545,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"
@@ -566,7 +567,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"
@@ -630,16 +631,6 @@ test:do_execsql_test(
-- </misc1-12.1>
})
-test:do_execsql_test(
- "misc1-12.2",
- [[
- SELECT '0'==0.0
- ]], {
- -- <misc1-12.2>
- true
- -- </misc1-12.2>
- })
-
test:do_execsql_test(
"misc1-12.3",
[[
diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua
index 06641a60e..dbc6e193d 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
]], {
-- <select1-2.5.3>
- 1, "Type mismatch: can not convert string('This is a string that is too big to fit inside a NBFS buffer') to number"
+ 1, "Type mismatch: can not convert integer(5) to string"
-- </select1-2.5.3>
})
@@ -1916,7 +1916,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"
@@ -1926,7 +1926,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 6b8f385e9..815f9110b 100755
--- a/test/sql-tap/select7.test.lua
+++ b/test/sql-tap/select7.test.lua
@@ -255,7 +255,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 NUMBER) < b;
]], {
-- <select7-7.7>
"string", "123"
diff --git a/test/sql-tap/sql-errors.test.lua b/test/sql-tap/sql-errors.test.lua
index 17fe4b5b2..409c97257 100755
--- a/test/sql-tap/sql-errors.test.lua
+++ b/test/sql-tap/sql-errors.test.lua
@@ -766,7 +766,7 @@ test:do_catchsql_test(
SELECT X'ff' >= false;
]], {
-- <sql-errors-2.8>
- 1, "Type mismatch: can not convert varbinary(x'FF') to boolean"
+ 1, "Type mismatch: can not convert boolean(FALSE) to varbinary"
-- </sql-errors-2.8>
})
@@ -776,7 +776,7 @@ test:do_catchsql_test(
SELECT X'ff' <= false;
]], {
-- <sql-errors-2.9>
- 1, "Type mismatch: can not convert varbinary(x'FF') to boolean"
+ 1, "Type mismatch: can not convert boolean(FALSE) to varbinary"
-- </sql-errors-2.9>
})
diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua
index 44032548f..1c5b3d02e 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 string('10') to number"
-- </subquery-2.3.2>
})
diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua
index bee4fdf6c..43322468d 100755
--- a/test/sql-tap/tkt-9a8b09f8e6.test.lua
+++ b/test/sql-tap/tkt-9a8b09f8e6.test.lua
@@ -79,23 +79,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 integer(1) to string"
-- </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 double(1.0) to string"
-- </2.2>
})
@@ -119,23 +119,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 integer(1) to string"
-- </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 double(1.0) to string"
-- </2.6>
})
@@ -439,23 +439,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 string('1') to number"
-- </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 string('1.0') to number"
-- </6.4>
})
@@ -479,23 +479,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 string('1') to number"
-- </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 string('1.0') to number"
-- </6.8>
})
diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua
index 0aac0ddb9..77c84a994 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,14 +79,14 @@ 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"
-- </tkt3493-1.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"tkt3493-1.5",
[[
SELECT DISTINCT
@@ -95,7 +95,7 @@ test:do_execsql_test(
FROM b;
]], {
-- <tkt3493-1.5>
- "1", "xyz", "2", "2"
+ 1, "Type mismatch: can not convert integer(1) to string"
-- </tkt3493-1.5>
})
@@ -123,23 +123,23 @@ test:do_execsql_test(
-- </tkt3493-2.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"tkt3493-2.2.1",
[[
SELECT a=123 FROM t1 GROUP BY a
]], {
-- <tkt3493-2.2.1>
- true
+ 1, "Type mismatch: can not convert integer(123) to string"
-- </tkt3493-2.2.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"tkt3493-2.2.2",
[[
SELECT a=123 FROM t1
]], {
-- <tkt3493-2.2.2>
- true
+ 1, "Type mismatch: can not convert integer(123) to string"
-- </tkt3493-2.2.2>
})
@@ -153,93 +153,93 @@ test:do_execsql_test(
-- </tkt3493-2.2.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"tkt3493-2.2.4",
[[
SELECT count(*), a=123 FROM t1
]], {
-- <tkt3493-2.2.4>
- 1, true
+ 1, "Type mismatch: can not convert integer(123) to string"
-- </tkt3493-2.2.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"tkt3493-2.2.5",
[[
SELECT count(*), +a=123 FROM t1
]], {
-- <tkt3493-2.2.5>
- 1, true
+ 1, "Type mismatch: can not convert integer(123) to string"
-- </tkt3493-2.2.5>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"tkt3493-2.3.3",
[[
SELECT b='456' FROM t1 GROUP BY a
]], {
-- <tkt3493-2.3.3>
- true
+ 1, "Type mismatch: can not convert string('456') to number"
-- </tkt3493-2.3.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"tkt3493-2.3.1",
[[
SELECT b='456' FROM t1 GROUP BY b
]], {
-- <tkt3493-2.3.1>
- true
+ 1, "Type mismatch: can not convert string('456') to number"
-- </tkt3493-2.3.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"tkt3493-2.3.2",
[[
SELECT b='456' FROM t1
]], {
-- <tkt3493-2.3.2>
- true
+ 1, "Type mismatch: can not convert string('456') to number"
-- </tkt3493-2.3.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"tkt3493-2.4.1",
[[
SELECT typeof(a), a FROM t1 GROUP BY a HAVING a=123
]], {
-- <tkt3493-2.4.1>
- "string", "123"
+ 1, "Type mismatch: can not convert integer(123) to string"
-- </tkt3493-2.4.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"tkt3493-2.4.2",
[[
SELECT typeof(a), a FROM t1 GROUP BY b HAVING a=123
]], {
-- <tkt3493-2.4.2>
- "string", "123"
+ 1, "Type mismatch: can not convert integer(123) to string"
-- </tkt3493-2.4.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"tkt3493-2.5.1",
[[
SELECT typeof(b), b FROM t1 GROUP BY a HAVING b='456'
]], {
-- <tkt3493-2.5.1>
- "integer", 456
+ 1, "Type mismatch: can not convert string('456') to number"
-- </tkt3493-2.5.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"tkt3493-2.5.2",
[[
SELECT typeof(b), b FROM t1 GROUP BY b HAVING b='456'
]], {
-- <tkt3493-2.5.2>
- "integer", 456
+ 1, "Type mismatch: can not convert string('456') to number"
-- </tkt3493-2.5.2>
})
diff --git a/test/sql-tap/transitive1.test.lua b/test/sql-tap/transitive1.test.lua
index dbc2559fa..9250cdfc3 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 = '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 >= '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 <= '20' ORDER BY +a;
]], {
-- <transitive1-220>
20, 20, "20", 100, 100, "100"
@@ -402,7 +402,7 @@ test:do_execsql_test(
[[
CREATE TABLE x(i INTEGER PRIMARY KEY, y TEXT);
INSERT INTO x VALUES(10, '10');
- SELECT * FROM x WHERE x.y>='1' AND x.y<'2' AND x.i=x.y;
+ SELECT * FROM x WHERE x.y >= '1' AND x.y < '2' AND x.i = CAST(y AS INT);
]], {
-- <transitive1-500>
10, "10"
@@ -430,23 +430,23 @@ 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 i = CAST(t AS NUMBER) AND t = '10 ';
]], {
-- <transitive1-520>
-- </transitive1-520>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"transitive1-530",
[[
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 x = y AND y = z AND z = '013';
]], {
-- <transitive1-530>
- "00013",13,"013"
+ 1, "Type mismatch: can not convert integer(13) to string"
-- </transitive1-530>
})
diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
index abb5960a7..bbe912c72 100755
--- a/test/sql-tap/uuid.test.lua
+++ b/test/sql-tap/uuid.test.lua
@@ -1128,7 +1128,7 @@ test:do_catchsql_test(
[[
SELECT u > true FROM t2;
]], {
- 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to boolean"
+ 1, "Type mismatch: can not convert boolean(TRUE) to uuid"
})
test:do_execsql_test(
@@ -1192,7 +1192,7 @@ test:do_catchsql_test(
[[
SELECT u = true FROM t2;
]], {
- 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to boolean"
+ 1, "Type mismatch: can not convert boolean(TRUE) to uuid"
})
test:do_execsql_test(
diff --git a/test/sql-tap/where2.test.lua b/test/sql-tap/where2.test.lua
index 58b795cd2..de060c66d 100755
--- a/test/sql-tap/where2.test.lua
+++ b/test/sql-tap/where2.test.lua
@@ -1,7 +1,7 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
local ffi = require("ffi")
-test:plan(74)
+test:plan(64)
ffi.cdef[[
int dup(int oldfd);
@@ -617,7 +617,7 @@ test:do_test(
test:do_test(
"where2-6.7",
function()
- test:execsql [[
+ test:catchsql [[
CREATE TABLE t2249a(a TEXT PRIMARY KEY, x VARCHAR(100));
CREATE TABLE t2249b(b INTEGER PRIMARY KEY);
INSERT INTO t2249a(a) VALUES('0123');
@@ -628,7 +628,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 INT) = b;
]])
end, {
-- <where2-6.7>
@@ -642,7 +642,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 INT) = +b;
]])
end, {
-- <where2-6.9>
@@ -650,141 +650,6 @@ test:do_test(
-- </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>
- })
-
-
-
-- Variations on the order of terms in a WHERE clause in order
-- to make sure the OR optimizer can recognize them all.
test:do_test(
diff --git a/test/sql-tap/where5.test.lua b/test/sql-tap/where5.test.lua
index 3d3c3a0ca..400597257 100755
--- a/test/sql-tap/where5.test.lua
+++ b/test/sql-tap/where5.test.lua
@@ -34,7 +34,7 @@ test:do_test("where5-1.0", function()
INSERT INTO t3 SELECT CAST(x AS INTEGER) FROM t1;
]]
return test:execsql [[
- SELECT * FROM t1 WHERE x<0
+ SELECT * FROM t1 WHERE CAST(x AS INTEGER) < 0
]]
end, {
-- <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/boolean.result b/test/sql/boolean.result
index 81acdb691..6e7ad179f 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -138,12 +138,12 @@ INSERT INTO ts(s) VALUES ('abc'), (12.5);
SELECT s FROM ts WHERE s = true;
| ---
| - null
- | - 'Type mismatch: can not convert string(''abc'') to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to string'
| ...
SELECT s FROM ts WHERE s < true;
| ---
| - null
- | - 'Type mismatch: can not convert string(''abc'') to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to string'
| ...
SELECT s FROM ts WHERE s IN (true, 1, 'abcd');
| ---
@@ -3394,22 +3394,22 @@ SELECT false < 2;
SELECT 2 > true;
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT 2 > false;
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT 2 < true;
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT 2 < false;
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, a1 > 2 FROM t6
@@ -3425,12 +3425,12 @@ SELECT a1, a1 < 2 FROM t6
SELECT a1, 2 > a1 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, 2 < a1 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a2, a2 > 2 FROM t6
| ---
@@ -3445,12 +3445,12 @@ SELECT a2, a2 < 2 FROM t6
SELECT a2, 2 > a2 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT a2, 2 < a2 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT b, true > b FROM t7;
@@ -3476,22 +3476,22 @@ SELECT b, false < b FROM t7;
SELECT b, b > true FROM t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT b, b > false FROM t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT b, b < true FROM t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT b, b < false FROM t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, b, a1 > b FROM t6, t7;
@@ -3507,12 +3507,12 @@ SELECT a1, b, a1 < b FROM t6, t7;
SELECT a1, b, b > a1 FROM t6, t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, b, b < a1 FROM t6, t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a2, b, a2 > b FROM t6, t7;
| ---
@@ -3527,12 +3527,12 @@ SELECT a2, b, a2 < b FROM t6, t7;
SELECT a2, b, b > a2 FROM t6, t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT a2, b, b < a2 FROM t6, t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT true >= 2;
@@ -3558,22 +3558,22 @@ SELECT false <= 2;
SELECT 2 >= true;
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT 2 >= false;
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT 2 <= true;
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT 2 <= false;
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, a1 >= 2 FROM t6
@@ -3589,12 +3589,12 @@ SELECT a1, a1 <= 2 FROM t6
SELECT a1, 2 >= a1 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, 2 <= a1 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a2, a2 >= 2 FROM t6
| ---
@@ -3609,12 +3609,12 @@ SELECT a2, a2 <= 2 FROM t6
SELECT a2, 2 >= a2 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT a2, 2 <= a2 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT b, true >= b FROM t7;
@@ -3640,22 +3640,22 @@ SELECT b, false <= b FROM t7;
SELECT b, b >= true FROM t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT b, b >= false FROM t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT b, b <= true FROM t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT b, b <= false FROM t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, b, a1 >= b FROM t6, t7;
@@ -3671,12 +3671,12 @@ SELECT a1, b, a1 <= b FROM t6, t7;
SELECT a1, b, b >= a1 FROM t6, t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, b, b <= a1 FROM t6, t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a2, b, a2 >= b FROM t6, t7;
| ---
@@ -3691,12 +3691,12 @@ SELECT a2, b, a2 <= b FROM t6, t7;
SELECT a2, b, b >= a2 FROM t6, t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT a2, b, b <= a2 FROM t6, t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT true == 2;
@@ -3722,22 +3722,22 @@ SELECT false != 2;
SELECT 2 == true;
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT 2 == false;
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT 2 != true;
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT 2 != false;
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, a1 == 2 FROM t6
@@ -3753,12 +3753,12 @@ SELECT a1, a1 != 2 FROM t6
SELECT a1, 2 == a1 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, 2 != a1 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a2, a2 == 2 FROM t6
| ---
@@ -3773,12 +3773,12 @@ SELECT a2, a2 != 2 FROM t6
SELECT a2, 2 == a2 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT a2, 2 != a2 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert integer(2) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT b, true == b FROM t7;
@@ -3804,22 +3804,22 @@ SELECT b, false != b FROM t7;
SELECT b, b == true FROM t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT b, b == false FROM t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT b, b != true FROM t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT b, b != false FROM t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, b, a1 == b FROM t6, t7;
@@ -3835,12 +3835,12 @@ SELECT a1, b, a1 != b FROM t6, t7;
SELECT a1, b, b == a1 FROM t6, t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, b, b != a1 FROM t6, t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a2, b, a2 == b FROM t6, t7;
| ---
@@ -3855,12 +3855,12 @@ SELECT a2, b, a2 != b FROM t6, t7;
SELECT a2, b, b == a2 FROM t6, t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT a2, b, b != a2 FROM t6, t7;
| ---
| - null
- | - 'Type mismatch: can not convert integer(123) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT true IN (0, 1, 2, 3);
@@ -4539,22 +4539,22 @@ SELECT false < 2.3;
SELECT 2.3 > true;
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT 2.3 > false;
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT 2.3 < true;
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT 2.3 < false;
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, a1 > 2.3 FROM t6
@@ -4570,12 +4570,12 @@ SELECT a1, a1 < 2.3 FROM t6
SELECT a1, 2.3 > a1 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, 2.3 < a1 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a2, a2 > 2.3 FROM t6
| ---
@@ -4590,12 +4590,12 @@ SELECT a2, a2 < 2.3 FROM t6
SELECT a2, 2.3 > a2 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT a2, 2.3 < a2 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT c, true > c FROM t8;
@@ -4621,22 +4621,22 @@ SELECT c, false < c FROM t8;
SELECT c, c > true FROM t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT c, c > false FROM t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT c, c < true FROM t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT c, c < false FROM t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, c, a1 > c FROM t6, t8;
@@ -4652,12 +4652,12 @@ SELECT a1, c, a1 < c FROM t6, t8;
SELECT a1, c, c > a1 FROM t6, t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, c, c < a1 FROM t6, t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a2, c, a2 > c FROM t6, t8;
| ---
@@ -4672,12 +4672,12 @@ SELECT a2, c, a2 < c FROM t6, t8;
SELECT a2, c, c > a2 FROM t6, t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT a2, c, c < a2 FROM t6, t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT true >= 2.3;
@@ -4703,22 +4703,22 @@ SELECT false <= 2.3;
SELECT 2.3 >= true;
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT 2.3 >= false;
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT 2.3 <= true;
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT 2.3 <= false;
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, a1 >= 2.3 FROM t6
@@ -4734,12 +4734,12 @@ SELECT a1, a1 <= 2.3 FROM t6
SELECT a1, 2.3 >= a1 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, 2.3 <= a1 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a2, a2 >= 2.3 FROM t6
| ---
@@ -4754,12 +4754,12 @@ SELECT a2, a2 <= 2.3 FROM t6
SELECT a2, 2.3 >= a2 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT a2, 2.3 <= a2 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT c, true >= c FROM t8;
@@ -4785,22 +4785,22 @@ SELECT c, false <= c FROM t8;
SELECT c, c >= true FROM t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT c, c >= false FROM t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT c, c <= true FROM t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT c, c <= false FROM t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, c, a1 >= c FROM t6, t8;
@@ -4816,12 +4816,12 @@ SELECT a1, c, a1 <= c FROM t6, t8;
SELECT a1, c, c >= a1 FROM t6, t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, c, c <= a1 FROM t6, t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a2, c, a2 >= c FROM t6, t8;
| ---
@@ -4836,12 +4836,12 @@ SELECT a2, c, a2 <= c FROM t6, t8;
SELECT a2, c, c >= a2 FROM t6, t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT a2, c, c <= a2 FROM t6, t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT true == 2.3;
@@ -4867,22 +4867,22 @@ SELECT false != 2.3;
SELECT 2.3 == true;
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT 2.3 == false;
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT 2.3 != true;
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT 2.3 != false;
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, a1 == 2.3 FROM t6
@@ -4898,12 +4898,12 @@ SELECT a1, a1 != 2.3 FROM t6
SELECT a1, 2.3 == a1 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, 2.3 != a1 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a2, a2 == 2.3 FROM t6
| ---
@@ -4918,12 +4918,12 @@ SELECT a2, a2 != 2.3 FROM t6
SELECT a2, 2.3 == a2 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT a2, 2.3 != a2 FROM t6
| ---
| - null
- | - 'Type mismatch: can not convert double(2.3) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT c, true == c FROM t8;
@@ -4949,22 +4949,22 @@ SELECT c, false != c FROM t8;
SELECT c, c == true FROM t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT c, c == false FROM t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT c, c != true FROM t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT c, c != false FROM t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, c, a1 == c FROM t6, t8;
@@ -4980,12 +4980,12 @@ SELECT a1, c, a1 != c FROM t6, t8;
SELECT a1, c, c == a1 FROM t6, t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a1, c, c != a1 FROM t6, t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to number'
| ...
SELECT a2, c, a2 == c FROM t6, t8;
| ---
@@ -5000,12 +5000,12 @@ SELECT a2, c, a2 != c FROM t6, t8;
SELECT a2, c, c == a2 FROM t6, t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT a2, c, c != a2 FROM t6, t8;
| ---
| - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to number'
| ...
SELECT true IN (0.1, 1.2, 2.3, 3.4);
@@ -5295,22 +5295,22 @@ SELECT false < 'abc';
SELECT 'abc' > true;
| ---
| - null
- | - 'Type mismatch: can not convert string(''abc'') to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to string'
| ...
SELECT 'abc' > false;
| ---
| - null
- | - 'Type mismatch: can not convert string(''abc'') to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to string'
| ...
SELECT 'abc' < true;
| ---
| - null
- | - 'Type mismatch: can not convert string(''abc'') to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to string'
| ...
SELECT 'abc' < false;
| ---
| - null
- | - 'Type mismatch: can not convert string(''abc'') to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to string'
| ...
SELECT d, true > d FROM t9;
@@ -5336,22 +5336,22 @@ SELECT d, false < d FROM t9;
SELECT d, d > true FROM t9;
| ---
| - null
- | - 'Type mismatch: can not convert string(''AsdF'') to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to string'
| ...
SELECT d, d > false FROM t9;
| ---
| - null
- | - 'Type mismatch: can not convert string(''AsdF'') to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to string'
| ...
SELECT d, d < true FROM t9;
| ---
| - null
- | - 'Type mismatch: can not convert string(''AsdF'') to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to string'
| ...
SELECT d, d < false FROM t9;
| ---
| - null
- | - 'Type mismatch: can not convert string(''AsdF'') to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to string'
| ...
SELECT a1, d, a1 > d FROM t6, t9;
@@ -5367,12 +5367,12 @@ SELECT a1, d, a1 < d FROM t6, t9;
SELECT a1, d, d > a1 FROM t6, t9;
| ---
| - null
- | - 'Type mismatch: can not convert string(''AsdF'') to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to string'
| ...
SELECT a1, d, d < a1 FROM t6, t9;
| ---
| - null
- | - 'Type mismatch: can not convert string(''AsdF'') to boolean'
+ | - 'Type mismatch: can not convert boolean(FALSE) to string'
| ...
SELECT a2, d, a2 > d FROM t6, t9;
| ---
@@ -5387,12 +5387,12 @@ SELECT a2, d, a2 < d FROM t6, t9;
SELECT a2, d, d > a2 FROM t6, t9;
| ---
| - null
- | - 'Type mismatch: can not convert string(''AsdF'') to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to string'
| ...
SELECT a2, d, d < a2 FROM t6, t9;
| ---
| - null
- | - 'Type mismatch: can not convert string(''AsdF'') to boolean'
+ | - 'Type mismatch: can not convert boolean(TRUE) to string'
| ...
SELECT true || 'abc';
diff --git a/test/sql/types.result b/test/sql/types.result
index 00c60cca9..07d5b46e4 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -605,11 +605,8 @@ box.execute("SELECT 18446744073709551615.0 > 18446744073709551615")
...
box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)")
---
-- metadata:
- - name: COLUMN_1
- type: boolean
- rows:
- - [true]
+- null
+- 'Type mismatch: can not convert integer(18446744073709551615) to string'
...
box.execute("SELECT 1 LIMIT 18446744073709551615;")
---
More information about the Tarantool-patches
mailing list