[Tarantool-patches] [PATCH v3 6/8] sql: remove implicit cast for comparison
Mergen Imeev
imeevma at tarantool.org
Sun Jul 5 17:47:20 MSK 2020
My answers and new patch below.
On Mon, Jun 29, 2020 at 11:51:29PM +0000, Nikita Pettik wrote:
> On 25 Jun 18:17, imeevma at tarantool.org wrote:
> > 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().
>
> Не понял как это связано...Твои изменения не затрагивают
> инвариант MEM_Dyn.
>
True, however original asserts checked that
mem_apply_type() does not changed it. Now we can say that
it definitely won't be changed. I can return flags*
variables for the sake of these asserts. Should I do this?
Also, after #4906 these asserts become outdated since flags
of the mems won't change at all. Well, it will be so if we
close #4906.
> > >> @@ -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.
>
> Гораздо лучше потом клепать фоллоу-апы, точечно затыкая креши..
> Я бы добавил хотя бы самых базовых примеров.
>
Added.
> > 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 (...)
> continue
> if (...)
> continue
>
> А еще лучше вынести этот чанк в хелпер.
>
Moved to a new function.
> > + /*
> > + * If the number was not converted without loss,
> > + * we will not find tuples using the EQ iterator.
> > + */
>
> Пожайлуста, не используй двойное отрицание - комментарий очень
> сложно воспринимать. Давай это переформулируем, можно пример
> добавить. Это довольно скользкое место.
>
Removed. Added comment in description of a new function.
> > + 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];
>
> Как-то мне эти два места (выше в SeekGE) вообще не нравятся.
> Какие-то они запутанные. Давай подумаем как можно зарефакторить их.
>
Moved to a new function. However, previously cast
here didn't always worked since in some cases field_type
and MEM_type of the mem not always were compatible. I think
this was fixed in previous commits. All tests passes.
However, there is another possiblity: it may be that I
already removed tests that fail on convertion but worked
on check. I do not think that it is the case though.
This also can be fully fixed in #4906 issue.
> > + 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)
>
> Ты в следующем коммите как раз делаешь клин-ап. Давай эту функцию
> там и удалим (как и остальные).
>
It is a bit troublesome since the functions I remove here
are 'static'. I think, that instead of removing 'static'
keyword here and removing the function in the next commit
it is better to just remove these runctions here.
I can change it if you think that removing in two commits
is better.
> > 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
>
> Когда кто-то ищет где посмотреть тесты связанные с типами,
> первое что приходит в голову - грепнуть *types*. Этот тест
> по сути ничего и не тестирует (уверен, эти пути уже покрыты
> в других тестах), зато хорошо отображает работу системы типов.
> Я бы просто подумал о других разработчиках, и все же замержил
> его в какой-нибудь тест типа sql/types.test.lua.
Moved to sql/types.test.lua
New patch:
commit f52e6669146e7a06b78d167abf369d67d9327789
Author: Mergen Imeev <imeevma at gmail.com>
Date: Thu Jun 25 12:39:19 2020 +0300
sql: remove implicit cast for comparison
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 ce9edaad9..0d3da49d3 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -561,6 +561,50 @@ mem_convert_to_numeric(struct Mem *mem, enum field_type type, bool is_precise)
return mem_convert_to_integer(mem, is_precise);
}
+/**
+ * Check that MEM types of mems are compatible with the
+ * corresponding types from key_def.
+ *
+ * If mem and type are comparable but incompatible according to
+ * field_mp_plain_type_is_compatible() and the comparison
+ * operation is EQ, we can say that the result of the comparison
+ * will be FALSE. This allows us to compare DOUBLE and INTEGER
+ * values.
+ *
+ * @param mems The first mem of the array.
+ * @param def key_def that contains types to check.
+ * @param size Size of the array.
+ * @param is_op_eq TRUE is comparison operator is EQ.
+ * @param[out] is_not_converted TRUE if operation is EQ and mem
+ and type are comparable but not
+ compatible. FALSE otherwise.
+ * @retval TRUE if the MEM types and types from key_def are
+ * comparable, FALSE otherwise.
+ */
+static bool
+mem_are_types_comparable(struct Mem *mems, struct key_def *def, uint32_t size,
+ bool is_op_eq, bool *is_not_converted) {
+ assert(!is_op_eq || !*is_not_converted);
+ for (uint32_t i = 0; i < size; ++i) {
+ enum field_type type = def->parts[i].type;
+ struct Mem *mem = &mems[i];
+ if (mem_is_type_compatible(mem, type))
+ continue;
+ if (mem_convert_to_numeric(mem, type, true) == 0)
+ continue;
+ if (sql_type_is_numeric(type) &&
+ (mem->flags & (MEM_Int | MEM_UInt | MEM_Real)) != 0) {
+ if (is_op_eq)
+ *is_not_converted = true;
+ continue;
+ }
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ field_type_strs[type], mem_type_to_str(mem));
+ return false;
+ }
+ return true;
+}
+
/*
* pMem currently only holds a string type (or maybe a BLOB that we can
* interpret as a string if we want to). Compute its corresponding
@@ -2289,8 +2333,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) {
@@ -2349,22 +2391,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()
@@ -2397,22 +2434,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);
@@ -2428,14 +2460,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 */
@@ -3530,8 +3554,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;
@@ -3623,7 +3645,20 @@ 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];
+ bool is_not_found = false;
+ if (!mem_are_types_comparable(r.aMem, r.key_def, r.nField, eqOnly == 1,
+ &is_not_found))
+ goto abort_due_to_error;
+ if (is_not_found) {
+ res = 1;
+ goto seek_not_found;
+ }
#ifdef SQL_DEBUG
{ int i; for(i=0; i<r.nField; i++) assert(memIsValid(&r.aMem[i])); }
#endif
@@ -4750,7 +4785,16 @@ case OP_IdxGE: { /* jump */
assert(pOp->opcode==OP_IdxGE || pOp->opcode==OP_IdxLT);
r.default_rc = 0;
}
+
+ /*
+ * Make sure that the types of mems are comparable with
+ * the field types of the space.
+ */
r.aMem = &aMem[pOp->p3];
+ bool unused = false;
+ if (!mem_are_types_comparable(r.aMem, r.key_def, r.nField, false,
+ &unused))
+ 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/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..1c8357bce 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:
@@ -2152,6 +2144,231 @@ box.execute([[SELECT * FROM "s" WHERE "id" = ?;]])
type: string
rows: []
...
+--
+-- gh-4230: Make sure that there is no implicit cast between
+-- string and number.
+--
+box.execute([[SELECT '1' > 0;]]);
+---
+- null
+- 'Type mismatch: can not convert 1 to numeric'
+...
+box.execute([[SELECT 1 > '0';]]);
+---
+- null
+- 'Type mismatch: can not convert 0 to numeric'
+...
+box.execute([[CREATE TABLE t (i INT PRIMARY KEY, d DOUBLE, n NUMBER, s STRING);]])
+---
+- row_count: 1
+...
+box.execute([[INSERT INTO t VALUES (1, 1.0, 1, '2'), (2, 2.0, 2.0, '2');]])
+---
+- row_count: 2
+...
+box.execute([[SELECT * from t WHERE i > s;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE s > i;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE d > s;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE s > d;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE i = 1 and n > s;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE i = 2 and s > n;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE i in (1);]])
+---
+- metadata:
+ - name: I
+ type: integer
+ rows:
+ - [1]
+...
+box.execute([[SELECT i FROM t WHERE d in (1);]])
+---
+- metadata:
+ - name: I
+ type: integer
+ rows:
+ - [1]
+...
+box.execute([[SELECT i FROM t WHERE n in (1);]])
+---
+- metadata:
+ - name: I
+ type: integer
+ rows:
+ - [1]
+...
+box.execute([[SELECT i FROM t WHERE s in (1);]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE i in (1.0);]])
+---
+- metadata:
+ - name: I
+ type: integer
+ rows:
+ - [1]
+...
+box.execute([[SELECT i FROM t WHERE d in (1.0);]])
+---
+- metadata:
+ - name: I
+ type: integer
+ rows:
+ - [1]
+...
+box.execute([[SELECT i FROM t WHERE n in (1.0);]])
+---
+- metadata:
+ - name: I
+ type: integer
+ rows:
+ - [1]
+...
+box.execute([[SELECT i FROM t WHERE s in (1.0);]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE i in ('1');]])
+---
+- null
+- 'Type mismatch: can not convert integer to text'
+...
+box.execute([[SELECT i FROM t WHERE d in ('1');]])
+---
+- null
+- 'Type mismatch: can not convert 1 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE n in ('1');]])
+---
+- null
+- 'Type mismatch: can not convert 1 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE s in ('1');]])
+---
+- metadata:
+ - name: I
+ type: integer
+ rows: []
+...
+box.execute([[SELECT i FROM t WHERE i in ('1.0');]])
+---
+- null
+- 'Type mismatch: can not convert integer to text'
+...
+box.execute([[SELECT i FROM t WHERE d in ('1.0');]])
+---
+- null
+- 'Type mismatch: can not convert 1.0 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE n in ('1.0');]])
+---
+- null
+- 'Type mismatch: can not convert 1.0 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE s in ('1.0');]])
+---
+- metadata:
+ - name: I
+ type: integer
+ rows: []
+...
+box.execute([[DROP TABLE t;]])
+---
+- row_count: 1
+...
+-- Comparison with SCALAR.
+box.execute([[CREATE TABLE t(a SCALAR PRIMARY KEY);]])
+---
+- row_count: 1
+...
+box.execute([[INSERT INTO t VALUES (1), (2.2), ('3');]]);
+---
+- row_count: 3
+...
+box.execute([[SELECT a FROM t WHERE a > 1]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a > 1.0]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a > '1']]);
+---
+- metadata:
+ - name: A
+ type: scalar
+ rows:
+ - ['3']
+...
+box.execute([[SELECT a FROM t WHERE a < 1]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a < 1.0]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a < '1']]);
+---
+- metadata:
+ - name: A
+ type: scalar
+ rows:
+ - [1]
+ - [2.2]
+...
+box.execute([[SELECT a FROM t WHERE a = 1]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a = 1.0]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a = '1']]);
+---
+- metadata:
+ - name: A
+ type: scalar
+ rows: []
+...
+box.execute([[DROP TABLE t;]])
+---
+- row_count: 1
+...
s:drop()
---
...
diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua
index bd14b342d..cf0db5ae0 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);")
@@ -486,4 +485,56 @@ s:format({ \
})
box.execute([[SELECT * FROM "s" WHERE "id" = ?;]])
+
+--
+-- gh-4230: Make sure that there is no implicit cast between
+-- string and number.
+--
+box.execute([[SELECT '1' > 0;]]);
+box.execute([[SELECT 1 > '0';]]);
+box.execute([[CREATE TABLE t (i INT PRIMARY KEY, d DOUBLE, n NUMBER, s STRING);]])
+box.execute([[INSERT INTO t VALUES (1, 1.0, 1, '2'), (2, 2.0, 2.0, '2');]])
+box.execute([[SELECT * from t WHERE i > s;]])
+box.execute([[SELECT * from t WHERE s > i;]])
+box.execute([[SELECT * from t WHERE d > s;]])
+box.execute([[SELECT * from t WHERE s > d;]])
+box.execute([[SELECT * from t WHERE i = 1 and n > s;]])
+box.execute([[SELECT * from t WHERE i = 2 and s > n;]])
+
+box.execute([[SELECT i FROM t WHERE i in (1);]])
+box.execute([[SELECT i FROM t WHERE d in (1);]])
+box.execute([[SELECT i FROM t WHERE n in (1);]])
+box.execute([[SELECT i FROM t WHERE s in (1);]])
+
+box.execute([[SELECT i FROM t WHERE i in (1.0);]])
+box.execute([[SELECT i FROM t WHERE d in (1.0);]])
+box.execute([[SELECT i FROM t WHERE n in (1.0);]])
+box.execute([[SELECT i FROM t WHERE s in (1.0);]])
+
+box.execute([[SELECT i FROM t WHERE i in ('1');]])
+box.execute([[SELECT i FROM t WHERE d in ('1');]])
+box.execute([[SELECT i FROM t WHERE n in ('1');]])
+box.execute([[SELECT i FROM t WHERE s in ('1');]])
+
+box.execute([[SELECT i FROM t WHERE i in ('1.0');]])
+box.execute([[SELECT i FROM t WHERE d in ('1.0');]])
+box.execute([[SELECT i FROM t WHERE n in ('1.0');]])
+box.execute([[SELECT i FROM t WHERE s in ('1.0');]])
+
+box.execute([[DROP TABLE t;]])
+
+-- Comparison with SCALAR.
+box.execute([[CREATE TABLE t(a SCALAR PRIMARY KEY);]])
+box.execute([[INSERT INTO t VALUES (1), (2.2), ('3');]]);
+box.execute([[SELECT a FROM t WHERE a > 1]]);
+box.execute([[SELECT a FROM t WHERE a > 1.0]]);
+box.execute([[SELECT a FROM t WHERE a > '1']]);
+box.execute([[SELECT a FROM t WHERE a < 1]]);
+box.execute([[SELECT a FROM t WHERE a < 1.0]]);
+box.execute([[SELECT a FROM t WHERE a < '1']]);
+box.execute([[SELECT a FROM t WHERE a = 1]]);
+box.execute([[SELECT a FROM t WHERE a = 1.0]]);
+box.execute([[SELECT a FROM t WHERE a = '1']]);
+box.execute([[DROP TABLE t;]])
+
s:drop()
More information about the Tarantool-patches
mailing list