* [Tarantool-patches] [PATCH v2 2/6] sql: remove implicit cast from comparison opcodes
2021-08-06 6:42 [Tarantool-patches] [PATCH v2 0/6] Rework implicit cast Mergen Imeev via Tarantool-patches
2021-08-06 6:42 ` [Tarantool-patches] [PATCH v2 1/6] sql: rework implicit cast fo assignment Mergen Imeev via Tarantool-patches
@ 2021-08-06 6:42 ` Mergen Imeev via Tarantool-patches
2021-08-06 6:42 ` [Tarantool-patches] [PATCH v2 3/6] sql: rework OP_Seek* opcodes Mergen Imeev via Tarantool-patches
` (4 subsequent siblings)
6 siblings, 0 replies; 9+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-06 6:42 UTC (permalink / raw)
To: kyukhin; +Cc: tarantool-patches
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
---
src/box/sql/func.c | 27 +--
src/box/sql/mem.c | 336 ++++++++++----------------
src/box/sql/mem.h | 51 +---
src/box/sql/vdbe.c | 287 +++++++---------------
src/box/sql/where.c | 20 +-
test/sql-tap/cast.test.lua | 39 ++-
test/sql-tap/func5.test.lua | 6 +-
test/sql-tap/identifier_case.test.lua | 10 +-
test/sql-tap/in1.test.lua | 15 +-
test/sql-tap/index1.test.lua | 4 +-
test/sql-tap/insert3.test.lua | 2 +-
test/sql-tap/join.test.lua | 4 +-
test/sql-tap/misc1.test.lua | 45 ++--
test/sql-tap/select1.test.lua | 6 +-
test/sql-tap/select7.test.lua | 2 +-
test/sql-tap/sql-errors.test.lua | 4 +-
test/sql-tap/subquery.test.lua | 4 +-
test/sql-tap/tkt-9a8b09f8e6.test.lua | 32 +--
test/sql-tap/tkt3493.test.lua | 54 ++---
test/sql-tap/transitive1.test.lua | 16 +-
test/sql-tap/uuid.test.lua | 4 +-
test/sql-tap/where2.test.lua | 143 +----------
test/sql-tap/where5.test.lua | 12 +-
test/sql/boolean.result | 220 ++++++++---------
test/sql/types.result | 7 +-
25 files changed, 494 insertions(+), 856 deletions(-)
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;")
---
--
2.25.1
^ permalink raw reply [flat|nested] 9+ messages in thread
* [Tarantool-patches] [PATCH v2 3/6] sql: rework OP_Seek* opcodes
2021-08-06 6:42 [Tarantool-patches] [PATCH v2 0/6] Rework implicit cast Mergen Imeev via Tarantool-patches
2021-08-06 6:42 ` [Tarantool-patches] [PATCH v2 1/6] sql: rework implicit cast fo assignment Mergen Imeev via Tarantool-patches
2021-08-06 6:42 ` [Tarantool-patches] [PATCH v2 2/6] sql: remove implicit cast from comparison opcodes Mergen Imeev via Tarantool-patches
@ 2021-08-06 6:42 ` Mergen Imeev via Tarantool-patches
2021-08-06 6:42 ` [Tarantool-patches] [PATCH v2 4/6] sql: remove unnecessary calls of OP_ApplyType Mergen Imeev via Tarantool-patches
` (3 subsequent siblings)
6 siblings, 0 replies; 9+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-06 6:42 UTC (permalink / raw)
To: kyukhin; +Cc: tarantool-patches
This patch changes the Seek* opcodes that are used to search in space
using index. After the redesign, searches using these opcodes work
according to the new implicit casting rules. However, currently implicit
cast in these opcodes is not invoked since there is OP_ApplyType before
them. Unnecessary OP_ApplyType calls will be removed in next patch.
Part of 4230
Part of 4470
---
src/box/sql.c | 69 +------
src/box/sql/cursor.c | 14 --
src/box/sql/cursor.h | 1 -
src/box/sql/mem.c | 154 +++++++++++++++-
src/box/sql/mem.h | 8 +
src/box/sql/sqlInt.h | 8 -
src/box/sql/tarantoolInt.h | 3 +
src/box/sql/vdbe.c | 357 +++++++++++++++----------------------
8 files changed, 311 insertions(+), 303 deletions(-)
diff --git a/src/box/sql.c b/src/box/sql.c
index 433264abe..a6a7864f1 100644
--- a/src/box/sql.c
+++ b/src/box/sql.c
@@ -204,75 +204,20 @@ int tarantoolsqlPrevious(BtCursor *pCur, int *pRes)
return cursor_advance(pCur, pRes);
}
-int tarantoolsqlMovetoUnpacked(BtCursor *pCur, UnpackedRecord *pIdxKey,
- int *pRes)
+int
+sql_cursor_seek(struct BtCursor *cur, struct Mem *mems, uint32_t len, int *res)
{
struct region *region = &fiber()->gc;
size_t used = region_used(region);
- uint32_t tuple_size;
- const char *tuple =
- sql_vdbe_mem_encode_tuple(pIdxKey->aMem, pIdxKey->nField,
- &tuple_size, region);
+ uint32_t size;
+ const char *tuple = sql_vdbe_mem_encode_tuple(mems, len, &size, region);
if (tuple == NULL)
return -1;
- if (key_alloc(pCur, tuple_size) != 0)
+ if (key_alloc(cur, size) != 0)
return -1;
- memcpy(pCur->key, tuple, tuple_size);
+ memcpy(cur->key, tuple, size);
region_truncate(region, used);
-
- int rc, res_success;
- switch (pIdxKey->opcode) {
- default:
- /* "Unexpected opcode" */
- assert(0);
- case 255:
- /* Restore saved state. Just re-seek cursor.
- TODO: replace w/ named constant. */
- res_success = 0;
- break;
- case OP_SeekLT:
- pCur->iter_type = ITER_LT;
- res_success = -1; /* item<key */
- break;
- case OP_SeekLE:
- pCur->iter_type = (pCur->hints & OPFLAG_SEEKEQ) != 0 ?
- ITER_REQ : ITER_LE;
- res_success = 0; /* item==key */
- break;
- case OP_SeekGE:
- pCur->iter_type = (pCur->hints & OPFLAG_SEEKEQ) != 0 ?
- ITER_EQ : ITER_GE;
- res_success = 0; /* item==key */
- break;
- case OP_SeekGT:
- pCur->iter_type = ITER_GT;
- res_success = 1; /* item>key */
- break;
- case OP_NoConflict:
- case OP_NotFound:
- case OP_Found:
- case OP_IdxDelete:
- pCur->iter_type = ITER_EQ;
- res_success = 0;
- break;
- }
- rc = cursor_seek(pCur, pRes);
- if (*pRes == 0) {
- *pRes = res_success;
- /*
- * To select the first item in a row of equal items
- * (last item), sql comparator is configured to
- * return +1 (-1) if an item equals the key making it
- * impossible to distinguish from an item>key (item<key)
- * from comparator output alone.
- * To make it possible to learn if the current item
- * equals the key, the comparator sets eqSeen.
- */
- pIdxKey->eqSeen = 1;
- } else {
- *pRes = -1; /* -1 also means EOF */
- }
- return rc;
+ return cursor_seek(cur, res);
}
/*
diff --git a/src/box/sql/cursor.c b/src/box/sql/cursor.c
index bb2dae898..694fd9a7f 100644
--- a/src/box/sql/cursor.c
+++ b/src/box/sql/cursor.c
@@ -132,20 +132,6 @@ sqlCursorPayload(BtCursor *pCur, u32 offset, u32 amt, void *pBuf)
* is larger than pIdxKey.
*/
-int
-sqlCursorMovetoUnpacked(BtCursor * pCur, /* The cursor to be moved */
- UnpackedRecord * pIdxKey, /* Unpacked index key */
- int *pRes /* Write search results here */
- )
-{
- assert(pRes);
- assert(pIdxKey);
- assert((pCur->curFlags & BTCF_TaCursor) ||
- (pCur->curFlags & BTCF_TEphemCursor));
-
- return tarantoolsqlMovetoUnpacked(pCur, pIdxKey, pRes);
-}
-
int
sqlCursorNext(BtCursor *pCur, int *pRes)
{
diff --git a/src/box/sql/cursor.h b/src/box/sql/cursor.h
index 88e544191..b82d69e9c 100644
--- a/src/box/sql/cursor.h
+++ b/src/box/sql/cursor.h
@@ -60,7 +60,6 @@ void sqlCursorZero(BtCursor *);
*/
void
sql_cursor_close(struct BtCursor *cursor);
-int sqlCursorMovetoUnpacked(BtCursor *, UnpackedRecord * pUnKey, int *pRes);
int sqlCursorNext(BtCursor *, int *pRes);
int sqlCursorPrevious(BtCursor *, int *pRes);
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index 32fdd9add..e654cac41 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -45,6 +45,8 @@
#include "uuid/mp_uuid.h"
#include "mp_decimal.h"
+#define CMP_OLD_NEW(a, b, type) (((a) > (type)(b)) - ((a) < (type)(b)))
+
/*
* Make sure pMem->z points to a writable allocation of at least
* min(n,32) bytes.
@@ -662,6 +664,23 @@ int_to_double_precise(struct Mem *mem)
return 0;
}
+/**
+ * Cast MEM with negative INTEGER to DOUBLE. Doesn't fail. The return value
+ * is < 0 if the original value is less than the result, > 0 if the original
+ * value is greater than the result, and 0 if the cast is precise.
+ */
+static inline int
+int_to_double_forced(struct Mem *mem)
+{
+ assert(mem->type == MEM_TYPE_INT);
+ int64_t i = mem->u.i;
+ double d = (double)i;
+ mem->u.r = d;
+ mem->type = MEM_TYPE_DOUBLE;
+ mem->field_type = FIELD_TYPE_DOUBLE;
+ return CMP_OLD_NEW(i, d, int64_t);
+}
+
static inline int
uint_to_double_precise(struct Mem *mem)
{
@@ -676,6 +695,23 @@ uint_to_double_precise(struct Mem *mem)
return 0;
}
+/**
+ * Cast MEM with positive INTEGER to DOUBLE. Doesn't fail. The return value
+ * is < 0 if the original value is less than the result, > 0 if the original
+ * value is greater than the result, and 0 if the cast is precise.
+ */
+static inline int
+uint_to_double_forced(struct Mem *mem)
+{
+ assert(mem->type == MEM_TYPE_UINT);
+ uint64_t u = mem->u.u;
+ double d = (double)u;
+ mem->u.r = d;
+ mem->type = MEM_TYPE_DOUBLE;
+ mem->field_type = FIELD_TYPE_DOUBLE;
+ return CMP_OLD_NEW(u, d, uint64_t);
+}
+
static inline int
int_to_str0(struct Mem *mem)
{
@@ -868,6 +904,43 @@ double_to_int_precise(struct Mem *mem)
return -1;
}
+/**
+ * Cast MEM with DOUBLE to INTEGER. Doesn't fail. The return value is < 0 if the
+ * original value is less than the result, > 0 if the original value is greater
+ * than the result, and 0 if the cast is precise.
+ */
+static inline int
+double_to_int_forced(struct Mem *mem)
+{
+ assert(mem->type == MEM_TYPE_DOUBLE);
+ double d = mem->u.r;
+ int64_t i;
+ enum mem_type type;
+ int res;
+ if (d < (double)INT64_MIN) {
+ i = INT64_MIN;
+ type = MEM_TYPE_INT;
+ res = -1;
+ } else if (d >= (double)UINT64_MAX) {
+ i = (int64_t)UINT64_MAX;
+ type = MEM_TYPE_UINT;
+ res = 1;
+ } else if (d <= -1.0) {
+ i = (int64_t)d;
+ type = MEM_TYPE_INT;
+ res = CMP_OLD_NEW(d, i, double);
+ } else {
+ uint64_t u = (uint64_t)d;
+ i = (int64_t)u;
+ type = MEM_TYPE_UINT;
+ res = CMP_OLD_NEW(d, u, double);
+ }
+ mem->u.i = i;
+ mem->type = type;
+ mem->field_type = FIELD_TYPE_INTEGER;
+ return res;
+}
+
static inline int
double_to_uint(struct Mem *mem)
{
@@ -898,6 +971,34 @@ double_to_uint_precise(struct Mem *mem)
return -1;
}
+/**
+ * Cast MEM with DOUBLE to UNSIGNED. Doesn't fail. The return value is < 0 if
+ * the original value is less than the result, > 0 if the original value is
+ * greater than the result, and 0 if the cast is precise.
+ */
+static inline int
+double_to_uint_forced(struct Mem *mem)
+{
+ assert(mem->type == MEM_TYPE_DOUBLE);
+ double d = mem->u.r;
+ uint64_t u;
+ int res;
+ if (d < 0.0) {
+ u = 0;
+ res = -1;
+ } else if (d >= (double)UINT64_MAX) {
+ u = UINT64_MAX;
+ res = 1;
+ } else {
+ u = (uint64_t)d;
+ res = CMP_OLD_NEW(d, u, double);
+ }
+ mem->u.u = u;
+ mem->type = MEM_TYPE_UINT;
+ mem->field_type = FIELD_TYPE_UNSIGNED;
+ return res;
+}
+
static inline int
double_to_str0(struct Mem *mem)
{
@@ -1274,6 +1375,57 @@ mem_cast_implicit_old(struct Mem *mem, enum field_type type)
return -1;
}
+int
+mem_cast_implicit_number(struct Mem *mem, enum field_type type)
+{
+ assert(mem_is_num(mem) && sql_type_is_numeric(type));
+ switch (type) {
+ case FIELD_TYPE_UNSIGNED:
+ switch (mem->type) {
+ case MEM_TYPE_UINT:
+ mem->field_type = FIELD_TYPE_UNSIGNED;
+ return 0;
+ case MEM_TYPE_INT:
+ mem->u.u = 0;
+ mem->type = MEM_TYPE_UINT;
+ mem->field_type = FIELD_TYPE_UNSIGNED;
+ return -1;
+ case MEM_TYPE_DOUBLE:
+ return double_to_uint_forced(mem);
+ default:
+ unreachable();
+ }
+ break;
+ case FIELD_TYPE_DOUBLE:
+ switch (mem->type) {
+ case MEM_TYPE_INT:
+ return int_to_double_forced(mem);
+ case MEM_TYPE_UINT:
+ return uint_to_double_forced(mem);
+ case MEM_TYPE_DOUBLE:
+ return 0;
+ default:
+ unreachable();
+ }
+ break;
+ case FIELD_TYPE_INTEGER:
+ switch (mem->type) {
+ case MEM_TYPE_UINT:
+ case MEM_TYPE_INT:
+ mem->field_type = FIELD_TYPE_INTEGER;
+ return 0;
+ case MEM_TYPE_DOUBLE:
+ return double_to_int_forced(mem);
+ default:
+ unreachable();
+ }
+ break;
+ default:
+ unreachable();
+ }
+ return 0;
+}
+
int
mem_get_int(const struct Mem *mem, int64_t *i, bool *is_neg)
{
@@ -2520,8 +2672,6 @@ sqlVdbeRecordCompareMsgpack(const void *key1,
return -rc;
}
}
-
- key2->eqSeen = 1;
return key2->default_rc;
}
diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h
index 70bbe557b..4f61cbab0 100644
--- a/src/box/sql/mem.h
+++ b/src/box/sql/mem.h
@@ -746,6 +746,14 @@ mem_cast_implicit(struct Mem *mem, enum field_type type);
int
mem_cast_implicit_old(struct Mem *mem, enum field_type type);
+/**
+ * Cast MEM with numeric value to given numeric type. Doesn't fail. The return
+ * value is < 0 if the original value is less than the result, > 0 if the
+ * original value is greater than the result, and 0 if the cast is precise.
+ */
+int
+mem_cast_implicit_number(struct Mem *mem, enum field_type type);
+
/**
* Return value for MEM of INTEGER type. For MEM of all other types convert
* value of the MEM to INTEGER if possible and return converted value. Original
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index 115c52f96..e2e550978 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -1312,13 +1312,6 @@ sql_space_tuple_log_count(struct space *space);
* at the first key_def->part_count) then default_rc can be set to -1 to
* cause the search to find the last match, or +1 to cause the search to
* find the first match.
- *
- * The key comparison functions will set eqSeen to true if they ever
- * get and equal results when comparing this structure to a b-tree record.
- * When default_rc!=0, the search might end up on the record immediately
- * before the first match or immediately after the last match. The
- * eqSeen field will indicate whether or not an exact match exists in the
- * b-tree.
*/
struct UnpackedRecord {
/** Collation and sort-order information. */
@@ -1328,7 +1321,6 @@ struct UnpackedRecord {
i8 default_rc; /* Comparison result if keys are equal */
i8 r1; /* Value to return if (lhs > rhs) */
i8 r2; /* Value to return if (rhs < lhs) */
- u8 eqSeen; /* True if an equality comparison has been seen */
u8 opcode; /* Currently executing opcode that invoked
* movetoUnpacked, used by Tarantool storage layer.
*/
diff --git a/src/box/sql/tarantoolInt.h b/src/box/sql/tarantoolInt.h
index 1ded6c709..8fdc50432 100644
--- a/src/box/sql/tarantoolInt.h
+++ b/src/box/sql/tarantoolInt.h
@@ -27,6 +27,9 @@ int tarantoolsqlReplace(struct space *space, const char *tuple,
const char *tuple_end);
int tarantoolsqlDelete(BtCursor * pCur, u8 flags);
+int
+sql_cursor_seek(struct BtCursor *cur, struct Mem *mems, uint32_t len, int *res);
+
/**
* Delete entry from space by its key.
*
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 2e147ec0b..371be205e 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -2504,35 +2504,25 @@ case OP_Close: {
break;
}
-/* Opcode: SeekGE P1 P2 P3 P4 P5
+/* Opcode: SeekLT P1 P2 P3 P4 P5
* Synopsis: key=r[P3@P4]
*
* If cursor P1 refers to an SQL table (B-Tree that uses integer keys),
- * use the value in register P3 as the key. If cursor P1 refers
+ * use the value in register P3 as a key. If cursor P1 refers
* to an SQL index, then P3 is the first in an array of P4 registers
* that are used as an unpacked index key.
*
- * Reposition cursor P1 so that it points to the smallest entry that
- * is greater than or equal to the key value. If there are no records
- * greater than or equal to the key and P2 is not zero, then jump to P2.
- *
- * If the cursor P1 was opened using the OPFLAG_SEEKEQ flag, then this
- * opcode will always land on a record that equally equals the key, or
- * else jump immediately to P2. When the cursor is OPFLAG_SEEKEQ, this
- * opcode must be followed by an IdxLE opcode with the same arguments.
- * The IdxLE opcode will be skipped if this opcode succeeds, but the
- * IdxLE opcode will be used on subsequent loop iterations.
+ * Reposition cursor P1 so that it points to the largest entry that
+ * is less than the key value. If there are no records less than
+ * the key and P2 is not zero, then jump to P2.
*
- * This opcode leaves the cursor configured to move in forward order,
- * from the beginning toward the end. In other words, the cursor is
- * configured to use Next, not Prev.
+ * This opcode leaves the cursor configured to move in reverse order,
+ * from the end toward the beginning. In other words, the cursor is
+ * configured to use Prev, not Next.
*
- * If P5 is not zero, than it is offset of integer fields in input
- * vector. Force corresponding value to be INTEGER, in case it
- * is floating point value. Alongside with that, type of
- * iterator may be changed: a > 1.5 -> a >= 2.
+ * P5 has the same meaning as for SeekGE.
*
- * See also: Found, NotFound, SeekLt, SeekGt, SeekLe
+ * See also: Found, NotFound, SeekGt, SeekGe, SeekLe
*/
/* Opcode: SeekGT P1 P2 P3 P4 P5
* Synopsis: key=r[P3@P4]
@@ -2555,7 +2545,54 @@ case OP_Close: {
*
* P5 has the same meaning as for SeekGE.
*/
-/* Opcode: SeekLT P1 P2 P3 P4 P5
+case OP_SeekLT: /* jump, in3 */
+case OP_SeekGT: { /* jump, in3 */
+ bool is_lt = pOp->opcode == OP_SeekLT;
+ struct VdbeCursor *cur = p->apCsr[pOp->p1];
+#ifdef SQL_DEBUG
+ cur->seekOp = pOp->opcode;
+#endif
+ cur->nullRow = 0;
+ cur->uc.pCursor->iter_type = is_lt ? ITER_LT : ITER_GT;
+
+ uint32_t len = pOp->p4.i;
+ assert(pOp->p4type == P4_INT32);
+ assert(len <= cur->key_def->part_count);
+ struct Mem *mems = &aMem[pOp->p3];
+ bool is_op_change = false;
+ for (uint32_t i = 0; i < len; ++i) {
+ enum field_type type = cur->key_def->parts[i].type;
+ struct Mem *mem = &mems[i];
+ if (mem_is_field_compatible(mem, type))
+ continue;
+ if (!sql_type_is_numeric(type) || !mem_is_num(mem)) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ mem_str(mem), field_type_strs[type]);
+ goto abort_due_to_error;
+ }
+ int cmp = mem_cast_implicit_number(mem, type);
+ is_op_change = is_op_change || (is_lt && cmp > 0) ||
+ (!is_lt && cmp < 0);
+ }
+ if (is_op_change)
+ cur->uc.pCursor->iter_type = is_lt ? ITER_LE : ITER_GE;
+
+ int res;
+ if (sql_cursor_seek(cur->uc.pCursor, mems, len, &res) != 0)
+ goto abort_due_to_error;
+ assert((res != 0) == (cur->uc.pCursor->eState == CURSOR_INVALID));
+ cur->cacheStatus = CACHE_STALE;
+#ifdef SQL_TEST
+ sql_search_count++;
+#endif
+ assert(pOp->p2 > 0);
+ VdbeBranchTaken(res, 2);
+ if (res != 0)
+ goto jump_to_p2;
+ break;
+}
+
+/* Opcode: SeekLE P1 P2 P3 P4 P5
* Synopsis: key=r[P3@P4]
*
* If cursor P1 refers to an SQL table (B-Tree that uses integer keys),
@@ -2563,227 +2600,119 @@ case OP_Close: {
* to an SQL index, then P3 is the first in an array of P4 registers
* that are used as an unpacked index key.
*
- * Reposition cursor P1 so that it points to the largest entry that
- * is less than the key value. If there are no records less than
- * the key and P2 is not zero, then jump to P2.
+ * Reposition cursor P1 so that it points to the largest entry that
+ * is less than or equal to the key value. If there are no records
+ * less than or equal to the key and P2 is not zero, then jump to P2.
*
* This opcode leaves the cursor configured to move in reverse order,
* from the end toward the beginning. In other words, the cursor is
* configured to use Prev, not Next.
*
+ * If the cursor P1 was opened using the OPFLAG_SEEKEQ flag, then this
+ * opcode will always land on a record that equally equals the key, or
+ * else jump immediately to P2. When the cursor is OPFLAG_SEEKEQ, this
+ * opcode must be followed by an IdxGE opcode with the same arguments.
+ * The IdxGE opcode will be skipped if this opcode succeeds, but the
+ * IdxGE opcode will be used on subsequent loop iterations.
+ *
* P5 has the same meaning as for SeekGE.
*
- * See also: Found, NotFound, SeekGt, SeekGe, SeekLe
+ * See also: Found, NotFound, SeekGt, SeekGe, SeekLt
*/
-/* Opcode: SeekLE P1 P2 P3 P4 P5
+/* Opcode: SeekGE P1 P2 P3 P4 P5
* Synopsis: key=r[P3@P4]
*
* If cursor P1 refers to an SQL table (B-Tree that uses integer keys),
- * use the value in register P3 as a key. If cursor P1 refers
+ * use the value in register P3 as the key. If cursor P1 refers
* to an SQL index, then P3 is the first in an array of P4 registers
* that are used as an unpacked index key.
*
- * Reposition cursor P1 so that it points to the largest entry that
- * is less than or equal to the key value. If there are no records
- * less than or equal to the key and P2 is not zero, then jump to P2.
- *
- * This opcode leaves the cursor configured to move in reverse order,
- * from the end toward the beginning. In other words, the cursor is
- * configured to use Prev, not Next.
+ * Reposition cursor P1 so that it points to the smallest entry that
+ * is greater than or equal to the key value. If there are no records
+ * greater than or equal to the key and P2 is not zero, then jump to P2.
*
* If the cursor P1 was opened using the OPFLAG_SEEKEQ flag, then this
* opcode will always land on a record that equally equals the key, or
* else jump immediately to P2. When the cursor is OPFLAG_SEEKEQ, this
- * opcode must be followed by an IdxGE opcode with the same arguments.
- * The IdxGE opcode will be skipped if this opcode succeeds, but the
- * IdxGE opcode will be used on subsequent loop iterations.
+ * opcode must be followed by an IdxLE opcode with the same arguments.
+ * The IdxLE opcode will be skipped if this opcode succeeds, but the
+ * IdxLE opcode will be used on subsequent loop iterations.
*
- * P5 has the same meaning as for SeekGE.
+ * This opcode leaves the cursor configured to move in forward order,
+ * from the beginning toward the end. In other words, the cursor is
+ * configured to use Next, not Prev.
*
- * See also: Found, NotFound, SeekGt, SeekGe, SeekLt
+ * If P5 is not zero, than it is offset of integer fields in input
+ * vector. Force corresponding value to be INTEGER, in case it
+ * is floating point value. Alongside with that, type of
+ * iterator may be changed: a > 1.5 -> a >= 2.
+ *
+ * See also: Found, NotFound, SeekLt, SeekGt, SeekLe
*/
-case OP_SeekLT: /* jump, in3 */
case OP_SeekLE: /* jump, in3 */
-case OP_SeekGE: /* jump, in3 */
-case OP_SeekGT: { /* jump, in3 */
- int res; /* Comparison result */
- int oc; /* Opcode */
- VdbeCursor *pC; /* The cursor to seek */
- UnpackedRecord r; /* The key to seek for */
- int nField; /* Number of columns or fields in the key */
- i64 iKey; /* The id we are to seek to */
- int eqOnly; /* Only interested in == results */
-
- assert(pOp->p1>=0 && pOp->p1<p->nCursor);
- assert(pOp->p2!=0);
- pC = p->apCsr[pOp->p1];
- assert(pC!=0);
- assert(pC->eCurType==CURTYPE_TARANTOOL);
- assert(OP_SeekLE == OP_SeekLT+1);
- assert(OP_SeekGE == OP_SeekLT+2);
- assert(OP_SeekGT == OP_SeekLT+3);
- assert(pC->uc.pCursor!=0);
- oc = pOp->opcode;
- eqOnly = 0;
- pC->nullRow = 0;
+case OP_SeekGE: { /* jump, in3 */
+ bool is_le = pOp->opcode == OP_SeekLE;
+ struct VdbeCursor *cur = p->apCsr[pOp->p1];
#ifdef SQL_DEBUG
- pC->seekOp = pOp->opcode;
+ cur->seekOp = pOp->opcode;
#endif
- iKey = 0;
- /*
- * In case floating value is intended to be passed to
- * iterator over integer field, we must truncate it to
- * integer value and change type of iterator:
- * a > 1.5 -> a >= 2
- */
- int int_field = pOp->p5;
- bool is_neg = false;
-
- if (int_field > 0) {
- /* The input value in P3 might be of any type: integer, real, string,
- * blob, or NULL. But it needs to be an integer before we can do
- * the seek, so convert it.
- */
- pIn3 = &aMem[int_field];
- if (mem_is_null(pIn3))
- goto skip_truncate;
- if (mem_is_str(pIn3))
- mem_to_number(pIn3);
- int64_t i;
- if (mem_get_int(pIn3, &i, &is_neg) != 0) {
- if (!mem_is_double(pIn3)) {
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
- mem_str(pIn3), "integer");
- goto abort_due_to_error;
- }
- double d = pIn3->u.r;
- assert(d >= (double)INT64_MAX || d < (double)INT64_MIN);
- /* TODO: add [INT64_MAX, UINT64_MAX) here. */
- if (d > (double)INT64_MAX)
- i = INT64_MAX;
- else if (d < (double)INT64_MIN)
- i = INT64_MIN;
- else
- i = d;
- is_neg = i < 0;
+ cur->nullRow = 0;
+ bool is_eq = (cur->uc.pCursor->hints & OPFLAG_SEEKEQ) != 0;
+ if (is_le)
+ cur->uc.pCursor->iter_type = is_eq ? ITER_REQ : ITER_LE;
+ else
+ cur->uc.pCursor->iter_type = is_eq ? ITER_EQ : ITER_GE;
+ assert(!is_eq || pOp[1].opcode == OP_IdxLT ||
+ pOp[1].opcode == OP_IdxGT);
+
+ uint32_t len = pOp->p4.i;
+ assert(pOp->p4type == P4_INT32);
+ assert(len <= cur->key_def->part_count);
+ struct Mem *mems = &aMem[pOp->p3];
+ bool is_op_change = false;
+ bool is_zero = false;
+ for (uint32_t i = 0; i < len; ++i) {
+ enum field_type type = cur->key_def->parts[i].type;
+ struct Mem *mem = &mems[i];
+ if (mem_is_field_compatible(mem, type))
+ continue;
+ if (!sql_type_is_numeric(type) || !mem_is_num(mem)) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ mem_str(mem), field_type_strs[type]);
+ goto abort_due_to_error;
}
- iKey = i;
-
- /* If the P3 value could not be converted into an integer without
- * loss of information, then special processing is required...
+ int cmp = mem_cast_implicit_number(mem, type);
+ is_op_change = is_op_change || (is_le && cmp < 0) ||
+ (!is_le && cmp > 0);
+ /*
+ * In case search using EQ or REQ, we will not find anything if
+ * conversion cannot be precise.
*/
- if (!mem_is_int(pIn3)) {
- if (!mem_is_double(pIn3)) {
- /* If the P3 value cannot be converted into any kind of a number,
- * then the seek is not possible, so jump to P2
- */
- VdbeBranchTaken(1,2); goto jump_to_p2;
- break;
- }
-
- /* If the approximation iKey is larger than the actual real search
- * term, substitute >= for > and < for <=. e.g. if the search term
- * is 4.9 and the integer approximation 5:
- *
- * (x > 4.9) -> (x >= 5)
- * (x <= 4.9) -> (x < 5)
- */
- if (pIn3->u.r<(double)iKey) {
- assert(OP_SeekGE==(OP_SeekGT-1));
- assert(OP_SeekLT==(OP_SeekLE-1));
- assert((OP_SeekLE & 0x0001)==(OP_SeekGT & 0x0001));
- if ((oc & 0x0001)==(OP_SeekGT & 0x0001)) oc--;
- }
-
- /* If the approximation iKey is smaller than the actual real search
- * term, substitute <= for < and > for >=.
- */
- else if (pIn3->u.r>(double)iKey) {
- assert(OP_SeekLE==(OP_SeekLT+1));
- assert(OP_SeekGT==(OP_SeekGE+1));
- assert((OP_SeekLT & 0x0001)==(OP_SeekGE & 0x0001));
- if ((oc & 0x0001)==(OP_SeekLT & 0x0001)) oc++;
- }
- }
+ is_zero = is_zero || (is_eq && cmp != 0);
}
-skip_truncate:
- /*
- * For a cursor with the OPFLAG_SEEKEQ hint, only the
- * OP_SeekGE and OP_SeekLE opcodes are allowed, and these
- * must be immediately followed by an OP_IdxGT or
- * OP_IdxLT opcode, respectively, with the same key.
- */
- if ((pC->uc.pCursor->hints & OPFLAG_SEEKEQ) != 0) {
- eqOnly = 1;
- assert(pOp->opcode==OP_SeekGE || pOp->opcode==OP_SeekLE);
- assert(pOp[1].opcode==OP_IdxLT || pOp[1].opcode==OP_IdxGT);
- assert(pOp[1].p1==pOp[0].p1);
- assert(pOp[1].p2==pOp[0].p2);
- assert(pOp[1].p3==pOp[0].p3);
- assert(pOp[1].p4.i==pOp[0].p4.i);
+ if (is_zero) {
+ assert(pOp->p2 > 0);
+ VdbeBranchTaken(1, 2);
+ goto jump_to_p2;
}
+ if (!is_eq && is_op_change)
+ cur->uc.pCursor->iter_type = is_le ? ITER_LT : ITER_GT;
- nField = pOp->p4.i;
- assert(pOp->p4type==P4_INT32);
- assert(nField>0);
- r.key_def = pC->key_def;
- r.nField = (u16)nField;
-
- if (int_field > 0)
- mem_set_int(&aMem[int_field], iKey, is_neg);
-
- r.default_rc = ((1 & (oc - OP_SeekLT)) ? -1 : +1);
- assert(oc!=OP_SeekGT || r.default_rc==-1);
- assert(oc!=OP_SeekLE || r.default_rc==-1);
- assert(oc!=OP_SeekGE || r.default_rc==+1);
- assert(oc!=OP_SeekLT || r.default_rc==+1);
-
- r.aMem = &aMem[pOp->p3];
-#ifdef SQL_DEBUG
- { int i; for(i=0; i<r.nField; i++) assert(memIsValid(&r.aMem[i])); }
-#endif
- r.eqSeen = 0;
- r.opcode = oc;
- if (sqlCursorMovetoUnpacked(pC->uc.pCursor, &r, &res) != 0)
+ int res;
+ if (sql_cursor_seek(cur->uc.pCursor, mems, len, &res) != 0)
goto abort_due_to_error;
- if (eqOnly && r.eqSeen==0) {
- assert(res!=0);
- goto seek_not_found;
- }
- pC->cacheStatus = CACHE_STALE;
+ assert((res != 0) == (cur->uc.pCursor->eState == CURSOR_INVALID));
+ cur->cacheStatus = CACHE_STALE;
#ifdef SQL_TEST
sql_search_count++;
#endif
- if (oc>=OP_SeekGE) { assert(oc==OP_SeekGE || oc==OP_SeekGT);
- if (res<0 || (res==0 && oc==OP_SeekGT)) {
- res = 0;
- if (sqlCursorNext(pC->uc.pCursor, &res) != 0)
- goto abort_due_to_error;
- } else {
- res = 0;
- }
- } else {
- assert(oc==OP_SeekLT || oc==OP_SeekLE);
- if (res>0 || (res==0 && oc==OP_SeekLT)) {
- res = 0;
- if (sqlCursorPrevious(pC->uc.pCursor, &res) != 0)
- goto abort_due_to_error;
- } else {
- /* res might be negative because the table is empty. Check to
- * see if this is the case.
- */
- res = (CURSOR_VALID != pC->uc.pCursor->eState);
- }
- }
- seek_not_found:
- assert(pOp->p2>0);
- VdbeBranchTaken(res!=0,2);
- if (res) {
+ assert(pOp->p2 > 0);
+ VdbeBranchTaken(res, 2);
+ if (res != 0)
goto jump_to_p2;
- } else if (eqOnly) {
- assert(pOp[1].opcode==OP_IdxLT || pOp[1].opcode==OP_IdxGT);
- pOp++; /* Skip the OP_IdxLt or OP_IdxGT that follows */
- }
+ /* Skip the OP_IdxLT/OP_IdxGT that follows if we have EQ. */
+ if (is_eq)
+ pOp++;
break;
}
@@ -2910,7 +2839,9 @@ case OP_Found: { /* jump, in3 */
}
}
}
- rc = sqlCursorMovetoUnpacked(pC->uc.pCursor, pIdxKey, &res);
+ pC->uc.pCursor->iter_type = ITER_EQ;
+ rc = sql_cursor_seek(pC->uc.pCursor, pIdxKey->aMem, pIdxKey->nField,
+ &res);
if (pFree != NULL)
sqlDbFree(db, pFree);
if (rc != 0)
@@ -3768,7 +3699,6 @@ case OP_IdxDelete: {
VdbeCursor *pC;
BtCursor *pCrsr;
int res;
- UnpackedRecord r;
assert(pOp->p3>0);
assert(pOp->p2>0 && pOp->p2+pOp->p3<=(p->nMem+1 - p->nCursor)+1);
@@ -3779,12 +3709,7 @@ case OP_IdxDelete: {
pCrsr = pC->uc.pCursor;
assert(pCrsr!=0);
assert(pOp->p5==0);
- r.key_def = pC->key_def;
- r.nField = (u16)pOp->p3;
- r.default_rc = 0;
- r.aMem = &aMem[pOp->p2];
- r.opcode = OP_IdxDelete;
- if (sqlCursorMovetoUnpacked(pCrsr, &r, &res) != 0)
+ if (sql_cursor_seek(pCrsr, &aMem[pOp->p2], (u16)pOp->p3, &res) != 0)
goto abort_due_to_error;
if (res==0) {
assert(pCrsr->eState == CURSOR_VALID);
--
2.25.1
^ permalink raw reply [flat|nested] 9+ messages in thread