From: Mergen Imeev <imeevma@tarantool.org>
To: v.shpilevoy@tarantool.org, tsafin@tarantool.org,
tarantool-patches@dev.tarantool.org
Subject: [Tarantool-patches] [PATCH 5/6] sql: remove implicit cast from string for comparison
Date: Thu, 28 May 2020 17:17:37 +0300 [thread overview]
Message-ID: <49b7078dd5f787e2dcde37554522afbe63f1d994.1590671266.git.imeevma@gmail.com> (raw)
In-Reply-To: <cover.1590671266.git.imeevma@gmail.com>
This patch removes implicit cast from strings to numbers for
comparison.
Closes #4230
@TarantoolBot document
Title: remove implicit cast between strings and numbers
This patch-set removes implicit cast from string to number and
from number to string.
Example:
For comparison:
tarantool> box.execute([[SELECT '1' > 0;]])
---
- null
- 'Type mismatch: can not convert 1 to numeric'
...
tarantool> box.execute([[SELECT "id" FROM "_space" WHERE '1' > "id";]])
---
- null
- 'Type mismatch: can not convert text to unsigned'
...
For assignment:
tarantool> box.execute([[CREATE TABLE t1(i INT PRIMARY KEY);]])
tarantool> box.execute([[INSERT INTO t1 VALUES ('1');]])
---
- null
- 'Type mismatch: can not convert text to integer'
...
tarantool> box.execute([[CREATE TABLE t2(t text PRIMARY KEY);]])
tarantool> box.execute([[INSERT INTO t2 VALUES (1);]])
---
- null
- 'Type mismatch: can not convert unsigned to string'
...
---
src/box/sql/vdbe.c | 105 ++++----
src/box/sql/wherecode.c | 203 +--------------
.../gh-4230-del-impl-cast-str-to-num.test.lua | 78 ++++++
test/sql-tap/identifier_case.test.lua | 6 +-
test/sql-tap/in1.test.lua | 4 +-
test/sql-tap/in4.test.lua | 3 +-
test/sql-tap/insert3.test.lua | 2 +-
test/sql-tap/intpkey.test.lua | 24 +-
test/sql-tap/join.test.lua | 7 +-
test/sql-tap/misc1.test.lua | 32 +--
test/sql-tap/select1.test.lua | 4 +-
test/sql-tap/select7.test.lua | 2 +-
test/sql-tap/subquery.test.lua | 4 +-
test/sql-tap/tkt-9a8b09f8e6.test.lua | 84 ++++---
test/sql-tap/tkt-f973c7ac31.test.lua | 32 +--
test/sql-tap/tkt-fc7bd6358f.test.lua | 6 +-
test/sql-tap/tkt3493.test.lua | 40 ++-
test/sql-tap/transitive1.test.lua | 12 +-
test/sql-tap/where2.test.lua | 183 +-------------
test/sql-tap/where5.test.lua | 12 +-
test/sql-tap/whereB.test.lua | 238 ++++++++++--------
test/sql/types.result | 12 +-
test/sql/types.test.lua | 1 -
23 files changed, 418 insertions(+), 676 deletions(-)
create mode 100755 test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 7add67ae7..021e09d1e 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -2237,8 +2237,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) {
@@ -2297,22 +2295,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()
@@ -2345,22 +2338,17 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */
goto compare_op;
}
} else if (type == FIELD_TYPE_STRING) {
- if ((flags1 & MEM_Str) == 0 &&
- (flags1 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) {
- testcase( pIn1->flags & MEM_Int);
- testcase( pIn1->flags & MEM_Real);
- sqlVdbeMemStringify(pIn1);
- testcase( (flags1&MEM_Dyn) != (pIn1->flags&MEM_Dyn));
- flags1 = (pIn1->flags & ~MEM_TypeMask) | (flags1 & MEM_TypeMask);
- assert(pIn1!=pIn3);
+ if ((flags1 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ mem_type_to_str(pIn3),
+ mem_type_to_str(pIn1));
+ goto abort_due_to_error;
}
- if ((flags3 & MEM_Str) == 0 &&
- (flags3 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) {
- testcase( pIn3->flags & MEM_Int);
- testcase( pIn3->flags & MEM_Real);
- sqlVdbeMemStringify(pIn3);
- testcase( (flags3&MEM_Dyn) != (pIn3->flags&MEM_Dyn));
- flags3 = (pIn3->flags & ~MEM_TypeMask) | (flags3 & MEM_TypeMask);
+ if ((flags3 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ mem_type_to_str(pIn1),
+ mem_type_to_str(pIn3));
+ goto abort_due_to_error;
}
}
assert(pOp->p4type==P4_COLLSEQ || pOp->p4.pColl==0);
@@ -2376,14 +2364,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 */
@@ -3479,8 +3459,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;
@@ -3573,6 +3551,26 @@ skip_truncate:
assert(oc!=OP_SeekLT || r.default_rc==+1);
r.aMem = &aMem[pOp->p3];
+ for (int i = 0; i < r.nField; ++i) {
+ enum field_type type = r.key_def->parts[i].type;
+ struct Mem *mem = &r.aMem[i];
+ if ((mem->flags & MEM_Str) != 0 && sql_type_is_numeric(type)) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ field_type_strs[type], mem_type_to_str(mem));
+ goto abort_due_to_error;
+ }
+ if (mem_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
@@ -4700,6 +4698,27 @@ case OP_IdxGE: { /* jump */
r.default_rc = 0;
}
r.aMem = &aMem[pOp->p3];
+ for (int i = 0; i < r.nField; ++i) {
+ struct Mem *mem = &r.aMem[i];
+ enum mp_type mp_type = sql_value_type(mem);
+ enum field_type field_type = r.key_def->parts[i].type;
+ if (field_type == FIELD_TYPE_SCALAR ||
+ mem->field_type == FIELD_TYPE_SCALAR)
+ continue;
+ bool is_nullable = r.key_def->parts[i].nullable_action ==
+ ON_CONFLICT_ACTION_NONE;
+ if (field_mp_plain_type_is_compatible(field_type, mp_type,
+ is_nullable))
+ continue;
+ if (!sql_type_is_numeric(field_type) ||
+ !(mp_type == MP_INT || mp_type == MP_UINT ||
+ mp_type == MP_DOUBLE || mp_type == MP_FLOAT)) {
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ mem_type_to_str(mem),
+ field_type_strs[field_type]);
+ goto abort_due_to_error;
+ }
+ }
#ifdef SQL_DEBUG
{ int i; for(i=0; i<r.nField; i++) assert(memIsValid(&r.aMem[i])); }
#endif
diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c
index 5bc27f134..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,91 +950,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
}
struct index_def *idx_pk = space->index[0]->def;
uint32_t pk_part_count = idx_pk->key_def->part_count;
- /*
- * Tarantool's iterator over integer fields doesn't
- * tolerate floating point values. Hence, if term
- * is equality comparison and value of operand is
- * not integer, we can skip it since it always
- * results in false: INT a == 0.5 -> false;
- * It is done using OP_MustBeInt facilities.
- * In case term is greater comparison (a > ?), we
- * should notify OP_SeekGT to process truncation of
- * floating point value: a > 0.5 -> a >= 1;
- * It is done by setting P5 flag for OP_Seek*.
- * It is worth mentioning that we do not need
- * this step when it comes for less (<) comparison
- * of nullable field. Key is NULL in this case:
- * values are ordered as NULL, ... NULL, min_value,
- * so to fetch min value we pass NULL to GT iterator.
- * The only exception is less comparison in
- * conjunction with ORDER BY DESC clause:
- * in such situation we use LE iterator and
- * truncated value to compare. But then
- * pRangeStart == NULL.
- * This procedure is correct for compound index:
- * only one comparison of less/greater type can be
- * used at the same time. For instance,
- * a < 1.5 AND b > 0.5 is handled by SeekGT using
- * column a and fetching column b from tuple and
- * OP_Le comparison.
- *
- * Note that OP_ApplyType, which is emitted before
- * OP_Seek** doesn't truncate floating point to
- * integer. That's why we need this routine.
- * Also, note that terms are separated by OR
- * predicates, so we consider term as sequence
- * of AND'ed predicates.
- */
- size_t addrs_sz = sizeof(int) * nEq;
- int *seek_addrs = region_alloc(&pParse->region, addrs_sz);
- if (seek_addrs == NULL) {
- diag_set(OutOfMemory, addrs_sz, "region", "seek_addrs");
- pParse->is_aborted = true;
- return 0;
- }
- memset(seek_addrs, 0, addrs_sz);
- for (int i = 0; i < nEq; i++) {
- enum field_type type = idx_def->key_def->parts[i].type;
- if (type == FIELD_TYPE_INTEGER ||
- type == FIELD_TYPE_UNSIGNED) {
- /*
- * OP_MustBeInt consider NULLs as
- * non-integer values, so firstly
- * check whether value is NULL or not.
- */
- seek_addrs[i] = sqlVdbeAddOp1(v, OP_IsNull,
- regBase);
- sqlVdbeAddOp2(v, OP_MustBeInt, regBase + i,
- addrNxt);
- start_types[i] = FIELD_TYPE_SCALAR;
- /*
- * We need to notify column cache
- * that type of value may change
- * so we should fetch value from
- * tuple again rather then copy
- * from register.
- */
- sql_expr_type_cache_change(pParse, regBase + i,
- 1);
- }
- }
- /* Inequality constraint comes always at the end of list. */
- part_count = idx_def->key_def->part_count;
- if (pRangeStart != NULL) {
- /*
- * nEq == 0 means that filter condition
- * contains only inequality.
- */
- uint32_t ineq_idx = nEq == 0 ? 0 : nEq - 1;
- assert(ineq_idx < part_count);
- enum field_type ineq_type =
- idx_def->key_def->parts[ineq_idx].type;
- if (ineq_type == FIELD_TYPE_INTEGER ||
- ineq_type == FIELD_TYPE_UNSIGNED)
- force_integer_reg = regBase + nEq;
- }
- emit_apply_type(pParse, regBase, nConstraint - bSeekPastNull,
- start_types);
if (pLoop->nSkip > 0 && nConstraint == pLoop->nSkip) {
/* The skip-scan logic inside the call to codeAllEqualityConstraints()
* above has already left the cursor sitting on the correct row,
@@ -1143,10 +959,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
op = aStartOp[(start_constraints << 2) +
(startEq << 1) + bRev];
assert(op != 0);
- for (uint32_t i = 0; i < nEq; ++i) {
- if (seek_addrs[i] != 0)
- sqlVdbeJumpHere(v, seek_addrs[i]);
- }
sqlVdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase,
nConstraint);
/* If this is Seek* opcode, and IPK is detected in the
@@ -1186,13 +998,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
addrNxt);
VdbeCoverage(v);
}
- if (end_types) {
- expr_cmp_update_rhs_type(pRight, nTop, end_types);
- emit_apply_type(pParse, regBase + nEq, nTop,
- end_types);
- } else {
- assert(pParse->db->mallocFailed);
- }
nConstraint += nTop;
testcase(pRangeEnd->wtFlags & TERM_VIRTUAL);
@@ -1206,8 +1011,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo, /* Complete information about the W
endEq = 0;
nConstraint++;
}
- sqlDbFree(db, start_types);
- sqlDbFree(db, end_types);
/* Top of the loop body */
pLevel->p2 = sqlVdbeCurrentAddr(v);
diff --git a/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua
new file mode 100755
index 000000000..ef4127e0e
--- /dev/null
+++ b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua
@@ -0,0 +1,78 @@
+#!/usr/bin/env tarantool
+test = require("sqltester")
+test:plan(8)
+
+--
+-- Make sure that there is no implicit cast between string and
+-- number.
+--
+test:do_catchsql_test(
+ "gh-4230-1",
+ [[
+ SELECT '1' > 0;
+ ]], {
+ 1, "Type mismatch: can not convert 1 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-2",
+ [[
+ SELECT 0 > '1';
+ ]], {
+ 1, "Type mismatch: can not convert 1 to numeric"
+ })
+
+test:execsql([[
+ CREATE TABLE t (i INT PRIMARY KEY, d DOUBLE, n NUMBER, s STRING);
+ INSERT INTO t VALUES (1, 1.0, 1, '2'), (2, 2.0, 2.0, '2');
+ ]])
+
+test:do_catchsql_test(
+ "gh-4230-3",
+ [[
+ SELECT * from t where i > s;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-4",
+ [[
+ SELECT * from t WHERE s > i;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-5",
+ [[
+ SELECT * from t WHERE d > s;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-6",
+ [[
+ SELECT * from t WHERE s > d;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-7",
+ [[
+ SELECT * from t WHERE i = 1 and n > s;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:do_catchsql_test(
+ "gh-4230-8",
+ [[
+ SELECT * from t WHERE i = 2 and s > n;
+ ]], {
+ 1, "Type mismatch: can not convert 2 to numeric"
+ })
+
+test:finish_test()
diff --git a/test/sql-tap/identifier_case.test.lua b/test/sql-tap/identifier_case.test.lua
index 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/intpkey.test.lua b/test/sql-tap/intpkey.test.lua
index 0db18ba91..bc3d701a7 100755
--- a/test/sql-tap/intpkey.test.lua
+++ b/test/sql-tap/intpkey.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(40)
+test:plan(39)
--!./tcltestrunner.lua
-- 2001 September 15
@@ -854,43 +854,33 @@ test:do_execsql_test(
-- </intpkey-14.2>
})
-test:do_execsql_test(
- "intpkey-14.3",
- [[
- SELECT * FROM t3 WHERE a>'2';
- ]], {
- -- <intpkey-14.3>
- 3, 3, "3"
- -- </intpkey-14.3>
- })
-
-test:do_execsql_test(
+test:do_catchsql_test(
"intpkey-14.4",
[[
SELECT * FROM t3 WHERE a<'2';
]], {
-- <intpkey-14.4>
- 1, 1, "one"
+ 1, "Type mismatch: can not convert text to integer"
-- </intpkey-14.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"intpkey-14.5",
[[
SELECT * FROM t3 WHERE a<c;
]], {
-- <intpkey-14.5>
- 1, 1, "one"
+ 1, "Type mismatch: can not convert one to numeric"
-- </intpkey-14.5>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"intpkey-14.6",
[[
SELECT * FROM t3 WHERE a=c;
]], {
-- <intpkey-14.6>
- 2, 2, "2", 3, 3, "3"
+ 1, "Type mismatch: can not convert one to numeric"
-- </intpkey-14.6>
})
diff --git a/test/sql-tap/join.test.lua b/test/sql-tap/join.test.lua
index 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
index 854ed774f..2a18b17be 100755
--- a/test/sql-tap/tkt-9a8b09f8e6.test.lua
+++ b/test/sql-tap/tkt-9a8b09f8e6.test.lua
@@ -83,23 +83,23 @@ test:do_execsql_test(
-- </1.5>
})
-test:do_execsql_test(
+test:do_catchsql_test(
2.1,
[[
SELECT x FROM t1 WHERE x IN (1);
]], {
-- <2.1>
- "1"
+ 1,"Type mismatch: can not convert 1 to numeric"
-- </2.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
2.2,
[[
SELECT x FROM t1 WHERE x IN (1.0);
]], {
-- <2.2>
- "1"
+ 1,"Type mismatch: can not convert 1 to numeric"
-- </2.2>
})
@@ -123,23 +123,23 @@ test:do_execsql_test(
-- </2.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
2.5,
[[
SELECT x FROM t1 WHERE 1 IN (x);
]], {
-- <2.5>
- "1"
+ 1,"Type mismatch: can not convert 1 to numeric"
-- </2.5>
})
-test:do_execsql_test(
+test:do_catchsql_test(
2.6,
[[
SELECT x FROM t1 WHERE 1.0 IN (x);
]], {
-- <2.6>
- "1"
+ 1,"Type mismatch: can not convert 1 to numeric"
-- </2.6>
})
@@ -183,21 +183,23 @@ test:do_execsql_test(
-- </3.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
3.3,
[[
SELECT x FROM t2 WHERE x IN ('1');
]], {
-- <3.3>
+ 1, "Type mismatch: can not convert integer to text"
-- </3.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
3.4,
[[
SELECT x FROM t2 WHERE x IN ('1');
]], {
-- <3.4>
+ 1, "Type mismatch: can not convert integer to text"
-- </3.4>
})
@@ -221,21 +223,23 @@ test:do_execsql_test(
-- </3.6>
})
-test:do_execsql_test(
+test:do_catchsql_test(
3.7,
[[
SELECT x FROM t2 WHERE '1' IN (x);
]], {
-- <3.7>
+ 1, "Type mismatch: can not convert integer to text"
-- </3.7>
})
-test:do_execsql_test(
+test:do_catchsql_test(
3.8,
[[
SELECT x FROM t2 WHERE '1' IN (x);
]], {
-- <3.8>
+ 1, "Type mismatch: can not convert integer to text"
-- </3.8>
})
@@ -259,23 +263,23 @@ test:do_execsql_test(
-- </4.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
4.3,
[[
SELECT x FROM t3 WHERE x IN ('1');
]], {
-- <4.3>
- 1.0
+ 1, "Type mismatch: can not convert number to text"
-- </4.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
4.4,
[[
SELECT x FROM t3 WHERE x IN ('1.0');
]], {
-- <4.4>
- 1.0
+ 1, "Type mismatch: can not convert number to text"
-- </4.4>
})
@@ -299,23 +303,23 @@ test:do_execsql_test(
-- </4.6>
})
-test:do_execsql_test(
+test:do_catchsql_test(
4.7,
[[
SELECT x FROM t3 WHERE '1' IN (x);
]], {
-- <4.7>
- 1
+ 1, "Type mismatch: can not convert number to text"
-- </4.7>
})
-test:do_execsql_test(
+test:do_catchsql_test(
4.8,
[[
SELECT x FROM t3 WHERE '1.0' IN (x);
]], {
-- <4.8>
- 1
+ 1, "Type mismatch: can not convert number to text"
-- </4.8>
})
@@ -339,23 +343,23 @@ test:do_execsql_test(
-- </5.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
5.3,
[[
SELECT x FROM t4 WHERE x IN ('1');
]], {
-- <5.3>
-
+ 1, "Type mismatch: can not convert number to text"
-- </5.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
5.4,
[[
SELECT x FROM t4 WHERE x IN ('1.0');
]], {
-- <5.4>
-
+ 1, "Type mismatch: can not convert number to text"
-- </5.4>
})
@@ -369,13 +373,13 @@ test:do_execsql_test(
-- </5.5>
})
-test:do_execsql_test(
+test:do_catchsql_test(
5.6,
[[
SELECT x FROM t4 WHERE x IN ('1.11');
]], {
-- <5.6>
- 1.11
+ 1, "Type mismatch: can not convert number to text"
-- </5.6>
})
@@ -399,23 +403,23 @@ test:do_execsql_test(
-- </5.8>
})
-test:do_execsql_test(
+test:do_catchsql_test(
5.9,
[[
SELECT x FROM t4 WHERE '1' IN (x);
]], {
-- <5.9>
-
+ 1, "Type mismatch: can not convert number to text"
-- </5.9>
})
-test:do_execsql_test(
+test:do_catchsql_test(
5.10,
[[
SELECT x FROM t4 WHERE '1.0' IN (x);
]], {
-- <5.10>
-
+ 1, "Type mismatch: can not convert number to text"
-- </5.10>
})
@@ -429,13 +433,13 @@ test:do_execsql_test(
-- </5.11>
})
-test:do_execsql_test(
+test:do_catchsql_test(
5.12,
[[
SELECT x FROM t4 WHERE '1.11' IN (x);
]], {
-- <5.12>
- 1.11
+ 1, "Type mismatch: can not convert number to text"
-- </5.12>
})
@@ -459,23 +463,23 @@ test:do_execsql_test(
-- </6.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
6.3,
[[
SELECT x, y FROM t5 WHERE x IN ('1');
]], {
-- <6.3>
- 1, "one", 1, "two", 1, "three", 1.0, "four"
+ 1, "Type mismatch: can not convert 1 to numeric"
-- </6.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
6.4,
[[
SELECT x, y FROM t5 WHERE x IN ('1.0');
]], {
-- <6.4>
- 1, "one", 1, "two", 1, "three", 1.0, "four"
+ 1, "Type mismatch: can not convert 1.0 to numeric"
-- </6.4>
})
@@ -499,23 +503,23 @@ test:do_execsql_test(
-- </6.6>
})
-test:do_execsql_test(
+test:do_catchsql_test(
6.7,
[[
SELECT x, y FROM t5 WHERE '1' IN (x);
]], {
-- <6.7>
- 1, "one", 1, "two", 1, "three", 1.0, "four"
+ 1, "Type mismatch: can not convert 1 to numeric"
-- </6.7>
})
-test:do_execsql_test(
+test:do_catchsql_test(
6.8,
[[
SELECT x, y FROM t5 WHERE '1.0' IN (x);
]], {
-- <6.8>
- 1, "one", 1, "two", 1, "three", 1, "four"
+ 1, "Type mismatch: can not convert 1.0 to numeric"
-- </6.8>
})
diff --git a/test/sql-tap/tkt-f973c7ac31.test.lua b/test/sql-tap/tkt-f973c7ac31.test.lua
index 82bdb52f8..5239a7785 100755
--- a/test/sql-tap/tkt-f973c7ac31.test.lua
+++ b/test/sql-tap/tkt-f973c7ac31.test.lua
@@ -36,12 +36,12 @@ local sqls = {
for tn, sql in ipairs(sqls) do
test:execsql(sql)
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".1",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 DESC
]], {
-
+ 1, "/Type mismatch: can not convert/"
})
test:do_execsql_test(
@@ -52,36 +52,36 @@ for tn, sql in ipairs(sqls) do
5, 5, 5, 4
})
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".3",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 DESC
]], {
- 5, 5, 5, 4
+ 1, "/Type mismatch: can not convert/"
})
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".4",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 DESC
]], {
- 5, 5, 5, 4
+ 1, "/Type mismatch: can not convert/"
})
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".5",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 DESC
]], {
- 5, 5, 5, 4
+ 1, "/Type mismatch: can not convert/"
})
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".6",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 ASC
]], {
-
+ 1, "/Type mismatch: can not convert/"
})
test:do_execsql_test(
@@ -92,28 +92,28 @@ for tn, sql in ipairs(sqls) do
5, 4, 5, 5
})
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".8",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 ASC
]], {
- 5, 4, 5, 5
+ 1, "/Type mismatch: can not convert/"
})
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".9",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 ASC
]], {
- 5, 4, 5, 5
+ 1, "/Type mismatch: can not convert/"
})
- test:do_execsql_test(
+ test:do_catchsql_test(
"tkt-f973c7ac3-1."..tn..".10",
[[
SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 ASC
]], {
- 5, 4, 5, 5
+ 1, "/Type mismatch: can not convert/"
})
end
diff --git a/test/sql-tap/tkt-fc7bd6358f.test.lua b/test/sql-tap/tkt-fc7bd6358f.test.lua
index f38ffa3d6..f77e4bea8 100755
--- a/test/sql-tap/tkt-fc7bd6358f.test.lua
+++ b/test/sql-tap/tkt-fc7bd6358f.test.lua
@@ -78,15 +78,17 @@ for a, from in ipairs(froms) do
test:do_test(
string.format("tkt-fc7bd6358f.110.%s.%s.1", a, b),
function()
- return test:execsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where))
+ return test:catchsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where))
end, {
+ 1, "Type mismatch: can not convert integer to text"
})
test:do_test(
string.format("tkt-fc7bd6358f.110.%s.%s.2", a, b),
function()
- return test:execsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where))
+ return test:catchsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where))
end, {
+ 1, "Type mismatch: can not convert integer to text"
})
end
diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua
index de77e61e9..ec12a4492 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-tap/whereB.test.lua b/test/sql-tap/whereB.test.lua
index 970ff1dec..9e99ea41c 100755
--- a/test/sql-tap/whereB.test.lua
+++ b/test/sql-tap/whereB.test.lua
@@ -27,8 +27,7 @@ test:plan(63)
-- These values are not equal and because neither affinity is NUMERIC
-- no type conversion occurs.
--
-test:do_execsql_test(
- "whereB-1.1",
+test:execsql(
[[
CREATE TABLE t1(x INT primary key,y INT ); -- affinity of t1.y is NONE
INSERT INTO t1 VALUES(1,99);
@@ -36,49 +35,54 @@ test:do_execsql_test(
CREATE TABLE t2(a INT primary key, b TEXT); -- affinity of t2.b is TEXT
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(2,'99');
+ ]]
+)
+test:do_catchsql_test(
+ "whereB-1.1",
+ [[
SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a;
]],
{
-- <whereB-1.1>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-1.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-1.2",
[[
SELECT x, a, y=b FROM t1, t2 WHERE y=b;
]],
{
-- <whereB-1.2>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-1.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-1.3",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-1.3>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-1.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-1.4",
[[
SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
]],
{
-- <whereB-1.4>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-1.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-1.100",
[[
DROP INDEX t2b ON t2;
@@ -86,29 +90,29 @@ test:do_execsql_test(
]],
{
-- <whereB-1.100>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-1.100>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-1.101",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-1.101>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-1.101>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-1.102",
[[
SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
]],
{
-- <whereB-1.102>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-1.102>
})
@@ -304,8 +308,7 @@ test:do_execsql_test(
-- Because t2.b has a numeric affinity, type conversion should occur
-- and the two fields should be equal.
--
-test:do_execsql_test(
- "whereB-4.1",
+test:execsql(
[[
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
@@ -316,38 +319,44 @@ test:do_execsql_test(
CREATE TABLE t2(a INT primary key, b NUMBER); -- affinity of t2.b is NUMERIC
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(2,99);
+ ]]
+)
+
+test:do_catchsql_test(
+ "whereB-4.1",
+ [[
SELECT x, a, y=b FROM t1, t2;
]],
{
-- <whereB-4.1>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-4.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-4.2",
[[
SELECT x, a, y=b FROM t1, t2 WHERE y=b;
]],
{
-- <whereB-4.2>
- 1, 2, true
+ 1, "Type mismatch: can not convert number to text"
-- </whereB-4.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-4.3",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-4.3>
- 1, 2, true
+ 1, "Type mismatch: can not convert number to text"
-- </whereB-4.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-4.4",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -356,11 +365,11 @@ test:do_execsql_test(
]],
{
-- <whereB-4.4>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-4.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-4.100",
[[
DROP INDEX t2b ON t2;
@@ -368,22 +377,22 @@ test:do_execsql_test(
]],
{
-- <whereB-4.100>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-4.100>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-4.101",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-4.101>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-4.101>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-4.102",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -392,7 +401,7 @@ test:do_execsql_test(
]],
{
-- <whereB-4.102>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-4.102>
})
@@ -404,8 +413,7 @@ test:do_execsql_test(
-- Because t2.b has a numeric affinity, type conversion should occur
-- and the two fields should be equal.
--
-test:do_execsql_test(
- "whereB-5.1",
+test:execsql(
[[
DROP TABLE t1;
DROP TABLE t2;
@@ -416,36 +424,43 @@ test:do_execsql_test(
CREATE TABLE t2(a INT primary key, b INT); -- affinity of t2.b is INTEGER
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(2,99);
+ ]]
+)
+test:do_catchsql_test(
+ "whereB-5.1",
+ [[
SELECT x, a, y=b FROM t1, t2;
]],
{
-- <whereB-5.1>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-5.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-5.2",
[[
SELECT x, a, y=b FROM t1, t2 WHERE y=b;
]],
{
-- <whereB-5.2>
+ 1, "Type mismatch: can not convert integer to text"
-- </whereB-5.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-5.3",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-5.3>
+ 1, "Type mismatch: can not convert integer to text"
-- </whereB-5.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-5.4",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -454,11 +469,11 @@ test:do_execsql_test(
]],
{
-- <whereB-5.4>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-5.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-5.100",
[[
DROP INDEX t2b ON t2;
@@ -466,22 +481,22 @@ test:do_execsql_test(
]],
{
-- <whereB-5.100>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-5.100>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-5.101",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-5.101>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-5.101>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-5.102",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -490,7 +505,7 @@ test:do_execsql_test(
]],
{
-- <whereB-5.102>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-5.102>
})
@@ -502,8 +517,8 @@ test:do_execsql_test(
-- Because t2.b has a numeric affinity, type conversion should occur
-- and the two fields should be equal.
--
-test:do_execsql_test(
- "whereB-6.1",
+
+test:execsql(
[[
DROP TABLE t1;
DROP TABLE t2;
@@ -514,38 +529,43 @@ test:do_execsql_test(
CREATE TABLE t2(a INT primary key, b NUMBER); -- affinity of t2.b is REAL
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(2,99.0);
+ ]]
+)
+test:do_catchsql_test(
+ "whereB-6.1",
+ [[
SELECT x, a, y=b FROM t1, t2;
]],
{
-- <whereB-6.1>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-6.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-6.2",
[[
SELECT x, a, y=b FROM t1, t2 WHERE y=b;
]],
{
-- <whereB-6.2>
- 1, 2, true
+ 1, "Type mismatch: can not convert number to text"
-- </whereB-6.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-6.3",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-6.3>
- 1, 2, true
+ 1, "Type mismatch: can not convert number to text"
-- </whereB-6.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-6.4",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -554,11 +574,11 @@ test:do_execsql_test(
]],
{
-- <whereB-6.4>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-6.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-6.100",
[[
DROP INDEX t2b ON t2;
@@ -566,22 +586,22 @@ test:do_execsql_test(
]],
{
-- <whereB-6.100>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-6.100>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-6.101",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-6.101>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-6.101>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-6.102",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -590,7 +610,7 @@ test:do_execsql_test(
]],
{
-- <whereB-6.102>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-6.102>
})
@@ -602,8 +622,7 @@ test:do_execsql_test(
-- Because t1.y has a numeric affinity, type conversion should occur
-- and the two fields should be equal.
--
-test:do_execsql_test(
- "whereB-7.1",
+test:execsql(
[[
DROP TABLE t1;
DROP TABLE t2;
@@ -614,38 +633,43 @@ test:do_execsql_test(
CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(2,'99');
+ ]]
+)
+test:do_catchsql_test(
+ "whereB-7.1",
+ [[
SELECT x, a, y=b FROM t1, t2;
]],
{
-- <whereB-7.1>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-7.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-7.2",
[[
SELECT x, a, y=b FROM t1, t2 WHERE y=b;
]],
{
-- <whereB-7.2>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-7.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-7.3",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-7.3>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-7.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-7.4",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -654,11 +678,11 @@ test:do_execsql_test(
]],
{
-- <whereB-7.4>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-7.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-7.100",
[[
DROP INDEX t2b ON t2;
@@ -666,22 +690,22 @@ test:do_execsql_test(
]],
{
-- <whereB-7.100>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-7.100>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-7.101",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-7.101>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-7.101>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-7.102",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -690,7 +714,7 @@ test:do_execsql_test(
]],
{
-- <whereB-7.102>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-7.102>
})
@@ -702,8 +726,7 @@ test:do_execsql_test(
-- Because t1.y has a numeric affinity, type conversion should occur
-- and the two fields should be equal.
--
-test:do_execsql_test(
- "whereB-8.1",
+test:execsql(
[[
DROP TABLE t1;
DROP TABLE t2;
@@ -714,38 +737,43 @@ test:do_execsql_test(
CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(2,'99');
+ ]]
+)
+test:do_catchsql_test(
+ "whereB-8.1",
+ [[
SELECT x, a, y=b FROM t1, t2;
]],
{
-- <whereB-8.1>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-8.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-8.2",
[[
SELECT x, a, y=b FROM t1, t2 WHERE y=b;
]],
{
-- <whereB-8.2>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-8.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-8.3",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-8.3>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-8.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-8.4",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -754,11 +782,11 @@ test:do_execsql_test(
]],
{
-- <whereB-8.4>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-8.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-8.100",
[[
DROP INDEX t2b ON t2;
@@ -766,22 +794,22 @@ test:do_execsql_test(
]],
{
-- <whereB-8.100>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-8.100>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-8.101",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-8.101>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-8.101>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-8.102",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -790,7 +818,7 @@ test:do_execsql_test(
]],
{
-- <whereB-8.102>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-8.102>
})
@@ -802,8 +830,7 @@ test:do_execsql_test(
-- Because t1.y has a numeric affinity, type conversion should occur
-- and the two fields should be equal.
--
-test:do_execsql_test(
- "whereB-9.1",
+test:execsql(
[[
DROP TABLE t1;
DROP TABLE t2;
@@ -814,38 +841,43 @@ test:do_execsql_test(
CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(2,'99');
+ ]]
+)
+test:do_catchsql_test(
+ "whereB-9.1",
+ [[
SELECT x, a, y=b FROM t1, t2;
]],
{
-- <whereB-9.1>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-9.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-9.2",
[[
SELECT x, a, y=b FROM t1, t2 WHERE y=b;
]],
{
-- <whereB-9.2>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-9.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-9.3",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-9.3>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-9.3>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-9.4",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -854,11 +886,11 @@ test:do_execsql_test(
]],
{
-- <whereB-9.4>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-9.4>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-9.100",
[[
DROP INDEX t2b ON t2;
@@ -866,22 +898,22 @@ test:do_execsql_test(
]],
{
-- <whereB-9.100>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-9.100>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-9.101",
[[
SELECT x, a, y=b FROM t1, t2 WHERE b=y;
]],
{
-- <whereB-9.101>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-9.101>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"whereB-9.102",
-- In this case the unary "+" operator shouldn't
-- affect result set of query.
@@ -890,7 +922,7 @@ test:do_execsql_test(
]],
{
-- <whereB-9.102>
- 1, 2, true
+ 1, "Type mismatch: can not convert 99 to numeric"
-- </whereB-9.102>
})
diff --git a/test/sql/types.result b/test/sql/types.result
index 6b0f7a651..9099bcea4 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);")
--
2.25.1
next prev parent reply other threads:[~2020-05-28 14:17 UTC|newest]
Thread overview: 21+ messages / expand[flat|nested] mbox.gz Atom feed top
2020-05-28 14:17 [Tarantool-patches] [PATCH 0/6] Remove implicit cast Mergen Imeev
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 1/6] sql: remove implicit cast for assignment Mergen Imeev
2020-06-01 17:03 ` Vladislav Shpilevoy
2020-06-09 11:41 ` Mergen Imeev
2020-06-09 22:28 ` Vladislav Shpilevoy
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 2/6] sql: remove mem_apply_type() from OP_MakeRecord Mergen Imeev
2020-06-01 17:03 ` Vladislav Shpilevoy
2020-06-09 11:43 ` Mergen Imeev
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 3/6] sql: replace ApplyType by CheckType for IN operator Mergen Imeev
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 4/6] sql: remove mem_apply_type() from OP_MustBeInt Mergen Imeev
2020-06-01 17:04 ` Vladislav Shpilevoy
2020-06-09 11:47 ` Mergen Imeev
2020-06-09 22:28 ` Vladislav Shpilevoy
2020-05-28 14:17 ` Mergen Imeev [this message]
2020-06-01 17:04 ` [Tarantool-patches] [PATCH 5/6] sql: remove implicit cast from string for comparison Vladislav Shpilevoy
2020-06-09 11:51 ` Mergen Imeev
2020-06-09 22:29 ` Vladislav Shpilevoy
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 6/6] sql: remove OP_ApplyType Mergen Imeev
2020-06-09 22:29 ` Vladislav Shpilevoy
2020-06-01 17:03 ` [Tarantool-patches] [PATCH 0/6] Remove implicit cast Vladislav Shpilevoy
2020-06-09 11:25 ` Mergen Imeev
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=49b7078dd5f787e2dcde37554522afbe63f1d994.1590671266.git.imeevma@gmail.com \
--to=imeevma@tarantool.org \
--cc=tarantool-patches@dev.tarantool.org \
--cc=tsafin@tarantool.org \
--cc=v.shpilevoy@tarantool.org \
--subject='Re: [Tarantool-patches] [PATCH 5/6] sql: remove implicit cast from string for comparison' \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox