[Tarantool-patches] [PATCH v5 02/17] sql: change implicit cast for assignment
Mergen Imeev
imeevma at tarantool.org
Wed Jul 15 16:41:31 MSK 2020
I removed unnecessary flag from convertion functions and rebased.
Diff and new patch below.
On Tue, Jul 14, 2020 at 06:48:13PM +0300, imeevma at tarantool.org wrote:
> This patch changes implicit cast for assignment.
>
> Closes #3809
> Needed for #4159
> Part of #4230
>
> @TarantoolBot document
> Title: change implicit cast for comparison
>
> After this patch, these rules will apply during the implicit cast:
>
> | | STRING | BINARY | BOOLEAN | INTEGER | UNSIGNED | DOUBLE |
> | :--- | :---: | :---: | :---: | :---: | :---: | :---: |
> | STRING | A | N | N | N | N | N |
> | BINARY | N | A | N | N | N | N |
> | BOOLEAN | N | N | A | N | N | N |
> | INTEGER | N | N | N | A | S | Aa |
> | UNSIGNED | N | N | N | A | A | Aa |
> | DOUBLE | N | N | N | Sa | Sa | A |
>
> In this table, the types of the assigned value are on the left,
> and the types that should be after the assignment are at the top.
>
> 'A' - the assignment will be completed.
> 'N' - the assignment won't be completed.
> 'S' - the appointment may be unsuccessful.
> 'a' - after assignment, the resulting value may be approximated.
>
> Rules for numeric values are these:
> 1) Loss of significant digits (overflow) is an error.
> 2) Loss of insignificant digits is not an error.
>
> Example:
> ```
> tarantool> box.execute([[CREATE TABLE t1(i INT PRIMARY KEY);]])
> tarantool> box.execute([[INSERT INTO t1 VALUES ('1');]])
> ---
> - null
> - 'Type mismatch: can not convert 1 to integer'
> ...
>
> tarantool> box.execute('INSERT INTO t1 VALUES (1.2345);')
> tarantool> box.execute('SELECT * FROM t1;')
> ---
> - metadata:
> - name: I
> type: integer
> rows:
> - [1]
> ...
>
> tarantool> box.execute([[CREATE TABLE t2(t text PRIMARY KEY);]])
> tarantool> box.execute([[INSERT INTO t2 VALUES (1);]])
> ---
> - null
> - 'Type mismatch: can not convert 1 to string'
> ...
> ```
> ---
> src/box/sql/vdbe.c | 168 ++++-
> src/box/sql/vdbeInt.h | 4 +
> test/sql-tap/autoinc.test.lua | 2 +-
> test/sql-tap/default.test.lua | 6 +-
> test/sql-tap/e_select1.test.lua | 27 +-
> test/sql-tap/in3.test.lua | 14 +-
> test/sql-tap/in4.test.lua | 96 +--
> test/sql-tap/index1.test.lua | 24 +-
> test/sql-tap/insert3.test.lua | 10 +-
> test/sql-tap/intpkey.test.lua | 133 +---
> test/sql-tap/limit.test.lua | 2 +-
> test/sql-tap/minmax2.test.lua | 6 +-
> test/sql-tap/misc1.test.lua | 24 +-
> test/sql-tap/numcast.test.lua | 6 +-
> test/sql-tap/select1.test.lua | 8 +-
> test/sql-tap/select4.test.lua | 12 +-
> test/sql-tap/select7.test.lua | 2 +-
> test/sql-tap/sort.test.lua | 8 +-
> test/sql-tap/subquery.test.lua | 69 +-
> test/sql-tap/tkt-3998683a16.test.lua | 26 +-
> test/sql-tap/tkt-54844eea3f.test.lua | 8 +-
> test/sql-tap/tkt-7bbfb7d442.test.lua | 4 +-
> test/sql-tap/tkt-9a8b09f8e6.test.lua | 62 +-
> test/sql-tap/tkt-f973c7ac31.test.lua | 18 +-
> test/sql-tap/tkt-fc7bd6358f.test.lua | 111 ----
> test/sql-tap/tkt1444.test.lua | 4 +-
> test/sql-tap/tkt3493.test.lua | 6 +-
> test/sql-tap/tkt3841.test.lua | 12 +-
> test/sql-tap/transitive1.test.lua | 4 +-
> test/sql-tap/triggerA.test.lua | 4 +-
> test/sql-tap/unique.test.lua | 8 +-
> test/sql-tap/view.test.lua | 2 +-
> test/sql-tap/where5.test.lua | 10 +-
> test/sql-tap/whereB.test.lua | 908 ---------------------------
> test/sql-tap/whereC.test.lua | 8 +-
> test/sql/types.result | 620 ++++++++++++++++++
> test/sql/types.test.lua | 129 ++++
> 37 files changed, 1050 insertions(+), 1515 deletions(-)
> delete mode 100755 test/sql-tap/tkt-fc7bd6358f.test.lua
> delete mode 100755 test/sql-tap/whereB.test.lua
>
> diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
> index 950f72ddd..41a4750da 100644
> --- a/src/box/sql/vdbe.c
> +++ b/src/box/sql/vdbe.c
> @@ -417,6 +417,144 @@ sql_value_apply_type(
> mem_apply_type((Mem *) pVal, type);
> }
>
> +/**
> + * Check that MEM_type of the mem is compatible with given type.
> + *
> + * @param mem The MEM that contains the value to check.
> + * @param type The type to check.
> + * @retval TRUE if the MEM_type of the value and the given type
> + * are compatible, FALSE otherwise.
> + */
> +static bool
> +mem_is_type_compatible(struct Mem *mem, enum field_type type)
> +{
> + enum mp_type mp_type = mem_mp_type(mem);
> + assert(mp_type < MP_EXT);
> + return field_mp_plain_type_is_compatible(type, mp_type, true);
> +}
> +
> +/**
> + * Convert the numeric value contained in MEM to double. If the
> + * is_precise flag is set, the conversion will succeed only if it
> + * is lossless.
> + *
> + * @param mem The MEM that contains the numeric value.
> + * @param is_precise Flag.
> + * @retval 0 if the conversion was successful, -1 otherwise.
> + */
> +static int
> +mem_convert_to_double(struct Mem *mem, bool is_precise)
> +{
> + if ((mem->flags & MEM_Real) != 0)
> + return 0;
> + if ((mem->flags & (MEM_Int | MEM_UInt)) == 0)
> + return -1;
> + if ((mem->flags & MEM_Int) != 0) {
> + int64_t i = mem->u.i;
> + double d = (double)i;
> + if (!is_precise || i == (int64_t)d)
> + mem_set_double(mem, d);
> + else
> + return -1;
> + } else {
> + uint64_t u = mem->u.u;
> + double d = (double)u;
> + if (!is_precise || u == (uint64_t)d)
> + mem_set_double(mem, d);
> + else
> + return -1;
> + }
> + return 0;
> +}
> +
> +/**
> + * Convert the numeric value contained in MEM to unsigned. If the
> + * is_precise flag is set, the conversion will succeed only if it
> + * is lossless.
> + *
> + * @param mem The MEM that contains the numeric value.
> + * @param is_precise Flag.
> + * @retval 0 if the conversion was successful, -1 otherwise.
> + */
> +static int
> +mem_convert_to_unsigned(struct Mem *mem, bool is_precise)
> +{
> + if ((mem->flags & MEM_UInt) != 0)
> + return 0;
> + if ((mem->flags & MEM_Int) != 0)
> + return -1;
> + if ((mem->flags & MEM_Real) == 0)
> + return -1;
> + double d = mem->u.r;
> + if (d < 0.0 || d >= (double)UINT64_MAX)
> + return -1;
> + uint64_t u = (uint64_t)d;
> + if (!is_precise || d == (double)u)
> + mem_set_u64(mem, (uint64_t) d);
> + else
> + return -1;
> + return 0;
> +}
> +
> +/**
> + * Convert the numeric value contained in MEM to integer. If the
> + * is_precise flag is set, the conversion will succeed only if it
> + * is lossless.
> + *
> + * @param mem The MEM that contains the numeric value.
> + * @param is_precise Flag.
> + * @retval 0 if the conversion was successful, -1 otherwise.
> + */
> +static int
> +mem_convert_to_integer(struct Mem *mem, bool is_precise)
> +{
> + if ((mem->flags & (MEM_UInt | MEM_Int)) != 0)
> + return 0;
> + if ((mem->flags & MEM_Real) == 0)
> + return -1;
> + double d = mem->u.r;
> + if (d >= (double)UINT64_MAX || d < (double)INT64_MIN)
> + return -1;
> + if (d < (double)INT64_MAX) {
> + int64_t i = (int64_t)d;
> + if (!is_precise || d == (double)i)
> + mem_set_int(mem, (int64_t) d, d < 0);
> + else
> + return -1;
> + } else {
> + uint64_t u = (uint64_t)d;
> + if (!is_precise || d == (double)u)
> + mem_set_int(mem, (uint64_t) d, false);
> + else
> + return -1;
> + }
> + return 0;
> +}
> +
> +/**
> + * Convert the numeric value contained in MEM to another numeric
> + * type. If the is_precise flag is set, the conversion will
> + * succeed only if it is lossless.
> + *
> + * @param mem The MEM that contains the numeric value.
> + * @param type The type to convert to.
> + * @param is_precise Flag.
> + * @retval 0 if the conversion was successful, -1 otherwise.
> + */
> +static int
> +mem_convert_to_numeric(struct Mem *mem, enum field_type type, bool is_precise)
> +{
> + assert(mp_type_is_numeric(mem_mp_type(mem)) &&
> + sql_type_is_numeric(type));
> + assert(type != FIELD_TYPE_NUMBER);
> + if (type == FIELD_TYPE_DOUBLE)
> + return mem_convert_to_double(mem, is_precise);
> + if (type == FIELD_TYPE_UNSIGNED)
> + return mem_convert_to_unsigned(mem, is_precise);
> + assert(type == FIELD_TYPE_INTEGER);
> + return mem_convert_to_integer(mem, is_precise);
> +}
> +
> /*
> * 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
> @@ -2747,11 +2885,11 @@ case OP_Fetch: {
> /* Opcode: ApplyType P1 P2 * P4 *
> * Synopsis: type(r[P1 at P2])
> *
> - * Apply types to a range of P2 registers starting with P1.
> - *
> - * P4 is a string that is P2 characters long. The nth character of the
> - * string indicates the column type that should be used for the nth
> - * memory cell in the range.
> + * Check that types of P2 registers starting from register P1 are
> + * compatible with given field types in P4. If the MEM_type of the
> + * value and the given type are incompatible according to
> + * field_mp_plain_type_is_compatible(), but both are numeric,
> + * this opcode attempts to convert the value to the type.
> */
> case OP_ApplyType: {
> enum field_type *types = pOp->p4.types;
> @@ -2762,13 +2900,23 @@ case OP_ApplyType: {
> while((type = *(types++)) != field_type_MAX) {
> assert(pIn1 <= &p->aMem[(p->nMem+1 - p->nCursor)]);
> assert(memIsValid(pIn1));
> - if (mem_apply_type(pIn1, type) != 0) {
> - diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
> - sql_value_to_diag_str(pIn1),
> - field_type_strs[type]);
> - goto abort_due_to_error;
> + if (!mem_is_type_compatible(pIn1, type)) {
> + /* Implicit cast is allowed only to numeric type. */
> + if (!sql_type_is_numeric(type))
> + goto type_mismatch;
> + /* Implicit cast is allowed only from numeric type. */
> + if (!mp_type_is_numeric(mem_mp_type(pIn1)))
> + goto type_mismatch;
> + /* Try to convert numeric-to-numeric. */
> + if (mem_convert_to_numeric(pIn1, type, false) != 0)
> + goto type_mismatch;
> }
> pIn1++;
> + continue;
> +type_mismatch:
> + diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
> + sql_value_to_diag_str(pIn1), field_type_strs[type]);
> + goto abort_due_to_error;
> }
> break;
> }
> diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
> index 44c27bdb7..ad46ab129 100644
> --- a/src/box/sql/vdbeInt.h
> +++ b/src/box/sql/vdbeInt.h
> @@ -566,6 +566,10 @@ mem_mp_type(struct Mem *mem);
> */
> #define mp_type_is_bloblike(X) ((X) == MP_BIN || (X) == MP_ARRAY || (X) == MP_MAP)
>
> +/** Return TRUE if MP_type of X is numeric, FALSE otherwise. */
> +#define mp_type_is_numeric(X) ((X) == MP_INT || (X) == MP_UINT ||\
> + (X) == MP_DOUBLE)
> +
> /**
> * Memory cell mem contains the context of an aggregate function.
> * This routine calls the finalize method for that function. The
> diff --git a/test/sql-tap/autoinc.test.lua b/test/sql-tap/autoinc.test.lua
> index 37e65e541..07442b60a 100755
> --- a/test/sql-tap/autoinc.test.lua
> +++ b/test/sql-tap/autoinc.test.lua
> @@ -694,7 +694,7 @@ test:do_test(
> INSERT INTO t3928(b) VALUES('after-int-' || CAST(new.b AS TEXT));
> END;
> DELETE FROM t3928 WHERE a!=1;
> - UPDATE t3928 SET b=456 WHERE a=1;
> + UPDATE t3928 SET b='456' WHERE a=1;
> SELECT * FROM t3928 ORDER BY a;
> ]])
> end, {
> diff --git a/test/sql-tap/default.test.lua b/test/sql-tap/default.test.lua
> index d3e35c71c..f1def2b10 100755
> --- a/test/sql-tap/default.test.lua
> +++ b/test/sql-tap/default.test.lua
> @@ -109,16 +109,12 @@ test:do_execsql_test(
> f VARCHAR(15), --COLLATE RTRIM,
> g INTEGER DEFAULT( 3600*12 )
> );
> - INSERT INTO t3 VALUES(null, 5, 'row1', 5.25, 8.67, 321, 432);
> + INSERT INTO t3 VALUES(null, 5, 'row1', 5.25, 8.67, '321', 432);
> SELECT a, typeof(a), b, typeof(b), c, typeof(c),
> d, typeof(d), e, typeof(e), f, typeof(f),
> g, typeof(g) FROM t3;
> ]], {
> -- <default-3.1>
> - -- TODO: In original test "321" is not a string, its a value.
> - -- In current situation I don't know what to do, need Kirill's
> - -- advice.
> - -- Bulat
> 1, "integer", 5, "integer", "row1", "string", 5.25, "number", 8.67, "number", "321", "string", 432, "integer"
> -- </default-3.1>
> })
> diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua
> index 1d3b964b9..578620fca 100755
> --- a/test/sql-tap/e_select1.test.lua
> +++ b/test/sql-tap/e_select1.test.lua
> @@ -1,6 +1,6 @@
> #!/usr/bin/env tarantool
> test = require("sqltester")
> -test:plan(510)
> +test:plan(509)
>
> --!./tcltestrunner.lua
> -- 2010 July 16
> @@ -753,11 +753,11 @@ test:do_execsql_test(
> CREATE TABLE z3(id INT primary key, a NUMBER, b NUMBER);
>
> INSERT INTO z1 VALUES(1, 51.65, -59.58, 'belfries');
> - INSERT INTO z1 VALUES(2, -5, NULL, 75);
> + INSERT INTO z1 VALUES(2, -5, NULL, '75');
> INSERT INTO z1 VALUES(3, -2.2, -23.18, 'suiters');
> INSERT INTO z1 VALUES(4, NULL, 67, 'quartets');
> INSERT INTO z1 VALUES(5, -1.04, -32.3, 'aspen');
> - INSERT INTO z1 VALUES(6, 63, '0', -26);
> + INSERT INTO z1 VALUES(6, 63, 0, '-26');
>
> INSERT INTO z2 VALUES(1, NULL, 21);
> INSERT INTO z2 VALUES(2, 36.0, 6.0);
> @@ -1457,13 +1457,13 @@ test:do_execsql_test(
> CREATE TABLE q2(id INT primary key, d TEXT, e NUMBER);
> CREATE TABLE q3(id INT primary key, f TEXT, g INT);
>
> - INSERT INTO q1 VALUES(1, 16, -87.66, NULL);
> + INSERT INTO q1 VALUES(1, '16', -87.66, NULL);
> INSERT INTO q1 VALUES(2, 'legible', 94, -42.47);
> INSERT INTO q1 VALUES(3, 'beauty', 36, NULL);
>
> INSERT INTO q2 VALUES(1, 'legible', 1);
> INSERT INTO q2 VALUES(2, 'beauty', 2);
> - INSERT INTO q2 VALUES(3, -65, 4);
> + INSERT INTO q2 VALUES(3, '-65', 4);
> INSERT INTO q2 VALUES(4, 'emanating', -16.56);
>
> INSERT INTO q3 VALUES(1, 'beauty', 2);
> @@ -1603,7 +1603,7 @@ test:do_execsql_test(
> CREATE TABLE w2(a INT PRIMARY KEY, b TEXT);
>
> INSERT INTO w1 VALUES('1', 4.1);
> - INSERT INTO w2 VALUES(1, 4.1);
> + INSERT INTO w2 VALUES(1, '4.1');
> ]], {
> -- <e_select-7.10.0>
>
> @@ -2150,7 +2150,6 @@ test:do_select_tests(
> {"2", "SELECT b FROM f1 ORDER BY a LIMIT 2+3 ", {"a", "b", "c", "d", "e"}},
> {"3", "SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b = 'e') ", {"a", "b", "c", "d", "e"}},
> {"4", "SELECT b FROM f1 ORDER BY a LIMIT 5.0 ", {"a", "b", "c", "d", "e"}},
> - {"5", "SELECT b FROM f1 ORDER BY a LIMIT '5' ", {"a", "b", "c", "d", "e"}},
> })
>
> -- EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value
> @@ -2195,7 +2194,7 @@ test:do_select_tests(
> {"1", "SELECT b FROM f1 ORDER BY a LIMIT 0 ", {}},
> {"2", "SELECT b FROM f1 ORDER BY a DESC LIMIT 4 ", {"z", "y", "x", "w"}},
> {"3", "SELECT b FROM f1 ORDER BY a DESC LIMIT 8 ", {"z", "y", "x", "w", "v", "u", "t", "s"}},
> - {"4", "SELECT b FROM f1 ORDER BY a DESC LIMIT '12' ", {"z", y, "x", "w", "v", "u", "t", "s", "r", "q", "p", "o"}},
> + {"4", "SELECT b FROM f1 ORDER BY a DESC LIMIT 12 ", {"z", y, "x", "w", "v", "u", "t", "s", "r", "q", "p", "o"}},
> })
>
> -- EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return
> @@ -2240,10 +2239,10 @@ test:do_select_tests(
> {"1", "SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 5", {"f", "g", "h", "i", "j", "k", "l", "m", "n", "o"}},
> {"2", "SELECT b FROM f1 ORDER BY a LIMIT 2+3 OFFSET 10", {"k", "l", "m", "n", "o"}},
> {"3", "SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b='j') OFFSET (SELECT a FROM f1 WHERE b='b') ", {"c", "d", "e", "f", "g", "h", "i", "j", "k", "l"}},
> - {"4", "SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 3.0 ", {"d", "e", "f", "g", "h"}},
> - {"5", "SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 0 ", {"a", "b", "c", "d", "e"}},
> + {"4", "SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 3.0 ", {"d", "e", "f", "g", "h"}},
> + {"5", "SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0 ", {"a", "b", "c", "d", "e"}},
> {"6", "SELECT b FROM f1 ORDER BY a LIMIT 0 OFFSET 10 ", {}},
> - {"7", "SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET '1'||'5' ", {"p", "q", "r"}},
> + {"7", "SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET CAST('1'||'5' AS INTEGER) ", {"p", "q", "r"}},
> })
>
> -- EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than
> @@ -2279,10 +2278,10 @@ test:do_select_tests(
> {"1", "SELECT b FROM f1 ORDER BY a LIMIT 5, 10 ", {"f", "g", "h", "i", "j", "k", "l", "m", "n", "o"}},
> {"2", "SELECT b FROM f1 ORDER BY a LIMIT 10, 2+3 ", {"k", "l", "m", "n", "o"}},
> {"3", "SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b='b'), (SELECT a FROM f1 WHERE b='j')", {"c", "d", "e", "f", "g", "h", "i", "j", "k", "l"}},
> - {"4", "SELECT b FROM f1 ORDER BY a LIMIT 3.0, '5' ", {"d", "e", "f", "g", "h"}},
> - {"5", "SELECT b FROM f1 ORDER BY a LIMIT 0, '5' ", {"a", "b", "c", "d", "e"}},
> + {"4", "SELECT b FROM f1 ORDER BY a LIMIT 3.0, 5 ", {"d", "e", "f", "g", "h"}},
> + {"5", "SELECT b FROM f1 ORDER BY a LIMIT 0, 5 ", {"a", "b", "c", "d", "e"}},
> {"6", "SELECT b FROM f1 ORDER BY a LIMIT 10, 0 ", {}},
> - {"7", "SELECT b FROM f1 ORDER BY a LIMIT '1'||'5', 3 ", {"p", "q", "r"}},
> + {"7", "SELECT b FROM f1 ORDER BY a LIMIT CAST('1'||'5' AS INTEGER), 3 ", {"p", "q", "r"}},
> {"8", "SELECT b FROM f1 ORDER BY a LIMIT 20, 10 ", {"u", "v", "w", "x", "y", "z"}},
> {"9", "SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 ", {4, 3, 2, 1}},
> {"10", "SELECT b FROM f1 ORDER BY a LIMIT 0, 5 ", {"a", "b", "c", "d", "e"}},
> diff --git a/test/sql-tap/in3.test.lua b/test/sql-tap/in3.test.lua
> index e29db9d93..a6d842962 100755
> --- a/test/sql-tap/in3.test.lua
> +++ b/test/sql-tap/in3.test.lua
> @@ -1,6 +1,6 @@
> #!/usr/bin/env tarantool
> test = require("sqltester")
> -test:plan(29)
> +test:plan(28)
>
> --!./tcltestrunner.lua
> -- 2007 November 29
> @@ -322,18 +322,6 @@ test:do_test(
> -- </in3-3.2>
> })
>
> -test:do_test(
> - "in3-3.3",
> - function()
> - -- Logically, numeric affinity is applied to both sides before
> - -- the comparison, but index can't be used.
> - return exec_neph(" SELECT x IN (SELECT b FROM t1) FROM t2 ")
> - end, {
> - -- <in3-3.3>
> - 1, true
> - -- </in3-3.3>
> - })
> -
> test:do_test(
> "in3-3.4",
> function()
> diff --git a/test/sql-tap/in4.test.lua b/test/sql-tap/in4.test.lua
> index 8c6917379..5c01ccdab 100755
> --- a/test/sql-tap/in4.test.lua
> +++ b/test/sql-tap/in4.test.lua
> @@ -1,6 +1,6 @@
> #!/usr/bin/env tarantool
> test = require("sqltester")
> -test:plan(61)
> +test:plan(52)
>
> --!./tcltestrunner.lua
> -- 2008 September 1
> @@ -140,7 +140,7 @@ test:do_execsql_test(
> test:do_execsql_test(
> "in4-2.7",
> [[
> - SELECT b FROM t2 WHERE a IN ('1', '2')
> + SELECT b FROM t2 WHERE a IN (1, 2)
> ]], {
> -- <in4-2.7>
> "one", "two"
> @@ -585,98 +585,6 @@ test:do_execsql_test(
> -- </in4-4.6>
> })
>
> -test:do_execsql_test(
> - "in4-4.11",
> - [[
> - CREATE TABLE t4b(a TEXT, b NUMBER, c INT PRIMARY KEY);
> - INSERT INTO t4b VALUES('1.0',1,4);
> - SELECT c FROM t4b WHERE a=b;
> - ]], {
> - -- <in4-4.11>
> - 4
> - -- </in4-4.11>
> - })
> -
> -test:do_execsql_test(
> - "in4-4.12",
> - [[
> - SELECT c FROM t4b WHERE b=a;
> - ]], {
> - -- <in4-4.12>
> - 4
> - -- </in4-4.12>
> - })
> -
> -test:do_execsql_test(
> - "in4-4.13",
> - [[
> - SELECT c FROM t4b WHERE +a=b;
> - ]], {
> - -- <in4-4.13>
> - 4
> - -- </in4-4.13>
> - })
> -
> -test:do_execsql_test(
> - "in4-4.14",
> - [[
> - SELECT c FROM t4b WHERE a=+b;
> - ]], {
> - -- <in4-4.14>
> - 4
> - -- </in4-4.14>
> - })
> -
> -test:do_execsql_test(
> - "in4-4.15",
> - [[
> - SELECT c FROM t4b WHERE +b=a;
> - ]], {
> - -- <in4-4.15>
> - 4
> - -- </in4-4.15>
> - })
> -
> -test:do_execsql_test(
> - "in4-4.16",
> - [[
> - SELECT c FROM t4b WHERE b=+a;
> - ]], {
> - -- <in4-4.16>
> - 4
> - -- </in4-4.16>
> - })
> -
> -test:do_execsql_test(
> - "in4-4.17",
> - [[
> - SELECT c FROM t4b WHERE a IN (b);
> - ]], {
> - -- <in4-4.17>
> - 4
> - -- </in4-4.17>
> - })
> -
> -test:do_execsql_test(
> - "in4-4.18",
> - [[
> - SELECT c FROM t4b WHERE b IN (a);
> - ]], {
> - -- <in4-4.18>
> - 4
> - -- </in4-4.18>
> - })
> -
> -test:do_execsql_test(
> - "in4-4.19",
> - [[
> - SELECT c FROM t4b WHERE +b IN (a);
> - ]], {
> - -- <in4-4.19>
> - 4
> - -- </in4-4.19>
> - })
> -
> -- MUST_WORK_TEST
> -- Tarantool: TBI: Need to support collations. Depends on #2121
> -- test:do_execsql_test(
> diff --git a/test/sql-tap/index1.test.lua b/test/sql-tap/index1.test.lua
> index e173e685c..ce66b7c1e 100755
> --- a/test/sql-tap/index1.test.lua
> +++ b/test/sql-tap/index1.test.lua
> @@ -593,25 +593,17 @@ test:do_test(
> -- </index-11.1>
> })
> end
> --- integrity_check index-11.2
> --- Numeric strings should compare as if they were numbers. So even if the
> --- strings are not character-by-character the same, if they represent the
> --- same number they should compare equal to one another. Verify that this
> --- is true in indices.
> ---
> --- Updated for sql v3: sql will now store these values as numbers
> --- (because the affinity of column a is NUMERIC) so the quirky
> --- representations are not retained. i.e. '+1.0' becomes '1'.
> +
> test:do_execsql_test(
> "index-12.1",
> [[
> CREATE TABLE t4(id INT primary key, a NUMBER,b INT );
> - INSERT INTO t4 VALUES(1, '0.0',1);
> - INSERT INTO t4 VALUES(2, '0.00',2);
> - INSERT INTO t4 VALUES(4, '-1.0',4);
> - INSERT INTO t4 VALUES(5, '+1.0',5);
> - INSERT INTO t4 VALUES(6, '0',6);
> - INSERT INTO t4 VALUES(7, '00000',7);
> + INSERT INTO t4 VALUES(1, 0.0, 1);
> + INSERT INTO t4 VALUES(2, 0.00, 2);
> + INSERT INTO t4 VALUES(4, -1.0, 4);
> + INSERT INTO t4 VALUES(5, +1.0, 5);
> + INSERT INTO t4 VALUES(6, 0, 6);
> + INSERT INTO t4 VALUES(7, 00000, 7);
> SELECT a FROM t4 ORDER BY b;
> ]], {
> -- <index-12.1>
> @@ -692,7 +684,7 @@ test:do_execsql_test(
> c TEXT,
> UNIQUE(a,c)
> );
> - INSERT INTO t5 VALUES(1,2,3);
> + INSERT INTO t5 VALUES(1,2,'3');
> SELECT * FROM t5;
> ]], {
> -- <index-13.1>
> diff --git a/test/sql-tap/insert3.test.lua b/test/sql-tap/insert3.test.lua
> index 43bb06630..b92bc508e 100755
> --- a/test/sql-tap/insert3.test.lua
> +++ b/test/sql-tap/insert3.test.lua
> @@ -60,7 +60,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;
> - INSERT OR IGNORE INTO log2(x, y) VALUES(new.b,1);
> + INSERT OR IGNORE INTO log2(x, y) VALUES(CAST(new.b AS STRING),1);
> END;
> INSERT INTO t1(a, b) VALUES('hi', 453);
> SELECT x,y FROM log ORDER BY x;
> @@ -129,8 +129,8 @@ test:do_execsql_test(
> INSERT INTO t2dup(a,b,c) VALUES(new.a,new.b,new.c);
> END;
> INSERT INTO t2(a) VALUES(123);
> - INSERT INTO t2(b) VALUES(234);
> - INSERT INTO t2(c) VALUES(345);
> + INSERT INTO t2(b) VALUES('234');
> + INSERT INTO t2(c) VALUES('345');
> SELECT * FROM t2dup;
> ]], {
> -- <insert3-2.1>
> @@ -143,8 +143,8 @@ test:do_execsql_test(
> [[
> DELETE FROM t2dup;
> INSERT INTO t2(a) SELECT 1 FROM t1 LIMIT 1;
> - INSERT INTO t2(b) SELECT 987 FROM t1 LIMIT 1;
> - INSERT INTO t2(c) SELECT 876 FROM t1 LIMIT 1;
> + INSERT INTO t2(b) SELECT '987' FROM t1 LIMIT 1;
> + INSERT INTO t2(c) SELECT '876' FROM t1 LIMIT 1;
> SELECT * FROM t2dup;
> ]], {
> -- <insert3-2.2>
> diff --git a/test/sql-tap/intpkey.test.lua b/test/sql-tap/intpkey.test.lua
> index b6b186632..684a24114 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(31)
>
> --!./tcltestrunner.lua
> -- 2001 September 15
> @@ -770,142 +770,13 @@ test:do_execsql_test(
> -- </intpkey-11.1>
> })
>
> --- integrity_check intpkey-12.1
> --- Try to use a string that looks like a floating point number as
> --- an integer primary key. This should actually work when the floating
> --- point value can be rounded to an integer without loss of data.
> ---
> -test:do_execsql_test(
> - "intpkey-13.1",
> - [[
> - SELECT * FROM t1 WHERE a=1;
> - ]], {
> - -- <intpkey-13.1>
> -
> - -- </intpkey-13.1>
> - })
> -
> -test:do_execsql_test(
> - "intpkey-13.2",
> - [[
> - INSERT INTO t1 VALUES('1',2,3);
> - SELECT * FROM t1 WHERE a=1;
> - ]], {
> - -- <intpkey-13.2>
> - 1, "2", "3"
> - -- </intpkey-13.2>
> - })
> -
> --- MUST_WORK_TEST
> -if (0 > 0) then
> - -- Tarantool: issue submitted #2315
> - test:do_catchsql_test(
> - "intpkey-13.3",
> - [[
> - INSERT INTO t1 VALUES('1.5',3,4);
> - ]], {
> - -- <intpkey-13.3>
> - 1, "datatype mismatch"
> - -- </intpkey-13.3>
> - })
> -
> - test:do_catchsql_test(
> - "intpkey-13.4",
> - [[
> - INSERT INTO t1 VALUES(x'123456',3,4);
> - ]], {
> - -- <intpkey-13.4>
> - 1, "datatype mismatch"
> - -- </intpkey-13.4>
> - })
> -
> -
> -
> -end
> -test:do_catchsql_test(
> - "intpkey-13.5",
> - [[
> - INSERT INTO t1 VALUES('+1234567890',3,4);
> - ]], {
> - -- <intpkey-13.5>
> - 0
> - -- </intpkey-13.5>
> - })
> -
> --- Compare an INTEGER PRIMARY KEY against a TEXT expression. The INTEGER
> --- affinity should be applied to the text value before the comparison
> --- takes place.
> ---
> -test:do_execsql_test(
> - "intpkey-14.1",
> - [[
> - CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT);
> - INSERT INTO t3 VALUES(1, 1, 'one');
> - INSERT INTO t3 VALUES(2, 2, '2');
> - INSERT INTO t3 VALUES(3, 3, 3);
> - ]], {
> - -- <intpkey-14.1>
> -
> - -- </intpkey-14.1>
> - })
> -
> -test:do_execsql_test(
> - "intpkey-14.2",
> - [[
> - SELECT * FROM t3 WHERE a>2;
> - ]], {
> - -- <intpkey-14.2>
> - 3, 3, "3"
> - -- </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(
> - "intpkey-14.4",
> - [[
> - SELECT * FROM t3 WHERE a<'2';
> - ]], {
> - -- <intpkey-14.4>
> - 1, 1, "one"
> - -- </intpkey-14.4>
> - })
> -
> -test:do_execsql_test(
> - "intpkey-14.5",
> - [[
> - SELECT * FROM t3 WHERE a<c;
> - ]], {
> - -- <intpkey-14.5>
> - 1, 1, "one"
> - -- </intpkey-14.5>
> - })
> -
> -test:do_execsql_test(
> - "intpkey-14.6",
> - [[
> - SELECT * FROM t3 WHERE a=c;
> - ]], {
> - -- <intpkey-14.6>
> - 2, 2, "2", 3, 3, "3"
> - -- </intpkey-14.6>
> - })
> -
> -- Check for proper handling of primary keys greater than 2^31.
> -- Ticket #1188
> --
> test:do_execsql_test(
> "intpkey-15.1",
> [[
> - INSERT INTO t1 VALUES(2147483647, 'big-1', 123);
> + INSERT INTO t1 VALUES(2147483647, 'big-1', '123');
> SELECT * FROM t1 WHERE a>2147483648;
> ]], {
> -- <intpkey-15.1>
> diff --git a/test/sql-tap/limit.test.lua b/test/sql-tap/limit.test.lua
> index 870233942..a7d1451f7 100755
> --- a/test/sql-tap/limit.test.lua
> +++ b/test/sql-tap/limit.test.lua
> @@ -441,7 +441,7 @@ test:do_catchsql_test(
> test:do_execsql_test(
> "limit-6.5.2",
> [[
> - SELECT * FROM t6 LIMIT '12'
> + SELECT * FROM t6 LIMIT 12
> ]], {
> -- <limit-6.5>
> 1, 2, 3, 4
> diff --git a/test/sql-tap/minmax2.test.lua b/test/sql-tap/minmax2.test.lua
> index 0e0f0d08e..707d1c4da 100755
> --- a/test/sql-tap/minmax2.test.lua
> +++ b/test/sql-tap/minmax2.test.lua
> @@ -441,9 +441,9 @@ test:do_execsql_test(
> "minmax2-8.2",
> [[
> CREATE TABLE t5(a INTEGER PRIMARY KEY);
> - INSERT INTO t5 VALUES('1234');
> - INSERT INTO t5 VALUES('234');
> - INSERT INTO t5 VALUES('34');
> + INSERT INTO t5 VALUES(1234);
> + INSERT INTO t5 VALUES(234);
> + INSERT INTO t5 VALUES(34);
> SELECT min(a), max(a) FROM t5;
> ]], {
> -- <minmax2-8.2>
> diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua
> index 32f38cc97..c0136d04c 100755
> --- a/test/sql-tap/misc1.test.lua
> +++ b/test/sql-tap/misc1.test.lua
> @@ -34,9 +34,9 @@ test:do_test(
> end
> cmd = cmd .. ")"
> test:execsql(cmd)
> - cmd = "INSERT INTO manycol VALUES(1, 0"
> + cmd = "INSERT INTO manycol VALUES(1, '0'"
> for i = 1, 99, 1 do
> - cmd = cmd .. ","..i..""
> + cmd = cmd .. ",'"..i.."'"
> end
> cmd = cmd .. ")"
> test:execsql(cmd)
> @@ -61,9 +61,9 @@ test:do_test(
> "misc1-1.3.1",
> function()
> for j = 100, 1000, 100 do
> - local cmd = string.format("INSERT INTO manycol VALUES(%s, %s", j, j)
> + local cmd = string.format("INSERT INTO manycol VALUES(%s, '%s'", j, j)
> for i = 1, 99, 1 do
> - cmd = cmd .. ","..(i + j)..""
> + cmd = cmd .. ",'"..(i + j).."'"
> end
> cmd = cmd .. ")"
> test:execsql(cmd)
> @@ -176,7 +176,7 @@ test:do_test(
> "misc1-2.1",
> function()
> test:execsql([[
> - CREATE TABLE agger(one text primary key, two text, three text, four text);
> + CREATE TABLE agger(one int primary key, two text, three text, four text);
> START TRANSACTION;
> INSERT INTO agger VALUES(1, 'one', 'hello', 'yes');
> INSERT INTO agger VALUES(2, 'two', 'howdy', 'no');
> @@ -531,7 +531,7 @@ test:do_test(
> "misc1-10.7",
> function()
> where = string.gsub(where, "x0=0", "x0=100")
> - return test:catchsql("UPDATE manycol SET x1=x1+1 "..where.."")
> + return test:catchsql("UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where.."")
> end, {
> -- <misc1-10.7>
> 0
> @@ -553,7 +553,7 @@ test:do_execsql_test(
> -- } {0 {}}
> test:do_execsql_test(
> "misc1-10.9",
> - "UPDATE manycol SET x1=x1+1 "..where
> + "UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where
> --"UPDATE manycol SET x1=x1+1 $::where AND rowid>0"
> , {})
>
> @@ -665,7 +665,7 @@ test:do_execsql_test(
> test:do_execsql_test(
> "misc1-12.6",
> [[
> - INSERT OR IGNORE INTO t6 VALUES('y',0);
> + INSERT OR IGNORE INTO t6 VALUES('y','0');
> SELECT * FROM t6;
> ]], {
> -- <misc1-12.6>
> @@ -679,10 +679,10 @@ test:do_execsql_test(
> "misc1-12.7",
> [[
> CREATE TABLE t7(x INTEGER, y TEXT, z INT primary key);
> - INSERT INTO t7 VALUES(0,0,1);
> - INSERT INTO t7 VALUES(0.0,0,2);
> - INSERT INTO t7 VALUES(0,0.0,3);
> - INSERT INTO t7 VALUES(0.0,0.0,4);
> + INSERT INTO t7 VALUES(0,'0',1);
> + INSERT INTO t7 VALUES(0.0,'0',2);
> + INSERT INTO t7 VALUES(0,'0.0',3);
> + INSERT INTO t7 VALUES(0.0,'0.0',4);
> SELECT DISTINCT x, y FROM t7 ORDER BY z;
> ]], {
> -- <misc1-12.7>
> diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua
> index eeac5353a..3161e48fa 100755
> --- a/test/sql-tap/numcast.test.lua
> +++ b/test/sql-tap/numcast.test.lua
> @@ -135,16 +135,16 @@ test:do_catchsql_test(
> INSERT INTO t VALUES(20000000000000000000.01);
> SELECT * FROM t;
> ]], {
> - 1,"Tuple field 1 type does not match one required by operation: expected integer"
> + 1,"Type mismatch: can not convert 2.0e+19 to integer"
> })
>
> -test:do_catchsql_test(
> +test:do_execsql_test(
> "cast-2.9",
> [[
> INSERT INTO t VALUES(2.1);
> SELECT * FROM t;
> ]], {
> - 1,"Tuple field 1 type does not match one required by operation: expected integer"
> + 2, 9223372036854775808ULL, 18000000000000000000ULL
> })
>
> --
> diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua
> index fbebfab37..9a969bf3c 100755
> --- a/test/sql-tap/select1.test.lua
> +++ b/test/sql-tap/select1.test.lua
> @@ -231,7 +231,7 @@ string.format([[
> CREATE TABLE t3(id INT, a TEXT, b TEXT, PRIMARY KEY(id));
> INSERT INTO t3 VALUES(1, 'abc',NULL);
> INSERT INTO t3 VALUES(2, NULL,'xyz');
> - INSERT INTO t3 SELECT f1, * FROM test1;
> + INSERT INTO t3 SELECT f1, CAST(f1 AS STRING), CAST(f2 AS STRING) FROM test1;
> DROP TABLE IF EXISTS t4;
> CREATE TABLE t4(id INT, a INT , b TEXT , PRIMARY KEY(id));
> INSERT INTO t4 VALUES(1, NULL,'%s');
> @@ -1671,8 +1671,8 @@ test:do_execsql_test(
> [[
> DELETE FROM t3;
> DELETE FROM t4;
> - INSERT INTO t3 VALUES(0,1,2);
> - INSERT INTO t4 VALUES(0,3,4);
> + INSERT INTO t3 VALUES(0,'1','2');
> + INSERT INTO t4 VALUES(0,3,'4');
> SELECT * FROM t3, t4;
> ]], {
> -- <select1-11.1>
> @@ -1878,7 +1878,7 @@ test:do_execsql_test(
> "select1-12.4",
> [[
> DELETE FROM t3;
> - INSERT INTO t3 VALUES(0,1,2);
> + INSERT INTO t3 VALUES(0,'1','2');
> ]], {
> -- <select1-12.4>
>
> diff --git a/test/sql-tap/select4.test.lua b/test/sql-tap/select4.test.lua
> index 23cf1bf1b..f7a320438 100755
> --- a/test/sql-tap/select4.test.lua
> +++ b/test/sql-tap/select4.test.lua
> @@ -761,12 +761,12 @@ test:do_test(
> test:execsql [[
> CREATE TABLE t3(a text primary key, b NUMBER, c text);
> START TRANSACTION;
> - INSERT INTO t3 VALUES(1, 1.1, '1.1');
> - INSERT INTO t3 VALUES(2, 1.10, '1.10');
> - INSERT INTO t3 VALUES(3, 1.10, '1.1');
> - INSERT INTO t3 VALUES(4, 1.1, '1.10');
> - INSERT INTO t3 VALUES(5, 1.2, '1.2');
> - INSERT INTO t3 VALUES(6, 1.3, '1.3');
> + INSERT INTO t3 VALUES('1', 1.1, '1.1');
> + INSERT INTO t3 VALUES('2', 1.10, '1.10');
> + INSERT INTO t3 VALUES('3', 1.10, '1.1');
> + INSERT INTO t3 VALUES('4', 1.1, '1.10');
> + INSERT INTO t3 VALUES('5', 1.2, '1.2');
> + INSERT INTO t3 VALUES('6', 1.3, '1.3');
> COMMIT;
> ]]
> return test:execsql [[
> diff --git a/test/sql-tap/select7.test.lua b/test/sql-tap/select7.test.lua
> index fec5d7a41..e1e43c557 100755
> --- a/test/sql-tap/select7.test.lua
> +++ b/test/sql-tap/select7.test.lua
> @@ -255,7 +255,7 @@ test:do_execsql_test(
> [[
> DROP TABLE IF EXISTS t5;
> CREATE TABLE t5(a TEXT primary key, b INT);
> - INSERT INTO t5 VALUES(123, 456);
> + INSERT INTO t5 VALUES('123', 456);
> SELECT typeof(a), a FROM t5 GROUP BY a HAVING a<b;
> ]], {
> -- <select7-7.7>
> diff --git a/test/sql-tap/sort.test.lua b/test/sql-tap/sort.test.lua
> index 36074d6ef..18bfd443d 100755
> --- a/test/sql-tap/sort.test.lua
> +++ b/test/sql-tap/sort.test.lua
> @@ -505,10 +505,10 @@ test:do_execsql_test(
> a INTEGER PRIMARY KEY,
> b VARCHAR(30)
> );
> - INSERT INTO t4 VALUES(1,1);
> - INSERT INTO t4 VALUES(2,2);
> - INSERT INTO t4 VALUES(11,11);
> - INSERT INTO t4 VALUES(12,12);
> + INSERT INTO t4 VALUES(1,'1');
> + INSERT INTO t4 VALUES(2,'2');
> + INSERT INTO t4 VALUES(11,'11');
> + INSERT INTO t4 VALUES(12,'12');
> SELECT a FROM t4 ORDER BY 1;
> ]], {
> -- <sort-7.1>
> diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua
> index 15c4c8276..e0771825e 100755
> --- a/test/sql-tap/subquery.test.lua
> +++ b/test/sql-tap/subquery.test.lua
> @@ -1,6 +1,6 @@
> #!/usr/bin/env tarantool
> test = require("sqltester")
> -test:plan(73)
> +test:plan(69)
>
> --!./tcltestrunner.lua
> -- 2005 January 19
> @@ -335,73 +335,6 @@ test:do_execsql_test(
> -- </subquery-2.4.3>
> })
>
> -test:do_execsql_test(
> - "subquery-2.5.1",
> - [[
> - CREATE TABLE t3(a INTEGER PRIMARY KEY);
> - INSERT INTO t3 VALUES(10);
> -
> - CREATE TABLE t4(x TEXT PRIMARY KEY);
> - INSERT INTO t4 VALUES('10');
> - ]], {
> - -- <subquery-2.5.1>
> -
> - -- </subquery-2.5.1>
> - })
> -
> -test:do_test(
> - "subquery-2.5.2",
> - function()
> - -- In the expr "x IN (SELECT a FROM t3)" the RHS of the IN operator
> - -- has text affinity and the LHS has integer affinity. The rule is
> - -- that we try to convert both sides to an integer before doing the
> - -- comparision. Hence, the integer value 10 in t3 will compare equal
> - -- to the string value '10.0' in t4 because the t4 value will be
> - -- converted into an integer.
> - return test:execsql [[
> - SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
> - ]]
> - end, {
> - -- <subquery-2.5.2>
> - "10"
> - -- </subquery-2.5.2>
> - })
> -
> -test:do_test(
> - "subquery-2.5.3.1",
> - function()
> - -- The t4i index cannot be used to resolve the "x IN (...)" constraint
> - -- because the constraint has integer affinity but t4i has text affinity.
> - return test:execsql [[
> - CREATE INDEX t4i ON t4(x);
> - SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
> - ]]
> - end, {
> - -- <subquery-2.5.3.1>
> - "10"
> - -- </subquery-2.5.3.1>
> - })
> -
> --- Tarantool: no-rowid is implied for the table, so query plan contains
> --- scan over t4i. Verified w/ vanilla sql. Comment this case
> ---do_test subquery-2.5.3.2 {
> --- Verify that the t4i index was not used in the previous query
> --- execsql {
> --- EXPLAIN QUERY PLAN
> --- SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
> --- }
> ---} {~/t4i/}
> -test:do_execsql_test(
> - "subquery-2.5.4",
> - [[
> - DROP TABLE t3;
> - DROP TABLE t4;
> - ]], {
> - -- <subquery-2.5.4>
> -
> - -- </subquery-2.5.4>
> - })
> -
> --------------------------------------------------------------------
> -- The following test cases - subquery-3.* - test tickets that
> -- were raised during development of correlated subqueries.
> diff --git a/test/sql-tap/tkt-3998683a16.test.lua b/test/sql-tap/tkt-3998683a16.test.lua
> index 885dcf5cd..9bc310358 100755
> --- a/test/sql-tap/tkt-3998683a16.test.lua
> +++ b/test/sql-tap/tkt-3998683a16.test.lua
> @@ -26,29 +26,17 @@ test:do_test(
> function()
> return test:execsql [[
> CREATE TABLE t1(x INT primary key, y NUMBER);
> - INSERT INTO t1 VALUES(1, '1.0');
> - INSERT INTO t1 VALUES(2, '.125');
> - INSERT INTO t1 VALUES(3, '123.');
> - INSERT INTO t1 VALUES(4, '123.e+2');
> - INSERT INTO t1 VALUES(5, '.125e+3');
> - INSERT INTO t1 VALUES(6, '123e4');
> - INSERT INTO t1 VALUES(11, ' 1.0');
> - INSERT INTO t1 VALUES(12, ' .125');
> - INSERT INTO t1 VALUES(13, ' 123.');
> - INSERT INTO t1 VALUES(14, ' 123.e+2');
> - INSERT INTO t1 VALUES(15, ' .125e+3');
> - INSERT INTO t1 VALUES(16, ' 123e4');
> - INSERT INTO t1 VALUES(21, '1.0 ');
> - INSERT INTO t1 VALUES(22, '.125 ');
> - INSERT INTO t1 VALUES(23, '123. ');
> - INSERT INTO t1 VALUES(24, '123.e+2 ');
> - INSERT INTO t1 VALUES(25, '.125e+3 ');
> - INSERT INTO t1 VALUES(26, '123e4 ');
> + INSERT INTO t1 VALUES(1, 1.0);
> + INSERT INTO t1 VALUES(2, .125);
> + INSERT INTO t1 VALUES(3, 123.);
> + INSERT INTO t1 VALUES(4, 123.e+2);
> + INSERT INTO t1 VALUES(5, .125e+3);
> + INSERT INTO t1 VALUES(6, 123e4);
> SELECT x FROM t1 WHERE typeof(y)=='number' ORDER BY x;
> ]]
> end, {
> -- <tkt-3998683a16.1>
> - 1, 2, 3, 4, 5, 6, 11, 12, 13, 14, 15, 16, 21, 22, 23, 24, 25, 26
> + 1, 2, 3, 4, 5, 6
> -- </tkt-3998683a16.1>
> })
>
> diff --git a/test/sql-tap/tkt-54844eea3f.test.lua b/test/sql-tap/tkt-54844eea3f.test.lua
> index d6cd56e52..89d0d1218 100755
> --- a/test/sql-tap/tkt-54844eea3f.test.lua
> +++ b/test/sql-tap/tkt-54844eea3f.test.lua
> @@ -62,10 +62,10 @@ test:do_execsql_test(
> "1.2",
> [[
> CREATE TABLE t4(id INT primary key, a TEXT, b TEXT, c TEXT);
> - INSERT INTO t4 VALUES(1, 'a', 1, 'one');
> - INSERT INTO t4 VALUES(2, 'a', 2, 'two');
> - INSERT INTO t4 VALUES(3, 'b', 1, 'three');
> - INSERT INTO t4 VALUES(4, 'b', 2, 'four');
> + INSERT INTO t4 VALUES(1, 'a', '1', 'one');
> + INSERT INTO t4 VALUES(2, 'a', '2', 'two');
> + INSERT INTO t4 VALUES(3, 'b', '1', 'three');
> + INSERT INTO t4 VALUES(4, 'b', '2', 'four');
> SELECT (
> SELECT c FROM (
> SELECT a,b,c FROM t4 WHERE a=output.a ORDER BY b LIMIT 10 OFFSET 1
> diff --git a/test/sql-tap/tkt-7bbfb7d442.test.lua b/test/sql-tap/tkt-7bbfb7d442.test.lua
> index 535303771..bfddcd920 100755
> --- a/test/sql-tap/tkt-7bbfb7d442.test.lua
> +++ b/test/sql-tap/tkt-7bbfb7d442.test.lua
> @@ -109,13 +109,13 @@ if (1 > 0)
> T1.Variant AS Variant,
> T1.ControlDate AS ControlDate,
> 1 AS ControlState,
> - COALESCE(T2.DeliveredQty,0) AS DeliveredQty
> + CAST(COALESCE(T2.DeliveredQty,0) AS STRING) AS DeliveredQty
> FROM (
> SELECT
> NEW.InventoryControlId AS InventoryControlId,
> II.SKU AS SKU,
> II.Variant AS Variant,
> - COALESCE(LastClosedIC.ControlDate,NEW.ControlDate) AS ControlDate
> + CAST(COALESCE(LastClosedIC.ControlDate,NEW.ControlDate) AS STRING) AS ControlDate
> FROM
> InventoryItem II
> LEFT JOIN
> diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua
> index cb5348ab4..ca3a5427a 100755
> --- a/test/sql-tap/tkt-9a8b09f8e6.test.lua
> +++ b/test/sql-tap/tkt-9a8b09f8e6.test.lua
> @@ -1,6 +1,6 @@
> #!/usr/bin/env tarantool
> test = require("sqltester")
> -test:plan(49)
> +test:plan(47)
>
> --!./tcltestrunner.lua
> -- 2014 June 26
> @@ -193,16 +193,6 @@ test:do_execsql_test(
> -- </3.3>
> })
>
> -test:do_execsql_test(
> - 3.4,
> - [[
> - SELECT x FROM t2 WHERE x IN ('1');
> - ]], {
> - -- <3.4>
> - 1
> - -- </3.4>
> - })
> -
> test:do_execsql_test(
> 3.5,
> [[
> @@ -233,16 +223,6 @@ test:do_execsql_test(
> -- </3.7>
> })
>
> -test:do_execsql_test(
> - 3.8,
> - [[
> - SELECT x FROM t2 WHERE '1' IN (x);
> - ]], {
> - -- <3.8>
> - 1
> - -- </3.8>
> - })
> -
> test:do_execsql_test(
> 4.1,
> [[
> @@ -263,23 +243,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 1 to number"
> -- </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 1.0 to number"
> -- </4.4>
> })
>
> @@ -303,23 +283,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 1 to number"
> -- </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 1.0 to number"
> -- </4.8>
> })
>
> @@ -343,23 +323,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 1 to number"
> -- </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 1.0 to number"
> -- </5.4>
> })
>
> @@ -373,13 +353,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 1.11 to number"
> -- </5.6>
> })
>
> @@ -403,23 +383,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 1 to number"
> -- </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 1.0 to number"
> -- </5.10>
> })
>
> @@ -433,13 +413,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 1.11 to number"
> -- </5.12>
> })
>
> 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/tkt-fc7bd6358f.test.lua b/test/sql-tap/tkt-fc7bd6358f.test.lua
> deleted file mode 100755
> index fe5d6200f..000000000
> --- a/test/sql-tap/tkt-fc7bd6358f.test.lua
> +++ /dev/null
> @@ -1,111 +0,0 @@
> -#!/usr/bin/env tarantool
> -test = require("sqltester")
> -test:plan(50)
> -
> ---!./tcltestrunner.lua
> --- 2013 March 05
> ---
> --- 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. Specifically,
> --- it tests that ticket [fc7bd6358f]:
> ---
> --- The following SQL yields an incorrect result (zero rows) in all
> --- versions of sql between 3.6.14 and 3.7.15.2:
> ---
> --- CREATE TABLE t(textid TEXT);
> --- INSERT INTO t VALUES('12');
> --- INSERT INTO t VALUES('34');
> --- CREATE TABLE i(intid INTEGER PRIMARY KEY);
> --- INSERT INTO i VALUES(12);
> --- INSERT INTO i VALUES(34);
> ---
> --- SELECT t1.textid AS a, i.intid AS b, t2.textid AS c
> --- FROM t t1, i, t t2
> --- WHERE t1.textid = i.intid
> --- AND t1.textid = t2.textid;
> ---
> --- The correct result should be two rows, one with 12|12|12 and the other
> --- with 34|34|34. With this bug, no rows are returned. Bisecting shows that
> --- this bug was introduced with check-in [dd4d67a67454] on 2009-04-23.
> ---
> --- ["set","testdir",[["file","dirname",["argv0"]]]]
> --- ["source",[["testdir"],"\/tester.tcl"]]
> -test:do_test(
> - "tkt-fc7bd6358f.100",
> - function()
> - return test:execsql [[
> - CREATE TABLE t(textid TEXT PRIMARY KEY);
> - INSERT INTO t VALUES('12');
> - INSERT INTO t VALUES('34');
> - CREATE TABLE i(intid INTEGER PRIMARY KEY);
> - INSERT INTO i VALUES(12);
> - INSERT INTO i VALUES(34);
> - ]]
> - end, {
> - -- <tkt-fc7bd6358f.100>
> -
> - -- </tkt-fc7bd6358f.100>
> - })
> -
> --- ["unset","-nocomplain","from"]
> --- ["unset","-nocomplain","where"]
> --- ["unset","-nocomplain","a"]
> --- ["unset","-nocomplain","b"]
> -local froms = {
> - "FROM t t1, i, t t2",
> - "FROM i, t t1, t t2",
> - "FROM t t1, t t2, i",
> -}
> -local wheres = {
> - "WHERE t1.textid=i.intid AND t1.textid=t2.textid",
> - "WHERE i.intid=t1.textid AND t1.textid=t2.textid",
> - "WHERE t1.textid=i.intid AND i.intid=t2.textid",
> - "WHERE t1.textid=i.intid AND t2.textid=i.intid",
> - "WHERE i.intid=t1.textid AND i.intid=t2.textid",
> - "WHERE i.intid=t1.textid AND t2.textid=i.intid",
> - "WHERE t1.textid=t2.textid AND i.intid=t2.textid",
> - "WHERE t1.textid=t2.textid AND t2.textid=i.intid",
> -}
> -for a, from in ipairs(froms) do
> - for b, where in ipairs(wheres) 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))
> - end, {
> - "12", 12, "12", "34", 34, "34"
> - })
> -
> - 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))
> - end, {
> - "12", 12, "12", "34", 34, "34"
> - })
> -
> - end
> -end
> -
> -test:do_test(
> - "tkt-fc7bd6358f.200",
> - function()
> - return test:execsql [[
> - DROP TABLE t;
> - DROP TABLE i;
> - ]]
> - end, {
> - -- <tkt-fc7bd6358f.100>
> -
> - -- </tkt-fc7bd6358f.100>
> - })
> -
> -test:finish_test()
> -
> diff --git a/test/sql-tap/tkt1444.test.lua b/test/sql-tap/tkt1444.test.lua
> index 82a5ded25..fb148bc5f 100755
> --- a/test/sql-tap/tkt1444.test.lua
> +++ b/test/sql-tap/tkt1444.test.lua
> @@ -30,8 +30,8 @@ test:do_execsql_test(
> [[
> CREATE TABLE DemoTable (id INT primary key, x INTEGER, TextKey TEXT, DKey NUMBER);
> CREATE INDEX DemoTableIdx ON DemoTable (TextKey);
> - INSERT INTO DemoTable VALUES(1, 9,8,7);
> - INSERT INTO DemoTable VALUES(2, 1,2,3);
> + INSERT INTO DemoTable VALUES(1, 9,'8',7);
> + INSERT INTO DemoTable VALUES(2, 1,'2',3);
> CREATE VIEW DemoView AS SELECT x, TextKey, DKey FROM DemoTable ORDER BY TextKey;
> SELECT x,TextKey,DKey FROM DemoTable UNION ALL SELECT * FROM DemoView ORDER BY 1;
> ]], {
> diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua
> index 7ceec4702..de77e61e9 100755
> --- a/test/sql-tap/tkt3493.test.lua
> +++ b/test/sql-tap/tkt3493.test.lua
> @@ -29,8 +29,8 @@ test:do_execsql_test(
> START TRANSACTION;
> INSERT INTO A VALUES(1,'123');
> INSERT INTO A VALUES(2,'456');
> - INSERT INTO B VALUES(1,1);
> - INSERT INTO B VALUES(2,2);
> + INSERT INTO B VALUES(1,'1');
> + INSERT INTO B VALUES(2,'2');
> INSERT INTO A_B VALUES(1,1);
> INSERT INTO A_B VALUES(2,2);
> COMMIT;
> @@ -116,7 +116,7 @@ test:do_execsql_test(
> "tkt3493-2.1",
> [[
> CREATE TABLE t1(a TEXT PRIMARY KEY, b INT);
> - INSERT INTO t1 VALUES(123, 456);
> + INSERT INTO t1 VALUES('123', 456);
> ]], {
> -- <tkt3493-2.1>
>
> diff --git a/test/sql-tap/tkt3841.test.lua b/test/sql-tap/tkt3841.test.lua
> index 5203d0cd4..56668f6a3 100755
> --- a/test/sql-tap/tkt3841.test.lua
> +++ b/test/sql-tap/tkt3841.test.lua
> @@ -31,12 +31,12 @@ test:do_execsql_test(
>
> INSERT INTO table2 VALUES ('a', 'alist');
> INSERT INTO table2 VALUES ('b', 'blist');
> - INSERT INTO list VALUES ('a', 1);
> - INSERT INTO list VALUES ('a', 2);
> - INSERT INTO list VALUES ('a', 3);
> - INSERT INTO list VALUES ('b', 4);
> - INSERT INTO list VALUES ('b', 5);
> - INSERT INTO list VALUES ('b', 6);
> + INSERT INTO list VALUES ('a', '1');
> + INSERT INTO list VALUES ('a', '2');
> + INSERT INTO list VALUES ('a', '3');
> + INSERT INTO list VALUES ('b', '4');
> + INSERT INTO list VALUES ('b', '5');
> + INSERT INTO list VALUES ('b', '6');
>
> SELECT
> table2.x,
> diff --git a/test/sql-tap/transitive1.test.lua b/test/sql-tap/transitive1.test.lua
> index e96056580..96895b4a7 100755
> --- a/test/sql-tap/transitive1.test.lua
> +++ b/test/sql-tap/transitive1.test.lua
> @@ -338,7 +338,7 @@ test:do_execsql_test(
> ON tvshow.idshow = episode.idshow
> LEFT JOIN seasons
> ON seasons.idshow = episode.idshow
> - AND seasons.season = episode.c12
> + AND seasons.season = CAST(episode.c12 AS INTEGER)
> JOIN path
> ON files.idpath = path.idpath
> LEFT JOIN bookmark
> @@ -378,7 +378,7 @@ test:do_execsql_test(
> FROM episodeview
> JOIN tvshowview ON tvshowview.idShow = episodeview.idShow
> JOIN seasons ON (seasons.idShow = tvshowview.idShow
> - AND seasons.season = episodeview.c12)
> + AND seasons.season = CAST(episodeview.c12 AS INTEGER))
> JOIN files ON files.idFile = episodeview.idFile
> JOIN tvshowlinkpath ON tvshowlinkpath.idShow = tvshowview.idShow
> JOIN path ON path.idPath = tvshowlinkpath.idPath
> diff --git a/test/sql-tap/triggerA.test.lua b/test/sql-tap/triggerA.test.lua
> index fac51ca14..fc8ecfe17 100755
> --- a/test/sql-tap/triggerA.test.lua
> +++ b/test/sql-tap/triggerA.test.lua
> @@ -283,7 +283,7 @@ test:do_test(
> CREATE TABLE result2(id INTEGER PRIMARY KEY, a TEXT,b INT);
> CREATE TRIGGER r5d INSTEAD OF DELETE ON v5 FOR EACH ROW BEGIN
> INSERT INTO result2(id, a,b) VALUES((SELECT coalesce(max(id),0) + 1 FROM result2),
> - old.x, old.b);
> + CAST(old.x AS STRING), old.b);
> END;
> DELETE FROM v5 WHERE x=5;
> SELECT a, b FROM result2;
> @@ -301,7 +301,7 @@ test:do_test(
> DELETE FROM result4;
> CREATE TRIGGER r5u INSTEAD OF UPDATE ON v5 FOR EACH ROW BEGIN
> INSERT INTO result4(id, a,b,c,d) VALUES((SELECT coalesce(max(id),0) + 1 FROM result4),
> - old.x, old.b, new.x, new.b);
> + CAST(old.x AS STRING), old.b, CAST(new.x AS STRING), new.b);
> END;
> UPDATE v5 SET b = b+9900000 WHERE x BETWEEN 3 AND 5;
> SELECT a,b,c,d FROM result4 ORDER BY a;
> diff --git a/test/sql-tap/unique.test.lua b/test/sql-tap/unique.test.lua
> index 9818f90a8..6b0a7e20d 100755
> --- a/test/sql-tap/unique.test.lua
> +++ b/test/sql-tap/unique.test.lua
> @@ -52,7 +52,7 @@ test:do_catchsql_test(
> test:do_catchsql_test(
> "unique-1.2",
> [[
> - INSERT INTO t1(a,b,c) VALUES(1,2,3)
> + INSERT INTO t1(a,b,c) VALUES(1,2,'3')
> ]], {
> -- <unique-1.2>
> 0
> @@ -62,7 +62,7 @@ test:do_catchsql_test(
> test:do_catchsql_test(
> "unique-1.3",
> [[
> - INSERT INTO t1(a,b,c) VALUES(1,3,4)
> + INSERT INTO t1(a,b,c) VALUES(1,3,'4')
> ]], {
> -- <unique-1.3>
> 1, "Duplicate key exists in unique index 'pk_unnamed_T1_1' in space 'T1'"
> @@ -83,7 +83,7 @@ test:do_execsql_test(
> test:do_catchsql_test(
> "unique-1.5",
> [[
> - INSERT INTO t1(a,b,c) VALUES(3,2,4)
> + INSERT INTO t1(a,b,c) VALUES(3,2,'4')
> ]], {
> -- <unique-1.5>
> 1, "Duplicate key exists in unique index 'unique_unnamed_T1_2' in space 'T1'"
> @@ -104,7 +104,7 @@ test:do_execsql_test(
> test:do_catchsql_test(
> "unique-1.7",
> [[
> - INSERT INTO t1(a,b,c) VALUES(3,4,5)
> + INSERT INTO t1(a,b,c) VALUES(3,4,'5')
> ]], {
> -- <unique-1.7>
> 0
> diff --git a/test/sql-tap/view.test.lua b/test/sql-tap/view.test.lua
> index e553b91c7..ab14c5edb 100755
> --- a/test/sql-tap/view.test.lua
> +++ b/test/sql-tap/view.test.lua
> @@ -757,7 +757,7 @@ test:do_execsql_test(
> "view-10.1",
> [=[
> CREATE TABLE t3("9" integer primary key, "4" text);
> - INSERT INTO t3 VALUES(1,2);
> + INSERT INTO t3 VALUES(1,'2');
> CREATE VIEW v_t3_a AS SELECT a."9" FROM t3 AS a;
> CREATE VIEW v_t3_b AS SELECT "4" FROM t3;
> SELECT * FROM v_t3_a;
> diff --git a/test/sql-tap/where5.test.lua b/test/sql-tap/where5.test.lua
> index 749201564..3aefcaca5 100755
> --- a/test/sql-tap/where5.test.lua
> +++ b/test/sql-tap/where5.test.lua
> @@ -27,11 +27,11 @@ test:do_test("where5-1.0", function()
> CREATE TABLE t1(x TEXT primary key);
> CREATE TABLE t2(x integer primary key);
> CREATE TABLE t3(x integer PRIMARY KEY);
> - INSERT INTO t1 VALUES(-1);
> - INSERT INTO t1 VALUES(0);
> - INSERT INTO t1 VALUES(1);
> - INSERT INTO t2 SELECT * FROM t1;
> - INSERT INTO t3 SELECT * FROM t1;
> + INSERT INTO t1 VALUES('-1');
> + INSERT INTO t1 VALUES('0');
> + INSERT INTO t1 VALUES('1');
> + INSERT INTO t2 SELECT CAST(x AS INTEGER) FROM t1;
> + INSERT INTO t3 SELECT CAST(x AS INTEGER) FROM t1;
> ]]
> return test:execsql [[
> SELECT * FROM t1 WHERE x<0
> diff --git a/test/sql-tap/whereB.test.lua b/test/sql-tap/whereB.test.lua
> deleted file mode 100755
> index d98645fdc..000000000
> --- a/test/sql-tap/whereB.test.lua
> +++ /dev/null
> @@ -1,908 +0,0 @@
> -#!/usr/bin/env tarantool
> -test = require("sqltester")
> -test:plan(63)
> -
> ---!./tcltestrunner.lua
> --- 2009 August 13
> ---
> --- 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. The
> --- focus of this file is testing WHERE clause conditions with
> --- subtle affinity issues.
> ---
> --- ["set","testdir",[["file","dirname",["argv0"]]]]
> --- ["source",[["testdir"],"\/tester.tcl"]]
> --- For this set of tests:
> ---
> --- * t1.y holds an integer value with affinity NONE
> --- * t2.b holds a text value with affinity TEXT
> ---
> --- These values are not equal and because neither affinity is NUMERIC
> --- no type conversion occurs.
> ---
> -test:do_execsql_test(
> - "whereB-1.1",
> - [[
> - CREATE TABLE t1(x INT primary key,y INT ); -- affinity of t1.y is NONE
> - INSERT INTO t1 VALUES(1,99);
> -
> - 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');
> -
> - SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a;
> - ]],
> - {
> - -- <whereB-1.1>
> - 1, 2, true
> - -- </whereB-1.1>
> - })
> -
> -test:do_execsql_test(
> - "whereB-1.2",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-1.2>
> - 1, 2, true
> - -- </whereB-1.2>
> - })
> -
> -test:do_execsql_test(
> - "whereB-1.3",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-1.3>
> - 1, 2, true
> - -- </whereB-1.3>
> - })
> -
> -test:do_execsql_test(
> - "whereB-1.4",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-1.4>
> - 1, 2, true
> - -- </whereB-1.4>
> - })
> -
> -test:do_execsql_test(
> - "whereB-1.100",
> - [[
> - DROP INDEX t2b ON t2;
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-1.100>
> - 1, 2, true
> - -- </whereB-1.100>
> - })
> -
> -test:do_execsql_test(
> - "whereB-1.101",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-1.101>
> - 1, 2, true
> - -- </whereB-1.101>
> - })
> -
> -test:do_execsql_test(
> - "whereB-1.102",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-1.102>
> - 1, 2, true
> - -- </whereB-1.102>
> - })
> -
> --- For this set of tests:
> ---
> --- * t1.y holds a text value with affinity TEXT
> --- * t2.b holds an integer value with affinity NONE
> ---
> --- These values are not equal and because neither affinity is NUMERIC
> --- no type conversion occurs.
> ---
> -test:do_execsql_test(
> - "whereB-2.1",
> - [[
> - DROP TABLE t1;
> - DROP TABLE t2;
> -
> - CREATE TABLE t1(x INT primary key, y TEXT); -- affinity of t1.y is TEXT
> - INSERT INTO t1 VALUES(1,99);
> -
> - 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);
> -
> - SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a;
> - ]],
> - {
> - -- <whereB-2.1>
> - 1, 2, false
> - -- </whereB-2.1>
> - })
> -
> -test:do_execsql_test(
> - "whereB-2.2",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-2.2>
> -
> - -- </whereB-2.2>
> - })
> -
> -test:do_execsql_test(
> - "whereB-2.3",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-2.3>
> -
> - -- </whereB-2.3>
> - })
> -
> -test:do_execsql_test(
> - "whereB-2.4",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-2.4>
> -
> - -- </whereB-2.4>
> - })
> -
> -test:do_execsql_test(
> - "whereB-2.100",
> - [[
> - DROP INDEX t2b ON t2;
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-2.100>
> -
> - -- </whereB-2.100>
> - })
> -
> -test:do_execsql_test(
> - "whereB-2.101",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-2.101>
> -
> - -- </whereB-2.101>
> - })
> -
> -test:do_execsql_test(
> - "whereB-2.102",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-2.102>
> -
> - -- </whereB-2.102>
> - })
> -
> --- For this set of tests:
> ---
> --- * t1.y holds a text value with affinity NONE
> --- * t2.b holds an integer value with affinity NONE
> ---
> --- These values are not equal and because neither affinity is NUMERIC
> --- no type conversion occurs.
> ---
> -test:do_execsql_test(
> - "whereB-3.1",
> - [[
> - DROP TABLE t1;
> - DROP TABLE t2;
> -
> - CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE
> - INSERT INTO t1 VALUES(1,99);
> -
> - 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');
> -
> - SELECT x, a, y=b FROM t1, t2;
> - ]],
> - {
> - -- <whereB-3.1>
> - 1, 2, false
> - -- </whereB-3.1>
> - })
> -
> -test:do_execsql_test(
> - "whereB-3.2",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-3.2>
> -
> - -- </whereB-3.2>
> - })
> -
> -test:do_execsql_test(
> - "whereB-3.3",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-3.3>
> -
> - -- </whereB-3.3>
> - })
> -
> -test:do_execsql_test(
> - "whereB-3.4",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-3.4>
> -
> - -- </whereB-3.4>
> - })
> -
> -test:do_execsql_test(
> - "whereB-3.100",
> - [[
> - DROP INDEX t2b ON t2;
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-3.100>
> -
> - -- </whereB-3.100>
> - })
> -
> -test:do_execsql_test(
> - "whereB-3.101",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-3.101>
> -
> - -- </whereB-3.101>
> - })
> -
> -test:do_execsql_test(
> - "whereB-3.102",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-3.102>
> -
> - -- </whereB-3.102>
> - })
> -
> --- For this set of tests:
> ---
> --- * t1.y holds a text value with affinity NONE
> --- * t2.b holds an integer value with affinity NUMERIC
> ---
> --- 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",
> - [[
> - DROP TABLE IF EXISTS t1;
> - DROP TABLE IF EXISTS t2;
> -
> - CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE
> - INSERT INTO t1 VALUES(1,'99');
> -
> - 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);
> -
> - SELECT x, a, y=b FROM t1, t2;
> - ]],
> - {
> - -- <whereB-4.1>
> - 1, 2, true
> - -- </whereB-4.1>
> - })
> -
> -test:do_execsql_test(
> - "whereB-4.2",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-4.2>
> - 1, 2, true
> - -- </whereB-4.2>
> - })
> -
> -test:do_execsql_test(
> - "whereB-4.3",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-4.3>
> - 1, 2, true
> - -- </whereB-4.3>
> - })
> -
> -test:do_execsql_test(
> - "whereB-4.4",
> - -- In this case the unary "+" operator shouldn't
> - -- affect result set of query.
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-4.4>
> - 1, 2, true
> - -- </whereB-4.4>
> - })
> -
> -test:do_execsql_test(
> - "whereB-4.100",
> - [[
> - DROP INDEX t2b ON t2;
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-4.100>
> - 1, 2, true
> - -- </whereB-4.100>
> - })
> -
> -test:do_execsql_test(
> - "whereB-4.101",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-4.101>
> - 1, 2, true
> - -- </whereB-4.101>
> - })
> -
> -test:do_execsql_test(
> - "whereB-4.102",
> - -- In this case the unary "+" operator shouldn't
> - -- affect result set of query.
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-4.102>
> - 1, 2, true
> - -- </whereB-4.102>
> - })
> -
> --- For this set of tests:
> ---
> --- * t1.y holds a text value with affinity NONE
> --- * t2.b holds an integer value with affinity INTEGER
> ---
> --- 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",
> - [[
> - DROP TABLE t1;
> - DROP TABLE t2;
> -
> - CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE
> - INSERT INTO t1 VALUES(1,'99');
> -
> - 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);
> -
> - SELECT x, a, y=b FROM t1, t2;
> - ]],
> - {
> - -- <whereB-5.1>
> - 1, 2, true
> - -- </whereB-5.1>
> - })
> -
> -test:do_execsql_test(
> - "whereB-5.2",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-5.2>
> - 1, 2, true
> - -- </whereB-5.2>
> - })
> -
> -test:do_execsql_test(
> - "whereB-5.3",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-5.3>
> - 1, 2, true
> - -- </whereB-5.3>
> - })
> -
> -test:do_execsql_test(
> - "whereB-5.4",
> - -- In this case the unary "+" operator shouldn't
> - -- affect result set of query.
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-5.4>
> - 1, 2, true
> - -- </whereB-5.4>
> - })
> -
> -test:do_execsql_test(
> - "whereB-5.100",
> - [[
> - DROP INDEX t2b ON t2;
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-5.100>
> - 1, 2, true
> - -- </whereB-5.100>
> - })
> -
> -test:do_execsql_test(
> - "whereB-5.101",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-5.101>
> - 1, 2, true
> - -- </whereB-5.101>
> - })
> -
> -test:do_execsql_test(
> - "whereB-5.102",
> - -- In this case the unary "+" operator shouldn't
> - -- affect result set of query.
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-5.102>
> - 1, 2, true
> - -- </whereB-5.102>
> - })
> -
> --- For this set of tests:
> ---
> --- * t1.y holds a text value with affinity NONE
> --- * t2.b holds an integer value with affinity REAL
> ---
> --- 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",
> - [[
> - DROP TABLE t1;
> - DROP TABLE t2;
> -
> - CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE
> - INSERT INTO t1 VALUES(1,'99');
> -
> - 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);
> -
> - SELECT x, a, y=b FROM t1, t2;
> - ]],
> - {
> - -- <whereB-6.1>
> - 1, 2, true
> - -- </whereB-6.1>
> - })
> -
> -test:do_execsql_test(
> - "whereB-6.2",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-6.2>
> - 1, 2, true
> - -- </whereB-6.2>
> - })
> -
> -test:do_execsql_test(
> - "whereB-6.3",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-6.3>
> - 1, 2, true
> - -- </whereB-6.3>
> - })
> -
> -test:do_execsql_test(
> - "whereB-6.4",
> - -- In this case the unary "+" operator shouldn't
> - -- affect result set of query.
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-6.4>
> - 1, 2, true
> - -- </whereB-6.4>
> - })
> -
> -test:do_execsql_test(
> - "whereB-6.100",
> - [[
> - DROP INDEX t2b ON t2;
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-6.100>
> - 1, 2, true
> - -- </whereB-6.100>
> - })
> -
> -test:do_execsql_test(
> - "whereB-6.101",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-6.101>
> - 1, 2, true
> - -- </whereB-6.101>
> - })
> -
> -test:do_execsql_test(
> - "whereB-6.102",
> - -- In this case the unary "+" operator shouldn't
> - -- affect result set of query.
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-6.102>
> - 1, 2, true
> - -- </whereB-6.102>
> - })
> -
> --- For this set of tests:
> ---
> --- * t1.y holds an integer value with affinity NUMERIC
> --- * t2.b holds a text value with affinity NONE
> ---
> --- 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",
> - [[
> - DROP TABLE t1;
> - DROP TABLE t2;
> -
> - CREATE TABLE t1(x INT primary key, y NUMBER); -- affinity of t1.y is NUMERIC
> - INSERT INTO t1 VALUES(1,99);
> -
> - 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');
> -
> - SELECT x, a, y=b FROM t1, t2;
> - ]],
> - {
> - -- <whereB-7.1>
> - 1, 2, true
> - -- </whereB-7.1>
> - })
> -
> -test:do_execsql_test(
> - "whereB-7.2",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-7.2>
> - 1, 2, true
> - -- </whereB-7.2>
> - })
> -
> -test:do_execsql_test(
> - "whereB-7.3",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-7.3>
> - 1, 2, true
> - -- </whereB-7.3>
> - })
> -
> -test:do_execsql_test(
> - "whereB-7.4",
> - -- In this case the unary "+" operator shouldn't
> - -- affect result set of query.
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-7.4>
> - 1, 2, true
> - -- </whereB-7.4>
> - })
> -
> -test:do_execsql_test(
> - "whereB-7.100",
> - [[
> - DROP INDEX t2b ON t2;
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-7.100>
> - 1, 2, true
> - -- </whereB-7.100>
> - })
> -
> -test:do_execsql_test(
> - "whereB-7.101",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-7.101>
> - 1, 2, true
> - -- </whereB-7.101>
> - })
> -
> -test:do_execsql_test(
> - "whereB-7.102",
> - -- In this case the unary "+" operator shouldn't
> - -- affect result set of query.
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-7.102>
> - 1, 2, true
> - -- </whereB-7.102>
> - })
> -
> --- For this set of tests:
> ---
> --- * t1.y holds an integer value with affinity INTEGER
> --- * t2.b holds a text value with affinity NONE
> ---
> --- 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",
> - [[
> - DROP TABLE t1;
> - DROP TABLE t2;
> -
> - CREATE TABLE t1(x INT primary key, y INT); -- affinity of t1.y is INTEGER
> - INSERT INTO t1 VALUES(1,99);
> -
> - 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');
> -
> - SELECT x, a, y=b FROM t1, t2;
> - ]],
> - {
> - -- <whereB-8.1>
> - 1, 2, true
> - -- </whereB-8.1>
> - })
> -
> -test:do_execsql_test(
> - "whereB-8.2",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-8.2>
> - 1, 2, true
> - -- </whereB-8.2>
> - })
> -
> -test:do_execsql_test(
> - "whereB-8.3",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-8.3>
> - 1, 2, true
> - -- </whereB-8.3>
> - })
> -
> -test:do_execsql_test(
> - "whereB-8.4",
> - -- In this case the unary "+" operator shouldn't
> - -- affect result set of query.
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-8.4>
> - 1, 2, true
> - -- </whereB-8.4>
> - })
> -
> -test:do_execsql_test(
> - "whereB-8.100",
> - [[
> - DROP INDEX t2b ON t2;
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-8.100>
> - 1, 2, true
> - -- </whereB-8.100>
> - })
> -
> -test:do_execsql_test(
> - "whereB-8.101",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-8.101>
> - 1, 2, true
> - -- </whereB-8.101>
> - })
> -
> -test:do_execsql_test(
> - "whereB-8.102",
> - -- In this case the unary "+" operator shouldn't
> - -- affect result set of query.
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-8.102>
> - 1, 2, true
> - -- </whereB-8.102>
> - })
> -
> --- For this set of tests:
> ---
> --- * t1.y holds an integer value with affinity REAL
> --- * t2.b holds a text value with affinity NONE
> ---
> --- 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",
> - [[
> - DROP TABLE t1;
> - DROP TABLE t2;
> -
> - CREATE TABLE t1(x INT primary key, y NUMBER); -- affinity of t1.y is REAL
> - INSERT INTO t1 VALUES(1,99.0);
> -
> - 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');
> -
> - SELECT x, a, y=b FROM t1, t2;
> - ]],
> - {
> - -- <whereB-9.1>
> - 1, 2, true
> - -- </whereB-9.1>
> - })
> -
> -test:do_execsql_test(
> - "whereB-9.2",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-9.2>
> - 1, 2, true
> - -- </whereB-9.2>
> - })
> -
> -test:do_execsql_test(
> - "whereB-9.3",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-9.3>
> - 1, 2, true
> - -- </whereB-9.3>
> - })
> -
> -test:do_execsql_test(
> - "whereB-9.4",
> - -- In this case the unary "+" operator shouldn't
> - -- affect result set of query.
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-9.4>
> - 1, 2, true
> - -- </whereB-9.4>
> - })
> -
> -test:do_execsql_test(
> - "whereB-9.100",
> - [[
> - DROP INDEX t2b ON t2;
> - SELECT x, a, y=b FROM t1, t2 WHERE y=b;
> - ]],
> - {
> - -- <whereB-9.100>
> - 1, 2, true
> - -- </whereB-9.100>
> - })
> -
> -test:do_execsql_test(
> - "whereB-9.101",
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE b=y;
> - ]],
> - {
> - -- <whereB-9.101>
> - 1, 2, true
> - -- </whereB-9.101>
> - })
> -
> -test:do_execsql_test(
> - "whereB-9.102",
> - -- In this case the unary "+" operator shouldn't
> - -- affect result set of query.
> - [[
> - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
> - ]],
> - {
> - -- <whereB-9.102>
> - 1, 2, true
> - -- </whereB-9.102>
> - })
> -
> -test:finish_test()
> -
> diff --git a/test/sql-tap/whereC.test.lua b/test/sql-tap/whereC.test.lua
> index 89459dee3..58c049553 100755
> --- a/test/sql-tap/whereC.test.lua
> +++ b/test/sql-tap/whereC.test.lua
> @@ -55,9 +55,9 @@ test:do_execsql_test(
> test:test("main", function()
> local data = {{"SELECT i FROM t1 WHERE a=1 AND b=2 AND i>3", {4, 5}},
> -- {"SELECT i FROM t1 WHERE rowid='12'", {12}},
> - {"SELECT i FROM t1 WHERE a=1 AND b='2'", {3, 4, 5}},
> - {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i>'3'", {4, 5}},
> - {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i<5", {3, 4}},
> + {"SELECT i FROM t1 WHERE a=1 AND b=2", {3, 4, 5}},
> + {"SELECT i FROM t1 WHERE a=1 AND b=2 AND i>3", {4, 5}},
> + {"SELECT i FROM t1 WHERE a=1 AND b=2 AND i<5", {3, 4}},
> {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i<12", {10, 11}},
> {"SELECT i FROM t1 WHERE a IN(1, 2) AND b=2 AND i<11", {3, 4, 5, 10}},
> {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 12", {10, 11, 12}},
> @@ -66,7 +66,7 @@ test:test("main", function()
> {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 12 AND 10", {}},
> {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i<NULL", {}},
> {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i>=NULL", {}},
> - {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i<4.5", {3, 4}}}
> + {"SELECT i FROM t1 WHERE a=1 AND b=2 AND i<4.5", {3, 4}}}
> -- {"SELECT i FROM t1 WHERE rowid IS '12'", {12}}}
>
> for tn, t in ipairs(data) do
> diff --git a/test/sql/types.result b/test/sql/types.result
> index 54aff460e..70fbbc5a2 100644
> --- a/test/sql/types.result
> +++ b/test/sql/types.result
> @@ -2155,3 +2155,623 @@ box.execute([[SELECT * FROM "s" WHERE "id" = ?;]])
> s:drop()
> ---
> ...
> +--
> +-- gh-3809: Make sure there are no implicit casts during
> +-- assignment, except for the implicit cast between numeric
> +-- values.
> +--
> +-- Check INSERT.
> +box.execute([[CREATE TABLE ti (a INT PRIMARY KEY AUTOINCREMENT, i INTEGER);]])
> +---
> +- row_count: 1
> +...
> +box.execute([[CREATE TABLE td (a INT PRIMARY KEY AUTOINCREMENT, d DOUBLE);]])
> +---
> +- row_count: 1
> +...
> +box.execute([[CREATE TABLE tb (a INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN);]])
> +---
> +- row_count: 1
> +...
> +box.execute([[CREATE TABLE tt (a INT PRIMARY KEY AUTOINCREMENT, t TEXT);]])
> +---
> +- row_count: 1
> +...
> +box.execute([[CREATE TABLE tv (a INT PRIMARY KEY AUTOINCREMENT, v VARBINARY);]])
> +---
> +- row_count: 1
> +...
> +box.execute([[CREATE TABLE ts (a INT PRIMARY KEY AUTOINCREMENT, s SCALAR);]])
> +---
> +- row_count: 1
> +...
> +box.execute([[INSERT INTO ti(i) VALUES (NULL);]])
> +---
> +- autoincrement_ids:
> + - 1
> + row_count: 1
> +...
> +box.execute([[INSERT INTO ti(i) VALUES (11);]])
> +---
> +- autoincrement_ids:
> + - 2
> + row_count: 1
> +...
> +box.execute([[INSERT INTO ti(i) VALUES (100000000000000000000000000000000.1);]])
> +---
> +- null
> +- 'Type mismatch: can not convert 1.0e+32 to integer'
> +...
> +box.execute([[INSERT INTO ti(i) VALUES (33.0);]])
> +---
> +- autoincrement_ids:
> + - 3
> + row_count: 1
> +...
> +box.execute([[INSERT INTO ti(i) VALUES (true);]])
> +---
> +- null
> +- 'Type mismatch: can not convert TRUE to integer'
> +...
> +box.execute([[INSERT INTO ti(i) VALUES ('33');]])
> +---
> +- null
> +- 'Type mismatch: can not convert 33 to integer'
> +...
> +box.execute([[INSERT INTO ti(i) VALUES (X'3434');]])
> +---
> +- null
> +- 'Type mismatch: can not convert varbinary to integer'
> +...
> +box.execute([[SELECT * FROM ti;]])
> +---
> +- metadata:
> + - name: A
> + type: integer
> + - name: I
> + type: integer
> + rows:
> + - [1, null]
> + - [2, 11]
> + - [3, 33]
> +...
> +box.execute([[INSERT INTO td(d) VALUES (NULL);]])
> +---
> +- autoincrement_ids:
> + - 1
> + row_count: 1
> +...
> +box.execute([[INSERT INTO td(d) VALUES (11);]])
> +---
> +- autoincrement_ids:
> + - 2
> + row_count: 1
> +...
> +box.execute([[INSERT INTO td(d) VALUES (100000000000000001);;]])
> +---
> +- null
> +- Syntax error at line 1 near ';'
> +...
> +box.execute([[INSERT INTO td(d) VALUES (22.2);]])
> +---
> +- autoincrement_ids:
> + - 3
> + row_count: 1
> +...
> +box.execute([[INSERT INTO td(d) VALUES (true);]])
> +---
> +- null
> +- 'Type mismatch: can not convert TRUE to double'
> +...
> +box.execute([[INSERT INTO td(d) VALUES ('33');]])
> +---
> +- null
> +- 'Type mismatch: can not convert 33 to double'
> +...
> +box.execute([[INSERT INTO td(d) VALUES (X'3434');]])
> +---
> +- null
> +- 'Type mismatch: can not convert varbinary to double'
> +...
> +box.execute([[SELECT * FROM td;]])
> +---
> +- metadata:
> + - name: A
> + type: integer
> + - name: D
> + type: double
> + rows:
> + - [1, null]
> + - [2, 11]
> + - [3, 22.2]
> +...
> +box.execute([[INSERT INTO tb(b) VALUES (NULL);]])
> +---
> +- autoincrement_ids:
> + - 1
> + row_count: 1
> +...
> +box.execute([[INSERT INTO tb(b) VALUES (11);]])
> +---
> +- null
> +- 'Type mismatch: can not convert 11 to boolean'
> +...
> +box.execute([[INSERT INTO tb(b) VALUES (22.2);]])
> +---
> +- null
> +- 'Type mismatch: can not convert 22.2 to boolean'
> +...
> +box.execute([[INSERT INTO tb(b) VALUES (true);]])
> +---
> +- autoincrement_ids:
> + - 2
> + row_count: 1
> +...
> +box.execute([[INSERT INTO tb(b) VALUES ('33');]])
> +---
> +- null
> +- 'Type mismatch: can not convert 33 to boolean'
> +...
> +box.execute([[INSERT INTO tb(b) VALUES (X'3434');]])
> +---
> +- null
> +- 'Type mismatch: can not convert varbinary to boolean'
> +...
> +box.execute([[SELECT * FROM tb;]])
> +---
> +- metadata:
> + - name: A
> + type: integer
> + - name: B
> + type: boolean
> + rows:
> + - [1, null]
> + - [2, true]
> +...
> +box.execute([[INSERT INTO tt(t) VALUES (NULL);]])
> +---
> +- autoincrement_ids:
> + - 1
> + row_count: 1
> +...
> +box.execute([[INSERT INTO tt(t) VALUES (11);]])
> +---
> +- null
> +- 'Type mismatch: can not convert 11 to string'
> +...
> +box.execute([[INSERT INTO tt(t) VALUES (22.2);]])
> +---
> +- null
> +- 'Type mismatch: can not convert 22.2 to string'
> +...
> +box.execute([[INSERT INTO tt(t) VALUES (true);]])
> +---
> +- null
> +- 'Type mismatch: can not convert TRUE to string'
> +...
> +box.execute([[INSERT INTO tt(t) VALUES ('33');]])
> +---
> +- autoincrement_ids:
> + - 2
> + row_count: 1
> +...
> +box.execute([[INSERT INTO tt(t) VALUES (X'3434');]])
> +---
> +- null
> +- 'Type mismatch: can not convert varbinary to string'
> +...
> +box.execute([[SELECT * FROM tt;]])
> +---
> +- metadata:
> + - name: A
> + type: integer
> + - name: T
> + type: string
> + rows:
> + - [1, null]
> + - [2, '33']
> +...
> +box.execute([[INSERT INTO tv(v) VALUES (NULL);]])
> +---
> +- autoincrement_ids:
> + - 1
> + row_count: 1
> +...
> +box.execute([[INSERT INTO tv(v) VALUES (11);]])
> +---
> +- null
> +- 'Type mismatch: can not convert 11 to varbinary'
> +...
> +box.execute([[INSERT INTO tv(v) VALUES (22.2);]])
> +---
> +- null
> +- 'Type mismatch: can not convert 22.2 to varbinary'
> +...
> +box.execute([[INSERT INTO tv(v) VALUES (true);]])
> +---
> +- null
> +- 'Type mismatch: can not convert TRUE to varbinary'
> +...
> +box.execute([[INSERT INTO tv(v) VALUES ('33');]])
> +---
> +- null
> +- 'Type mismatch: can not convert 33 to varbinary'
> +...
> +box.execute([[INSERT INTO tv(v) VALUES (X'3434');]])
> +---
> +- autoincrement_ids:
> + - 2
> + row_count: 1
> +...
> +box.execute([[SELECT * FROM tv;]])
> +---
> +- metadata:
> + - name: A
> + type: integer
> + - name: V
> + type: varbinary
> + rows:
> + - [1, null]
> + - [2, '44']
> +...
> +box.execute([[INSERT INTO ts(s) VALUES (NULL);]])
> +---
> +- autoincrement_ids:
> + - 1
> + row_count: 1
> +...
> +box.execute([[INSERT INTO ts(s) VALUES (11);]])
> +---
> +- autoincrement_ids:
> + - 2
> + row_count: 1
> +...
> +box.execute([[INSERT INTO ts(s) VALUES (22.2);]])
> +---
> +- autoincrement_ids:
> + - 3
> + row_count: 1
> +...
> +box.execute([[INSERT INTO ts(s) VALUES (true);]])
> +---
> +- autoincrement_ids:
> + - 4
> + row_count: 1
> +...
> +box.execute([[INSERT INTO ts(s) VALUES ('33');]])
> +---
> +- autoincrement_ids:
> + - 5
> + row_count: 1
> +...
> +box.execute([[INSERT INTO ts(s) VALUES (X'3434');]])
> +---
> +- autoincrement_ids:
> + - 6
> + row_count: 1
> +...
> +box.execute([[SELECT * FROM ts;]])
> +---
> +- metadata:
> + - name: A
> + type: integer
> + - name: S
> + type: scalar
> + rows:
> + - [1, null]
> + - [2, 11]
> + - [3, 22.2]
> + - [4, true]
> + - [5, '33']
> + - [6, '44']
> +...
> +-- Check for UPDATE.
> +box.execute([[DELETE FROM ti;]])
> +---
> +- row_count: 3
> +...
> +box.execute([[DELETE FROM td;]])
> +---
> +- row_count: 3
> +...
> +box.execute([[DELETE FROM tb;]])
> +---
> +- row_count: 2
> +...
> +box.execute([[DELETE FROM tt;]])
> +---
> +- row_count: 2
> +...
> +box.execute([[DELETE FROM tv;]])
> +---
> +- row_count: 2
> +...
> +box.execute([[DELETE FROM ts;]])
> +---
> +- row_count: 6
> +...
> +box.execute([[INSERT INTO ti VALUES(1, NULL);]])
> +---
> +- row_count: 1
> +...
> +box.execute([[INSERT INTO td VALUES(1, NULL);]])
> +---
> +- row_count: 1
> +...
> +box.execute([[INSERT INTO tb VALUES(1, NULL);]])
> +---
> +- row_count: 1
> +...
> +box.execute([[INSERT INTO tt VALUES(1, NULL);]])
> +---
> +- row_count: 1
> +...
> +box.execute([[INSERT INTO tv VALUES(1, NULL);]])
> +---
> +- row_count: 1
> +...
> +box.execute([[INSERT INTO ts VALUES(1, NULL);]])
> +---
> +- row_count: 1
> +...
> +box.execute([[SELECT * FROM ti, td, tb, tt, tv, ts;]])
> +---
> +- metadata:
> + - name: A
> + type: integer
> + - name: I
> + type: integer
> + - name: A
> + type: integer
> + - name: D
> + type: double
> + - name: A
> + type: integer
> + - name: B
> + type: boolean
> + - name: A
> + type: integer
> + - name: T
> + type: string
> + - name: A
> + type: integer
> + - name: V
> + type: varbinary
> + - name: A
> + type: integer
> + - name: S
> + type: scalar
> + rows:
> + - [1, null, 1, null, 1, null, 1, null, 1, null, 1, null]
> +...
> +box.execute([[UPDATE ti SET i = NULL WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE ti SET i = 11 WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE ti SET i = 100000000000000000000000000000000.1 WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert 1.0e+32 to integer'
> +...
> +box.execute([[UPDATE ti SET i = 33.0 WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE ti SET i = true WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert TRUE to integer'
> +...
> +box.execute([[UPDATE ti SET i = '33' WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert 33 to integer'
> +...
> +box.execute([[UPDATE ti SET i = X'3434' WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert varbinary to integer'
> +...
> +box.execute([[SELECT * FROM ti;]])
> +---
> +- metadata:
> + - name: A
> + type: integer
> + - name: I
> + type: integer
> + rows:
> + - [1, 33]
> +...
> +box.execute([[UPDATE td SET d = NULL WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE td SET d = 11 WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE td SET d = 100000000000000001 WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE td SET d = 22.2 WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE td SET d = true WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert TRUE to double'
> +...
> +box.execute([[UPDATE td SET d = '33' WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert 33 to double'
> +...
> +box.execute([[UPDATE td SET d = X'3434' WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert varbinary to double'
> +...
> +box.execute([[SELECT * FROM td;]])
> +---
> +- metadata:
> + - name: A
> + type: integer
> + - name: D
> + type: double
> + rows:
> + - [1, 22.2]
> +...
> +box.execute([[UPDATE tb SET b = NULL WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE tb SET b = 11 WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert 11 to boolean'
> +...
> +box.execute([[UPDATE tb SET b = 22.2 WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert 22.2 to boolean'
> +...
> +box.execute([[UPDATE tb SET b = true WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE tb SET b = '33' WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert 33 to boolean'
> +...
> +box.execute([[UPDATE tb SET b = X'3434' WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert varbinary to boolean'
> +...
> +box.execute([[SELECT * FROM tb;]])
> +---
> +- metadata:
> + - name: A
> + type: integer
> + - name: B
> + type: boolean
> + rows:
> + - [1, true]
> +...
> +box.execute([[UPDATE tt SET t = NULL WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE tt SET t = 11 WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert 11 to string'
> +...
> +box.execute([[UPDATE tt SET t = 22.2 WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert 22.2 to string'
> +...
> +box.execute([[UPDATE tt SET t = true WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert TRUE to string'
> +...
> +box.execute([[UPDATE tt SET t = '33' WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE tt SET t = X'3434' WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert varbinary to string'
> +...
> +box.execute([[SELECT * FROM tt;]])
> +---
> +- metadata:
> + - name: A
> + type: integer
> + - name: T
> + type: string
> + rows:
> + - [1, '33']
> +...
> +box.execute([[UPDATE tv SET v = NULL WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE tv SET v = 11 WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert 11 to varbinary'
> +...
> +box.execute([[UPDATE tv SET v = 22.2 WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert 22.2 to varbinary'
> +...
> +box.execute([[UPDATE tv SET v = true WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert TRUE to varbinary'
> +...
> +box.execute([[UPDATE tv SET v = '33' WHERE a = 1;]])
> +---
> +- null
> +- 'Type mismatch: can not convert 33 to varbinary'
> +...
> +box.execute([[UPDATE tv SET v = X'3434' WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[SELECT * FROM tv;]])
> +---
> +- metadata:
> + - name: A
> + type: integer
> + - name: V
> + type: varbinary
> + rows:
> + - [1, '44']
> +...
> +box.execute([[UPDATE ts SET s = NULL WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE ts SET s = 11 WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE ts SET s = 22.2 WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE ts SET s = true WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE ts SET s = '33' WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[UPDATE ts SET s = X'3434' WHERE a = 1;]])
> +---
> +- row_count: 1
> +...
> +box.execute([[SELECT * FROM ts;]])
> +---
> +- metadata:
> + - name: A
> + type: integer
> + - name: S
> + type: scalar
> + rows:
> + - [1, '44']
> +...
> diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua
> index bd14b342d..2dc70f3c5 100644
> --- a/test/sql/types.test.lua
> +++ b/test/sql/types.test.lua
> @@ -487,3 +487,132 @@ s:format({ \
>
> box.execute([[SELECT * FROM "s" WHERE "id" = ?;]])
> s:drop()
> +
> +--
> +-- gh-3809: Make sure there are no implicit casts during
> +-- assignment, except for the implicit cast between numeric
> +-- values.
> +--
> +
> +-- Check INSERT.
> +box.execute([[CREATE TABLE ti (a INT PRIMARY KEY AUTOINCREMENT, i INTEGER);]])
> +box.execute([[CREATE TABLE td (a INT PRIMARY KEY AUTOINCREMENT, d DOUBLE);]])
> +box.execute([[CREATE TABLE tb (a INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN);]])
> +box.execute([[CREATE TABLE tt (a INT PRIMARY KEY AUTOINCREMENT, t TEXT);]])
> +box.execute([[CREATE TABLE tv (a INT PRIMARY KEY AUTOINCREMENT, v VARBINARY);]])
> +box.execute([[CREATE TABLE ts (a INT PRIMARY KEY AUTOINCREMENT, s SCALAR);]])
> +
> +box.execute([[INSERT INTO ti(i) VALUES (NULL);]])
> +box.execute([[INSERT INTO ti(i) VALUES (11);]])
> +box.execute([[INSERT INTO ti(i) VALUES (100000000000000000000000000000000.1);]])
> +box.execute([[INSERT INTO ti(i) VALUES (33.0);]])
> +box.execute([[INSERT INTO ti(i) VALUES (true);]])
> +box.execute([[INSERT INTO ti(i) VALUES ('33');]])
> +box.execute([[INSERT INTO ti(i) VALUES (X'3434');]])
> +box.execute([[SELECT * FROM ti;]])
> +
> +box.execute([[INSERT INTO td(d) VALUES (NULL);]])
> +box.execute([[INSERT INTO td(d) VALUES (11);]])
> +box.execute([[INSERT INTO td(d) VALUES (100000000000000001);;]])
> +box.execute([[INSERT INTO td(d) VALUES (22.2);]])
> +box.execute([[INSERT INTO td(d) VALUES (true);]])
> +box.execute([[INSERT INTO td(d) VALUES ('33');]])
> +box.execute([[INSERT INTO td(d) VALUES (X'3434');]])
> +box.execute([[SELECT * FROM td;]])
> +
> +box.execute([[INSERT INTO tb(b) VALUES (NULL);]])
> +box.execute([[INSERT INTO tb(b) VALUES (11);]])
> +box.execute([[INSERT INTO tb(b) VALUES (22.2);]])
> +box.execute([[INSERT INTO tb(b) VALUES (true);]])
> +box.execute([[INSERT INTO tb(b) VALUES ('33');]])
> +box.execute([[INSERT INTO tb(b) VALUES (X'3434');]])
> +box.execute([[SELECT * FROM tb;]])
> +
> +box.execute([[INSERT INTO tt(t) VALUES (NULL);]])
> +box.execute([[INSERT INTO tt(t) VALUES (11);]])
> +box.execute([[INSERT INTO tt(t) VALUES (22.2);]])
> +box.execute([[INSERT INTO tt(t) VALUES (true);]])
> +box.execute([[INSERT INTO tt(t) VALUES ('33');]])
> +box.execute([[INSERT INTO tt(t) VALUES (X'3434');]])
> +box.execute([[SELECT * FROM tt;]])
> +
> +box.execute([[INSERT INTO tv(v) VALUES (NULL);]])
> +box.execute([[INSERT INTO tv(v) VALUES (11);]])
> +box.execute([[INSERT INTO tv(v) VALUES (22.2);]])
> +box.execute([[INSERT INTO tv(v) VALUES (true);]])
> +box.execute([[INSERT INTO tv(v) VALUES ('33');]])
> +box.execute([[INSERT INTO tv(v) VALUES (X'3434');]])
> +box.execute([[SELECT * FROM tv;]])
> +
> +box.execute([[INSERT INTO ts(s) VALUES (NULL);]])
> +box.execute([[INSERT INTO ts(s) VALUES (11);]])
> +box.execute([[INSERT INTO ts(s) VALUES (22.2);]])
> +box.execute([[INSERT INTO ts(s) VALUES (true);]])
> +box.execute([[INSERT INTO ts(s) VALUES ('33');]])
> +box.execute([[INSERT INTO ts(s) VALUES (X'3434');]])
> +box.execute([[SELECT * FROM ts;]])
> +
> +-- Check for UPDATE.
> +box.execute([[DELETE FROM ti;]])
> +box.execute([[DELETE FROM td;]])
> +box.execute([[DELETE FROM tb;]])
> +box.execute([[DELETE FROM tt;]])
> +box.execute([[DELETE FROM tv;]])
> +box.execute([[DELETE FROM ts;]])
> +box.execute([[INSERT INTO ti VALUES(1, NULL);]])
> +box.execute([[INSERT INTO td VALUES(1, NULL);]])
> +box.execute([[INSERT INTO tb VALUES(1, NULL);]])
> +box.execute([[INSERT INTO tt VALUES(1, NULL);]])
> +box.execute([[INSERT INTO tv VALUES(1, NULL);]])
> +box.execute([[INSERT INTO ts VALUES(1, NULL);]])
> +box.execute([[SELECT * FROM ti, td, tb, tt, tv, ts;]])
> +
> +box.execute([[UPDATE ti SET i = NULL WHERE a = 1;]])
> +box.execute([[UPDATE ti SET i = 11 WHERE a = 1;]])
> +box.execute([[UPDATE ti SET i = 100000000000000000000000000000000.1 WHERE a = 1;]])
> +box.execute([[UPDATE ti SET i = 33.0 WHERE a = 1;]])
> +box.execute([[UPDATE ti SET i = true WHERE a = 1;]])
> +box.execute([[UPDATE ti SET i = '33' WHERE a = 1;]])
> +box.execute([[UPDATE ti SET i = X'3434' WHERE a = 1;]])
> +box.execute([[SELECT * FROM ti;]])
> +
> +box.execute([[UPDATE td SET d = NULL WHERE a = 1;]])
> +box.execute([[UPDATE td SET d = 11 WHERE a = 1;]])
> +box.execute([[UPDATE td SET d = 100000000000000001 WHERE a = 1;]])
> +box.execute([[UPDATE td SET d = 22.2 WHERE a = 1;]])
> +box.execute([[UPDATE td SET d = true WHERE a = 1;]])
> +box.execute([[UPDATE td SET d = '33' WHERE a = 1;]])
> +box.execute([[UPDATE td SET d = X'3434' WHERE a = 1;]])
> +box.execute([[SELECT * FROM td;]])
> +
> +box.execute([[UPDATE tb SET b = NULL WHERE a = 1;]])
> +box.execute([[UPDATE tb SET b = 11 WHERE a = 1;]])
> +box.execute([[UPDATE tb SET b = 22.2 WHERE a = 1;]])
> +box.execute([[UPDATE tb SET b = true WHERE a = 1;]])
> +box.execute([[UPDATE tb SET b = '33' WHERE a = 1;]])
> +box.execute([[UPDATE tb SET b = X'3434' WHERE a = 1;]])
> +box.execute([[SELECT * FROM tb;]])
> +
> +box.execute([[UPDATE tt SET t = NULL WHERE a = 1;]])
> +box.execute([[UPDATE tt SET t = 11 WHERE a = 1;]])
> +box.execute([[UPDATE tt SET t = 22.2 WHERE a = 1;]])
> +box.execute([[UPDATE tt SET t = true WHERE a = 1;]])
> +box.execute([[UPDATE tt SET t = '33' WHERE a = 1;]])
> +box.execute([[UPDATE tt SET t = X'3434' WHERE a = 1;]])
> +box.execute([[SELECT * FROM tt;]])
> +
> +box.execute([[UPDATE tv SET v = NULL WHERE a = 1;]])
> +box.execute([[UPDATE tv SET v = 11 WHERE a = 1;]])
> +box.execute([[UPDATE tv SET v = 22.2 WHERE a = 1;]])
> +box.execute([[UPDATE tv SET v = true WHERE a = 1;]])
> +box.execute([[UPDATE tv SET v = '33' WHERE a = 1;]])
> +box.execute([[UPDATE tv SET v = X'3434' WHERE a = 1;]])
> +box.execute([[SELECT * FROM tv;]])
> +
> +box.execute([[UPDATE ts SET s = NULL WHERE a = 1;]])
> +box.execute([[UPDATE ts SET s = 11 WHERE a = 1;]])
> +box.execute([[UPDATE ts SET s = 22.2 WHERE a = 1;]])
> +box.execute([[UPDATE ts SET s = true WHERE a = 1;]])
> +box.execute([[UPDATE ts SET s = '33' WHERE a = 1;]])
> +box.execute([[UPDATE ts SET s = X'3434' WHERE a = 1;]])
> +box.execute([[SELECT * FROM ts;]])
> --
> 2.25.1
>
Diff:
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 41a4750da..14ddb5160 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -434,50 +434,35 @@ mem_is_type_compatible(struct Mem *mem, enum field_type type)
}
/**
- * Convert the numeric value contained in MEM to double. If the
- * is_precise flag is set, the conversion will succeed only if it
- * is lossless.
+ * Convert the numeric value contained in MEM to double.
*
* @param mem The MEM that contains the numeric value.
- * @param is_precise Flag.
* @retval 0 if the conversion was successful, -1 otherwise.
*/
static int
-mem_convert_to_double(struct Mem *mem, bool is_precise)
+mem_convert_to_double(struct Mem *mem)
{
if ((mem->flags & MEM_Real) != 0)
return 0;
if ((mem->flags & (MEM_Int | MEM_UInt)) == 0)
return -1;
- if ((mem->flags & MEM_Int) != 0) {
- int64_t i = mem->u.i;
- double d = (double)i;
- if (!is_precise || i == (int64_t)d)
- mem_set_double(mem, d);
- else
- return -1;
- } else {
- uint64_t u = mem->u.u;
- double d = (double)u;
- if (!is_precise || u == (uint64_t)d)
- mem_set_double(mem, d);
- else
- return -1;
- }
+ double d;
+ if ((mem->flags & MEM_Int) != 0)
+ d = (double)mem->u.i;
+ else
+ d = (double)mem->u.u;
+ mem_set_double(mem, d);
return 0;
}
/**
- * Convert the numeric value contained in MEM to unsigned. If the
- * is_precise flag is set, the conversion will succeed only if it
- * is lossless.
+ * Convert the numeric value contained in MEM to unsigned.
*
* @param mem The MEM that contains the numeric value.
- * @param is_precise Flag.
* @retval 0 if the conversion was successful, -1 otherwise.
*/
static int
-mem_convert_to_unsigned(struct Mem *mem, bool is_precise)
+mem_convert_to_unsigned(struct Mem *mem)
{
if ((mem->flags & MEM_UInt) != 0)
return 0;
@@ -488,25 +473,18 @@ mem_convert_to_unsigned(struct Mem *mem, bool is_precise)
double d = mem->u.r;
if (d < 0.0 || d >= (double)UINT64_MAX)
return -1;
- uint64_t u = (uint64_t)d;
- if (!is_precise || d == (double)u)
- mem_set_u64(mem, (uint64_t) d);
- else
- return -1;
+ mem_set_u64(mem, (uint64_t) d);
return 0;
}
/**
- * Convert the numeric value contained in MEM to integer. If the
- * is_precise flag is set, the conversion will succeed only if it
- * is lossless.
+ * Convert the numeric value contained in MEM to integer.
*
* @param mem The MEM that contains the numeric value.
- * @param is_precise Flag.
* @retval 0 if the conversion was successful, -1 otherwise.
*/
static int
-mem_convert_to_integer(struct Mem *mem, bool is_precise)
+mem_convert_to_integer(struct Mem *mem)
{
if ((mem->flags & (MEM_UInt | MEM_Int)) != 0)
return 0;
@@ -515,44 +493,33 @@ mem_convert_to_integer(struct Mem *mem, bool is_precise)
double d = mem->u.r;
if (d >= (double)UINT64_MAX || d < (double)INT64_MIN)
return -1;
- if (d < (double)INT64_MAX) {
- int64_t i = (int64_t)d;
- if (!is_precise || d == (double)i)
- mem_set_int(mem, (int64_t) d, d < 0);
- else
- return -1;
- } else {
- uint64_t u = (uint64_t)d;
- if (!is_precise || d == (double)u)
- mem_set_int(mem, (uint64_t) d, false);
- else
- return -1;
- }
+ if (d < (double)INT64_MAX)
+ mem_set_int(mem, (int64_t) d, d < 0);
+ else
+ mem_set_int(mem, (uint64_t) d, false);
return 0;
}
/**
* Convert the numeric value contained in MEM to another numeric
- * type. If the is_precise flag is set, the conversion will
- * succeed only if it is lossless.
+ * type.
*
* @param mem The MEM that contains the numeric value.
* @param type The type to convert to.
- * @param is_precise Flag.
* @retval 0 if the conversion was successful, -1 otherwise.
*/
static int
-mem_convert_to_numeric(struct Mem *mem, enum field_type type, bool is_precise)
+mem_convert_to_numeric(struct Mem *mem, enum field_type type)
{
assert(mp_type_is_numeric(mem_mp_type(mem)) &&
sql_type_is_numeric(type));
assert(type != FIELD_TYPE_NUMBER);
if (type == FIELD_TYPE_DOUBLE)
- return mem_convert_to_double(mem, is_precise);
+ return mem_convert_to_double(mem);
if (type == FIELD_TYPE_UNSIGNED)
- return mem_convert_to_unsigned(mem, is_precise);
+ return mem_convert_to_unsigned(mem);
assert(type == FIELD_TYPE_INTEGER);
- return mem_convert_to_integer(mem, is_precise);
+ return mem_convert_to_integer(mem);
}
/*
@@ -2908,7 +2875,7 @@ case OP_ApplyType: {
if (!mp_type_is_numeric(mem_mp_type(pIn1)))
goto type_mismatch;
/* Try to convert numeric-to-numeric. */
- if (mem_convert_to_numeric(pIn1, type, false) != 0)
+ if (mem_convert_to_numeric(pIn1, type) != 0)
goto type_mismatch;
}
pIn1++;
New patch:
>From 92e6b17ecc58833b37bf91bef6bd218e1c5cdc54 Mon Sep 17 00:00:00 2001
From: Mergen Imeev <imeevma at gmail.com>
Date: Wed, 27 May 2020 13:49:11 +0300
Subject: [PATCH] sql: change implicit cast for assignment
This patch changes implicit cast for assignment.
Closes #3809
Needed for #4159
Part of #4230
@TarantoolBot document
Title: change implicit cast for comparison
After this patch, these rules will apply during the implicit cast:
| | STRING | BINARY | BOOLEAN | INTEGER | UNSIGNED | DOUBLE |
| :--- | :---: | :---: | :---: | :---: | :---: | :---: |
| STRING | A | N | N | N | N | N |
| BINARY | N | A | N | N | N | N |
| BOOLEAN | N | N | A | N | N | N |
| INTEGER | N | N | N | A | S | Aa |
| UNSIGNED | N | N | N | A | A | Aa |
| DOUBLE | N | N | N | Sa | Sa | A |
In this table, the types of the assigned value are on the left,
and the types that should be after the assignment are at the top.
'A' - the assignment will be completed.
'N' - the assignment won't be completed.
'S' - the appointment may be unsuccessful.
'a' - after assignment, the resulting value may be approximated.
Rules for numeric values are these:
1) Loss of significant digits (overflow) is an error.
2) Loss of insignificant digits is not an error.
Example:
```
tarantool> box.execute([[CREATE TABLE t1(i INT PRIMARY KEY);]])
tarantool> box.execute([[INSERT INTO t1 VALUES ('1');]])
---
- null
- 'Type mismatch: can not convert 1 to integer'
...
tarantool> box.execute('INSERT INTO t1 VALUES (1.2345);')
tarantool> box.execute('SELECT * FROM t1;')
---
- metadata:
- name: I
type: integer
rows:
- [1]
...
tarantool> box.execute([[CREATE TABLE t2(t text PRIMARY KEY);]])
tarantool> box.execute([[INSERT INTO t2 VALUES (1);]])
---
- null
- 'Type mismatch: can not convert 1 to string'
...
```
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 950f72ddd..14ddb5160 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -417,6 +417,111 @@ sql_value_apply_type(
mem_apply_type((Mem *) pVal, type);
}
+/**
+ * Check that MEM_type of the mem is compatible with given type.
+ *
+ * @param mem The MEM that contains the value to check.
+ * @param type The type to check.
+ * @retval TRUE if the MEM_type of the value and the given type
+ * are compatible, FALSE otherwise.
+ */
+static bool
+mem_is_type_compatible(struct Mem *mem, enum field_type type)
+{
+ enum mp_type mp_type = mem_mp_type(mem);
+ assert(mp_type < MP_EXT);
+ return field_mp_plain_type_is_compatible(type, mp_type, true);
+}
+
+/**
+ * Convert the numeric value contained in MEM to double.
+ *
+ * @param mem The MEM that contains the numeric value.
+ * @retval 0 if the conversion was successful, -1 otherwise.
+ */
+static int
+mem_convert_to_double(struct Mem *mem)
+{
+ if ((mem->flags & MEM_Real) != 0)
+ return 0;
+ if ((mem->flags & (MEM_Int | MEM_UInt)) == 0)
+ return -1;
+ double d;
+ if ((mem->flags & MEM_Int) != 0)
+ d = (double)mem->u.i;
+ else
+ d = (double)mem->u.u;
+ mem_set_double(mem, d);
+ return 0;
+}
+
+/**
+ * Convert the numeric value contained in MEM to unsigned.
+ *
+ * @param mem The MEM that contains the numeric value.
+ * @retval 0 if the conversion was successful, -1 otherwise.
+ */
+static int
+mem_convert_to_unsigned(struct Mem *mem)
+{
+ if ((mem->flags & MEM_UInt) != 0)
+ return 0;
+ if ((mem->flags & MEM_Int) != 0)
+ return -1;
+ if ((mem->flags & MEM_Real) == 0)
+ return -1;
+ double d = mem->u.r;
+ if (d < 0.0 || d >= (double)UINT64_MAX)
+ return -1;
+ mem_set_u64(mem, (uint64_t) d);
+ return 0;
+}
+
+/**
+ * Convert the numeric value contained in MEM to integer.
+ *
+ * @param mem The MEM that contains the numeric value.
+ * @retval 0 if the conversion was successful, -1 otherwise.
+ */
+static int
+mem_convert_to_integer(struct Mem *mem)
+{
+ if ((mem->flags & (MEM_UInt | MEM_Int)) != 0)
+ return 0;
+ if ((mem->flags & MEM_Real) == 0)
+ return -1;
+ double d = mem->u.r;
+ if (d >= (double)UINT64_MAX || d < (double)INT64_MIN)
+ return -1;
+ if (d < (double)INT64_MAX)
+ mem_set_int(mem, (int64_t) d, d < 0);
+ else
+ mem_set_int(mem, (uint64_t) d, false);
+ return 0;
+}
+
+/**
+ * Convert the numeric value contained in MEM to another numeric
+ * type.
+ *
+ * @param mem The MEM that contains the numeric value.
+ * @param type The type to convert to.
+ * @retval 0 if the conversion was successful, -1 otherwise.
+ */
+static int
+mem_convert_to_numeric(struct Mem *mem, enum field_type type)
+{
+ assert(mp_type_is_numeric(mem_mp_type(mem)) &&
+ sql_type_is_numeric(type));
+ assert(type != FIELD_TYPE_NUMBER);
+ if (type == FIELD_TYPE_DOUBLE)
+ return mem_convert_to_double(mem);
+ if (type == FIELD_TYPE_UNSIGNED)
+ return mem_convert_to_unsigned(mem);
+ assert(type == FIELD_TYPE_INTEGER);
+ return mem_convert_to_integer(mem);
+}
+
/*
* 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
@@ -2747,11 +2852,11 @@ case OP_Fetch: {
/* Opcode: ApplyType P1 P2 * P4 *
* Synopsis: type(r[P1 at P2])
*
- * Apply types to a range of P2 registers starting with P1.
- *
- * P4 is a string that is P2 characters long. The nth character of the
- * string indicates the column type that should be used for the nth
- * memory cell in the range.
+ * Check that types of P2 registers starting from register P1 are
+ * compatible with given field types in P4. If the MEM_type of the
+ * value and the given type are incompatible according to
+ * field_mp_plain_type_is_compatible(), but both are numeric,
+ * this opcode attempts to convert the value to the type.
*/
case OP_ApplyType: {
enum field_type *types = pOp->p4.types;
@@ -2762,13 +2867,23 @@ case OP_ApplyType: {
while((type = *(types++)) != field_type_MAX) {
assert(pIn1 <= &p->aMem[(p->nMem+1 - p->nCursor)]);
assert(memIsValid(pIn1));
- if (mem_apply_type(pIn1, type) != 0) {
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
- sql_value_to_diag_str(pIn1),
- field_type_strs[type]);
- goto abort_due_to_error;
+ if (!mem_is_type_compatible(pIn1, type)) {
+ /* Implicit cast is allowed only to numeric type. */
+ if (!sql_type_is_numeric(type))
+ goto type_mismatch;
+ /* Implicit cast is allowed only from numeric type. */
+ if (!mp_type_is_numeric(mem_mp_type(pIn1)))
+ goto type_mismatch;
+ /* Try to convert numeric-to-numeric. */
+ if (mem_convert_to_numeric(pIn1, type) != 0)
+ goto type_mismatch;
}
pIn1++;
+ continue;
+type_mismatch:
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+ sql_value_to_diag_str(pIn1), field_type_strs[type]);
+ goto abort_due_to_error;
}
break;
}
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index 44c27bdb7..ad46ab129 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -566,6 +566,10 @@ mem_mp_type(struct Mem *mem);
*/
#define mp_type_is_bloblike(X) ((X) == MP_BIN || (X) == MP_ARRAY || (X) == MP_MAP)
+/** Return TRUE if MP_type of X is numeric, FALSE otherwise. */
+#define mp_type_is_numeric(X) ((X) == MP_INT || (X) == MP_UINT ||\
+ (X) == MP_DOUBLE)
+
/**
* Memory cell mem contains the context of an aggregate function.
* This routine calls the finalize method for that function. The
diff --git a/test/sql-tap/autoinc.test.lua b/test/sql-tap/autoinc.test.lua
index 37e65e541..07442b60a 100755
--- a/test/sql-tap/autoinc.test.lua
+++ b/test/sql-tap/autoinc.test.lua
@@ -694,7 +694,7 @@ test:do_test(
INSERT INTO t3928(b) VALUES('after-int-' || CAST(new.b AS TEXT));
END;
DELETE FROM t3928 WHERE a!=1;
- UPDATE t3928 SET b=456 WHERE a=1;
+ UPDATE t3928 SET b='456' WHERE a=1;
SELECT * FROM t3928 ORDER BY a;
]])
end, {
diff --git a/test/sql-tap/default.test.lua b/test/sql-tap/default.test.lua
index d3e35c71c..f1def2b10 100755
--- a/test/sql-tap/default.test.lua
+++ b/test/sql-tap/default.test.lua
@@ -109,16 +109,12 @@ test:do_execsql_test(
f VARCHAR(15), --COLLATE RTRIM,
g INTEGER DEFAULT( 3600*12 )
);
- INSERT INTO t3 VALUES(null, 5, 'row1', 5.25, 8.67, 321, 432);
+ INSERT INTO t3 VALUES(null, 5, 'row1', 5.25, 8.67, '321', 432);
SELECT a, typeof(a), b, typeof(b), c, typeof(c),
d, typeof(d), e, typeof(e), f, typeof(f),
g, typeof(g) FROM t3;
]], {
-- <default-3.1>
- -- TODO: In original test "321" is not a string, its a value.
- -- In current situation I don't know what to do, need Kirill's
- -- advice.
- -- Bulat
1, "integer", 5, "integer", "row1", "string", 5.25, "number", 8.67, "number", "321", "string", 432, "integer"
-- </default-3.1>
})
diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua
index 1d3b964b9..578620fca 100755
--- a/test/sql-tap/e_select1.test.lua
+++ b/test/sql-tap/e_select1.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(510)
+test:plan(509)
--!./tcltestrunner.lua
-- 2010 July 16
@@ -753,11 +753,11 @@ test:do_execsql_test(
CREATE TABLE z3(id INT primary key, a NUMBER, b NUMBER);
INSERT INTO z1 VALUES(1, 51.65, -59.58, 'belfries');
- INSERT INTO z1 VALUES(2, -5, NULL, 75);
+ INSERT INTO z1 VALUES(2, -5, NULL, '75');
INSERT INTO z1 VALUES(3, -2.2, -23.18, 'suiters');
INSERT INTO z1 VALUES(4, NULL, 67, 'quartets');
INSERT INTO z1 VALUES(5, -1.04, -32.3, 'aspen');
- INSERT INTO z1 VALUES(6, 63, '0', -26);
+ INSERT INTO z1 VALUES(6, 63, 0, '-26');
INSERT INTO z2 VALUES(1, NULL, 21);
INSERT INTO z2 VALUES(2, 36.0, 6.0);
@@ -1457,13 +1457,13 @@ test:do_execsql_test(
CREATE TABLE q2(id INT primary key, d TEXT, e NUMBER);
CREATE TABLE q3(id INT primary key, f TEXT, g INT);
- INSERT INTO q1 VALUES(1, 16, -87.66, NULL);
+ INSERT INTO q1 VALUES(1, '16', -87.66, NULL);
INSERT INTO q1 VALUES(2, 'legible', 94, -42.47);
INSERT INTO q1 VALUES(3, 'beauty', 36, NULL);
INSERT INTO q2 VALUES(1, 'legible', 1);
INSERT INTO q2 VALUES(2, 'beauty', 2);
- INSERT INTO q2 VALUES(3, -65, 4);
+ INSERT INTO q2 VALUES(3, '-65', 4);
INSERT INTO q2 VALUES(4, 'emanating', -16.56);
INSERT INTO q3 VALUES(1, 'beauty', 2);
@@ -1603,7 +1603,7 @@ test:do_execsql_test(
CREATE TABLE w2(a INT PRIMARY KEY, b TEXT);
INSERT INTO w1 VALUES('1', 4.1);
- INSERT INTO w2 VALUES(1, 4.1);
+ INSERT INTO w2 VALUES(1, '4.1');
]], {
-- <e_select-7.10.0>
@@ -2150,7 +2150,6 @@ test:do_select_tests(
{"2", "SELECT b FROM f1 ORDER BY a LIMIT 2+3 ", {"a", "b", "c", "d", "e"}},
{"3", "SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b = 'e') ", {"a", "b", "c", "d", "e"}},
{"4", "SELECT b FROM f1 ORDER BY a LIMIT 5.0 ", {"a", "b", "c", "d", "e"}},
- {"5", "SELECT b FROM f1 ORDER BY a LIMIT '5' ", {"a", "b", "c", "d", "e"}},
})
-- EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value
@@ -2195,7 +2194,7 @@ test:do_select_tests(
{"1", "SELECT b FROM f1 ORDER BY a LIMIT 0 ", {}},
{"2", "SELECT b FROM f1 ORDER BY a DESC LIMIT 4 ", {"z", "y", "x", "w"}},
{"3", "SELECT b FROM f1 ORDER BY a DESC LIMIT 8 ", {"z", "y", "x", "w", "v", "u", "t", "s"}},
- {"4", "SELECT b FROM f1 ORDER BY a DESC LIMIT '12' ", {"z", y, "x", "w", "v", "u", "t", "s", "r", "q", "p", "o"}},
+ {"4", "SELECT b FROM f1 ORDER BY a DESC LIMIT 12 ", {"z", y, "x", "w", "v", "u", "t", "s", "r", "q", "p", "o"}},
})
-- EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return
@@ -2240,10 +2239,10 @@ test:do_select_tests(
{"1", "SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 5", {"f", "g", "h", "i", "j", "k", "l", "m", "n", "o"}},
{"2", "SELECT b FROM f1 ORDER BY a LIMIT 2+3 OFFSET 10", {"k", "l", "m", "n", "o"}},
{"3", "SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b='j') OFFSET (SELECT a FROM f1 WHERE b='b') ", {"c", "d", "e", "f", "g", "h", "i", "j", "k", "l"}},
- {"4", "SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 3.0 ", {"d", "e", "f", "g", "h"}},
- {"5", "SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 0 ", {"a", "b", "c", "d", "e"}},
+ {"4", "SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 3.0 ", {"d", "e", "f", "g", "h"}},
+ {"5", "SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0 ", {"a", "b", "c", "d", "e"}},
{"6", "SELECT b FROM f1 ORDER BY a LIMIT 0 OFFSET 10 ", {}},
- {"7", "SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET '1'||'5' ", {"p", "q", "r"}},
+ {"7", "SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET CAST('1'||'5' AS INTEGER) ", {"p", "q", "r"}},
})
-- EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than
@@ -2279,10 +2278,10 @@ test:do_select_tests(
{"1", "SELECT b FROM f1 ORDER BY a LIMIT 5, 10 ", {"f", "g", "h", "i", "j", "k", "l", "m", "n", "o"}},
{"2", "SELECT b FROM f1 ORDER BY a LIMIT 10, 2+3 ", {"k", "l", "m", "n", "o"}},
{"3", "SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b='b'), (SELECT a FROM f1 WHERE b='j')", {"c", "d", "e", "f", "g", "h", "i", "j", "k", "l"}},
- {"4", "SELECT b FROM f1 ORDER BY a LIMIT 3.0, '5' ", {"d", "e", "f", "g", "h"}},
- {"5", "SELECT b FROM f1 ORDER BY a LIMIT 0, '5' ", {"a", "b", "c", "d", "e"}},
+ {"4", "SELECT b FROM f1 ORDER BY a LIMIT 3.0, 5 ", {"d", "e", "f", "g", "h"}},
+ {"5", "SELECT b FROM f1 ORDER BY a LIMIT 0, 5 ", {"a", "b", "c", "d", "e"}},
{"6", "SELECT b FROM f1 ORDER BY a LIMIT 10, 0 ", {}},
- {"7", "SELECT b FROM f1 ORDER BY a LIMIT '1'||'5', 3 ", {"p", "q", "r"}},
+ {"7", "SELECT b FROM f1 ORDER BY a LIMIT CAST('1'||'5' AS INTEGER), 3 ", {"p", "q", "r"}},
{"8", "SELECT b FROM f1 ORDER BY a LIMIT 20, 10 ", {"u", "v", "w", "x", "y", "z"}},
{"9", "SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 ", {4, 3, 2, 1}},
{"10", "SELECT b FROM f1 ORDER BY a LIMIT 0, 5 ", {"a", "b", "c", "d", "e"}},
diff --git a/test/sql-tap/in3.test.lua b/test/sql-tap/in3.test.lua
index e29db9d93..a6d842962 100755
--- a/test/sql-tap/in3.test.lua
+++ b/test/sql-tap/in3.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(29)
+test:plan(28)
--!./tcltestrunner.lua
-- 2007 November 29
@@ -322,18 +322,6 @@ test:do_test(
-- </in3-3.2>
})
-test:do_test(
- "in3-3.3",
- function()
- -- Logically, numeric affinity is applied to both sides before
- -- the comparison, but index can't be used.
- return exec_neph(" SELECT x IN (SELECT b FROM t1) FROM t2 ")
- end, {
- -- <in3-3.3>
- 1, true
- -- </in3-3.3>
- })
-
test:do_test(
"in3-3.4",
function()
diff --git a/test/sql-tap/in4.test.lua b/test/sql-tap/in4.test.lua
index 8c6917379..5c01ccdab 100755
--- a/test/sql-tap/in4.test.lua
+++ b/test/sql-tap/in4.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(61)
+test:plan(52)
--!./tcltestrunner.lua
-- 2008 September 1
@@ -140,7 +140,7 @@ test:do_execsql_test(
test:do_execsql_test(
"in4-2.7",
[[
- SELECT b FROM t2 WHERE a IN ('1', '2')
+ SELECT b FROM t2 WHERE a IN (1, 2)
]], {
-- <in4-2.7>
"one", "two"
@@ -585,98 +585,6 @@ test:do_execsql_test(
-- </in4-4.6>
})
-test:do_execsql_test(
- "in4-4.11",
- [[
- CREATE TABLE t4b(a TEXT, b NUMBER, c INT PRIMARY KEY);
- INSERT INTO t4b VALUES('1.0',1,4);
- SELECT c FROM t4b WHERE a=b;
- ]], {
- -- <in4-4.11>
- 4
- -- </in4-4.11>
- })
-
-test:do_execsql_test(
- "in4-4.12",
- [[
- SELECT c FROM t4b WHERE b=a;
- ]], {
- -- <in4-4.12>
- 4
- -- </in4-4.12>
- })
-
-test:do_execsql_test(
- "in4-4.13",
- [[
- SELECT c FROM t4b WHERE +a=b;
- ]], {
- -- <in4-4.13>
- 4
- -- </in4-4.13>
- })
-
-test:do_execsql_test(
- "in4-4.14",
- [[
- SELECT c FROM t4b WHERE a=+b;
- ]], {
- -- <in4-4.14>
- 4
- -- </in4-4.14>
- })
-
-test:do_execsql_test(
- "in4-4.15",
- [[
- SELECT c FROM t4b WHERE +b=a;
- ]], {
- -- <in4-4.15>
- 4
- -- </in4-4.15>
- })
-
-test:do_execsql_test(
- "in4-4.16",
- [[
- SELECT c FROM t4b WHERE b=+a;
- ]], {
- -- <in4-4.16>
- 4
- -- </in4-4.16>
- })
-
-test:do_execsql_test(
- "in4-4.17",
- [[
- SELECT c FROM t4b WHERE a IN (b);
- ]], {
- -- <in4-4.17>
- 4
- -- </in4-4.17>
- })
-
-test:do_execsql_test(
- "in4-4.18",
- [[
- SELECT c FROM t4b WHERE b IN (a);
- ]], {
- -- <in4-4.18>
- 4
- -- </in4-4.18>
- })
-
-test:do_execsql_test(
- "in4-4.19",
- [[
- SELECT c FROM t4b WHERE +b IN (a);
- ]], {
- -- <in4-4.19>
- 4
- -- </in4-4.19>
- })
-
-- MUST_WORK_TEST
-- Tarantool: TBI: Need to support collations. Depends on #2121
-- test:do_execsql_test(
diff --git a/test/sql-tap/index1.test.lua b/test/sql-tap/index1.test.lua
index e173e685c..ce66b7c1e 100755
--- a/test/sql-tap/index1.test.lua
+++ b/test/sql-tap/index1.test.lua
@@ -593,25 +593,17 @@ test:do_test(
-- </index-11.1>
})
end
--- integrity_check index-11.2
--- Numeric strings should compare as if they were numbers. So even if the
--- strings are not character-by-character the same, if they represent the
--- same number they should compare equal to one another. Verify that this
--- is true in indices.
---
--- Updated for sql v3: sql will now store these values as numbers
--- (because the affinity of column a is NUMERIC) so the quirky
--- representations are not retained. i.e. '+1.0' becomes '1'.
+
test:do_execsql_test(
"index-12.1",
[[
CREATE TABLE t4(id INT primary key, a NUMBER,b INT );
- INSERT INTO t4 VALUES(1, '0.0',1);
- INSERT INTO t4 VALUES(2, '0.00',2);
- INSERT INTO t4 VALUES(4, '-1.0',4);
- INSERT INTO t4 VALUES(5, '+1.0',5);
- INSERT INTO t4 VALUES(6, '0',6);
- INSERT INTO t4 VALUES(7, '00000',7);
+ INSERT INTO t4 VALUES(1, 0.0, 1);
+ INSERT INTO t4 VALUES(2, 0.00, 2);
+ INSERT INTO t4 VALUES(4, -1.0, 4);
+ INSERT INTO t4 VALUES(5, +1.0, 5);
+ INSERT INTO t4 VALUES(6, 0, 6);
+ INSERT INTO t4 VALUES(7, 00000, 7);
SELECT a FROM t4 ORDER BY b;
]], {
-- <index-12.1>
@@ -692,7 +684,7 @@ test:do_execsql_test(
c TEXT,
UNIQUE(a,c)
);
- INSERT INTO t5 VALUES(1,2,3);
+ INSERT INTO t5 VALUES(1,2,'3');
SELECT * FROM t5;
]], {
-- <index-13.1>
diff --git a/test/sql-tap/insert3.test.lua b/test/sql-tap/insert3.test.lua
index 43bb06630..b92bc508e 100755
--- a/test/sql-tap/insert3.test.lua
+++ b/test/sql-tap/insert3.test.lua
@@ -60,7 +60,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;
- INSERT OR IGNORE INTO log2(x, y) VALUES(new.b,1);
+ INSERT OR IGNORE INTO log2(x, y) VALUES(CAST(new.b AS STRING),1);
END;
INSERT INTO t1(a, b) VALUES('hi', 453);
SELECT x,y FROM log ORDER BY x;
@@ -129,8 +129,8 @@ test:do_execsql_test(
INSERT INTO t2dup(a,b,c) VALUES(new.a,new.b,new.c);
END;
INSERT INTO t2(a) VALUES(123);
- INSERT INTO t2(b) VALUES(234);
- INSERT INTO t2(c) VALUES(345);
+ INSERT INTO t2(b) VALUES('234');
+ INSERT INTO t2(c) VALUES('345');
SELECT * FROM t2dup;
]], {
-- <insert3-2.1>
@@ -143,8 +143,8 @@ test:do_execsql_test(
[[
DELETE FROM t2dup;
INSERT INTO t2(a) SELECT 1 FROM t1 LIMIT 1;
- INSERT INTO t2(b) SELECT 987 FROM t1 LIMIT 1;
- INSERT INTO t2(c) SELECT 876 FROM t1 LIMIT 1;
+ INSERT INTO t2(b) SELECT '987' FROM t1 LIMIT 1;
+ INSERT INTO t2(c) SELECT '876' FROM t1 LIMIT 1;
SELECT * FROM t2dup;
]], {
-- <insert3-2.2>
diff --git a/test/sql-tap/intpkey.test.lua b/test/sql-tap/intpkey.test.lua
index b6b186632..684a24114 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(31)
--!./tcltestrunner.lua
-- 2001 September 15
@@ -770,142 +770,13 @@ test:do_execsql_test(
-- </intpkey-11.1>
})
--- integrity_check intpkey-12.1
--- Try to use a string that looks like a floating point number as
--- an integer primary key. This should actually work when the floating
--- point value can be rounded to an integer without loss of data.
---
-test:do_execsql_test(
- "intpkey-13.1",
- [[
- SELECT * FROM t1 WHERE a=1;
- ]], {
- -- <intpkey-13.1>
-
- -- </intpkey-13.1>
- })
-
-test:do_execsql_test(
- "intpkey-13.2",
- [[
- INSERT INTO t1 VALUES('1',2,3);
- SELECT * FROM t1 WHERE a=1;
- ]], {
- -- <intpkey-13.2>
- 1, "2", "3"
- -- </intpkey-13.2>
- })
-
--- MUST_WORK_TEST
-if (0 > 0) then
- -- Tarantool: issue submitted #2315
- test:do_catchsql_test(
- "intpkey-13.3",
- [[
- INSERT INTO t1 VALUES('1.5',3,4);
- ]], {
- -- <intpkey-13.3>
- 1, "datatype mismatch"
- -- </intpkey-13.3>
- })
-
- test:do_catchsql_test(
- "intpkey-13.4",
- [[
- INSERT INTO t1 VALUES(x'123456',3,4);
- ]], {
- -- <intpkey-13.4>
- 1, "datatype mismatch"
- -- </intpkey-13.4>
- })
-
-
-
-end
-test:do_catchsql_test(
- "intpkey-13.5",
- [[
- INSERT INTO t1 VALUES('+1234567890',3,4);
- ]], {
- -- <intpkey-13.5>
- 0
- -- </intpkey-13.5>
- })
-
--- Compare an INTEGER PRIMARY KEY against a TEXT expression. The INTEGER
--- affinity should be applied to the text value before the comparison
--- takes place.
---
-test:do_execsql_test(
- "intpkey-14.1",
- [[
- CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT);
- INSERT INTO t3 VALUES(1, 1, 'one');
- INSERT INTO t3 VALUES(2, 2, '2');
- INSERT INTO t3 VALUES(3, 3, 3);
- ]], {
- -- <intpkey-14.1>
-
- -- </intpkey-14.1>
- })
-
-test:do_execsql_test(
- "intpkey-14.2",
- [[
- SELECT * FROM t3 WHERE a>2;
- ]], {
- -- <intpkey-14.2>
- 3, 3, "3"
- -- </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(
- "intpkey-14.4",
- [[
- SELECT * FROM t3 WHERE a<'2';
- ]], {
- -- <intpkey-14.4>
- 1, 1, "one"
- -- </intpkey-14.4>
- })
-
-test:do_execsql_test(
- "intpkey-14.5",
- [[
- SELECT * FROM t3 WHERE a<c;
- ]], {
- -- <intpkey-14.5>
- 1, 1, "one"
- -- </intpkey-14.5>
- })
-
-test:do_execsql_test(
- "intpkey-14.6",
- [[
- SELECT * FROM t3 WHERE a=c;
- ]], {
- -- <intpkey-14.6>
- 2, 2, "2", 3, 3, "3"
- -- </intpkey-14.6>
- })
-
-- Check for proper handling of primary keys greater than 2^31.
-- Ticket #1188
--
test:do_execsql_test(
"intpkey-15.1",
[[
- INSERT INTO t1 VALUES(2147483647, 'big-1', 123);
+ INSERT INTO t1 VALUES(2147483647, 'big-1', '123');
SELECT * FROM t1 WHERE a>2147483648;
]], {
-- <intpkey-15.1>
diff --git a/test/sql-tap/limit.test.lua b/test/sql-tap/limit.test.lua
index 870233942..a7d1451f7 100755
--- a/test/sql-tap/limit.test.lua
+++ b/test/sql-tap/limit.test.lua
@@ -441,7 +441,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"limit-6.5.2",
[[
- SELECT * FROM t6 LIMIT '12'
+ SELECT * FROM t6 LIMIT 12
]], {
-- <limit-6.5>
1, 2, 3, 4
diff --git a/test/sql-tap/minmax2.test.lua b/test/sql-tap/minmax2.test.lua
index 0e0f0d08e..707d1c4da 100755
--- a/test/sql-tap/minmax2.test.lua
+++ b/test/sql-tap/minmax2.test.lua
@@ -441,9 +441,9 @@ test:do_execsql_test(
"minmax2-8.2",
[[
CREATE TABLE t5(a INTEGER PRIMARY KEY);
- INSERT INTO t5 VALUES('1234');
- INSERT INTO t5 VALUES('234');
- INSERT INTO t5 VALUES('34');
+ INSERT INTO t5 VALUES(1234);
+ INSERT INTO t5 VALUES(234);
+ INSERT INTO t5 VALUES(34);
SELECT min(a), max(a) FROM t5;
]], {
-- <minmax2-8.2>
diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua
index 32f38cc97..c0136d04c 100755
--- a/test/sql-tap/misc1.test.lua
+++ b/test/sql-tap/misc1.test.lua
@@ -34,9 +34,9 @@ test:do_test(
end
cmd = cmd .. ")"
test:execsql(cmd)
- cmd = "INSERT INTO manycol VALUES(1, 0"
+ cmd = "INSERT INTO manycol VALUES(1, '0'"
for i = 1, 99, 1 do
- cmd = cmd .. ","..i..""
+ cmd = cmd .. ",'"..i.."'"
end
cmd = cmd .. ")"
test:execsql(cmd)
@@ -61,9 +61,9 @@ test:do_test(
"misc1-1.3.1",
function()
for j = 100, 1000, 100 do
- local cmd = string.format("INSERT INTO manycol VALUES(%s, %s", j, j)
+ local cmd = string.format("INSERT INTO manycol VALUES(%s, '%s'", j, j)
for i = 1, 99, 1 do
- cmd = cmd .. ","..(i + j)..""
+ cmd = cmd .. ",'"..(i + j).."'"
end
cmd = cmd .. ")"
test:execsql(cmd)
@@ -176,7 +176,7 @@ test:do_test(
"misc1-2.1",
function()
test:execsql([[
- CREATE TABLE agger(one text primary key, two text, three text, four text);
+ CREATE TABLE agger(one int primary key, two text, three text, four text);
START TRANSACTION;
INSERT INTO agger VALUES(1, 'one', 'hello', 'yes');
INSERT INTO agger VALUES(2, 'two', 'howdy', 'no');
@@ -531,7 +531,7 @@ test:do_test(
"misc1-10.7",
function()
where = string.gsub(where, "x0=0", "x0=100")
- return test:catchsql("UPDATE manycol SET x1=x1+1 "..where.."")
+ return test:catchsql("UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where.."")
end, {
-- <misc1-10.7>
0
@@ -553,7 +553,7 @@ test:do_execsql_test(
-- } {0 {}}
test:do_execsql_test(
"misc1-10.9",
- "UPDATE manycol SET x1=x1+1 "..where
+ "UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where
--"UPDATE manycol SET x1=x1+1 $::where AND rowid>0"
, {})
@@ -665,7 +665,7 @@ test:do_execsql_test(
test:do_execsql_test(
"misc1-12.6",
[[
- INSERT OR IGNORE INTO t6 VALUES('y',0);
+ INSERT OR IGNORE INTO t6 VALUES('y','0');
SELECT * FROM t6;
]], {
-- <misc1-12.6>
@@ -679,10 +679,10 @@ test:do_execsql_test(
"misc1-12.7",
[[
CREATE TABLE t7(x INTEGER, y TEXT, z INT primary key);
- INSERT INTO t7 VALUES(0,0,1);
- INSERT INTO t7 VALUES(0.0,0,2);
- INSERT INTO t7 VALUES(0,0.0,3);
- INSERT INTO t7 VALUES(0.0,0.0,4);
+ INSERT INTO t7 VALUES(0,'0',1);
+ INSERT INTO t7 VALUES(0.0,'0',2);
+ INSERT INTO t7 VALUES(0,'0.0',3);
+ INSERT INTO t7 VALUES(0.0,'0.0',4);
SELECT DISTINCT x, y FROM t7 ORDER BY z;
]], {
-- <misc1-12.7>
diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua
index eeac5353a..3161e48fa 100755
--- a/test/sql-tap/numcast.test.lua
+++ b/test/sql-tap/numcast.test.lua
@@ -135,16 +135,16 @@ test:do_catchsql_test(
INSERT INTO t VALUES(20000000000000000000.01);
SELECT * FROM t;
]], {
- 1,"Tuple field 1 type does not match one required by operation: expected integer"
+ 1,"Type mismatch: can not convert 2.0e+19 to integer"
})
-test:do_catchsql_test(
+test:do_execsql_test(
"cast-2.9",
[[
INSERT INTO t VALUES(2.1);
SELECT * FROM t;
]], {
- 1,"Tuple field 1 type does not match one required by operation: expected integer"
+ 2, 9223372036854775808ULL, 18000000000000000000ULL
})
--
diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua
index fbebfab37..9a969bf3c 100755
--- a/test/sql-tap/select1.test.lua
+++ b/test/sql-tap/select1.test.lua
@@ -231,7 +231,7 @@ string.format([[
CREATE TABLE t3(id INT, a TEXT, b TEXT, PRIMARY KEY(id));
INSERT INTO t3 VALUES(1, 'abc',NULL);
INSERT INTO t3 VALUES(2, NULL,'xyz');
- INSERT INTO t3 SELECT f1, * FROM test1;
+ INSERT INTO t3 SELECT f1, CAST(f1 AS STRING), CAST(f2 AS STRING) FROM test1;
DROP TABLE IF EXISTS t4;
CREATE TABLE t4(id INT, a INT , b TEXT , PRIMARY KEY(id));
INSERT INTO t4 VALUES(1, NULL,'%s');
@@ -1671,8 +1671,8 @@ test:do_execsql_test(
[[
DELETE FROM t3;
DELETE FROM t4;
- INSERT INTO t3 VALUES(0,1,2);
- INSERT INTO t4 VALUES(0,3,4);
+ INSERT INTO t3 VALUES(0,'1','2');
+ INSERT INTO t4 VALUES(0,3,'4');
SELECT * FROM t3, t4;
]], {
-- <select1-11.1>
@@ -1878,7 +1878,7 @@ test:do_execsql_test(
"select1-12.4",
[[
DELETE FROM t3;
- INSERT INTO t3 VALUES(0,1,2);
+ INSERT INTO t3 VALUES(0,'1','2');
]], {
-- <select1-12.4>
diff --git a/test/sql-tap/select4.test.lua b/test/sql-tap/select4.test.lua
index 23cf1bf1b..f7a320438 100755
--- a/test/sql-tap/select4.test.lua
+++ b/test/sql-tap/select4.test.lua
@@ -761,12 +761,12 @@ test:do_test(
test:execsql [[
CREATE TABLE t3(a text primary key, b NUMBER, c text);
START TRANSACTION;
- INSERT INTO t3 VALUES(1, 1.1, '1.1');
- INSERT INTO t3 VALUES(2, 1.10, '1.10');
- INSERT INTO t3 VALUES(3, 1.10, '1.1');
- INSERT INTO t3 VALUES(4, 1.1, '1.10');
- INSERT INTO t3 VALUES(5, 1.2, '1.2');
- INSERT INTO t3 VALUES(6, 1.3, '1.3');
+ INSERT INTO t3 VALUES('1', 1.1, '1.1');
+ INSERT INTO t3 VALUES('2', 1.10, '1.10');
+ INSERT INTO t3 VALUES('3', 1.10, '1.1');
+ INSERT INTO t3 VALUES('4', 1.1, '1.10');
+ INSERT INTO t3 VALUES('5', 1.2, '1.2');
+ INSERT INTO t3 VALUES('6', 1.3, '1.3');
COMMIT;
]]
return test:execsql [[
diff --git a/test/sql-tap/select7.test.lua b/test/sql-tap/select7.test.lua
index fec5d7a41..e1e43c557 100755
--- a/test/sql-tap/select7.test.lua
+++ b/test/sql-tap/select7.test.lua
@@ -255,7 +255,7 @@ test:do_execsql_test(
[[
DROP TABLE IF EXISTS t5;
CREATE TABLE t5(a TEXT primary key, b INT);
- INSERT INTO t5 VALUES(123, 456);
+ INSERT INTO t5 VALUES('123', 456);
SELECT typeof(a), a FROM t5 GROUP BY a HAVING a<b;
]], {
-- <select7-7.7>
diff --git a/test/sql-tap/sort.test.lua b/test/sql-tap/sort.test.lua
index 36074d6ef..18bfd443d 100755
--- a/test/sql-tap/sort.test.lua
+++ b/test/sql-tap/sort.test.lua
@@ -505,10 +505,10 @@ test:do_execsql_test(
a INTEGER PRIMARY KEY,
b VARCHAR(30)
);
- INSERT INTO t4 VALUES(1,1);
- INSERT INTO t4 VALUES(2,2);
- INSERT INTO t4 VALUES(11,11);
- INSERT INTO t4 VALUES(12,12);
+ INSERT INTO t4 VALUES(1,'1');
+ INSERT INTO t4 VALUES(2,'2');
+ INSERT INTO t4 VALUES(11,'11');
+ INSERT INTO t4 VALUES(12,'12');
SELECT a FROM t4 ORDER BY 1;
]], {
-- <sort-7.1>
diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua
index 15c4c8276..e0771825e 100755
--- a/test/sql-tap/subquery.test.lua
+++ b/test/sql-tap/subquery.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(73)
+test:plan(69)
--!./tcltestrunner.lua
-- 2005 January 19
@@ -335,73 +335,6 @@ test:do_execsql_test(
-- </subquery-2.4.3>
})
-test:do_execsql_test(
- "subquery-2.5.1",
- [[
- CREATE TABLE t3(a INTEGER PRIMARY KEY);
- INSERT INTO t3 VALUES(10);
-
- CREATE TABLE t4(x TEXT PRIMARY KEY);
- INSERT INTO t4 VALUES('10');
- ]], {
- -- <subquery-2.5.1>
-
- -- </subquery-2.5.1>
- })
-
-test:do_test(
- "subquery-2.5.2",
- function()
- -- In the expr "x IN (SELECT a FROM t3)" the RHS of the IN operator
- -- has text affinity and the LHS has integer affinity. The rule is
- -- that we try to convert both sides to an integer before doing the
- -- comparision. Hence, the integer value 10 in t3 will compare equal
- -- to the string value '10.0' in t4 because the t4 value will be
- -- converted into an integer.
- return test:execsql [[
- SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
- ]]
- end, {
- -- <subquery-2.5.2>
- "10"
- -- </subquery-2.5.2>
- })
-
-test:do_test(
- "subquery-2.5.3.1",
- function()
- -- The t4i index cannot be used to resolve the "x IN (...)" constraint
- -- because the constraint has integer affinity but t4i has text affinity.
- return test:execsql [[
- CREATE INDEX t4i ON t4(x);
- SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
- ]]
- end, {
- -- <subquery-2.5.3.1>
- "10"
- -- </subquery-2.5.3.1>
- })
-
--- Tarantool: no-rowid is implied for the table, so query plan contains
--- scan over t4i. Verified w/ vanilla sql. Comment this case
---do_test subquery-2.5.3.2 {
--- Verify that the t4i index was not used in the previous query
--- execsql {
--- EXPLAIN QUERY PLAN
--- SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
--- }
---} {~/t4i/}
-test:do_execsql_test(
- "subquery-2.5.4",
- [[
- DROP TABLE t3;
- DROP TABLE t4;
- ]], {
- -- <subquery-2.5.4>
-
- -- </subquery-2.5.4>
- })
-
--------------------------------------------------------------------
-- The following test cases - subquery-3.* - test tickets that
-- were raised during development of correlated subqueries.
diff --git a/test/sql-tap/tkt-3998683a16.test.lua b/test/sql-tap/tkt-3998683a16.test.lua
index 885dcf5cd..9bc310358 100755
--- a/test/sql-tap/tkt-3998683a16.test.lua
+++ b/test/sql-tap/tkt-3998683a16.test.lua
@@ -26,29 +26,17 @@ test:do_test(
function()
return test:execsql [[
CREATE TABLE t1(x INT primary key, y NUMBER);
- INSERT INTO t1 VALUES(1, '1.0');
- INSERT INTO t1 VALUES(2, '.125');
- INSERT INTO t1 VALUES(3, '123.');
- INSERT INTO t1 VALUES(4, '123.e+2');
- INSERT INTO t1 VALUES(5, '.125e+3');
- INSERT INTO t1 VALUES(6, '123e4');
- INSERT INTO t1 VALUES(11, ' 1.0');
- INSERT INTO t1 VALUES(12, ' .125');
- INSERT INTO t1 VALUES(13, ' 123.');
- INSERT INTO t1 VALUES(14, ' 123.e+2');
- INSERT INTO t1 VALUES(15, ' .125e+3');
- INSERT INTO t1 VALUES(16, ' 123e4');
- INSERT INTO t1 VALUES(21, '1.0 ');
- INSERT INTO t1 VALUES(22, '.125 ');
- INSERT INTO t1 VALUES(23, '123. ');
- INSERT INTO t1 VALUES(24, '123.e+2 ');
- INSERT INTO t1 VALUES(25, '.125e+3 ');
- INSERT INTO t1 VALUES(26, '123e4 ');
+ INSERT INTO t1 VALUES(1, 1.0);
+ INSERT INTO t1 VALUES(2, .125);
+ INSERT INTO t1 VALUES(3, 123.);
+ INSERT INTO t1 VALUES(4, 123.e+2);
+ INSERT INTO t1 VALUES(5, .125e+3);
+ INSERT INTO t1 VALUES(6, 123e4);
SELECT x FROM t1 WHERE typeof(y)=='number' ORDER BY x;
]]
end, {
-- <tkt-3998683a16.1>
- 1, 2, 3, 4, 5, 6, 11, 12, 13, 14, 15, 16, 21, 22, 23, 24, 25, 26
+ 1, 2, 3, 4, 5, 6
-- </tkt-3998683a16.1>
})
diff --git a/test/sql-tap/tkt-54844eea3f.test.lua b/test/sql-tap/tkt-54844eea3f.test.lua
index d6cd56e52..89d0d1218 100755
--- a/test/sql-tap/tkt-54844eea3f.test.lua
+++ b/test/sql-tap/tkt-54844eea3f.test.lua
@@ -62,10 +62,10 @@ test:do_execsql_test(
"1.2",
[[
CREATE TABLE t4(id INT primary key, a TEXT, b TEXT, c TEXT);
- INSERT INTO t4 VALUES(1, 'a', 1, 'one');
- INSERT INTO t4 VALUES(2, 'a', 2, 'two');
- INSERT INTO t4 VALUES(3, 'b', 1, 'three');
- INSERT INTO t4 VALUES(4, 'b', 2, 'four');
+ INSERT INTO t4 VALUES(1, 'a', '1', 'one');
+ INSERT INTO t4 VALUES(2, 'a', '2', 'two');
+ INSERT INTO t4 VALUES(3, 'b', '1', 'three');
+ INSERT INTO t4 VALUES(4, 'b', '2', 'four');
SELECT (
SELECT c FROM (
SELECT a,b,c FROM t4 WHERE a=output.a ORDER BY b LIMIT 10 OFFSET 1
diff --git a/test/sql-tap/tkt-7bbfb7d442.test.lua b/test/sql-tap/tkt-7bbfb7d442.test.lua
index 535303771..bfddcd920 100755
--- a/test/sql-tap/tkt-7bbfb7d442.test.lua
+++ b/test/sql-tap/tkt-7bbfb7d442.test.lua
@@ -109,13 +109,13 @@ if (1 > 0)
T1.Variant AS Variant,
T1.ControlDate AS ControlDate,
1 AS ControlState,
- COALESCE(T2.DeliveredQty,0) AS DeliveredQty
+ CAST(COALESCE(T2.DeliveredQty,0) AS STRING) AS DeliveredQty
FROM (
SELECT
NEW.InventoryControlId AS InventoryControlId,
II.SKU AS SKU,
II.Variant AS Variant,
- COALESCE(LastClosedIC.ControlDate,NEW.ControlDate) AS ControlDate
+ CAST(COALESCE(LastClosedIC.ControlDate,NEW.ControlDate) AS STRING) AS ControlDate
FROM
InventoryItem II
LEFT JOIN
diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua
index cb5348ab4..ca3a5427a 100755
--- a/test/sql-tap/tkt-9a8b09f8e6.test.lua
+++ b/test/sql-tap/tkt-9a8b09f8e6.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(49)
+test:plan(47)
--!./tcltestrunner.lua
-- 2014 June 26
@@ -193,16 +193,6 @@ test:do_execsql_test(
-- </3.3>
})
-test:do_execsql_test(
- 3.4,
- [[
- SELECT x FROM t2 WHERE x IN ('1');
- ]], {
- -- <3.4>
- 1
- -- </3.4>
- })
-
test:do_execsql_test(
3.5,
[[
@@ -233,16 +223,6 @@ test:do_execsql_test(
-- </3.7>
})
-test:do_execsql_test(
- 3.8,
- [[
- SELECT x FROM t2 WHERE '1' IN (x);
- ]], {
- -- <3.8>
- 1
- -- </3.8>
- })
-
test:do_execsql_test(
4.1,
[[
@@ -263,23 +243,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 1 to number"
-- </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 1.0 to number"
-- </4.4>
})
@@ -303,23 +283,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 1 to number"
-- </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 1.0 to number"
-- </4.8>
})
@@ -343,23 +323,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 1 to number"
-- </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 1.0 to number"
-- </5.4>
})
@@ -373,13 +353,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 1.11 to number"
-- </5.6>
})
@@ -403,23 +383,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 1 to number"
-- </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 1.0 to number"
-- </5.10>
})
@@ -433,13 +413,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 1.11 to number"
-- </5.12>
})
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/tkt-fc7bd6358f.test.lua b/test/sql-tap/tkt-fc7bd6358f.test.lua
deleted file mode 100755
index fe5d6200f..000000000
--- a/test/sql-tap/tkt-fc7bd6358f.test.lua
+++ /dev/null
@@ -1,111 +0,0 @@
-#!/usr/bin/env tarantool
-test = require("sqltester")
-test:plan(50)
-
---!./tcltestrunner.lua
--- 2013 March 05
---
--- 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. Specifically,
--- it tests that ticket [fc7bd6358f]:
---
--- The following SQL yields an incorrect result (zero rows) in all
--- versions of sql between 3.6.14 and 3.7.15.2:
---
--- CREATE TABLE t(textid TEXT);
--- INSERT INTO t VALUES('12');
--- INSERT INTO t VALUES('34');
--- CREATE TABLE i(intid INTEGER PRIMARY KEY);
--- INSERT INTO i VALUES(12);
--- INSERT INTO i VALUES(34);
---
--- SELECT t1.textid AS a, i.intid AS b, t2.textid AS c
--- FROM t t1, i, t t2
--- WHERE t1.textid = i.intid
--- AND t1.textid = t2.textid;
---
--- The correct result should be two rows, one with 12|12|12 and the other
--- with 34|34|34. With this bug, no rows are returned. Bisecting shows that
--- this bug was introduced with check-in [dd4d67a67454] on 2009-04-23.
---
--- ["set","testdir",[["file","dirname",["argv0"]]]]
--- ["source",[["testdir"],"\/tester.tcl"]]
-test:do_test(
- "tkt-fc7bd6358f.100",
- function()
- return test:execsql [[
- CREATE TABLE t(textid TEXT PRIMARY KEY);
- INSERT INTO t VALUES('12');
- INSERT INTO t VALUES('34');
- CREATE TABLE i(intid INTEGER PRIMARY KEY);
- INSERT INTO i VALUES(12);
- INSERT INTO i VALUES(34);
- ]]
- end, {
- -- <tkt-fc7bd6358f.100>
-
- -- </tkt-fc7bd6358f.100>
- })
-
--- ["unset","-nocomplain","from"]
--- ["unset","-nocomplain","where"]
--- ["unset","-nocomplain","a"]
--- ["unset","-nocomplain","b"]
-local froms = {
- "FROM t t1, i, t t2",
- "FROM i, t t1, t t2",
- "FROM t t1, t t2, i",
-}
-local wheres = {
- "WHERE t1.textid=i.intid AND t1.textid=t2.textid",
- "WHERE i.intid=t1.textid AND t1.textid=t2.textid",
- "WHERE t1.textid=i.intid AND i.intid=t2.textid",
- "WHERE t1.textid=i.intid AND t2.textid=i.intid",
- "WHERE i.intid=t1.textid AND i.intid=t2.textid",
- "WHERE i.intid=t1.textid AND t2.textid=i.intid",
- "WHERE t1.textid=t2.textid AND i.intid=t2.textid",
- "WHERE t1.textid=t2.textid AND t2.textid=i.intid",
-}
-for a, from in ipairs(froms) do
- for b, where in ipairs(wheres) 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))
- end, {
- "12", 12, "12", "34", 34, "34"
- })
-
- 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))
- end, {
- "12", 12, "12", "34", 34, "34"
- })
-
- end
-end
-
-test:do_test(
- "tkt-fc7bd6358f.200",
- function()
- return test:execsql [[
- DROP TABLE t;
- DROP TABLE i;
- ]]
- end, {
- -- <tkt-fc7bd6358f.100>
-
- -- </tkt-fc7bd6358f.100>
- })
-
-test:finish_test()
-
diff --git a/test/sql-tap/tkt1444.test.lua b/test/sql-tap/tkt1444.test.lua
index 82a5ded25..fb148bc5f 100755
--- a/test/sql-tap/tkt1444.test.lua
+++ b/test/sql-tap/tkt1444.test.lua
@@ -30,8 +30,8 @@ test:do_execsql_test(
[[
CREATE TABLE DemoTable (id INT primary key, x INTEGER, TextKey TEXT, DKey NUMBER);
CREATE INDEX DemoTableIdx ON DemoTable (TextKey);
- INSERT INTO DemoTable VALUES(1, 9,8,7);
- INSERT INTO DemoTable VALUES(2, 1,2,3);
+ INSERT INTO DemoTable VALUES(1, 9,'8',7);
+ INSERT INTO DemoTable VALUES(2, 1,'2',3);
CREATE VIEW DemoView AS SELECT x, TextKey, DKey FROM DemoTable ORDER BY TextKey;
SELECT x,TextKey,DKey FROM DemoTable UNION ALL SELECT * FROM DemoView ORDER BY 1;
]], {
diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua
index 7ceec4702..de77e61e9 100755
--- a/test/sql-tap/tkt3493.test.lua
+++ b/test/sql-tap/tkt3493.test.lua
@@ -29,8 +29,8 @@ test:do_execsql_test(
START TRANSACTION;
INSERT INTO A VALUES(1,'123');
INSERT INTO A VALUES(2,'456');
- INSERT INTO B VALUES(1,1);
- INSERT INTO B VALUES(2,2);
+ INSERT INTO B VALUES(1,'1');
+ INSERT INTO B VALUES(2,'2');
INSERT INTO A_B VALUES(1,1);
INSERT INTO A_B VALUES(2,2);
COMMIT;
@@ -116,7 +116,7 @@ test:do_execsql_test(
"tkt3493-2.1",
[[
CREATE TABLE t1(a TEXT PRIMARY KEY, b INT);
- INSERT INTO t1 VALUES(123, 456);
+ INSERT INTO t1 VALUES('123', 456);
]], {
-- <tkt3493-2.1>
diff --git a/test/sql-tap/tkt3841.test.lua b/test/sql-tap/tkt3841.test.lua
index 5203d0cd4..56668f6a3 100755
--- a/test/sql-tap/tkt3841.test.lua
+++ b/test/sql-tap/tkt3841.test.lua
@@ -31,12 +31,12 @@ test:do_execsql_test(
INSERT INTO table2 VALUES ('a', 'alist');
INSERT INTO table2 VALUES ('b', 'blist');
- INSERT INTO list VALUES ('a', 1);
- INSERT INTO list VALUES ('a', 2);
- INSERT INTO list VALUES ('a', 3);
- INSERT INTO list VALUES ('b', 4);
- INSERT INTO list VALUES ('b', 5);
- INSERT INTO list VALUES ('b', 6);
+ INSERT INTO list VALUES ('a', '1');
+ INSERT INTO list VALUES ('a', '2');
+ INSERT INTO list VALUES ('a', '3');
+ INSERT INTO list VALUES ('b', '4');
+ INSERT INTO list VALUES ('b', '5');
+ INSERT INTO list VALUES ('b', '6');
SELECT
table2.x,
diff --git a/test/sql-tap/transitive1.test.lua b/test/sql-tap/transitive1.test.lua
index e96056580..96895b4a7 100755
--- a/test/sql-tap/transitive1.test.lua
+++ b/test/sql-tap/transitive1.test.lua
@@ -338,7 +338,7 @@ test:do_execsql_test(
ON tvshow.idshow = episode.idshow
LEFT JOIN seasons
ON seasons.idshow = episode.idshow
- AND seasons.season = episode.c12
+ AND seasons.season = CAST(episode.c12 AS INTEGER)
JOIN path
ON files.idpath = path.idpath
LEFT JOIN bookmark
@@ -378,7 +378,7 @@ test:do_execsql_test(
FROM episodeview
JOIN tvshowview ON tvshowview.idShow = episodeview.idShow
JOIN seasons ON (seasons.idShow = tvshowview.idShow
- AND seasons.season = episodeview.c12)
+ AND seasons.season = CAST(episodeview.c12 AS INTEGER))
JOIN files ON files.idFile = episodeview.idFile
JOIN tvshowlinkpath ON tvshowlinkpath.idShow = tvshowview.idShow
JOIN path ON path.idPath = tvshowlinkpath.idPath
diff --git a/test/sql-tap/triggerA.test.lua b/test/sql-tap/triggerA.test.lua
index fac51ca14..fc8ecfe17 100755
--- a/test/sql-tap/triggerA.test.lua
+++ b/test/sql-tap/triggerA.test.lua
@@ -283,7 +283,7 @@ test:do_test(
CREATE TABLE result2(id INTEGER PRIMARY KEY, a TEXT,b INT);
CREATE TRIGGER r5d INSTEAD OF DELETE ON v5 FOR EACH ROW BEGIN
INSERT INTO result2(id, a,b) VALUES((SELECT coalesce(max(id),0) + 1 FROM result2),
- old.x, old.b);
+ CAST(old.x AS STRING), old.b);
END;
DELETE FROM v5 WHERE x=5;
SELECT a, b FROM result2;
@@ -301,7 +301,7 @@ test:do_test(
DELETE FROM result4;
CREATE TRIGGER r5u INSTEAD OF UPDATE ON v5 FOR EACH ROW BEGIN
INSERT INTO result4(id, a,b,c,d) VALUES((SELECT coalesce(max(id),0) + 1 FROM result4),
- old.x, old.b, new.x, new.b);
+ CAST(old.x AS STRING), old.b, CAST(new.x AS STRING), new.b);
END;
UPDATE v5 SET b = b+9900000 WHERE x BETWEEN 3 AND 5;
SELECT a,b,c,d FROM result4 ORDER BY a;
diff --git a/test/sql-tap/unique.test.lua b/test/sql-tap/unique.test.lua
index 9818f90a8..6b0a7e20d 100755
--- a/test/sql-tap/unique.test.lua
+++ b/test/sql-tap/unique.test.lua
@@ -52,7 +52,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"unique-1.2",
[[
- INSERT INTO t1(a,b,c) VALUES(1,2,3)
+ INSERT INTO t1(a,b,c) VALUES(1,2,'3')
]], {
-- <unique-1.2>
0
@@ -62,7 +62,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"unique-1.3",
[[
- INSERT INTO t1(a,b,c) VALUES(1,3,4)
+ INSERT INTO t1(a,b,c) VALUES(1,3,'4')
]], {
-- <unique-1.3>
1, "Duplicate key exists in unique index 'pk_unnamed_T1_1' in space 'T1'"
@@ -83,7 +83,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"unique-1.5",
[[
- INSERT INTO t1(a,b,c) VALUES(3,2,4)
+ INSERT INTO t1(a,b,c) VALUES(3,2,'4')
]], {
-- <unique-1.5>
1, "Duplicate key exists in unique index 'unique_unnamed_T1_2' in space 'T1'"
@@ -104,7 +104,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"unique-1.7",
[[
- INSERT INTO t1(a,b,c) VALUES(3,4,5)
+ INSERT INTO t1(a,b,c) VALUES(3,4,'5')
]], {
-- <unique-1.7>
0
diff --git a/test/sql-tap/view.test.lua b/test/sql-tap/view.test.lua
index e553b91c7..ab14c5edb 100755
--- a/test/sql-tap/view.test.lua
+++ b/test/sql-tap/view.test.lua
@@ -757,7 +757,7 @@ test:do_execsql_test(
"view-10.1",
[=[
CREATE TABLE t3("9" integer primary key, "4" text);
- INSERT INTO t3 VALUES(1,2);
+ INSERT INTO t3 VALUES(1,'2');
CREATE VIEW v_t3_a AS SELECT a."9" FROM t3 AS a;
CREATE VIEW v_t3_b AS SELECT "4" FROM t3;
SELECT * FROM v_t3_a;
diff --git a/test/sql-tap/where5.test.lua b/test/sql-tap/where5.test.lua
index 749201564..3aefcaca5 100755
--- a/test/sql-tap/where5.test.lua
+++ b/test/sql-tap/where5.test.lua
@@ -27,11 +27,11 @@ test:do_test("where5-1.0", function()
CREATE TABLE t1(x TEXT primary key);
CREATE TABLE t2(x integer primary key);
CREATE TABLE t3(x integer PRIMARY KEY);
- INSERT INTO t1 VALUES(-1);
- INSERT INTO t1 VALUES(0);
- INSERT INTO t1 VALUES(1);
- INSERT INTO t2 SELECT * FROM t1;
- INSERT INTO t3 SELECT * FROM t1;
+ INSERT INTO t1 VALUES('-1');
+ INSERT INTO t1 VALUES('0');
+ INSERT INTO t1 VALUES('1');
+ INSERT INTO t2 SELECT CAST(x AS INTEGER) FROM t1;
+ INSERT INTO t3 SELECT CAST(x AS INTEGER) FROM t1;
]]
return test:execsql [[
SELECT * FROM t1 WHERE x<0
diff --git a/test/sql-tap/whereB.test.lua b/test/sql-tap/whereB.test.lua
deleted file mode 100755
index d98645fdc..000000000
--- a/test/sql-tap/whereB.test.lua
+++ /dev/null
@@ -1,908 +0,0 @@
-#!/usr/bin/env tarantool
-test = require("sqltester")
-test:plan(63)
-
---!./tcltestrunner.lua
--- 2009 August 13
---
--- 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. The
--- focus of this file is testing WHERE clause conditions with
--- subtle affinity issues.
---
--- ["set","testdir",[["file","dirname",["argv0"]]]]
--- ["source",[["testdir"],"\/tester.tcl"]]
--- For this set of tests:
---
--- * t1.y holds an integer value with affinity NONE
--- * t2.b holds a text value with affinity TEXT
---
--- These values are not equal and because neither affinity is NUMERIC
--- no type conversion occurs.
---
-test:do_execsql_test(
- "whereB-1.1",
- [[
- CREATE TABLE t1(x INT primary key,y INT ); -- affinity of t1.y is NONE
- INSERT INTO t1 VALUES(1,99);
-
- 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');
-
- SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a;
- ]],
- {
- -- <whereB-1.1>
- 1, 2, true
- -- </whereB-1.1>
- })
-
-test:do_execsql_test(
- "whereB-1.2",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-1.2>
- 1, 2, true
- -- </whereB-1.2>
- })
-
-test:do_execsql_test(
- "whereB-1.3",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-1.3>
- 1, 2, true
- -- </whereB-1.3>
- })
-
-test:do_execsql_test(
- "whereB-1.4",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-1.4>
- 1, 2, true
- -- </whereB-1.4>
- })
-
-test:do_execsql_test(
- "whereB-1.100",
- [[
- DROP INDEX t2b ON t2;
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-1.100>
- 1, 2, true
- -- </whereB-1.100>
- })
-
-test:do_execsql_test(
- "whereB-1.101",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-1.101>
- 1, 2, true
- -- </whereB-1.101>
- })
-
-test:do_execsql_test(
- "whereB-1.102",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-1.102>
- 1, 2, true
- -- </whereB-1.102>
- })
-
--- For this set of tests:
---
--- * t1.y holds a text value with affinity TEXT
--- * t2.b holds an integer value with affinity NONE
---
--- These values are not equal and because neither affinity is NUMERIC
--- no type conversion occurs.
---
-test:do_execsql_test(
- "whereB-2.1",
- [[
- DROP TABLE t1;
- DROP TABLE t2;
-
- CREATE TABLE t1(x INT primary key, y TEXT); -- affinity of t1.y is TEXT
- INSERT INTO t1 VALUES(1,99);
-
- 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);
-
- SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a;
- ]],
- {
- -- <whereB-2.1>
- 1, 2, false
- -- </whereB-2.1>
- })
-
-test:do_execsql_test(
- "whereB-2.2",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-2.2>
-
- -- </whereB-2.2>
- })
-
-test:do_execsql_test(
- "whereB-2.3",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-2.3>
-
- -- </whereB-2.3>
- })
-
-test:do_execsql_test(
- "whereB-2.4",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-2.4>
-
- -- </whereB-2.4>
- })
-
-test:do_execsql_test(
- "whereB-2.100",
- [[
- DROP INDEX t2b ON t2;
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-2.100>
-
- -- </whereB-2.100>
- })
-
-test:do_execsql_test(
- "whereB-2.101",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-2.101>
-
- -- </whereB-2.101>
- })
-
-test:do_execsql_test(
- "whereB-2.102",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-2.102>
-
- -- </whereB-2.102>
- })
-
--- For this set of tests:
---
--- * t1.y holds a text value with affinity NONE
--- * t2.b holds an integer value with affinity NONE
---
--- These values are not equal and because neither affinity is NUMERIC
--- no type conversion occurs.
---
-test:do_execsql_test(
- "whereB-3.1",
- [[
- DROP TABLE t1;
- DROP TABLE t2;
-
- CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE
- INSERT INTO t1 VALUES(1,99);
-
- 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');
-
- SELECT x, a, y=b FROM t1, t2;
- ]],
- {
- -- <whereB-3.1>
- 1, 2, false
- -- </whereB-3.1>
- })
-
-test:do_execsql_test(
- "whereB-3.2",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-3.2>
-
- -- </whereB-3.2>
- })
-
-test:do_execsql_test(
- "whereB-3.3",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-3.3>
-
- -- </whereB-3.3>
- })
-
-test:do_execsql_test(
- "whereB-3.4",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-3.4>
-
- -- </whereB-3.4>
- })
-
-test:do_execsql_test(
- "whereB-3.100",
- [[
- DROP INDEX t2b ON t2;
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-3.100>
-
- -- </whereB-3.100>
- })
-
-test:do_execsql_test(
- "whereB-3.101",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-3.101>
-
- -- </whereB-3.101>
- })
-
-test:do_execsql_test(
- "whereB-3.102",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-3.102>
-
- -- </whereB-3.102>
- })
-
--- For this set of tests:
---
--- * t1.y holds a text value with affinity NONE
--- * t2.b holds an integer value with affinity NUMERIC
---
--- 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",
- [[
- DROP TABLE IF EXISTS t1;
- DROP TABLE IF EXISTS t2;
-
- CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE
- INSERT INTO t1 VALUES(1,'99');
-
- 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);
-
- SELECT x, a, y=b FROM t1, t2;
- ]],
- {
- -- <whereB-4.1>
- 1, 2, true
- -- </whereB-4.1>
- })
-
-test:do_execsql_test(
- "whereB-4.2",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-4.2>
- 1, 2, true
- -- </whereB-4.2>
- })
-
-test:do_execsql_test(
- "whereB-4.3",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-4.3>
- 1, 2, true
- -- </whereB-4.3>
- })
-
-test:do_execsql_test(
- "whereB-4.4",
- -- In this case the unary "+" operator shouldn't
- -- affect result set of query.
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-4.4>
- 1, 2, true
- -- </whereB-4.4>
- })
-
-test:do_execsql_test(
- "whereB-4.100",
- [[
- DROP INDEX t2b ON t2;
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-4.100>
- 1, 2, true
- -- </whereB-4.100>
- })
-
-test:do_execsql_test(
- "whereB-4.101",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-4.101>
- 1, 2, true
- -- </whereB-4.101>
- })
-
-test:do_execsql_test(
- "whereB-4.102",
- -- In this case the unary "+" operator shouldn't
- -- affect result set of query.
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-4.102>
- 1, 2, true
- -- </whereB-4.102>
- })
-
--- For this set of tests:
---
--- * t1.y holds a text value with affinity NONE
--- * t2.b holds an integer value with affinity INTEGER
---
--- 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",
- [[
- DROP TABLE t1;
- DROP TABLE t2;
-
- CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE
- INSERT INTO t1 VALUES(1,'99');
-
- 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);
-
- SELECT x, a, y=b FROM t1, t2;
- ]],
- {
- -- <whereB-5.1>
- 1, 2, true
- -- </whereB-5.1>
- })
-
-test:do_execsql_test(
- "whereB-5.2",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-5.2>
- 1, 2, true
- -- </whereB-5.2>
- })
-
-test:do_execsql_test(
- "whereB-5.3",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-5.3>
- 1, 2, true
- -- </whereB-5.3>
- })
-
-test:do_execsql_test(
- "whereB-5.4",
- -- In this case the unary "+" operator shouldn't
- -- affect result set of query.
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-5.4>
- 1, 2, true
- -- </whereB-5.4>
- })
-
-test:do_execsql_test(
- "whereB-5.100",
- [[
- DROP INDEX t2b ON t2;
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-5.100>
- 1, 2, true
- -- </whereB-5.100>
- })
-
-test:do_execsql_test(
- "whereB-5.101",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-5.101>
- 1, 2, true
- -- </whereB-5.101>
- })
-
-test:do_execsql_test(
- "whereB-5.102",
- -- In this case the unary "+" operator shouldn't
- -- affect result set of query.
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-5.102>
- 1, 2, true
- -- </whereB-5.102>
- })
-
--- For this set of tests:
---
--- * t1.y holds a text value with affinity NONE
--- * t2.b holds an integer value with affinity REAL
---
--- 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",
- [[
- DROP TABLE t1;
- DROP TABLE t2;
-
- CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE
- INSERT INTO t1 VALUES(1,'99');
-
- 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);
-
- SELECT x, a, y=b FROM t1, t2;
- ]],
- {
- -- <whereB-6.1>
- 1, 2, true
- -- </whereB-6.1>
- })
-
-test:do_execsql_test(
- "whereB-6.2",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-6.2>
- 1, 2, true
- -- </whereB-6.2>
- })
-
-test:do_execsql_test(
- "whereB-6.3",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-6.3>
- 1, 2, true
- -- </whereB-6.3>
- })
-
-test:do_execsql_test(
- "whereB-6.4",
- -- In this case the unary "+" operator shouldn't
- -- affect result set of query.
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-6.4>
- 1, 2, true
- -- </whereB-6.4>
- })
-
-test:do_execsql_test(
- "whereB-6.100",
- [[
- DROP INDEX t2b ON t2;
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-6.100>
- 1, 2, true
- -- </whereB-6.100>
- })
-
-test:do_execsql_test(
- "whereB-6.101",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-6.101>
- 1, 2, true
- -- </whereB-6.101>
- })
-
-test:do_execsql_test(
- "whereB-6.102",
- -- In this case the unary "+" operator shouldn't
- -- affect result set of query.
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-6.102>
- 1, 2, true
- -- </whereB-6.102>
- })
-
--- For this set of tests:
---
--- * t1.y holds an integer value with affinity NUMERIC
--- * t2.b holds a text value with affinity NONE
---
--- 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",
- [[
- DROP TABLE t1;
- DROP TABLE t2;
-
- CREATE TABLE t1(x INT primary key, y NUMBER); -- affinity of t1.y is NUMERIC
- INSERT INTO t1 VALUES(1,99);
-
- 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');
-
- SELECT x, a, y=b FROM t1, t2;
- ]],
- {
- -- <whereB-7.1>
- 1, 2, true
- -- </whereB-7.1>
- })
-
-test:do_execsql_test(
- "whereB-7.2",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-7.2>
- 1, 2, true
- -- </whereB-7.2>
- })
-
-test:do_execsql_test(
- "whereB-7.3",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-7.3>
- 1, 2, true
- -- </whereB-7.3>
- })
-
-test:do_execsql_test(
- "whereB-7.4",
- -- In this case the unary "+" operator shouldn't
- -- affect result set of query.
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-7.4>
- 1, 2, true
- -- </whereB-7.4>
- })
-
-test:do_execsql_test(
- "whereB-7.100",
- [[
- DROP INDEX t2b ON t2;
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-7.100>
- 1, 2, true
- -- </whereB-7.100>
- })
-
-test:do_execsql_test(
- "whereB-7.101",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-7.101>
- 1, 2, true
- -- </whereB-7.101>
- })
-
-test:do_execsql_test(
- "whereB-7.102",
- -- In this case the unary "+" operator shouldn't
- -- affect result set of query.
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-7.102>
- 1, 2, true
- -- </whereB-7.102>
- })
-
--- For this set of tests:
---
--- * t1.y holds an integer value with affinity INTEGER
--- * t2.b holds a text value with affinity NONE
---
--- 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",
- [[
- DROP TABLE t1;
- DROP TABLE t2;
-
- CREATE TABLE t1(x INT primary key, y INT); -- affinity of t1.y is INTEGER
- INSERT INTO t1 VALUES(1,99);
-
- 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');
-
- SELECT x, a, y=b FROM t1, t2;
- ]],
- {
- -- <whereB-8.1>
- 1, 2, true
- -- </whereB-8.1>
- })
-
-test:do_execsql_test(
- "whereB-8.2",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-8.2>
- 1, 2, true
- -- </whereB-8.2>
- })
-
-test:do_execsql_test(
- "whereB-8.3",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-8.3>
- 1, 2, true
- -- </whereB-8.3>
- })
-
-test:do_execsql_test(
- "whereB-8.4",
- -- In this case the unary "+" operator shouldn't
- -- affect result set of query.
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-8.4>
- 1, 2, true
- -- </whereB-8.4>
- })
-
-test:do_execsql_test(
- "whereB-8.100",
- [[
- DROP INDEX t2b ON t2;
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-8.100>
- 1, 2, true
- -- </whereB-8.100>
- })
-
-test:do_execsql_test(
- "whereB-8.101",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-8.101>
- 1, 2, true
- -- </whereB-8.101>
- })
-
-test:do_execsql_test(
- "whereB-8.102",
- -- In this case the unary "+" operator shouldn't
- -- affect result set of query.
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-8.102>
- 1, 2, true
- -- </whereB-8.102>
- })
-
--- For this set of tests:
---
--- * t1.y holds an integer value with affinity REAL
--- * t2.b holds a text value with affinity NONE
---
--- 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",
- [[
- DROP TABLE t1;
- DROP TABLE t2;
-
- CREATE TABLE t1(x INT primary key, y NUMBER); -- affinity of t1.y is REAL
- INSERT INTO t1 VALUES(1,99.0);
-
- 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');
-
- SELECT x, a, y=b FROM t1, t2;
- ]],
- {
- -- <whereB-9.1>
- 1, 2, true
- -- </whereB-9.1>
- })
-
-test:do_execsql_test(
- "whereB-9.2",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-9.2>
- 1, 2, true
- -- </whereB-9.2>
- })
-
-test:do_execsql_test(
- "whereB-9.3",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-9.3>
- 1, 2, true
- -- </whereB-9.3>
- })
-
-test:do_execsql_test(
- "whereB-9.4",
- -- In this case the unary "+" operator shouldn't
- -- affect result set of query.
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-9.4>
- 1, 2, true
- -- </whereB-9.4>
- })
-
-test:do_execsql_test(
- "whereB-9.100",
- [[
- DROP INDEX t2b ON t2;
- SELECT x, a, y=b FROM t1, t2 WHERE y=b;
- ]],
- {
- -- <whereB-9.100>
- 1, 2, true
- -- </whereB-9.100>
- })
-
-test:do_execsql_test(
- "whereB-9.101",
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE b=y;
- ]],
- {
- -- <whereB-9.101>
- 1, 2, true
- -- </whereB-9.101>
- })
-
-test:do_execsql_test(
- "whereB-9.102",
- -- In this case the unary "+" operator shouldn't
- -- affect result set of query.
- [[
- SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
- ]],
- {
- -- <whereB-9.102>
- 1, 2, true
- -- </whereB-9.102>
- })
-
-test:finish_test()
-
diff --git a/test/sql-tap/whereC.test.lua b/test/sql-tap/whereC.test.lua
index 89459dee3..58c049553 100755
--- a/test/sql-tap/whereC.test.lua
+++ b/test/sql-tap/whereC.test.lua
@@ -55,9 +55,9 @@ test:do_execsql_test(
test:test("main", function()
local data = {{"SELECT i FROM t1 WHERE a=1 AND b=2 AND i>3", {4, 5}},
-- {"SELECT i FROM t1 WHERE rowid='12'", {12}},
- {"SELECT i FROM t1 WHERE a=1 AND b='2'", {3, 4, 5}},
- {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i>'3'", {4, 5}},
- {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i<5", {3, 4}},
+ {"SELECT i FROM t1 WHERE a=1 AND b=2", {3, 4, 5}},
+ {"SELECT i FROM t1 WHERE a=1 AND b=2 AND i>3", {4, 5}},
+ {"SELECT i FROM t1 WHERE a=1 AND b=2 AND i<5", {3, 4}},
{"SELECT i FROM t1 WHERE a=2 AND b=2 AND i<12", {10, 11}},
{"SELECT i FROM t1 WHERE a IN(1, 2) AND b=2 AND i<11", {3, 4, 5, 10}},
{"SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 12", {10, 11, 12}},
@@ -66,7 +66,7 @@ test:test("main", function()
{"SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 12 AND 10", {}},
{"SELECT i FROM t1 WHERE a=2 AND b=2 AND i<NULL", {}},
{"SELECT i FROM t1 WHERE a=2 AND b=2 AND i>=NULL", {}},
- {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i<4.5", {3, 4}}}
+ {"SELECT i FROM t1 WHERE a=1 AND b=2 AND i<4.5", {3, 4}}}
-- {"SELECT i FROM t1 WHERE rowid IS '12'", {12}}}
for tn, t in ipairs(data) do
diff --git a/test/sql/types.result b/test/sql/types.result
index 54aff460e..70fbbc5a2 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -2155,3 +2155,623 @@ box.execute([[SELECT * FROM "s" WHERE "id" = ?;]])
s:drop()
---
...
+--
+-- gh-3809: Make sure there are no implicit casts during
+-- assignment, except for the implicit cast between numeric
+-- values.
+--
+-- Check INSERT.
+box.execute([[CREATE TABLE ti (a INT PRIMARY KEY AUTOINCREMENT, i INTEGER);]])
+---
+- row_count: 1
+...
+box.execute([[CREATE TABLE td (a INT PRIMARY KEY AUTOINCREMENT, d DOUBLE);]])
+---
+- row_count: 1
+...
+box.execute([[CREATE TABLE tb (a INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN);]])
+---
+- row_count: 1
+...
+box.execute([[CREATE TABLE tt (a INT PRIMARY KEY AUTOINCREMENT, t TEXT);]])
+---
+- row_count: 1
+...
+box.execute([[CREATE TABLE tv (a INT PRIMARY KEY AUTOINCREMENT, v VARBINARY);]])
+---
+- row_count: 1
+...
+box.execute([[CREATE TABLE ts (a INT PRIMARY KEY AUTOINCREMENT, s SCALAR);]])
+---
+- row_count: 1
+...
+box.execute([[INSERT INTO ti(i) VALUES (NULL);]])
+---
+- autoincrement_ids:
+ - 1
+ row_count: 1
+...
+box.execute([[INSERT INTO ti(i) VALUES (11);]])
+---
+- autoincrement_ids:
+ - 2
+ row_count: 1
+...
+box.execute([[INSERT INTO ti(i) VALUES (100000000000000000000000000000000.1);]])
+---
+- null
+- 'Type mismatch: can not convert 1.0e+32 to integer'
+...
+box.execute([[INSERT INTO ti(i) VALUES (33.0);]])
+---
+- autoincrement_ids:
+ - 3
+ row_count: 1
+...
+box.execute([[INSERT INTO ti(i) VALUES (true);]])
+---
+- null
+- 'Type mismatch: can not convert TRUE to integer'
+...
+box.execute([[INSERT INTO ti(i) VALUES ('33');]])
+---
+- null
+- 'Type mismatch: can not convert 33 to integer'
+...
+box.execute([[INSERT INTO ti(i) VALUES (X'3434');]])
+---
+- null
+- 'Type mismatch: can not convert varbinary to integer'
+...
+box.execute([[SELECT * FROM ti;]])
+---
+- metadata:
+ - name: A
+ type: integer
+ - name: I
+ type: integer
+ rows:
+ - [1, null]
+ - [2, 11]
+ - [3, 33]
+...
+box.execute([[INSERT INTO td(d) VALUES (NULL);]])
+---
+- autoincrement_ids:
+ - 1
+ row_count: 1
+...
+box.execute([[INSERT INTO td(d) VALUES (11);]])
+---
+- autoincrement_ids:
+ - 2
+ row_count: 1
+...
+box.execute([[INSERT INTO td(d) VALUES (100000000000000001);;]])
+---
+- null
+- Syntax error at line 1 near ';'
+...
+box.execute([[INSERT INTO td(d) VALUES (22.2);]])
+---
+- autoincrement_ids:
+ - 3
+ row_count: 1
+...
+box.execute([[INSERT INTO td(d) VALUES (true);]])
+---
+- null
+- 'Type mismatch: can not convert TRUE to double'
+...
+box.execute([[INSERT INTO td(d) VALUES ('33');]])
+---
+- null
+- 'Type mismatch: can not convert 33 to double'
+...
+box.execute([[INSERT INTO td(d) VALUES (X'3434');]])
+---
+- null
+- 'Type mismatch: can not convert varbinary to double'
+...
+box.execute([[SELECT * FROM td;]])
+---
+- metadata:
+ - name: A
+ type: integer
+ - name: D
+ type: double
+ rows:
+ - [1, null]
+ - [2, 11]
+ - [3, 22.2]
+...
+box.execute([[INSERT INTO tb(b) VALUES (NULL);]])
+---
+- autoincrement_ids:
+ - 1
+ row_count: 1
+...
+box.execute([[INSERT INTO tb(b) VALUES (11);]])
+---
+- null
+- 'Type mismatch: can not convert 11 to boolean'
+...
+box.execute([[INSERT INTO tb(b) VALUES (22.2);]])
+---
+- null
+- 'Type mismatch: can not convert 22.2 to boolean'
+...
+box.execute([[INSERT INTO tb(b) VALUES (true);]])
+---
+- autoincrement_ids:
+ - 2
+ row_count: 1
+...
+box.execute([[INSERT INTO tb(b) VALUES ('33');]])
+---
+- null
+- 'Type mismatch: can not convert 33 to boolean'
+...
+box.execute([[INSERT INTO tb(b) VALUES (X'3434');]])
+---
+- null
+- 'Type mismatch: can not convert varbinary to boolean'
+...
+box.execute([[SELECT * FROM tb;]])
+---
+- metadata:
+ - name: A
+ type: integer
+ - name: B
+ type: boolean
+ rows:
+ - [1, null]
+ - [2, true]
+...
+box.execute([[INSERT INTO tt(t) VALUES (NULL);]])
+---
+- autoincrement_ids:
+ - 1
+ row_count: 1
+...
+box.execute([[INSERT INTO tt(t) VALUES (11);]])
+---
+- null
+- 'Type mismatch: can not convert 11 to string'
+...
+box.execute([[INSERT INTO tt(t) VALUES (22.2);]])
+---
+- null
+- 'Type mismatch: can not convert 22.2 to string'
+...
+box.execute([[INSERT INTO tt(t) VALUES (true);]])
+---
+- null
+- 'Type mismatch: can not convert TRUE to string'
+...
+box.execute([[INSERT INTO tt(t) VALUES ('33');]])
+---
+- autoincrement_ids:
+ - 2
+ row_count: 1
+...
+box.execute([[INSERT INTO tt(t) VALUES (X'3434');]])
+---
+- null
+- 'Type mismatch: can not convert varbinary to string'
+...
+box.execute([[SELECT * FROM tt;]])
+---
+- metadata:
+ - name: A
+ type: integer
+ - name: T
+ type: string
+ rows:
+ - [1, null]
+ - [2, '33']
+...
+box.execute([[INSERT INTO tv(v) VALUES (NULL);]])
+---
+- autoincrement_ids:
+ - 1
+ row_count: 1
+...
+box.execute([[INSERT INTO tv(v) VALUES (11);]])
+---
+- null
+- 'Type mismatch: can not convert 11 to varbinary'
+...
+box.execute([[INSERT INTO tv(v) VALUES (22.2);]])
+---
+- null
+- 'Type mismatch: can not convert 22.2 to varbinary'
+...
+box.execute([[INSERT INTO tv(v) VALUES (true);]])
+---
+- null
+- 'Type mismatch: can not convert TRUE to varbinary'
+...
+box.execute([[INSERT INTO tv(v) VALUES ('33');]])
+---
+- null
+- 'Type mismatch: can not convert 33 to varbinary'
+...
+box.execute([[INSERT INTO tv(v) VALUES (X'3434');]])
+---
+- autoincrement_ids:
+ - 2
+ row_count: 1
+...
+box.execute([[SELECT * FROM tv;]])
+---
+- metadata:
+ - name: A
+ type: integer
+ - name: V
+ type: varbinary
+ rows:
+ - [1, null]
+ - [2, '44']
+...
+box.execute([[INSERT INTO ts(s) VALUES (NULL);]])
+---
+- autoincrement_ids:
+ - 1
+ row_count: 1
+...
+box.execute([[INSERT INTO ts(s) VALUES (11);]])
+---
+- autoincrement_ids:
+ - 2
+ row_count: 1
+...
+box.execute([[INSERT INTO ts(s) VALUES (22.2);]])
+---
+- autoincrement_ids:
+ - 3
+ row_count: 1
+...
+box.execute([[INSERT INTO ts(s) VALUES (true);]])
+---
+- autoincrement_ids:
+ - 4
+ row_count: 1
+...
+box.execute([[INSERT INTO ts(s) VALUES ('33');]])
+---
+- autoincrement_ids:
+ - 5
+ row_count: 1
+...
+box.execute([[INSERT INTO ts(s) VALUES (X'3434');]])
+---
+- autoincrement_ids:
+ - 6
+ row_count: 1
+...
+box.execute([[SELECT * FROM ts;]])
+---
+- metadata:
+ - name: A
+ type: integer
+ - name: S
+ type: scalar
+ rows:
+ - [1, null]
+ - [2, 11]
+ - [3, 22.2]
+ - [4, true]
+ - [5, '33']
+ - [6, '44']
+...
+-- Check for UPDATE.
+box.execute([[DELETE FROM ti;]])
+---
+- row_count: 3
+...
+box.execute([[DELETE FROM td;]])
+---
+- row_count: 3
+...
+box.execute([[DELETE FROM tb;]])
+---
+- row_count: 2
+...
+box.execute([[DELETE FROM tt;]])
+---
+- row_count: 2
+...
+box.execute([[DELETE FROM tv;]])
+---
+- row_count: 2
+...
+box.execute([[DELETE FROM ts;]])
+---
+- row_count: 6
+...
+box.execute([[INSERT INTO ti VALUES(1, NULL);]])
+---
+- row_count: 1
+...
+box.execute([[INSERT INTO td VALUES(1, NULL);]])
+---
+- row_count: 1
+...
+box.execute([[INSERT INTO tb VALUES(1, NULL);]])
+---
+- row_count: 1
+...
+box.execute([[INSERT INTO tt VALUES(1, NULL);]])
+---
+- row_count: 1
+...
+box.execute([[INSERT INTO tv VALUES(1, NULL);]])
+---
+- row_count: 1
+...
+box.execute([[INSERT INTO ts VALUES(1, NULL);]])
+---
+- row_count: 1
+...
+box.execute([[SELECT * FROM ti, td, tb, tt, tv, ts;]])
+---
+- metadata:
+ - name: A
+ type: integer
+ - name: I
+ type: integer
+ - name: A
+ type: integer
+ - name: D
+ type: double
+ - name: A
+ type: integer
+ - name: B
+ type: boolean
+ - name: A
+ type: integer
+ - name: T
+ type: string
+ - name: A
+ type: integer
+ - name: V
+ type: varbinary
+ - name: A
+ type: integer
+ - name: S
+ type: scalar
+ rows:
+ - [1, null, 1, null, 1, null, 1, null, 1, null, 1, null]
+...
+box.execute([[UPDATE ti SET i = NULL WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE ti SET i = 11 WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE ti SET i = 100000000000000000000000000000000.1 WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert 1.0e+32 to integer'
+...
+box.execute([[UPDATE ti SET i = 33.0 WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE ti SET i = true WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert TRUE to integer'
+...
+box.execute([[UPDATE ti SET i = '33' WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert 33 to integer'
+...
+box.execute([[UPDATE ti SET i = X'3434' WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert varbinary to integer'
+...
+box.execute([[SELECT * FROM ti;]])
+---
+- metadata:
+ - name: A
+ type: integer
+ - name: I
+ type: integer
+ rows:
+ - [1, 33]
+...
+box.execute([[UPDATE td SET d = NULL WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE td SET d = 11 WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE td SET d = 100000000000000001 WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE td SET d = 22.2 WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE td SET d = true WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert TRUE to double'
+...
+box.execute([[UPDATE td SET d = '33' WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert 33 to double'
+...
+box.execute([[UPDATE td SET d = X'3434' WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert varbinary to double'
+...
+box.execute([[SELECT * FROM td;]])
+---
+- metadata:
+ - name: A
+ type: integer
+ - name: D
+ type: double
+ rows:
+ - [1, 22.2]
+...
+box.execute([[UPDATE tb SET b = NULL WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE tb SET b = 11 WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert 11 to boolean'
+...
+box.execute([[UPDATE tb SET b = 22.2 WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert 22.2 to boolean'
+...
+box.execute([[UPDATE tb SET b = true WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE tb SET b = '33' WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert 33 to boolean'
+...
+box.execute([[UPDATE tb SET b = X'3434' WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert varbinary to boolean'
+...
+box.execute([[SELECT * FROM tb;]])
+---
+- metadata:
+ - name: A
+ type: integer
+ - name: B
+ type: boolean
+ rows:
+ - [1, true]
+...
+box.execute([[UPDATE tt SET t = NULL WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE tt SET t = 11 WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert 11 to string'
+...
+box.execute([[UPDATE tt SET t = 22.2 WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert 22.2 to string'
+...
+box.execute([[UPDATE tt SET t = true WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert TRUE to string'
+...
+box.execute([[UPDATE tt SET t = '33' WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE tt SET t = X'3434' WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert varbinary to string'
+...
+box.execute([[SELECT * FROM tt;]])
+---
+- metadata:
+ - name: A
+ type: integer
+ - name: T
+ type: string
+ rows:
+ - [1, '33']
+...
+box.execute([[UPDATE tv SET v = NULL WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE tv SET v = 11 WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert 11 to varbinary'
+...
+box.execute([[UPDATE tv SET v = 22.2 WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert 22.2 to varbinary'
+...
+box.execute([[UPDATE tv SET v = true WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert TRUE to varbinary'
+...
+box.execute([[UPDATE tv SET v = '33' WHERE a = 1;]])
+---
+- null
+- 'Type mismatch: can not convert 33 to varbinary'
+...
+box.execute([[UPDATE tv SET v = X'3434' WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[SELECT * FROM tv;]])
+---
+- metadata:
+ - name: A
+ type: integer
+ - name: V
+ type: varbinary
+ rows:
+ - [1, '44']
+...
+box.execute([[UPDATE ts SET s = NULL WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE ts SET s = 11 WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE ts SET s = 22.2 WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE ts SET s = true WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE ts SET s = '33' WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[UPDATE ts SET s = X'3434' WHERE a = 1;]])
+---
+- row_count: 1
+...
+box.execute([[SELECT * FROM ts;]])
+---
+- metadata:
+ - name: A
+ type: integer
+ - name: S
+ type: scalar
+ rows:
+ - [1, '44']
+...
diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua
index bd14b342d..2dc70f3c5 100644
--- a/test/sql/types.test.lua
+++ b/test/sql/types.test.lua
@@ -487,3 +487,132 @@ s:format({ \
box.execute([[SELECT * FROM "s" WHERE "id" = ?;]])
s:drop()
+
+--
+-- gh-3809: Make sure there are no implicit casts during
+-- assignment, except for the implicit cast between numeric
+-- values.
+--
+
+-- Check INSERT.
+box.execute([[CREATE TABLE ti (a INT PRIMARY KEY AUTOINCREMENT, i INTEGER);]])
+box.execute([[CREATE TABLE td (a INT PRIMARY KEY AUTOINCREMENT, d DOUBLE);]])
+box.execute([[CREATE TABLE tb (a INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN);]])
+box.execute([[CREATE TABLE tt (a INT PRIMARY KEY AUTOINCREMENT, t TEXT);]])
+box.execute([[CREATE TABLE tv (a INT PRIMARY KEY AUTOINCREMENT, v VARBINARY);]])
+box.execute([[CREATE TABLE ts (a INT PRIMARY KEY AUTOINCREMENT, s SCALAR);]])
+
+box.execute([[INSERT INTO ti(i) VALUES (NULL);]])
+box.execute([[INSERT INTO ti(i) VALUES (11);]])
+box.execute([[INSERT INTO ti(i) VALUES (100000000000000000000000000000000.1);]])
+box.execute([[INSERT INTO ti(i) VALUES (33.0);]])
+box.execute([[INSERT INTO ti(i) VALUES (true);]])
+box.execute([[INSERT INTO ti(i) VALUES ('33');]])
+box.execute([[INSERT INTO ti(i) VALUES (X'3434');]])
+box.execute([[SELECT * FROM ti;]])
+
+box.execute([[INSERT INTO td(d) VALUES (NULL);]])
+box.execute([[INSERT INTO td(d) VALUES (11);]])
+box.execute([[INSERT INTO td(d) VALUES (100000000000000001);;]])
+box.execute([[INSERT INTO td(d) VALUES (22.2);]])
+box.execute([[INSERT INTO td(d) VALUES (true);]])
+box.execute([[INSERT INTO td(d) VALUES ('33');]])
+box.execute([[INSERT INTO td(d) VALUES (X'3434');]])
+box.execute([[SELECT * FROM td;]])
+
+box.execute([[INSERT INTO tb(b) VALUES (NULL);]])
+box.execute([[INSERT INTO tb(b) VALUES (11);]])
+box.execute([[INSERT INTO tb(b) VALUES (22.2);]])
+box.execute([[INSERT INTO tb(b) VALUES (true);]])
+box.execute([[INSERT INTO tb(b) VALUES ('33');]])
+box.execute([[INSERT INTO tb(b) VALUES (X'3434');]])
+box.execute([[SELECT * FROM tb;]])
+
+box.execute([[INSERT INTO tt(t) VALUES (NULL);]])
+box.execute([[INSERT INTO tt(t) VALUES (11);]])
+box.execute([[INSERT INTO tt(t) VALUES (22.2);]])
+box.execute([[INSERT INTO tt(t) VALUES (true);]])
+box.execute([[INSERT INTO tt(t) VALUES ('33');]])
+box.execute([[INSERT INTO tt(t) VALUES (X'3434');]])
+box.execute([[SELECT * FROM tt;]])
+
+box.execute([[INSERT INTO tv(v) VALUES (NULL);]])
+box.execute([[INSERT INTO tv(v) VALUES (11);]])
+box.execute([[INSERT INTO tv(v) VALUES (22.2);]])
+box.execute([[INSERT INTO tv(v) VALUES (true);]])
+box.execute([[INSERT INTO tv(v) VALUES ('33');]])
+box.execute([[INSERT INTO tv(v) VALUES (X'3434');]])
+box.execute([[SELECT * FROM tv;]])
+
+box.execute([[INSERT INTO ts(s) VALUES (NULL);]])
+box.execute([[INSERT INTO ts(s) VALUES (11);]])
+box.execute([[INSERT INTO ts(s) VALUES (22.2);]])
+box.execute([[INSERT INTO ts(s) VALUES (true);]])
+box.execute([[INSERT INTO ts(s) VALUES ('33');]])
+box.execute([[INSERT INTO ts(s) VALUES (X'3434');]])
+box.execute([[SELECT * FROM ts;]])
+
+-- Check for UPDATE.
+box.execute([[DELETE FROM ti;]])
+box.execute([[DELETE FROM td;]])
+box.execute([[DELETE FROM tb;]])
+box.execute([[DELETE FROM tt;]])
+box.execute([[DELETE FROM tv;]])
+box.execute([[DELETE FROM ts;]])
+box.execute([[INSERT INTO ti VALUES(1, NULL);]])
+box.execute([[INSERT INTO td VALUES(1, NULL);]])
+box.execute([[INSERT INTO tb VALUES(1, NULL);]])
+box.execute([[INSERT INTO tt VALUES(1, NULL);]])
+box.execute([[INSERT INTO tv VALUES(1, NULL);]])
+box.execute([[INSERT INTO ts VALUES(1, NULL);]])
+box.execute([[SELECT * FROM ti, td, tb, tt, tv, ts;]])
+
+box.execute([[UPDATE ti SET i = NULL WHERE a = 1;]])
+box.execute([[UPDATE ti SET i = 11 WHERE a = 1;]])
+box.execute([[UPDATE ti SET i = 100000000000000000000000000000000.1 WHERE a = 1;]])
+box.execute([[UPDATE ti SET i = 33.0 WHERE a = 1;]])
+box.execute([[UPDATE ti SET i = true WHERE a = 1;]])
+box.execute([[UPDATE ti SET i = '33' WHERE a = 1;]])
+box.execute([[UPDATE ti SET i = X'3434' WHERE a = 1;]])
+box.execute([[SELECT * FROM ti;]])
+
+box.execute([[UPDATE td SET d = NULL WHERE a = 1;]])
+box.execute([[UPDATE td SET d = 11 WHERE a = 1;]])
+box.execute([[UPDATE td SET d = 100000000000000001 WHERE a = 1;]])
+box.execute([[UPDATE td SET d = 22.2 WHERE a = 1;]])
+box.execute([[UPDATE td SET d = true WHERE a = 1;]])
+box.execute([[UPDATE td SET d = '33' WHERE a = 1;]])
+box.execute([[UPDATE td SET d = X'3434' WHERE a = 1;]])
+box.execute([[SELECT * FROM td;]])
+
+box.execute([[UPDATE tb SET b = NULL WHERE a = 1;]])
+box.execute([[UPDATE tb SET b = 11 WHERE a = 1;]])
+box.execute([[UPDATE tb SET b = 22.2 WHERE a = 1;]])
+box.execute([[UPDATE tb SET b = true WHERE a = 1;]])
+box.execute([[UPDATE tb SET b = '33' WHERE a = 1;]])
+box.execute([[UPDATE tb SET b = X'3434' WHERE a = 1;]])
+box.execute([[SELECT * FROM tb;]])
+
+box.execute([[UPDATE tt SET t = NULL WHERE a = 1;]])
+box.execute([[UPDATE tt SET t = 11 WHERE a = 1;]])
+box.execute([[UPDATE tt SET t = 22.2 WHERE a = 1;]])
+box.execute([[UPDATE tt SET t = true WHERE a = 1;]])
+box.execute([[UPDATE tt SET t = '33' WHERE a = 1;]])
+box.execute([[UPDATE tt SET t = X'3434' WHERE a = 1;]])
+box.execute([[SELECT * FROM tt;]])
+
+box.execute([[UPDATE tv SET v = NULL WHERE a = 1;]])
+box.execute([[UPDATE tv SET v = 11 WHERE a = 1;]])
+box.execute([[UPDATE tv SET v = 22.2 WHERE a = 1;]])
+box.execute([[UPDATE tv SET v = true WHERE a = 1;]])
+box.execute([[UPDATE tv SET v = '33' WHERE a = 1;]])
+box.execute([[UPDATE tv SET v = X'3434' WHERE a = 1;]])
+box.execute([[SELECT * FROM tv;]])
+
+box.execute([[UPDATE ts SET s = NULL WHERE a = 1;]])
+box.execute([[UPDATE ts SET s = 11 WHERE a = 1;]])
+box.execute([[UPDATE ts SET s = 22.2 WHERE a = 1;]])
+box.execute([[UPDATE ts SET s = true WHERE a = 1;]])
+box.execute([[UPDATE ts SET s = '33' WHERE a = 1;]])
+box.execute([[UPDATE ts SET s = X'3434' WHERE a = 1;]])
+box.execute([[SELECT * FROM ts;]])
More information about the Tarantool-patches
mailing list