[Tarantool-patches] [PATCH v3 6/8] sql: remove implicit cast for comparison
imeevma at tarantool.org
imeevma at tarantool.org
Thu Jun 25 18:17:46 MSK 2020
Thank you for review! My answers and new patch below.
On 22.06.2020 15:25, Nikita Pettik wrote:
> On 17 Jun 15:36, imeevma at tarantool.org wrote:
>> @@ -2399,14 +2387,6 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */
>> default: res2 = res>=0; break;
>> }
>>
>> - /* Undo any changes made by mem_apply_type() to the input registers. */
>> - assert((pIn1->flags & MEM_Dyn) == (flags1 & MEM_Dyn));
>> - pIn1->flags = flags1;
>> - pIn1->field_type = ft_p1;
>> - assert((pIn3->flags & MEM_Dyn) == (flags3 & MEM_Dyn));
>> - pIn3->flags = flags3;
>> - pIn3->field_type = ft_p3;
>> -
>
> Replace these assertions with relevant ones.
>
Not sure if this is necessary, since now we have much less flag
changes after removal of mem_apply_type().
>> @@ -3496,8 +3476,6 @@ case OP_SeekGT: { /* jump, in3 */
>> pIn3 = &aMem[int_field];
>> if ((pIn3->flags & MEM_Null) != 0)
>> goto skip_truncate;
>> - if ((pIn3->flags & MEM_Str) != 0)
>> - mem_apply_numeric_type(pIn3);
>> int64_t i;
>> if ((pIn3->flags & MEM_Int) == MEM_Int) {
>> i = pIn3->u.i;
>
> Could you substitute routine below with mem_check_types()
> (or whatever this function is called)?
>
Partially substituted.
>> @@ -3590,6 +3568,26 @@ skip_truncate:
>> assert(oc!=OP_SeekLT || r.default_rc==+1);
>>
>> r.aMem = &aMem[pOp->p3];
>
> Code below definitely lacks some comments.
>
Fixed.
>> + for (int i = 0; i < r.nField; ++i) {
>> + enum field_type type = r.key_def->parts[i].type;
>> + struct Mem *mem = &r.aMem[i];
>> + if ((mem->flags & MEM_Str) != 0 && sql_type_is_numeric(type)) {
>
> What if key_def contains scalar/any type?
>
It will be fine since mem_check_type() process these cases.
>> + diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
>> + field_type_strs[type], mem_type_to_str(mem));
>> + goto abort_due_to_error;
>> + }
>> + if (mem_check_types(mem, type) == 0)
>> + continue;
>> + if ((mem->flags & MEM_Real) != 0 &&
>> + (type == FIELD_TYPE_INTEGER ||
>> + type == FIELD_TYPE_UNSIGNED)) {
>> + res = 1;
>> + goto seek_not_found;
>> + }
>> + diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
>> + field_type_strs[type], mem_type_to_str(mem));
>> + goto abort_due_to_error;
>> + }
>> #ifdef SQL_DEBUG
>> { int i; for(i=0; i<r.nField; i++) assert(memIsValid(&r.aMem[i])); }
>> #endif
>> @@ -4717,6 +4715,27 @@ case OP_IdxGE: { /* jump */
>> r.default_rc = 0;
>> }
>> r.aMem = &aMem[pOp->p3];
>> + for (int i = 0; i < r.nField; ++i) {
>> + struct Mem *mem = &r.aMem[i];
>> + enum mp_type mp_type = sql_value_type(mem);
>> + enum field_type field_type = r.key_def->parts[i].type;
>> + if (field_type == FIELD_TYPE_SCALAR ||
>> + mem->field_type == FIELD_TYPE_SCALAR)
>> + continue;
>> + bool is_nullable = r.key_def->parts[i].nullable_action ==
>> + ON_CONFLICT_ACTION_NONE;
>> + if (field_mp_plain_type_is_compatible(field_type, mp_type,
>> + is_nullable))
>> + continue;
>
> Why this procedure is different from one in Seek* opcode
> handling routine? Could you put it in one function and reuse?
> Also lacks comments.
>
At Seek*, we convert numbers to use them in an iterator. This is
not so in Idx*. In addition, Seek* is used only once during a
selection from space, but Idx* can be used more than once, so we
cannot convert here.
>> + if (!sql_type_is_numeric(field_type) ||
>> + !(mp_type == MP_INT || mp_type == MP_UINT ||
>> + mp_type == MP_DOUBLE || mp_type == MP_FLOAT)) {
>> + diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
>> + mem_type_to_str(mem),
>> + field_type_strs[field_type]);
>> + goto abort_due_to_error;
>> + }
>> + }
>> diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c
>> index 6d8768865..1d7c76670 100644
>> --- a/src/box/sql/wherecode.c
>> +++ b/src/box/sql/wherecode.c
>> @@ -335,72 +335,6 @@ disableTerm(WhereLevel * pLevel, WhereTerm * pTerm)
>> }
>> }
>>
>> -/**
>> - types[i] = FIELD_TYPE_SCALAR;
>> - }
>> - }
>> -}
>> -
>> /*
>> * Generate code for a single equality term of the WHERE clause. An equality
>> * term can be either X=expr or X IN (...). pTerm is the term to be
>> testcase(pRangeStart->wtFlags & TERM_VIRTUAL);
>> if (sqlExprIsVector(pRight) == 0) {
>> @@ -1049,94 +950,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
>> }
>> struct index_def *idx_pk = space->index[0]->def;
>> uint32_t pk_part_count = idx_pk->key_def->part_count;
>> - /*
>> - * Tarantool's iterator over integer fields doesn't
>> - * tolerate floating point values. Hence, if term
>> - * is equality comparison and value of operand is
>> - * not integer, we can skip it since it always
>> - * results in false: INT a == 0.5 -> false;
>> - * It is done using OP_MustBeInt facilities.
>> - * In case term is greater comparison (a > ?), we
>> - * should notify OP_SeekGT to process truncation of
>> - * floating point value: a > 0.5 -> a >= 1;
>> - * It is done by setting P5 flag for OP_Seek*.
>> - * It is worth mentioning that we do not need
>> - * this step when it comes for less (<) comparison
>> - * of nullable field. Key is NULL in this case:
>> - * values are ordered as NULL, ... NULL, min_value,
>> - * so to fetch min value we pass NULL to GT iterator.
>> - * The only exception is less comparison in
>> - * conjunction with ORDER BY DESC clause:
>> - * in such situation we use LE iterator and
>> - * truncated value to compare. But then
>> - * pRangeStart == NULL.
>> - * This procedure is correct for compound index:
>> - * only one comparison of less/greater type can be
>> - * used at the same time. For instance,
>> - * a < 1.5 AND b > 0.5 is handled by SeekGT using
>> - * column a and fetching column b from tuple and
>> - * OP_Le comparison.
>> - *
>> - * Note that OP_ApplyType, which is emitted before
>> - * OP_Seek** doesn't truncate floating point to
>> - * integer. That's why we need this routine.
>> - * Also, note that terms are separated by OR
>> - * predicates, so we consider term as sequence
>> - * of AND'ed predicates.
>> - */
>> - size_t addrs_sz;
>> - int *seek_addrs = region_alloc_array(&pParse->region,
>> - typeof(seek_addrs[0]), nEq,
>> - &addrs_sz);
>> - if (seek_addrs == NULL) {
>> - diag_set(OutOfMemory, addrs_sz, "region_alloc_array",
>> - "seek_addrs");
>> - pParse->is_aborted = true;
>> - return 0;
>
> Solid code removal. I won't review it, I hope you verified that all
> tests are working as desired.
>
Sure.
>> - memset(seek_addrs, 0, addrs_sz);
>> - for (int i = 0; i < nEq; i++) {
>> - enum field_type type = idx_def->key_def->parts[i].type;
>> - if (type == FIELD_TYPE_INTEGER ||
>> - type == FIELD_TYPE_UNSIGNED) {
>> - /*
>> - * OP_MustBeInt consider NULLs as
>> - * non-integer values, so firstly
>> - * check whether value is NULL or not.
>> - */
>> - seek_addrs[i] = sqlVdbeAddOp1(v, OP_IsNull,
>> - regBase);
>> - sqlVdbeAddOp2(v, OP_MustBeInt, regBase + i,
>> - addrNxt);
>> - start_types[i] = FIELD_TYPE_SCALAR;
>> - /*
>> - * We need to notify column cache
>> - * that type of value may change
>> - * so we should fetch value from
>> - * tuple again rather then copy
>> - * from register.
>> - */
>> - sql_expr_type_cache_change(pParse, regBase + i,
>> - 1);
>> - }
>> - }
>> - /* Inequality constraint comes always at the end of list. */
>> - part_count = idx_def->key_def->part_count;
>> - if (pRangeStart != NULL) {
>> - /*
>> - * nEq == 0 means that filter condition
>> - * contains only inequality.
>> - */
>> - uint32_t ineq_idx = nEq == 0 ? 0 : nEq - 1;
>> - assert(ineq_idx < part_count);
>> - enum field_type ineq_type =
>> - idx_def->key_def->parts[ineq_idx].type;
>> - if (ineq_type == FIELD_TYPE_INTEGER ||
>> - ineq_type == FIELD_TYPE_UNSIGNED)
>> - force_integer_reg = regBase + nEq;
>> - }
>> - emit_apply_type(pParse, regBase, nConstraint - bSeekPastNull,
>> - start_types);
>> if (pLoop->nSkip > 0 && nConstraint == pLoop->nSkip) {
>> /* The skip-scan logic inside the call to codeAllEqualityConstraints()
>> * above has already left the cursor sitting on the correct row,
>> --- /dev/null
>> +++ b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua
>
> Same note: I'd better use .sql test format and/or amalgamate it with
> already existing test file.
>
I was said that TAP is preferred.
>> @@ -0,0 +1,281 @@
>> +#!/usr/bin/env tarantool
>> +test = require("sqltester")
>> +test:plan(32)
>> +
>> +--
>> +
>> +test:execsql([[
>> + CREATE TABLE t1(x TEXT primary key);
>> + INSERT INTO t1 VALUES('1');
>> + CREATE TABLE t2(x INTEGER primary key);
>> + INSERT INTO t2 VALUES(1);
>> + CREATE TABLE t3(x DOUBLE primary key);
>> + INSERT INTO t3 VALUES(1.0);
>
> What about table with scalar/any fields?
>
I think this is too big a problem to solve it here.
New patch:
>From 64653f49b79f2888243a76c30425d762acd301db Mon Sep 17 00:00:00 2001
From: Mergen Imeev <imeevma at gmail.com>
Date: Thu, 25 Jun 2020 12:39:19 +0300
Subject: [PATCH] sql: remove implicit cast for comparison
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This patch removes implicit cast for comparison.
Closes #4230
@TarantoolBot document
Title: remove implicit cast for comparison
After this patch-set, there will be no implicit casts for
comparison. This means that the values of the field types STRING,
BOOLEAN and VARBINARY can be compared with the values of the same
field type. Any numerical value can be compared with any other
numerical value.
Example:
For comparison:
```
tarantool> box.execute([[SELECT '1' > 0;]])
---
- null
- "Type mismatch: can not convert '1' (type: text) to numeric"
...
tarantool> box.execute([[SELECT true > X'33';]])
---
- null
- 'Type mismatch: can not convert ''TRUE'' (type: boolean) to varbinary'
...
tarantool> box.execute([[SELECT 1.23 > 123;]])
---
- metadata:
- name: 1.23 > 123
type: boolean
rows:
- [false]
...
```
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index a609fa985..f531a2241 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -2283,8 +2283,6 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */
pIn3 = &aMem[pOp->p3];
flags1 = pIn1->flags;
flags3 = pIn3->flags;
- enum field_type ft_p1 = pIn1->field_type;
- enum field_type ft_p3 = pIn3->field_type;
if ((flags1 | flags3)&MEM_Null) {
/* One or both operands are NULL */
if (pOp->p5 & SQL_NULLEQ) {
@@ -2343,22 +2341,17 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */
} else {
enum field_type type = pOp->p5 & FIELD_TYPE_MASK;
if (sql_type_is_numeric(type)) {
- if ((flags1 | flags3)&MEM_Str) {
- if ((flags1 & MEM_Str) == MEM_Str) {
- mem_apply_numeric_type(pIn1);
- testcase( flags3!=pIn3->flags); /* Possible if pIn1==pIn3 */
- flags3 = pIn3->flags;
- }
- if ((flags3 & MEM_Str) == MEM_Str) {
- if (mem_apply_numeric_type(pIn3) != 0) {
- diag_set(ClientError,
- ER_SQL_TYPE_MISMATCH,
- sql_value_to_diag_str(pIn3),
- "numeric");
- goto abort_due_to_error;
- }
-
- }
+ if ((flags1 & MEM_Str) == MEM_Str) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ sql_value_to_diag_str(pIn1),
+ "numeric");
+ goto abort_due_to_error;
+ }
+ if ((flags3 & MEM_Str) == MEM_Str) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ sql_value_to_diag_str(pIn3),
+ "numeric");
+ goto abort_due_to_error;
}
/* Handle the common case of integer comparison here, as an
* optimization, to avoid a call to sqlMemCompare()
@@ -2391,22 +2384,17 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */
goto compare_op;
}
} else if (type == FIELD_TYPE_STRING) {
- if ((flags1 & MEM_Str) == 0 &&
- (flags1 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) {
- testcase( pIn1->flags & MEM_Int);
- testcase( pIn1->flags & MEM_Real);
- sqlVdbeMemStringify(pIn1);
- testcase( (flags1&MEM_Dyn) != (pIn1->flags&MEM_Dyn));
- flags1 = (pIn1->flags & ~MEM_TypeMask) | (flags1 & MEM_TypeMask);
- assert(pIn1!=pIn3);
+ if ((flags1 & MEM_Str) == 0) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ mem_type_to_str(pIn3),
+ mem_type_to_str(pIn1));
+ goto abort_due_to_error;
}
- if ((flags3 & MEM_Str) == 0 &&
- (flags3 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) {
- testcase( pIn3->flags & MEM_Int);
- testcase( pIn3->flags & MEM_Real);
- sqlVdbeMemStringify(pIn3);
- testcase( (flags3&MEM_Dyn) != (pIn3->flags&MEM_Dyn));
- flags3 = (pIn3->flags & ~MEM_TypeMask) | (flags3 & MEM_TypeMask);
+ if ((flags3 & MEM_Str) == 0) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ mem_type_to_str(pIn1),
+ mem_type_to_str(pIn3));
+ goto abort_due_to_error;
}
}
assert(pOp->p4type==P4_COLLSEQ || pOp->p4.pColl==0);
@@ -2422,14 +2410,6 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */
default: res2 = res>=0; break;
}
- /* Undo any changes made by mem_apply_type() to the input registers. */
- assert((pIn1->flags & MEM_Dyn) == (flags1 & MEM_Dyn));
- pIn1->flags = flags1;
- pIn1->field_type = ft_p1;
- assert((pIn3->flags & MEM_Dyn) == (flags3 & MEM_Dyn));
- pIn3->flags = flags3;
- pIn3->field_type = ft_p3;
-
if (pOp->p5 & SQL_STOREP2) {
iCompare = res;
res2 = res2!=0; /* For this path res2 must be exactly 0 or 1 */
@@ -3524,8 +3504,6 @@ case OP_SeekGT: { /* jump, in3 */
pIn3 = &aMem[int_field];
if ((pIn3->flags & MEM_Null) != 0)
goto skip_truncate;
- if ((pIn3->flags & MEM_Str) != 0)
- mem_apply_numeric_type(pIn3);
int64_t i;
if ((pIn3->flags & MEM_Int) == MEM_Int) {
i = pIn3->u.i;
@@ -3617,7 +3595,38 @@ skip_truncate:
assert(oc!=OP_SeekGE || r.default_rc==+1);
assert(oc!=OP_SeekLT || r.default_rc==+1);
+ /*
+ * Make sure that the types of all the fields in the tuple
+ * that will be used in the iterator match the field types
+ * of the space.
+ */
r.aMem = &aMem[pOp->p3];
+ for (int i = 0; i < r.nField; ++i) {
+ enum field_type type = r.key_def->parts[i].type;
+ struct Mem *mem = &r.aMem[i];
+ if (mem_check_type(mem, type) == 0 ||
+ mem_convert_numeric(mem, type, true) == 0)
+ continue;
+ /*
+ * If the number was not converted without loss,
+ * we will not find tuples using the EQ iterator.
+ */
+ if ((mem->flags & MEM_Real) != 0 &&
+ (type == FIELD_TYPE_INTEGER ||
+ type == FIELD_TYPE_UNSIGNED)) {
+ assert(eqOnly == 1);
+ res = 1;
+ goto seek_not_found;
+ }
+ if ((mem->flags & (MEM_Int | MEM_UInt)) != 0 &&
+ type == FIELD_TYPE_DOUBLE && eqOnly == 1) {
+ res = 1;
+ goto seek_not_found;
+ }
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ field_type_strs[type], mem_type_to_str(mem));
+ goto abort_due_to_error;
+ }
#ifdef SQL_DEBUG
{ int i; for(i=0; i<r.nField; i++) assert(memIsValid(&r.aMem[i])); }
#endif
@@ -4744,7 +4753,25 @@ case OP_IdxGE: { /* jump */
assert(pOp->opcode==OP_IdxGE || pOp->opcode==OP_IdxLT);
r.default_rc = 0;
}
+
+ /*
+ * Make sure that the types of all the fields in the tuple
+ * that will be used in the iterator comparable with the
+ * fields of the space.
+ */
r.aMem = &aMem[pOp->p3];
+ for (int i = 0; i < r.nField; ++i) {
+ enum field_type type = r.key_def->parts[i].type;
+ struct Mem *mem = &r.aMem[i];
+ if (mem_check_type(mem, type) == 0)
+ continue;
+ if (sql_type_is_numeric(type) &&
+ (mem->flags & (MEM_Real | MEM_Int | MEM_UInt)) != 0)
+ continue;
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ field_type_strs[type], mem_type_to_str(mem));
+ goto abort_due_to_error;
+ }
#ifdef SQL_DEBUG
{ int i; for(i=0; i<r.nField; i++) assert(memIsValid(&r.aMem[i])); }
#endif
diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c
index 6d8768865..1d7c76670 100644
--- a/src/box/sql/wherecode.c
+++ b/src/box/sql/wherecode.c
@@ -335,72 +335,6 @@ disableTerm(WhereLevel * pLevel, WhereTerm * pTerm)
}
}
-/**
- * Code an OP_ApplyType opcode to apply the column type string
- * @types to the n registers starting at @base.
- *
- * As an optimization, SCALAR entries (which are no-ops) at the
- * beginning and end of @types are ignored. If all entries in
- * @types are SCALAR, then no code gets generated.
- *
- * This routine makes its own copy of @types so that the caller is
- * free to modify @types after this routine returns.
- */
-static void
-emit_apply_type(Parse *pParse, int base, int n, enum field_type *types)
-{
- Vdbe *v = pParse->pVdbe;
- if (types == NULL) {
- assert(pParse->db->mallocFailed);
- return;
- }
- assert(v != 0);
-
- /*
- * Adjust base and n to skip over SCALAR entries at the
- * beginning and end of the type sequence.
- */
- while (n > 0 && types[0] == FIELD_TYPE_SCALAR) {
- n--;
- base++;
- types++;
- }
- while (n > 1 && types[n - 1] == FIELD_TYPE_SCALAR) {
- n--;
- }
-
- if (n > 0) {
- enum field_type *types_dup = field_type_sequence_dup(pParse,
- types, n);
- sqlVdbeAddOp4(v, OP_ApplyType, base, n, 0,
- (char *) types_dup, P4_DYNAMIC);
- sql_expr_type_cache_change(pParse, base, n);
- }
-}
-
-/**
- * Expression @rhs, which is the RHS of a comparison operation, is
- * either a vector of n elements or, if n==1, a scalar expression.
- * Before the comparison operation, types @types are to be applied
- * to the @rhs values. This function modifies entries within the
- * field sequence to SCALAR if either:
- *
- * * the comparison will be performed with no type, or
- * * the type change in @types is guaranteed not to change the value.
- */
-static void
-expr_cmp_update_rhs_type(struct Expr *rhs, int n, enum field_type *types)
-{
- for (int i = 0; i < n; i++) {
- Expr *p = sqlVectorFieldSubexpr(rhs, i);
- enum field_type expr_type = sql_expr_type(p);
- if (sql_type_result(expr_type, types[i]) == FIELD_TYPE_SCALAR ||
- sql_expr_needs_no_type_change(p, types[i])) {
- types[i] = FIELD_TYPE_SCALAR;
- }
- }
-}
-
/*
* Generate code for a single equality term of the WHERE clause. An equality
* term can be either X=expr or X IN (...). pTerm is the term to be
@@ -644,8 +578,7 @@ static int
codeAllEqualityTerms(Parse * pParse, /* Parsing context */
WhereLevel * pLevel, /* Which nested loop of the FROM we are coding */
int bRev, /* Reverse the order of IN operators */
- int nExtraReg, /* Number of extra registers to allocate */
- enum field_type **res_type)
+ int nExtraReg) /* Number of extra registers to allocate */
{
u16 nEq; /* The number of == or IN constraints to code */
u16 nSkip; /* Number of left-most columns to skip */
@@ -669,9 +602,6 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */
nReg = pLoop->nEq + nExtraReg;
pParse->nMem += nReg;
- enum field_type *type = sql_index_type_str(pParse->db, idx_def);
- assert(type != NULL || pParse->db->mallocFailed);
-
if (nSkip) {
int iIdxCur = pLevel->iIdxCur;
sqlVdbeAddOp1(v, (bRev ? OP_Last : OP_Rewind), iIdxCur);
@@ -714,17 +644,7 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */
sqlVdbeAddOp2(v, OP_SCopy, r1, regBase + j);
}
}
- if (pTerm->eOperator & WO_IN) {
- if (pTerm->pExpr->flags & EP_xIsSelect) {
- /* No type ever needs to be (or should be) applied to a value
- * from the RHS of an "? IN (SELECT ...)" expression. The
- * sqlFindInIndex() routine has already ensured that the
- * type of the comparison has been applied to the value.
- */
- if (type != NULL)
- type[j] = FIELD_TYPE_SCALAR;
- }
- } else if ((pTerm->eOperator & WO_ISNULL) == 0) {
+ if ((pTerm->eOperator & (WO_IN | WO_ISNULL)) == 0) {
Expr *pRight = pTerm->pExpr->pRight;
if (sqlExprCanBeNull(pRight)) {
sqlVdbeAddOp2(v, OP_IsNull, regBase + j,
@@ -733,7 +653,6 @@ codeAllEqualityTerms(Parse * pParse, /* Parsing context */
}
}
}
- *res_type = type;
return regBase;
}
@@ -904,10 +823,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
int iIdxCur; /* The VDBE cursor for the index */
int nExtraReg = 0; /* Number of extra registers needed */
int op; /* Instruction opcode */
- /* Types for start of range constraint. */
- enum field_type *start_types;
- /* Types for end of range constraint */
- enum field_type *end_types = NULL;
u8 bSeekPastNull = 0; /* True to seek past initial nulls */
u8 bStopAtNull = 0; /* Add condition to terminate at NULLs */
int force_integer_reg = -1; /* If non-negative: number of
@@ -994,17 +909,7 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
* and store the values of those terms in an array of registers
* starting at regBase.
*/
- regBase =
- codeAllEqualityTerms(pParse, pLevel, bRev, nExtraReg,
- &start_types);
- if (start_types != NULL && nTop) {
- uint32_t len = 0;
- for (enum field_type *tmp = &start_types[nEq];
- *tmp != field_type_MAX; tmp++, len++);
- uint32_t sz = len * sizeof(enum field_type);
- end_types = sqlDbMallocRaw(db, sz);
- memcpy(end_types, &start_types[nEq], sz);
- }
+ regBase = codeAllEqualityTerms(pParse, pLevel, bRev, nExtraReg);
addrNxt = pLevel->addrNxt;
testcase(pRangeStart && (pRangeStart->eOperator & WO_LE) != 0);
@@ -1029,10 +934,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
VdbeCoverage(v);
}
- if (start_types) {
- expr_cmp_update_rhs_type(pRight, nBtm,
- &start_types[nEq]);
- }
nConstraint += nBtm;
testcase(pRangeStart->wtFlags & TERM_VIRTUAL);
if (sqlExprIsVector(pRight) == 0) {
@@ -1049,94 +950,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
}
struct index_def *idx_pk = space->index[0]->def;
uint32_t pk_part_count = idx_pk->key_def->part_count;
- /*
- * Tarantool's iterator over integer fields doesn't
- * tolerate floating point values. Hence, if term
- * is equality comparison and value of operand is
- * not integer, we can skip it since it always
- * results in false: INT a == 0.5 -> false;
- * It is done using OP_MustBeInt facilities.
- * In case term is greater comparison (a > ?), we
- * should notify OP_SeekGT to process truncation of
- * floating point value: a > 0.5 -> a >= 1;
- * It is done by setting P5 flag for OP_Seek*.
- * It is worth mentioning that we do not need
- * this step when it comes for less (<) comparison
- * of nullable field. Key is NULL in this case:
- * values are ordered as NULL, ... NULL, min_value,
- * so to fetch min value we pass NULL to GT iterator.
- * The only exception is less comparison in
- * conjunction with ORDER BY DESC clause:
- * in such situation we use LE iterator and
- * truncated value to compare. But then
- * pRangeStart == NULL.
- * This procedure is correct for compound index:
- * only one comparison of less/greater type can be
- * used at the same time. For instance,
- * a < 1.5 AND b > 0.5 is handled by SeekGT using
- * column a and fetching column b from tuple and
- * OP_Le comparison.
- *
- * Note that OP_ApplyType, which is emitted before
- * OP_Seek** doesn't truncate floating point to
- * integer. That's why we need this routine.
- * Also, note that terms are separated by OR
- * predicates, so we consider term as sequence
- * of AND'ed predicates.
- */
- size_t addrs_sz;
- int *seek_addrs = region_alloc_array(&pParse->region,
- typeof(seek_addrs[0]), nEq,
- &addrs_sz);
- if (seek_addrs == NULL) {
- diag_set(OutOfMemory, addrs_sz, "region_alloc_array",
- "seek_addrs");
- pParse->is_aborted = true;
- return 0;
- }
- memset(seek_addrs, 0, addrs_sz);
- for (int i = 0; i < nEq; i++) {
- enum field_type type = idx_def->key_def->parts[i].type;
- if (type == FIELD_TYPE_INTEGER ||
- type == FIELD_TYPE_UNSIGNED) {
- /*
- * OP_MustBeInt consider NULLs as
- * non-integer values, so firstly
- * check whether value is NULL or not.
- */
- seek_addrs[i] = sqlVdbeAddOp1(v, OP_IsNull,
- regBase);
- sqlVdbeAddOp2(v, OP_MustBeInt, regBase + i,
- addrNxt);
- start_types[i] = FIELD_TYPE_SCALAR;
- /*
- * We need to notify column cache
- * that type of value may change
- * so we should fetch value from
- * tuple again rather then copy
- * from register.
- */
- sql_expr_type_cache_change(pParse, regBase + i,
- 1);
- }
- }
- /* Inequality constraint comes always at the end of list. */
- part_count = idx_def->key_def->part_count;
- if (pRangeStart != NULL) {
- /*
- * nEq == 0 means that filter condition
- * contains only inequality.
- */
- uint32_t ineq_idx = nEq == 0 ? 0 : nEq - 1;
- assert(ineq_idx < part_count);
- enum field_type ineq_type =
- idx_def->key_def->parts[ineq_idx].type;
- if (ineq_type == FIELD_TYPE_INTEGER ||
- ineq_type == FIELD_TYPE_UNSIGNED)
- force_integer_reg = regBase + nEq;
- }
- emit_apply_type(pParse, regBase, nConstraint - bSeekPastNull,
- start_types);
if (pLoop->nSkip > 0 && nConstraint == pLoop->nSkip) {
/* The skip-scan logic inside the call to codeAllEqualityConstraints()
* above has already left the cursor sitting on the correct row,
@@ -1146,10 +959,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
op = aStartOp[(start_constraints << 2) +
(startEq << 1) + bRev];
assert(op != 0);
- for (uint32_t i = 0; i < nEq; ++i) {
- if (seek_addrs[i] != 0)
- sqlVdbeJumpHere(v, seek_addrs[i]);
- }
sqlVdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase,
nConstraint);
/* If this is Seek* opcode, and IPK is detected in the
@@ -1189,13 +998,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
addrNxt);
VdbeCoverage(v);
}
- if (end_types) {
- expr_cmp_update_rhs_type(pRight, nTop, end_types);
- emit_apply_type(pParse, regBase + nEq, nTop,
- end_types);
- } else {
- assert(pParse->db->mallocFailed);
- }
nConstraint += nTop;
testcase(pRangeEnd->wtFlags & TERM_VIRTUAL);
@@ -1209,8 +1011,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
endEq = 0;
nConstraint++;
}
- sqlDbFree(db, start_types);
- sqlDbFree(db, end_types);
/* Top of the loop body */
pLevel->p2 = sqlVdbeCurrentAddr(v);
diff --git a/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua
new file mode 100755
index 000000000..b405a11b6
--- /dev/null
+++ b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua
@@ -0,0 +1,281 @@
+#!/usr/bin/env tarantool
+test = require("sqltester")
+test:plan(32)
+
+--
+-- Make sure that there is no implicit cast between string and
+-- number.
+--
+test:do_catchsql_test(
+ "gh-4230-1",
+ [[
+ SELECT '1' > 0;
+ ]], {
+ 1, "Type mismatch: can not convert 1 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-2",
+ [[
+ SELECT 0 > '1';
+ ]], {
+ 1, "Type mismatch: can not convert 1 to numeric"
+ })
+
+test:execsql([[
+ CREATE TABLE t (i INT PRIMARY KEY, d DOUBLE, n NUMBER, s STRING);
+ INSERT INTO t VALUES (1, 1.0, 1, '2'), (2, 2.0, 2.0, '2');
+ ]])
+
+test:do_catchsql_test(
+ "gh-4230-3",
+ [[
+ SELECT * from t where i > s;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-4",
+ [[
+ SELECT * from t WHERE s > i;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-5",
+ [[
+ SELECT * from t WHERE d > s;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-6",
+ [[
+ SELECT * from t WHERE s > d;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-7",
+ [[
+ SELECT * from t WHERE i = 1 and n > s;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-8",
+ [[
+ SELECT * from t WHERE i = 2 and s > n;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:execsql([[
+ CREATE TABLE t1(x TEXT primary key);
+ INSERT INTO t1 VALUES('1');
+ CREATE TABLE t2(x INTEGER primary key);
+ INSERT INTO t2 VALUES(1);
+ CREATE TABLE t3(x DOUBLE primary key);
+ INSERT INTO t3 VALUES(1.0);
+]])
+
+test:do_catchsql_test(
+ "gh-4230-9",
+ [[
+ SELECT x FROM t1 WHERE x IN (1);
+ ]], {
+ 1, "Type mismatch: can not convert 1 to numeric"
+ })
+
+
+test:do_catchsql_test(
+ "gh-4230-10",
+ [[
+ SELECT x FROM t1 WHERE x IN (1.0);
+ ]], {
+ 1, "Type mismatch: can not convert 1 to numeric"
+ })
+
+test:do_execsql_test(
+ "gh-4230-11",
+ [[
+ SELECT x FROM t1 WHERE x IN ('1');
+ ]], {
+ "1"
+ })
+
+test:do_execsql_test(
+ "gh-4230-12",
+ [[
+ SELECT x FROM t1 WHERE x IN ('1.0');
+ ]], {
+ })
+
+test:do_catchsql_test(
+ "gh-4230-13",
+ [[
+ SELECT x FROM t1 WHERE 1 IN (x);
+ ]], {
+ 1, "Type mismatch: can not convert 1 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-14",
+ [[
+ SELECT x FROM t1 WHERE 1.0 IN (x);
+ ]], {
+ 1, "Type mismatch: can not convert 1 to numeric"
+ })
+
+test:do_execsql_test(
+ "gh-4230-15",
+ [[
+ SELECT x FROM t1 WHERE '1' IN (x);
+ ]], {
+ -- <2.7>
+ "1"
+ -- </2.7>
+ })
+
+test:do_execsql_test(
+ "gh-4230-16",
+ [[
+ SELECT x FROM t1 WHERE '1.0' IN (x);
+ ]], {
+ })
+
+test:do_execsql_test(
+ "gh-4230-17",
+ [[
+ SELECT x FROM t2 WHERE x IN (1);
+ ]], {
+ 1
+ })
+
+
+test:do_execsql_test(
+ "gh-4230-18",
+ [[
+ SELECT x FROM t2 WHERE x IN (1.0);
+ ]], {
+ 1
+ })
+
+test:do_catchsql_test(
+ "gh-4230-19",
+ [[
+ SELECT x FROM t2 WHERE x IN ('1');
+ ]], {
+ 1, "Type mismatch: can not convert integer to text"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-20",
+ [[
+ SELECT x FROM t2 WHERE x IN ('1.0');
+ ]], {
+ 1, "Type mismatch: can not convert integer to text"
+ })
+
+test:do_execsql_test(
+ "gh-4230-21",
+ [[
+ SELECT x FROM t2 WHERE 1 IN (x);
+ ]], {
+ 1
+ })
+
+test:do_execsql_test(
+ "gh-4230-22",
+ [[
+ SELECT x FROM t2 WHERE 1.0 IN (x);
+ ]], {
+ 1
+ })
+
+test:do_catchsql_test(
+ "gh-4230-23",
+ [[
+ SELECT x FROM t2 WHERE '1' IN (x);
+ ]], {
+ 1, "Type mismatch: can not convert integer to text"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-24",
+ [[
+ SELECT x FROM t2 WHERE '1.0' IN (x);
+ ]], {
+ 1, "Type mismatch: can not convert integer to text"
+ })
+
+test:do_execsql_test(
+ "gh-4230-25",
+ [[
+ SELECT x FROM t3 WHERE x IN (1);
+ ]], {
+ 1
+ })
+
+test:do_execsql_test(
+ "gh-4230-26",
+ [[
+ SELECT x FROM t3 WHERE x IN (1.0);
+ ]], {
+ 1
+ })
+
+test:do_catchsql_test(
+ "gh-4230-27",
+ [[
+ SELECT x FROM t3 WHERE x IN ('1');
+ ]], {
+ 1, "Type mismatch: can not convert double to text"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-28",
+ [[
+ SELECT x FROM t3 WHERE x IN ('1.0');
+ ]], {
+ 1, "Type mismatch: can not convert double to text"
+ })
+
+test:do_execsql_test(
+ "gh-4230-29",
+ [[
+ SELECT x FROM t3 WHERE 1 IN (x);
+ ]], {
+ 1
+ })
+
+test:do_execsql_test(
+ "gh-4230-30",
+ [[
+ SELECT x FROM t3 WHERE 1.0 IN (x);
+ ]], {
+ 1
+ })
+
+test:do_catchsql_test(
+ "gh-4230-31",
+ [[
+ SELECT x FROM t3 WHERE '1' IN (x);
+ ]], {
+ 1, "Type mismatch: can not convert double to text"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-32",
+ [[
+ SELECT x FROM t3 WHERE '1.0' IN (x);
+ ]], {
+ 1, "Type mismatch: can not convert double to text"
+ })
+
+test:finish_test()
diff --git a/test/sql-tap/identifier_case.test.lua b/test/sql-tap/identifier_case.test.lua
index 2a00626fc..1d56ffb44 100755
--- a/test/sql-tap/identifier_case.test.lua
+++ b/test/sql-tap/identifier_case.test.lua
@@ -242,11 +242,11 @@ data = {
{ 2, [[ 'a' < 'b' collate "binary" ]], {0, {true}}},
{ 3, [[ 'a' < 'b' collate 'binary' ]], {1, [[Syntax error at line 1 near ''binary'']]}},
{ 4, [[ 'a' < 'b' collate "unicode" ]], {0, {true}}},
- { 5, [[ 5 < 'b' collate "unicode" ]], {0, {true}}},
+ { 5, [[ 5 < 'b' collate "unicode" ]], {1, "Type mismatch: can not convert b to numeric"}},
{ 6, [[ 5 < 'b' collate unicode ]], {1,"Collation 'UNICODE' does not exist"}},
- { 7, [[ 5 < 'b' collate "unicode_ci" ]], {0, {true}}},
+ { 7, [[ 5 < 'b' collate "unicode_ci" ]], {1, "Type mismatch: can not convert b to numeric"}},
{ 8, [[ 5 < 'b' collate NONE ]], {1, "Collation 'NONE' does not exist"}},
- { 9, [[ 5 < 'b' collate "none" ]], {0, {true}}},
+ { 9, [[ 5 < 'b' collate "none" ]], {1, "Type mismatch: can not convert b to numeric"}},
}
for _, row in ipairs(data) do
diff --git a/test/sql-tap/in1.test.lua b/test/sql-tap/in1.test.lua
index 570cc1779..e2f498889 100755
--- a/test/sql-tap/in1.test.lua
+++ b/test/sql-tap/in1.test.lua
@@ -637,12 +637,12 @@ test:do_test(
"in-11.2",
function()
-- The '2' should be coerced into 2 because t6.b is NUMERIC
- return test:execsql [[
+ return test:catchsql [[
SELECT * FROM t6 WHERE b IN ('2');
]]
end, {
-- <in-11.2>
- 1, 2
+ 1, "Type mismatch: can not convert 2 to numeric"
-- </in-11.2>
})
diff --git a/test/sql-tap/in4.test.lua b/test/sql-tap/in4.test.lua
index 33947d0ab..a494e846f 100755
--- a/test/sql-tap/in4.test.lua
+++ b/test/sql-tap/in4.test.lua
@@ -147,12 +147,13 @@ test:do_execsql_test(
-- </in4-2.7>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"in4-2.8",
[[
SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2')
]], {
-- <in4-2.8>
+ 1, "Type mismatch: can not convert integer to text"
-- </in4-2.8>
})
diff --git a/test/sql-tap/insert3.test.lua b/test/sql-tap/insert3.test.lua
index b92bc508e..3276f0db2 100755
--- a/test/sql-tap/insert3.test.lua
+++ b/test/sql-tap/insert3.test.lua
@@ -59,7 +59,7 @@ test:do_execsql_test(
[[
CREATE TABLE log2(rowid INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT UNIQUE,y INT );
CREATE TRIGGER r2 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
- UPDATE log2 SET y=y+1 WHERE x=new.b;
+ UPDATE log2 SET y=y+1 WHERE x=CAST(new.b AS STRING);
INSERT OR IGNORE INTO log2(x, y) VALUES(CAST(new.b AS STRING),1);
END;
INSERT INTO t1(a, b) VALUES('hi', 453);
diff --git a/test/sql-tap/join.test.lua b/test/sql-tap/join.test.lua
index 51e0ecb79..792302ab5 100755
--- a/test/sql-tap/join.test.lua
+++ b/test/sql-tap/join.test.lua
@@ -1028,22 +1028,23 @@ test:do_test(
-- </join-11.8>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"join-11.9",
[[
SELECT * FROM t1 NATURAL JOIN t2
]], {
-- <join-11.9>
+ 1, "Type mismatch: can not convert integer to text"
-- </join-11.9>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"join-11.10",
[[
SELECT * FROM t2 NATURAL JOIN t1
]], {
-- <join-11.10>
- 1, "one", 2, "two"
+ 1, "Type mismatch: can not convert 1 to numeric"
-- </join-11.10>
})
diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua
index e0fe50bbe..3cef617f4 100755
--- a/test/sql-tap/misc1.test.lua
+++ b/test/sql-tap/misc1.test.lua
@@ -88,7 +88,7 @@ test:do_execsql_test(
test:do_execsql_test(
"misc1-1.4",
[[
- SELECT x75 FROM manycol WHERE x50=350
+ SELECT x75 FROM manycol WHERE x50='350'
]], {
-- <misc1-1.4>
"375"
@@ -98,7 +98,7 @@ test:do_execsql_test(
test:do_execsql_test(
"misc1-1.5",
[[
- SELECT x50 FROM manycol WHERE x99=599
+ SELECT x50 FROM manycol WHERE x99='599'
]], {
-- <misc1-1.5>
"550"
@@ -109,7 +109,7 @@ test:do_test(
"misc1-1.6",
function()
test:execsql("CREATE INDEX manycol_idx1 ON manycol(x99)")
- return test:execsql("SELECT x50 FROM manycol WHERE x99=899")
+ return test:execsql("SELECT x50 FROM manycol WHERE x99='899'")
end, {
-- <misc1-1.6>
"850"
@@ -129,7 +129,7 @@ test:do_execsql_test(
test:do_test(
"misc1-1.8",
function()
- test:execsql("DELETE FROM manycol WHERE x98=1234")
+ test:execsql("DELETE FROM manycol WHERE x98='1234'")
return test:execsql("SELECT count(*) FROM manycol")
end, {
-- <misc1-1.8>
@@ -140,7 +140,7 @@ test:do_test(
test:do_test(
"misc1-1.9",
function()
- test:execsql("DELETE FROM manycol WHERE x98=998")
+ test:execsql("DELETE FROM manycol WHERE x98='998'")
return test:execsql("SELECT count(*) FROM manycol")
end, {
-- <misc1-1.9>
@@ -151,7 +151,7 @@ test:do_test(
test:do_test(
"misc1-1.10",
function()
- test:execsql("DELETE FROM manycol WHERE x99=500")
+ test:execsql("DELETE FROM manycol WHERE x99='500'")
return test:execsql("SELECT count(*) FROM manycol")
end, {
-- <misc1-1.10>
@@ -162,7 +162,7 @@ test:do_test(
test:do_test(
"misc1-1.11",
function()
- test:execsql("DELETE FROM manycol WHERE x99=599")
+ test:execsql("DELETE FROM manycol WHERE x99='599'")
return test:execsql("SELECT count(*) FROM manycol")
end, {
-- <misc1-1.11>
@@ -479,9 +479,9 @@ local where = ""
test:do_test(
"misc1-10.1",
function()
- where = "WHERE x0>=0"
+ where = "WHERE x0>='0'"
for i = 1, 99, 1 do
- where = where .. " AND x"..i.."<>0"
+ where = where .. " AND x"..i.."<>'0'"
end
return test:catchsql("SELECT count(*) FROM manycol "..where.."")
end, {
@@ -496,7 +496,7 @@ test:do_test(
test:do_test(
"misc1-10.3",
function()
- where = string.gsub(where,"x0>=0", "x0=0")
+ where = string.gsub(where,"x0>='0'", "x0='0'")
return test:catchsql("DELETE FROM manycol "..where.."")
end, {
-- <misc1-10.3>
@@ -520,7 +520,7 @@ test:do_execsql_test(
test:do_execsql_test(
"misc1-10.6",
[[
- SELECT x1 FROM manycol WHERE x0=100
+ SELECT x1 FROM manycol WHERE x0='100'
]], {
-- <misc1-10.6>
"101"
@@ -530,7 +530,7 @@ test:do_execsql_test(
test:do_test(
"misc1-10.7",
function()
- where = string.gsub(where, "x0=0", "x0=100")
+ where = string.gsub(where, "x0='0'", "x0='100'")
return test:catchsql("UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where.."")
end, {
-- <misc1-10.7>
@@ -541,7 +541,7 @@ test:do_test(
test:do_execsql_test(
"misc1-10.8",
[[
- SELECT x1 FROM manycol WHERE x0=100
+ SELECT x1 FROM manycol WHERE x0='100'
]], {
-- <misc1-10.8>
"102"
@@ -563,7 +563,7 @@ test:do_execsql_test(
test:do_execsql_test(
"misc1-10.10",
[[
- SELECT x1 FROM manycol WHERE x0=100
+ SELECT x1 FROM manycol WHERE x0='100'
]], {
-- <misc1-10.10>
"103"
@@ -619,13 +619,13 @@ test:do_execsql_test(
-- </misc1-12.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"misc1-12.2",
[[
SELECT '0'==0.0
]], {
-- <misc1-12.2>
- true
+ 1, "Type mismatch: can not convert 0 to numeric"
-- </misc1-12.2>
})
diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua
index 9a969bf3c..f5a9b63fe 100755
--- a/test/sql-tap/select1.test.lua
+++ b/test/sql-tap/select1.test.lua
@@ -1912,7 +1912,7 @@ test:do_execsql_test(
test:do_execsql_test(
"select1-12.7",
[[
- SELECT * FROM t3 WHERE a=(SELECT 1);
+ SELECT * FROM t3 WHERE a=(SELECT '1');
]], {
-- <select1-12.7>
0, "1", "2"
@@ -1922,7 +1922,7 @@ test:do_execsql_test(
test:do_execsql_test(
"select1-12.8",
[[
- SELECT * FROM t3 WHERE a=(SELECT 2);
+ SELECT * FROM t3 WHERE a=(SELECT '2');
]], {
-- <select1-12.8>
diff --git a/test/sql-tap/select7.test.lua b/test/sql-tap/select7.test.lua
index e1e43c557..0d1390fd6 100755
--- a/test/sql-tap/select7.test.lua
+++ b/test/sql-tap/select7.test.lua
@@ -256,7 +256,7 @@ test:do_execsql_test(
DROP TABLE IF EXISTS t5;
CREATE TABLE t5(a TEXT primary key, b INT);
INSERT INTO t5 VALUES('123', 456);
- SELECT typeof(a), a FROM t5 GROUP BY a HAVING a<b;
+ SELECT typeof(a), a FROM t5 GROUP BY a HAVING CAST(a AS INTEGER)<b;
]], {
-- <select7-7.7>
"string", "123"
diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua
index e0771825e..bad702de9 100755
--- a/test/sql-tap/subquery.test.lua
+++ b/test/sql-tap/subquery.test.lua
@@ -284,13 +284,13 @@ test:do_execsql_test(
-- </subquery-2.3.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"subquery-2.3.2",
[[
SELECT a IN (10.0, 20) FROM t3;
]], {
-- <subquery-2.3.2>
- false
+ 1, "Type mismatch: can not convert text to real"
-- </subquery-2.3.2>
})
diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua
deleted file mode 100755
index ac89c7df2..000000000
--- a/test/sql-tap/tkt-9a8b09f8e6.test.lua
+++ /dev/null
@@ -1,506 +0,0 @@
-#!/usr/bin/env tarantool
-test = require("sqltester")
-test:plan(47)
-
---!./tcltestrunner.lua
--- 2014 June 26
---
--- The author disclaims copyright to this source code. In place of
--- a legal notice, here is a blessing:
---
--- May you do good and not evil.
--- May you find forgiveness for yourself and forgive others.
--- May you share freely, never taking more than you give.
---
--------------------------------------------------------------------------
--- This file implements regression tests for sql library.
---
--- This file implements tests to verify that ticket [9a8b09f8e6] has been
--- fixed.
---
--- ["set","testdir",[["file","dirname",["argv0"]]]]
--- ["source",[["testdir"],"\/tester.tcl"]]
-testprefix = "tkt-9a8b09f8e6"
--- MUST_WORK_TEST
-if (0 > 0)
- then
-end
-test:do_execsql_test(
- 1.1,
- [[
- CREATE TABLE t1(x TEXT primary key);
- INSERT INTO t1 VALUES('1');
- ]], {
- -- <1.1>
-
- -- </1.1>
- })
-
-test:do_execsql_test(
- 1.2,
- [[
- CREATE TABLE t2(x INTEGER primary key);
- INSERT INTO t2 VALUES(1);
- ]], {
- -- <1.2>
-
- -- </1.2>
- })
-
-test:do_execsql_test(
- 1.3,
- [[
- CREATE TABLE t3(x NUMBER primary key);
- INSERT INTO t3 VALUES(1.0);
- ]], {
- -- <1.3>
-
- -- </1.3>
- })
-
-test:do_execsql_test(
- 1.4,
- [[
- CREATE TABLE t4(x NUMBER primary key);
- INSERT INTO t4 VALUES(1.11);
- ]], {
- -- <1.4>
-
- -- </1.4>
- })
-
-test:do_execsql_test(
- 1.5,
- [[
- CREATE TABLE t5(id INT primary key, x INT , y TEXT);
- INSERT INTO t5 VALUES(1, 1, 'one');
- INSERT INTO t5 VALUES(2, 1, 'two');
- INSERT INTO t5 VALUES(3, 1.0, 'three');
- INSERT INTO t5 VALUES(4, 1.0, 'four');
- ]], {
- -- <1.5>
-
- -- </1.5>
- })
-
-test:do_execsql_test(
- 2.1,
- [[
- SELECT x FROM t1 WHERE x IN (1);
- ]], {
- -- <2.1>
- "1"
- -- </2.1>
- })
-
-test:do_execsql_test(
- 2.2,
- [[
- SELECT x FROM t1 WHERE x IN (1.0);
- ]], {
- -- <2.2>
- "1"
- -- </2.2>
- })
-
-test:do_execsql_test(
- 2.3,
- [[
- SELECT x FROM t1 WHERE x IN ('1');
- ]], {
- -- <2.3>
- "1"
- -- </2.3>
- })
-
-test:do_execsql_test(
- 2.4,
- [[
- SELECT x FROM t1 WHERE x IN ('1.0');
- ]], {
- -- <2.4>
-
- -- </2.4>
- })
-
-test:do_execsql_test(
- 2.5,
- [[
- SELECT x FROM t1 WHERE 1 IN (x);
- ]], {
- -- <2.5>
- "1"
- -- </2.5>
- })
-
-test:do_execsql_test(
- 2.6,
- [[
- SELECT x FROM t1 WHERE 1.0 IN (x);
- ]], {
- -- <2.6>
- "1"
- -- </2.6>
- })
-
-test:do_execsql_test(
- 2.7,
- [[
- SELECT x FROM t1 WHERE '1' IN (x);
- ]], {
- -- <2.7>
- "1"
- -- </2.7>
- })
-
-test:do_execsql_test(
- 2.8,
- [[
- SELECT x FROM t1 WHERE '1.0' IN (x);
- ]], {
- -- <2.8>
-
- -- </2.8>
- })
-
-test:do_execsql_test(
- 3.1,
- [[
- SELECT x FROM t2 WHERE x IN (1);
- ]], {
- -- <3.1>
- 1
- -- </3.1>
- })
-
-test:do_execsql_test(
- 3.2,
- [[
- SELECT x FROM t2 WHERE x IN (1.0);
- ]], {
- -- <3.2>
- 1
- -- </3.2>
- })
-
-test:do_execsql_test(
- 3.3,
- [[
- SELECT x FROM t2 WHERE x IN ('1');
- ]], {
- -- <3.3>
- -- </3.3>
- })
-
-test:do_execsql_test(
- 3.5,
- [[
- SELECT x FROM t2 WHERE 1 IN (x);
- ]], {
- -- <3.5>
- 1
- -- </3.5>
- })
-
-test:do_execsql_test(
- 3.6,
- [[
- SELECT x FROM t2 WHERE 1.0 IN (x);
- ]], {
- -- <3.6>
- 1
- -- </3.6>
- })
-
-test:do_execsql_test(
- 3.7,
- [[
- SELECT x FROM t2 WHERE '1' IN (x);
- ]], {
- -- <3.7>
- -- </3.7>
- })
-
-test:do_execsql_test(
- 4.1,
- [[
- SELECT x FROM t3 WHERE x IN (1);
- ]], {
- -- <4.1>
- 1.0
- -- </4.1>
- })
-
-test:do_execsql_test(
- 4.2,
- [[
- SELECT x FROM t3 WHERE x IN (1.0);
- ]], {
- -- <4.2>
- 1.0
- -- </4.2>
- })
-
-test:do_execsql_test(
- 4.3,
- [[
- SELECT x FROM t3 WHERE x IN ('1');
- ]], {
- -- <4.3>
- 1.0
- -- </4.3>
- })
-
-test:do_execsql_test(
- 4.4,
- [[
- SELECT x FROM t3 WHERE x IN ('1.0');
- ]], {
- -- <4.4>
- 1.0
- -- </4.4>
- })
-
-test:do_execsql_test(
- 4.5,
- [[
- SELECT x FROM t3 WHERE 1 IN (x);
- ]], {
- -- <4.5>
- 1.0
- -- </4.5>
- })
-
-test:do_execsql_test(
- 4.6,
- [[
- SELECT x FROM t3 WHERE 1.0 IN (x);
- ]], {
- -- <4.6>
- 1.0
- -- </4.6>
- })
-
-test:do_execsql_test(
- 4.7,
- [[
- SELECT x FROM t3 WHERE '1' IN (x);
- ]], {
- -- <4.7>
- 1
- -- </4.7>
- })
-
-test:do_execsql_test(
- 4.8,
- [[
- SELECT x FROM t3 WHERE '1.0' IN (x);
- ]], {
- -- <4.8>
- 1
- -- </4.8>
- })
-
-test:do_execsql_test(
- 5.1,
- [[
- SELECT x FROM t4 WHERE x IN (1);
- ]], {
- -- <5.1>
-
- -- </5.1>
- })
-
-test:do_execsql_test(
- 5.2,
- [[
- SELECT x FROM t4 WHERE x IN (1.0);
- ]], {
- -- <5.2>
-
- -- </5.2>
- })
-
-test:do_execsql_test(
- 5.3,
- [[
- SELECT x FROM t4 WHERE x IN ('1');
- ]], {
- -- <5.3>
-
- -- </5.3>
- })
-
-test:do_execsql_test(
- 5.4,
- [[
- SELECT x FROM t4 WHERE x IN ('1.0');
- ]], {
- -- <5.4>
-
- -- </5.4>
- })
-
-test:do_execsql_test(
- 5.5,
- [[
- SELECT x FROM t4 WHERE x IN (1.11);
- ]], {
- -- <5.5>
- 1.11
- -- </5.5>
- })
-
-test:do_execsql_test(
- 5.6,
- [[
- SELECT x FROM t4 WHERE x IN ('1.11');
- ]], {
- -- <5.6>
- 1.11
- -- </5.6>
- })
-
-test:do_execsql_test(
- 5.7,
- [[
- SELECT x FROM t4 WHERE 1 IN (x);
- ]], {
- -- <5.7>
-
- -- </5.7>
- })
-
-test:do_execsql_test(
- 5.8,
- [[
- SELECT x FROM t4 WHERE 1.0 IN (x);
- ]], {
- -- <5.8>
-
- -- </5.8>
- })
-
-test:do_execsql_test(
- 5.9,
- [[
- SELECT x FROM t4 WHERE '1' IN (x);
- ]], {
- -- <5.9>
-
- -- </5.9>
- })
-
-test:do_execsql_test(
- 5.10,
- [[
- SELECT x FROM t4 WHERE '1.0' IN (x);
- ]], {
- -- <5.10>
-
- -- </5.10>
- })
-
-test:do_execsql_test(
- 5.11,
- [[
- SELECT x FROM t4 WHERE 1.11 IN (x);
- ]], {
- -- <5.11>
- 1.11
- -- </5.11>
- })
-
-test:do_execsql_test(
- 5.12,
- [[
- SELECT x FROM t4 WHERE '1.11' IN (x);
- ]], {
- -- <5.12>
- 1.11
- -- </5.12>
- })
-
-test:do_execsql_test(
- 6.1,
- [[
- SELECT x, y FROM t5 WHERE x IN (1);
- ]], {
- -- <6.1>
- 1, "one", 1, "two", 1, "three", 1.0, "four"
- -- </6.1>
- })
-
-test:do_execsql_test(
- 6.2,
- [[
- SELECT x, y FROM t5 WHERE x IN (1.0);
- ]], {
- -- <6.2>
- 1, "one", 1, "two", 1, "three", 1.0, "four"
- -- </6.2>
- })
-
-test:do_execsql_test(
- 6.3,
- [[
- SELECT x, y FROM t5 WHERE x IN ('1');
- ]], {
- -- <6.3>
- 1, "one", 1, "two", 1, "three", 1.0, "four"
- -- </6.3>
- })
-
-test:do_execsql_test(
- 6.4,
- [[
- SELECT x, y FROM t5 WHERE x IN ('1.0');
- ]], {
- -- <6.4>
- 1, "one", 1, "two", 1, "three", 1.0, "four"
- -- </6.4>
- })
-
-test:do_execsql_test(
- 6.5,
- [[
- SELECT x, y FROM t5 WHERE 1 IN (x);
- ]], {
- -- <6.5>
- 1, "one", 1, "two", 1, "three", 1.0, "four"
- -- </6.5>
- })
-
-test:do_execsql_test(
- 6.6,
- [[
- SELECT x, y FROM t5 WHERE 1.0 IN (x);
- ]], {
- -- <6.6>
- 1, "one", 1, "two", 1, "three", 1.0, "four"
- -- </6.6>
- })
-
-test:do_execsql_test(
- 6.7,
- [[
- SELECT x, y FROM t5 WHERE '1' IN (x);
- ]], {
- -- <6.7>
- 1, "one", 1, "two", 1, "three", 1.0, "four"
- -- </6.7>
- })
-
-test:do_execsql_test(
- 6.8,
- [[
- SELECT x, y FROM t5 WHERE '1.0' IN (x);
- ]], {
- -- <6.8>
- 1, "one", 1, "two", 1, "three", 1, "four"
- -- </6.8>
- })
-
-
-
-test:finish_test()
diff --git a/test/sql-tap/tkt-f973c7ac31.test.lua b/test/sql-tap/tkt-f973c7ac31.test.lua
index 82bdb52f8..381f29c65 100755
--- a/test/sql-tap/tkt-f973c7ac31.test.lua
+++ b/test/sql-tap/tkt-f973c7ac31.test.lua
@@ -39,9 +39,8 @@ for tn, sql in ipairs(sqls) do
test:do_execsql_test(
"tkt-f973c7ac3-1."..tn..".1",
[[
- SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 DESC
+ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='2' ORDER BY c2 DESC
]], {
-
})
test:do_execsql_test(
@@ -55,7 +54,7 @@ for tn, sql in ipairs(sqls) do
test:do_execsql_test(
"tkt-f973c7ac3-1."..tn..".3",
[[
- SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 DESC
+ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='5' ORDER BY c2 DESC
]], {
5, 5, 5, 4
})
@@ -63,7 +62,7 @@ for tn, sql in ipairs(sqls) do
test:do_execsql_test(
"tkt-f973c7ac3-1."..tn..".4",
[[
- SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 DESC
+ SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND c2<=5 ORDER BY c2 DESC
]], {
5, 5, 5, 4
})
@@ -71,7 +70,7 @@ for tn, sql in ipairs(sqls) do
test:do_execsql_test(
"tkt-f973c7ac3-1."..tn..".5",
[[
- SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 DESC
+ SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND CAST(c2 AS STRING)<='5' ORDER BY c2 DESC
]], {
5, 5, 5, 4
})
@@ -79,9 +78,8 @@ for tn, sql in ipairs(sqls) do
test:do_execsql_test(
"tkt-f973c7ac3-1."..tn..".6",
[[
- SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 ASC
+ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='2' ORDER BY c2 ASC
]], {
-
})
test:do_execsql_test(
@@ -95,7 +93,7 @@ for tn, sql in ipairs(sqls) do
test:do_execsql_test(
"tkt-f973c7ac3-1."..tn..".8",
[[
- SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 ASC
+ SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='5' ORDER BY c2 ASC
]], {
5, 4, 5, 5
})
@@ -103,7 +101,7 @@ for tn, sql in ipairs(sqls) do
test:do_execsql_test(
"tkt-f973c7ac3-1."..tn..".9",
[[
- SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 ASC
+ SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND c2<=5 ORDER BY c2 ASC
]], {
5, 4, 5, 5
})
@@ -111,7 +109,7 @@ for tn, sql in ipairs(sqls) do
test:do_execsql_test(
"tkt-f973c7ac3-1."..tn..".10",
[[
- SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 ASC
+ SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND CAST(c2 AS STRING)<='5' ORDER BY c2 ASC
]], {
5, 4, 5, 5
})
diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua
index de77e61e9..82ba828d0 100755
--- a/test/sql-tap/tkt3493.test.lua
+++ b/test/sql-tap/tkt3493.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(26)
+test:plan(25)
--!./tcltestrunner.lua
-- 2008 October 13
@@ -45,7 +45,7 @@ test:do_execsql_test(
[[
SELECT
CASE
- WHEN B.val = 1 THEN 'XYZ'
+ WHEN B.val = '1' THEN 'XYZ'
ELSE A.val
END AS Col1
FROM B
@@ -63,7 +63,7 @@ test:do_execsql_test(
[[
SELECT DISTINCT
CASE
- WHEN B.val = 1 THEN 'XYZ'
+ WHEN B.val = '1' THEN 'XYZ'
ELSE A.val
END AS Col1
FROM B
@@ -79,7 +79,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-1.4",
[[
- SELECT b.val, CASE WHEN b.val = 1 THEN 'xyz' ELSE b.val END AS col1 FROM b;
+ SELECT b.val, CASE WHEN b.val = '1' THEN 'xyz' ELSE b.val END AS col1 FROM b;
]], {
-- <tkt3493-1.4>
"1", "xyz", "2", "2"
@@ -91,7 +91,7 @@ test:do_execsql_test(
[[
SELECT DISTINCT
b.val,
- CASE WHEN b.val = 1 THEN 'xyz' ELSE b.val END AS col1
+ CASE WHEN b.val = '1' THEN 'xyz' ELSE b.val END AS col1
FROM b;
]], {
-- <tkt3493-1.5>
@@ -126,23 +126,13 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.2.1",
[[
- SELECT a=123 FROM t1 GROUP BY a
+ SELECT a='123' FROM t1 GROUP BY a
]], {
-- <tkt3493-2.2.1>
true
-- </tkt3493-2.2.1>
})
-test:do_execsql_test(
- "tkt3493-2.2.2",
- [[
- SELECT a=123 FROM t1
- ]], {
- -- <tkt3493-2.2.2>
- true
- -- </tkt3493-2.2.2>
- })
-
test:do_execsql_test(
"tkt3493-2.2.3",
[[
@@ -156,7 +146,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.2.4",
[[
- SELECT count(*), a=123 FROM t1
+ SELECT count(*), a='123' FROM t1
]], {
-- <tkt3493-2.2.4>
1, true
@@ -166,7 +156,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.2.5",
[[
- SELECT count(*), +a=123 FROM t1
+ SELECT count(*), +a='123' FROM t1
]], {
-- <tkt3493-2.2.5>
1, true
@@ -176,7 +166,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.3.3",
[[
- SELECT b='456' FROM t1 GROUP BY a
+ SELECT b = 456 FROM t1 GROUP BY a
]], {
-- <tkt3493-2.3.3>
true
@@ -186,7 +176,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.3.1",
[[
- SELECT b='456' FROM t1 GROUP BY b
+ SELECT b = 456 FROM t1 GROUP BY b
]], {
-- <tkt3493-2.3.1>
true
@@ -196,7 +186,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.3.2",
[[
- SELECT b='456' FROM t1
+ SELECT b = 456 FROM t1
]], {
-- <tkt3493-2.3.2>
true
@@ -206,7 +196,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.4.1",
[[
- SELECT typeof(a), a FROM t1 GROUP BY a HAVING a=123
+ SELECT typeof(a), a FROM t1 GROUP BY a HAVING a='123'
]], {
-- <tkt3493-2.4.1>
"string", "123"
@@ -216,7 +206,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.4.2",
[[
- SELECT typeof(a), a FROM t1 GROUP BY b HAVING a=123
+ SELECT typeof(a), a FROM t1 GROUP BY b HAVING a='123'
]], {
-- <tkt3493-2.4.2>
"string", "123"
@@ -226,7 +216,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.5.1",
[[
- SELECT typeof(b), b FROM t1 GROUP BY a HAVING b='456'
+ SELECT typeof(b), b FROM t1 GROUP BY a HAVING b=456
]], {
-- <tkt3493-2.5.1>
"integer", 456
@@ -236,7 +226,7 @@ test:do_execsql_test(
test:do_execsql_test(
"tkt3493-2.5.2",
[[
- SELECT typeof(b), b FROM t1 GROUP BY b HAVING b='456'
+ SELECT typeof(b), b FROM t1 GROUP BY b HAVING b=456
]], {
-- <tkt3493-2.5.2>
"integer", 456
diff --git a/test/sql-tap/transitive1.test.lua b/test/sql-tap/transitive1.test.lua
index 96895b4a7..cc7e066bf 100755
--- a/test/sql-tap/transitive1.test.lua
+++ b/test/sql-tap/transitive1.test.lua
@@ -63,7 +63,7 @@ test:do_execsql_test(
INSERT INTO t2 VALUES(2, 20,20,'20');
INSERT INTO t2 VALUES(3, 3,3,'3');
- SELECT a,b,c FROM t2 WHERE a=b AND c=b AND c=20;
+ SELECT a,b,c FROM t2 WHERE a=b AND c=CAST(b AS STRING) AND c='20';
]], {
-- <transitive1-200>
20, 20, "20"
@@ -73,7 +73,7 @@ test:do_execsql_test(
test:do_execsql_test(
"transitive1-210",
[[
- SELECT a,b,c FROM t2 WHERE a=b AND c=b AND c>='20' ORDER BY +a;
+ SELECT a,b,c FROM t2 WHERE a=b AND c=CAST(b AS STRING) AND c>='20' ORDER BY +a;
]], {
-- <transitive1-210>
3, 3, "3", 20, 20, "20"
@@ -83,7 +83,7 @@ test:do_execsql_test(
test:do_execsql_test(
"transitive1-220",
[[
- SELECT a,b,c FROM t2 WHERE a=b AND c=b AND c<='20' ORDER BY +a;
+ SELECT a,b,c FROM t2 WHERE a=b AND c=CAST(b AS STRING) AND c<='20' ORDER BY +a;
]], {
-- <transitive1-220>
20, 20, "20", 100, 100, "100"
@@ -402,7 +402,7 @@ test:do_execsql_test(
[[
CREATE TABLE x(i INTEGER PRIMARY KEY, y TEXT);
INSERT INTO x VALUES(10, '10');
- SELECT * FROM x WHERE x.y>='1' AND x.y<'2' AND x.i=x.y;
+ SELECT * FROM x WHERE x.y>='1' AND x.y<'2' AND CAST(x.i AS STRING)=x.y;
]], {
-- <transitive1-500>
10, "10"
@@ -430,7 +430,7 @@ test:do_execsql_test(
[[
CREATE TABLE t3(i INTEGER PRIMARY KEY, t TEXT);
INSERT INTO t3 VALUES(10, '10');
- SELECT * FROM t3 WHERE i=t AND t = '10 ';
+ SELECT * FROM t3 WHERE CAST(i AS STRING)=t AND t = '10 ';
]], {
-- <transitive1-520>
@@ -443,7 +443,7 @@ test:do_execsql_test(
CREATE TABLE u1(x TEXT PRIMARY KEY, y INTEGER, z TEXT);
CREATE INDEX i1 ON u1(x);
INSERT INTO u1 VALUES('00013', 13, '013');
- SELECT * FROM u1 WHERE x=y AND y=z AND z='013';
+ SELECT * FROM u1 WHERE CAST(x AS INTEGER)=y AND y=CAST(z AS INTEGER) AND z='013';
]], {
-- <transitive1-530>
"00013",13,"013"
diff --git a/test/sql-tap/where2.test.lua b/test/sql-tap/where2.test.lua
index f267be8e6..7348a855a 100755
--- a/test/sql-tap/where2.test.lua
+++ b/test/sql-tap/where2.test.lua
@@ -4,7 +4,7 @@ yaml = require("yaml")
fio = require("fio")
ffi = require("ffi")
-test:plan(74)
+test:plan(62)
ffi.cdef[[
int dup(int oldfd);
@@ -622,181 +622,12 @@ test:do_test(
-- </where2-6.6>
})
--- if X(356, "X!cmd", [=[["expr","[permutation] != \"no_optimization\""]]=])
--- then
- -- Ticket #2249. Make sure the OR optimization is not attempted if
- -- comparisons between columns of different affinities are needed.
- --
- test:do_test(
- "where2-6.7",
- function()
- test:execsql [[
- CREATE TABLE t2249a(a TEXT PRIMARY KEY, x VARCHAR(100));
- CREATE TABLE t2249b(b INTEGER PRIMARY KEY);
- INSERT INTO t2249a(a) VALUES('0123');
- INSERT INTO t2249b VALUES(123);
- ]]
- return queryplan([[
- -- Because a is type TEXT and b is type INTEGER, both a and b
- -- will attempt to convert to NUMERIC before the comparison.
- -- They will thus compare equal.
- --
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b;
- ]])
- end, {
- -- <where2-6.7>
- 123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.7>
- })
-
- test:do_test(
- "where2-6.9",
- function()
- return queryplan([[
- -- The + operator doesn't affect RHS.
- --
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b;
- ]])
- end, {
- -- <where2-6.9>
- 123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.9>
- })
-
- test:do_test(
- "where2-6.9.2",
- function()
- -- The same thing but with the expression flipped around.
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a
- ]])
- end, {
- -- <where2-6.9.2>
- 123, "0123","nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.9.2>
- })
-
- test:do_test(
- "where2-6.10",
- function()
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +a=+b;
- ]])
- end, {
- -- <where2-6.10>
- 123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.10>
- })
-
- test:do_test(
- "where2-6.11",
- function()
- -- This will not attempt the OR optimization because of the a=b
- -- comparison.
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello';
- ]])
- end, {
- -- <where2-6.11>
- 123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.11>
- })
-
- test:do_test(
- "where2-6.11.2",
- function()
- -- Permutations of the expression terms.
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello';
- ]])
- end, {
- -- <where2-6.11.2>
- 123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.11.2>
- })
-
- test:do_test(
- "where2-6.11.3",
- function()
- -- Permutations of the expression terms.
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a;
- ]])
- end, {
- -- <where2-6.11.3>
- 123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.11.3>
- })
-
- test:do_test(
- "where2-6.11.4",
- function()
- -- Permutations of the expression terms.
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a;
- ]])
- end, {
- -- <where2-6.11.4>
- 123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.11.4>
- })
-
- -- These tests are not run if subquery support is not included in the
- -- build. This is because these tests test the "a = 1 OR a = 2" to
- -- "a IN (1, 2)" optimisation transformation, which is not enabled if
- -- subqueries and the IN operator is not available.
- --
- test:do_test(
- "where2-6.12",
- function()
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello';
- ]])
- end, {
- -- <where2-6.12>
- 123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.12>
- })
-
- test:do_test(
- "where2-6.12.2",
- function()
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a;
- ]])
- end, {
- -- <where2-6.12.2>
- 123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.12.2>
- })
-
- test:do_test(
- "where2-6.12.3",
- function()
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello';
- ]])
- end, {
- -- <where2-6.12.3>
- 123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.12.3>
- })
-
- test:do_test(
- "where2-6.13",
- function()
- -- The addition of +a on the second term disabled the OR optimization.
- -- But we should still get the same empty-set result as in where2-6.9.
- return queryplan([[
- SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello';
- ]])
- end, {
- -- <where2-6.13>
- 123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
- -- </where2-6.13>
- })
-
-
+ test:execsql [[
+ CREATE TABLE t2249a(a TEXT PRIMARY KEY, x VARCHAR(100));
+ CREATE TABLE t2249b(b INTEGER PRIMARY KEY);
+ INSERT INTO t2249a(a) VALUES('0123');
+ INSERT INTO t2249b VALUES(123);
+ ]]
-- Variations on the order of terms in a WHERE clause in order
-- to make sure the OR optimizer can recognize them all.
diff --git a/test/sql-tap/where5.test.lua b/test/sql-tap/where5.test.lua
index 3aefcaca5..a93ba7854 100755
--- a/test/sql-tap/where5.test.lua
+++ b/test/sql-tap/where5.test.lua
@@ -34,7 +34,7 @@ test:do_test("where5-1.0", function()
INSERT INTO t3 SELECT CAST(x AS INTEGER) FROM t1;
]]
return test:execsql [[
- SELECT * FROM t1 WHERE x<0
+ SELECT * FROM t1 WHERE CAST(x AS INTEGER)<0
]]
end, {
-- <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/types.result b/test/sql/types.result
index 54aff460e..62161cfe1 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -339,12 +339,12 @@ box.execute("INSERT INTO tboolean VALUES (TRUE);")
box.execute("SELECT * FROM tboolean WHERE s1 = x'44';")
---
- null
-- 'Type mismatch: can not convert varbinary to boolean'
+- 'Type mismatch: can not convert boolean to varbinary'
...
box.execute("SELECT * FROM tboolean WHERE s1 = 'abc';")
---
- null
-- 'Type mismatch: can not convert abc to boolean'
+- 'Type mismatch: can not convert boolean to text'
...
box.execute("SELECT * FROM tboolean WHERE s1 = 1;")
---
@@ -606,14 +606,6 @@ box.execute("SELECT 18446744073709551615.0 > 18446744073709551615")
rows:
- [true]
...
-box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)")
----
-- metadata:
- - name: 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)
- type: boolean
- rows:
- - [true]
-...
box.execute("SELECT 1 LIMIT 18446744073709551615;")
---
- metadata:
diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua
index bd14b342d..f9603c60d 100644
--- a/test/sql/types.test.lua
+++ b/test/sql/types.test.lua
@@ -151,7 +151,6 @@ box.execute("SELECT 18446744073709551610 - 18446744073709551615;")
box.execute("SELECT 18446744073709551615 = null;")
box.execute("SELECT 18446744073709551615 = 18446744073709551615.0;")
box.execute("SELECT 18446744073709551615.0 > 18446744073709551615")
-box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)")
box.execute("SELECT 1 LIMIT 18446744073709551615;")
box.execute("SELECT 1 LIMIT 1 OFFSET 18446744073709551614;")
box.execute("SELECT CAST('18446744073' || '709551616' AS INTEGER);")
More information about the Tarantool-patches
mailing list