After this patch, arithmetic operations will only accept numeric values. For the "%" operation, the rules have become even stricter, now it accepts only INTEGER and UNSIGNED values. Part of #4470 Closes #5756 --- https://github.com/tarantool/tarantool/issues/5756 https://github.com/tarantool/tarantool/tree/imeevma/gh-5756-implicit-cast-in-arithmetic ...5756-remove-implicit-cast-in-arithmetic.md | 3 + src/box/sql/mem.c | 207 ++++++------ ...-5756-implicit-cast-in-arithmetic.test.lua | 294 ++++++++++++++++++ test/sql-tap/misc1.test.lua | 9 +- test/sql-tap/misc3.test.lua | 8 +- test/sql-tap/tkt-a8a0d2996a.test.lua | 44 +-- test/sql-tap/uuid.test.lua | 2 +- test/sql/boolean.result | 82 ++--- test/sql/types.result | 7 +- 9 files changed, 476 insertions(+), 180 deletions(-) create mode 100644 changelogs/unreleased/gh-5756-remove-implicit-cast-in-arithmetic.md create mode 100755 test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua diff --git a/changelogs/unreleased/gh-5756-remove-implicit-cast-in-arithmetic.md b/changelogs/unreleased/gh-5756-remove-implicit-cast-in-arithmetic.md new file mode 100644 index 000000000..4b21fed59 --- /dev/null +++ b/changelogs/unreleased/gh-5756-remove-implicit-cast-in-arithmetic.md @@ -0,0 +1,3 @@ +## bugfix/sql + +* All arithmetic operations can now only accept numeric values (gh-5756). diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c index fa80f6d5a..b49623e20 100644 --- a/src/box/sql/mem.c +++ b/src/box/sql/mem.c @@ -1580,194 +1580,193 @@ get_number(const struct Mem *mem, struct sql_num *number) return -1; } -static int -arithmetic_prepare(const struct Mem *left, const struct Mem *right, - struct sql_num *a, struct sql_num *b) +int +mem_add(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (get_number(right, b) != 0) { + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_NUMBER; + return 0; + } + if (!mem_is_num(right)) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), "number"); return -1; } - if (get_number(left, a) != 0) { + if (!mem_is_num(left)) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), "number"); return -1; } - assert(a->type != 0 && b->type != 0); - if (a->type == b->type || ((a->type | b->type) & MEM_TYPE_DOUBLE) == 0) - return 0; - if (a->type == MEM_TYPE_DOUBLE) { - b->d = b->type == MEM_TYPE_INT ? (double)b->i : (double)b->u; - b->type = MEM_TYPE_DOUBLE; - return 0; - } - assert(b->type == MEM_TYPE_DOUBLE); - a->d = a->type == MEM_TYPE_INT ? (double)a->i : (double)a->u; - a->type = MEM_TYPE_DOUBLE; - return 0; -} - -int -mem_add(const struct Mem *left, const struct Mem *right, struct Mem *result) -{ - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) - return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) - return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - if (a.type == MEM_TYPE_DOUBLE) { - result->u.r = a.d + b.d; - result->type = MEM_TYPE_DOUBLE; - assert(result->flags == 0); + if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) { + double a; + double b; + mem_get_double(left, &a); + mem_get_double(right, &b); + mem_set_double(result, a + b); return 0; } - int64_t res; bool is_neg; - if (sql_add_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_add_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } int mem_sub(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_NUMBER; return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) + } + if (!mem_is_num(right)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), + "number"); return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - if (a.type == MEM_TYPE_DOUBLE) { - result->u.r = a.d - b.d; - result->type = MEM_TYPE_DOUBLE; - assert(result->flags == 0); + } + if (!mem_is_num(left)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), + "number"); + return -1; + } + if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) { + double a; + double b; + mem_get_double(left, &a); + mem_get_double(right, &b); + mem_set_double(result, a - b); return 0; } - int64_t res; bool is_neg; - if (sql_sub_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_sub_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } int mem_mul(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_NUMBER; return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) + } + if (!mem_is_num(right)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), + "number"); return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - if (a.type == MEM_TYPE_DOUBLE) { - result->u.r = a.d * b.d; - result->type = MEM_TYPE_DOUBLE; - assert(result->flags == 0); + } + if (!mem_is_num(left)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), + "number"); + return -1; + } + if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) { + double a; + double b; + mem_get_double(left, &a); + mem_get_double(right, &b); + mem_set_double(result, a * b); return 0; } - int64_t res; bool is_neg; - if (sql_mul_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_mul_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } int mem_div(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_NUMBER; return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) + } + if (!mem_is_num(right)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), + "number"); return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - if (a.type == MEM_TYPE_DOUBLE) { - if (b.d == 0.) { + } + if (!mem_is_num(left)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), + "number"); + return -1; + } + if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) { + double a; + double b; + mem_get_double(left, &a); + mem_get_double(right, &b); + if (b == 0.0) { diag_set(ClientError, ER_SQL_EXECUTE, "division by zero"); return -1; } - result->u.r = a.d / b.d; - result->type = MEM_TYPE_DOUBLE; - assert(result->flags == 0); + mem_set_double(result, a / b); return 0; } - - if (b.i == 0) { + if (right->u.u == 0) { diag_set(ClientError, ER_SQL_EXECUTE, "division by zero"); return -1; } int64_t res; bool is_neg; - if (sql_div_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_div_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } int mem_rem(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_INTEGER; return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) + } + if (!mem_is_int(right)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), + "integer"); return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - /* - * TODO: This operation works wrong when double d > INT64_MAX and - * d < UINT64_MAX. Also, there may be precision losses due to - * conversion integer to double and back. - */ - a.i = a.type == MEM_TYPE_DOUBLE ? (int64_t)a.d : a.i; - b.i = b.type == MEM_TYPE_DOUBLE ? (int64_t)b.d : b.i; - if (b.i == 0) { + } + if (!mem_is_int(left)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), + "integer"); + return -1; + } + if (right->u.u == 0) { diag_set(ClientError, ER_SQL_EXECUTE, "division by zero"); return -1; } int64_t res; bool is_neg; - if (sql_rem_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_rem_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } diff --git a/test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua b/test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua new file mode 100755 index 000000000..9ce595fe0 --- /dev/null +++ b/test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua @@ -0,0 +1,294 @@ +#!/usr/bin/env tarantool +local test = require("sqltester") +test:plan(35) + +-- +-- Make sure that arithmetic operations can only accept numeric values. Also, +-- operation '%' can accept only INTEGER and UNSIGNED values. +-- +test:do_execsql_test( + "gh-5756-1.1", + [[ + SELECT 9 + 2; + ]], { + 11 + }) + +test:do_execsql_test( + "gh-5756-1.2", + [[ + SELECT 9 + -2; + ]], { + 7 + }) + +test:do_execsql_test( + "gh-5756-1.3", + [[ + SELECT 9 + 2.0; + ]], { + 11 + }) + +test:do_catchsql_test( + "gh-5756-1.4", + [[ + SELECT 9 + '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to number" + }) + +test:do_catchsql_test( + "gh-5756-1.5", + [[ + SELECT 9 + x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to number" + }) + +test:do_catchsql_test( + "gh-5756-1.6", + [[ + SELECT 9 + true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to number" + }) + +test:do_catchsql_test( + "gh-5756-1.7", + [[ + SELECT 9 + CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to number" + }) + +test:do_execsql_test( + "gh-5756-2.1", + [[ + SELECT 9 - 2; + ]], { + 7 + }) + +test:do_execsql_test( + "gh-5756-2.2", + [[ + SELECT 9 - -2; + ]], { + 11 + }) + +test:do_execsql_test( + "gh-5756-2.3", + [[ + SELECT 9 - 2.0; + ]], { + 7 + }) + +test:do_catchsql_test( + "gh-5756-2.4", + [[ + SELECT 9 - '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to number" + }) + +test:do_catchsql_test( + "gh-5756-2.5", + [[ + SELECT 9 - x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to number" + }) + +test:do_catchsql_test( + "gh-5756-2.6", + [[ + SELECT 9 - true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to number" + }) + +test:do_catchsql_test( + "gh-5756-2.7", + [[ + SELECT 9 - CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to number" + }) + +test:do_execsql_test( + "gh-5756-3.1", + [[ + SELECT 9 * 2; + ]], { + 18 + }) + +test:do_execsql_test( + "gh-5756-3.2", + [[ + SELECT 9 * -2; + ]], { + -18 + }) + +test:do_execsql_test( + "gh-5756-3.3", + [[ + SELECT 9 * 2.0; + ]], { + 18 + }) + +test:do_catchsql_test( + "gh-5756-3.4", + [[ + SELECT 9 * '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to number" + }) + +test:do_catchsql_test( + "gh-5756-3.5", + [[ + SELECT 9 * x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to number" + }) + +test:do_catchsql_test( + "gh-5756-3.6", + [[ + SELECT 9 * true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to number" + }) + +test:do_catchsql_test( + "gh-5756-3.7", + [[ + SELECT 9 * CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to number" + }) + +test:do_execsql_test( + "gh-5756-4.1", + [[ + SELECT 9 / 2; + ]], { + 4 + }) + +test:do_execsql_test( + "gh-5756-4.2", + [[ + SELECT 9 / -2; + ]], { + -4 + }) + +test:do_execsql_test( + "gh-5756-4.3", + [[ + SELECT 9 / 2.0; + ]], { + 4.5 + }) + +test:do_catchsql_test( + "gh-5756-4.4", + [[ + SELECT 9 / '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to number" + }) + +test:do_catchsql_test( + "gh-5756-4.5", + [[ + SELECT 9 / x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to number" + }) + +test:do_catchsql_test( + "gh-5756-4.6", + [[ + SELECT 9 / true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to number" + }) + +test:do_catchsql_test( + "gh-5756-4.7", + [[ + SELECT 9 / CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to number" + }) + +test:do_execsql_test( + "gh-5756-5.1", + [[ + SELECT 9 % 2; + ]], { + 1 + }) + +test:do_execsql_test( + "gh-5756-5.2", + [[ + SELECT 9 % -2; + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-5756-5.3", + [[ + SELECT 9 % 2.0; + ]], { + 1, "Type mismatch: can not convert double(2.0) to integer" + }) + +test:do_catchsql_test( + "gh-5756-5.4", + [[ + SELECT 9 % '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to integer" + }) + +test:do_catchsql_test( + "gh-5756-5.5", + [[ + SELECT 9 % x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to integer" + }) + +test:do_catchsql_test( + "gh-5756-5.6", + [[ + SELECT 9 % true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to integer" + }) + +test:do_catchsql_test( + "gh-5756-5.7", + [[ + SELECT 9 % CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to integer" + }) + +test:finish_test() diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua index bcd8e665a..52c7945fe 100755 --- a/test/sql-tap/misc1.test.lua +++ b/test/sql-tap/misc1.test.lua @@ -68,7 +68,9 @@ test:do_test( cmd = cmd .. ")" test:execsql(cmd) end - return test:execsql("SELECT x50 FROM manycol ORDER BY x80+0") + return test:execsql([[ + SELECT x50 FROM manycol ORDER BY CAST(x80 AS INTEGER); + ]]) end, { -- <misc1-1.3.1> "50", "150", "250", "350", "450", "550", "650", "750", "850", "950", "1050" @@ -527,11 +529,12 @@ test:do_execsql_test( -- </misc1-10.6> }) +local cast = "CAST(CAST(x1 AS INTEGER) + 1 AS STRING)" test:do_test( "misc1-10.7", function() where = string.gsub(where, "x0=0", "x0=100") - return test:catchsql("UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where.."") + return test:catchsql("UPDATE manycol SET x1 = "..cast.." "..where..";") end, { -- <misc1-10.7> 0 @@ -553,7 +556,7 @@ test:do_execsql_test( -- } {0 {}} test:do_execsql_test( "misc1-10.9", - "UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where + "UPDATE manycol SET x1 = "..cast.." "..where --"UPDATE manycol SET x1=x1+1 $::where AND rowid>0" , {}) diff --git a/test/sql-tap/misc3.test.lua b/test/sql-tap/misc3.test.lua index 313484b5d..6e063a059 100755 --- a/test/sql-tap/misc3.test.lua +++ b/test/sql-tap/misc3.test.lua @@ -143,7 +143,7 @@ test:do_execsql_test( test:do_execsql_test( "misc3-2.6", [[ - SELECT '-2.0e-127' * '-0.5e27' + SELECT CAST('-2.0e-127' AS DOUBLE) * CAST('-0.5e27' AS DOUBLE); ]], { -- <misc3-2.6> 1e-100 @@ -153,7 +153,7 @@ test:do_execsql_test( test:do_execsql_test( "misc3-2.7", [[ - SELECT '+2.0e-127' * '-0.5e27' + SELECT CAST('+2.0e-127' AS DOUBLE) * CAST('-0.5e27' AS DOUBLE); ]], { -- <misc3-2.7> -1e-100 @@ -163,7 +163,7 @@ test:do_execsql_test( test:do_execsql_test( "misc3-2.8", [[ - SELECT 2.0e-27 * '+0.5e+127' + SELECT 2.0e-27 * CAST('+0.5e+127' AS DOUBLE); ]], { -- <misc3-2.8> 1e+100 @@ -173,7 +173,7 @@ test:do_execsql_test( test:do_execsql_test( "misc3-2.9", [[ - SELECT 2.0e-27 * '+0.000005e+132' + SELECT 2.0e-27 * CAST('+0.000005e+132' AS DOUBLE); ]], { -- <misc3-2.9> 1e+100 diff --git a/test/sql-tap/tkt-a8a0d2996a.test.lua b/test/sql-tap/tkt-a8a0d2996a.test.lua index 0dfa4653d..e97ff8c60 100755 --- a/test/sql-tap/tkt-a8a0d2996a.test.lua +++ b/test/sql-tap/tkt-a8a0d2996a.test.lua @@ -19,7 +19,7 @@ test:plan(12) -- -- ["set","testdir",[["file","dirname",["argv0"]]]] -- ["source",[["testdir"],"\/tester.tcl"]] -test:do_execsql_test( +test:do_catchsql_test( 1.0, [[ CREATE TABLE t(id INT PRIMARY KEY, x TEXT UNIQUE, y TEXT); @@ -27,98 +27,98 @@ test:do_execsql_test( SELECT typeof(x), typeof(y) FROM t WHERE 1=x+0 AND y=='1'; ]], { -- <1.0> - "string", "string" + 1, "Type mismatch: can not convert string('1') to number" -- </1.0> }) -test:do_execsql_test( +test:do_catchsql_test( 1.1, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x-0 AND y=='1'; ]], { -- <1.1> - "string", "string" + 1, "Type mismatch: can not convert string('1') to number" -- </1.1> }) -test:do_execsql_test( +test:do_catchsql_test( 1.2, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x*1 AND y=='1'; ]], { -- <1.2> - "string", "string" + 1, "Type mismatch: can not convert string('1') to number" -- </1.2> }) -test:do_execsql_test( +test:do_catchsql_test( 1.3, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x/1 AND y=='1'; ]], { -- <1.3> - "string", "string" + 1, "Type mismatch: can not convert string('1') to number" -- </1.3> }) -test:do_execsql_test( +test:do_catchsql_test( 1.4, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x%4 AND y=='1'; ]], { -- <1.4> - "string", "string" + 1, "Type mismatch: can not convert string('1') to integer" -- </1.4> }) -test:do_execsql_test( +test:do_catchsql_test( 3.0, [[ UPDATE t SET x='1.0'; SELECT typeof(x), typeof(y) FROM t WHERE 1=x+0 AND y=='1'; ]], { -- <3.0> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to number" -- </3.0> }) -test:do_execsql_test( +test:do_catchsql_test( 3.1, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x-0 AND y=='1'; ]], { -- <3.1> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to number" -- </3.1> }) -test:do_execsql_test( +test:do_catchsql_test( 3.2, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x*1 AND y=='1'; ]], { -- <3.2> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to number" -- </3.2> }) -test:do_execsql_test( +test:do_catchsql_test( 3.3, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x/1 AND y=='1'; ]], { -- <3.3> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to number" -- </3.3> }) -test:do_execsql_test( +test:do_catchsql_test( 3.4, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x%4 AND y=='1'; ]], { -- <3.4> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to integer" -- </3.4> }) @@ -132,13 +132,13 @@ test:do_execsql_test( -- </4.0> }) -test:do_execsql_test( +test:do_catchsql_test( 4.1, [[ SELECT '1.23e64'/'1.0000e+62'; ]], { -- <4.1> - 123.0 + 1, "Type mismatch: can not convert string('1.0000e+62') to number" -- </4.1> }) diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua index 70683a4fd..643c8f67d 100755 --- a/test/sql-tap/uuid.test.lua +++ b/test/sql-tap/uuid.test.lua @@ -1011,7 +1011,7 @@ test:do_catchsql_test( [[ SELECT u % 1 FROM t2; ]], { - 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number" + 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to integer" }) -- Check that bitwise operations work with UUIDs as intended. diff --git a/test/sql/boolean.result b/test/sql/boolean.result index 5594d92cb..4e69b8ef4 100644 --- a/test/sql/boolean.result +++ b/test/sql/boolean.result @@ -1218,22 +1218,22 @@ SELECT false / false; SELECT true % true; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT true % false; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT false % true; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT false % false; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a, true + a FROM t; @@ -1279,12 +1279,12 @@ SELECT a, false / a FROM t; SELECT a, true % a FROM t; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a, false % a FROM t; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a, a + true FROM t; | --- @@ -1329,12 +1329,12 @@ SELECT a, a / false FROM t; SELECT a, a % true FROM t; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT a, a % false FROM t; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a, a1, a + a1 FROM t, t6; @@ -1360,7 +1360,7 @@ SELECT a, a1, a / a1 FROM t, t6; SELECT a, a1, a % a1 FROM t, t6; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT ~true; @@ -2683,12 +2683,12 @@ SELECT false / 2; SELECT true % 2; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT false % 2; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT 2 + true; | --- @@ -2733,12 +2733,12 @@ SELECT 2 / false; SELECT 2 % true; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT 2 % false; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, a1 + 2 FROM t6 @@ -2764,7 +2764,7 @@ SELECT a1, a1 / 2 FROM t6 SELECT a1, a1 % 2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, 2 + a1 FROM t6 | --- @@ -2789,7 +2789,7 @@ SELECT a1, 2 / a1 FROM t6 SELECT a1, 2 % a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a2, a2 + 2 FROM t6 | --- @@ -2814,7 +2814,7 @@ SELECT a2, a2 / 2 FROM t6 SELECT a2, a2 % 2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT a2, 2 + a2 FROM t6 | --- @@ -2839,7 +2839,7 @@ SELECT a2, 2 / a2 FROM t6 SELECT a2, 2 % a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT b, true + b FROM t7; @@ -2885,12 +2885,12 @@ SELECT b, false / b FROM t7; SELECT b, true % b FROM t7; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT b, false % b FROM t7; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT b, b + true FROM t7; | --- @@ -2935,12 +2935,12 @@ SELECT b, b / false FROM t7; SELECT b, b % true FROM t7; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT b, b % false FROM t7; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, b, a1 + b FROM t6, t7; @@ -2966,7 +2966,7 @@ SELECT a1, b, a1 / b FROM t6, t7; SELECT a1, b, a1 % b FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, b, b + a1 FROM t6, t7; | --- @@ -2991,7 +2991,7 @@ SELECT a1, b, b / a1 FROM t6, t7; SELECT a1, b, b % a1 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a2, b, a2 + b FROM t6, t7; | --- @@ -3016,7 +3016,7 @@ SELECT a2, b, a2 / b FROM t6, t7; SELECT a2, b, a2 % b FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT a2, b, b + a2 FROM t6, t7; | --- @@ -3041,7 +3041,7 @@ SELECT a2, b, b / a2 FROM t6, t7; SELECT a2, b, b % a2 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT true & 2; @@ -4152,12 +4152,12 @@ SELECT false / 2.3; SELECT true % 2.3; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert double(2.3) to integer' | ... SELECT false % 2.3; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert double(2.3) to integer' | ... SELECT 2.3 + true; | --- @@ -4202,12 +4202,12 @@ SELECT 2.3 / false; SELECT 2.3 % true; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT 2.3 % false; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, a1 + 2.3 FROM t6 @@ -4233,7 +4233,7 @@ SELECT a1, a1 / 2.3 FROM t6 SELECT a1, a1 % 2.3 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert double(2.3) to integer' | ... SELECT a1, 2.3 + a1 FROM t6 | --- @@ -4258,7 +4258,7 @@ SELECT a1, 2.3 / a1 FROM t6 SELECT a1, 2.3 % a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a2, a2 + 2.3 FROM t6 | --- @@ -4283,7 +4283,7 @@ SELECT a2, a2 / 2.3 FROM t6 SELECT a2, a2 % 2.3 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert double(2.3) to integer' | ... SELECT a2, 2.3 + a2 FROM t6 | --- @@ -4308,7 +4308,7 @@ SELECT a2, 2.3 / a2 FROM t6 SELECT a2, 2.3 % a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT c, true + c FROM t8; @@ -4354,12 +4354,12 @@ SELECT c, false / c FROM t8; SELECT c, true % c FROM t8; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert double(4.56) to integer' | ... SELECT c, false % c FROM t8; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert double(4.56) to integer' | ... SELECT c, c + true FROM t8; | --- @@ -4404,12 +4404,12 @@ SELECT c, c / false FROM t8; SELECT c, c % true FROM t8; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT c, c % false FROM t8; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, c, a1 + c FROM t6, t8; @@ -4435,7 +4435,7 @@ SELECT a1, c, a1 / c FROM t6, t8; SELECT a1, c, a1 % c FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert double(4.56) to integer' | ... SELECT a1, c, c + a1 FROM t6, t8; | --- @@ -4460,7 +4460,7 @@ SELECT a1, c, c / a1 FROM t6, t8; SELECT a1, c, c % a1 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a2, c, a2 + c FROM t6, t8; | --- @@ -4485,7 +4485,7 @@ SELECT a2, c, a2 / c FROM t6, t8; SELECT a2, c, a2 % c FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert double(4.56) to integer' | ... SELECT a2, c, c + a2 FROM t6, t8; | --- @@ -4510,7 +4510,7 @@ SELECT a2, c, c / a2 FROM t6, t8; SELECT a2, c, c % a2 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT true > 2.3; diff --git a/test/sql/types.result b/test/sql/types.result index a0b8668be..62abc6eb6 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -310,11 +310,8 @@ box.execute('SELECT 1 + 1.1;') ... box.execute('SELECT \'9223372036854\' + 1;') --- -- metadata: - - name: COLUMN_1 - type: integer - rows: - - [9223372036855] +- null +- 'Type mismatch: can not convert string(''9223372036854'') to number' ... -- Fix BOOLEAN bindings. box.execute('SELECT ?', {true}) -- 2.25.1
Hi! Thanks for the patch! LGTM.
After this patch, arithmetic operations will only accept numeric values. For the "%" operation, the rules have become even stricter, now it accepts only INTEGER and UNSIGNED values. Part of #4470 Closes #5756 --- https://github.com/tarantool/tarantool/issues/5756 https://github.com/tarantool/tarantool/tree/imeevma/gh-5756-implicit-cast-in-arithmetic ...5756-remove-implicit-cast-in-arithmetic.md | 3 + src/box/sql/mem.c | 207 ++++++------ ...-5756-implicit-cast-in-arithmetic.test.lua | 294 ++++++++++++++++++ test/sql-tap/misc1.test.lua | 9 +- test/sql-tap/misc3.test.lua | 8 +- test/sql-tap/tkt-a8a0d2996a.test.lua | 44 +-- test/sql-tap/uuid.test.lua | 2 +- test/sql/boolean.result | 82 ++--- test/sql/types.result | 7 +- 9 files changed, 476 insertions(+), 180 deletions(-) create mode 100644 changelogs/unreleased/gh-5756-remove-implicit-cast-in-arithmetic.md create mode 100755 test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua diff --git a/changelogs/unreleased/gh-5756-remove-implicit-cast-in-arithmetic.md b/changelogs/unreleased/gh-5756-remove-implicit-cast-in-arithmetic.md new file mode 100644 index 000000000..4b21fed59 --- /dev/null +++ b/changelogs/unreleased/gh-5756-remove-implicit-cast-in-arithmetic.md @@ -0,0 +1,3 @@ +## bugfix/sql + +* All arithmetic operations can now only accept numeric values (gh-5756). diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c index e4ce233e0..299fa1898 100644 --- a/src/box/sql/mem.c +++ b/src/box/sql/mem.c @@ -1580,194 +1580,193 @@ get_number(const struct Mem *mem, struct sql_num *number) return -1; } -static int -arithmetic_prepare(const struct Mem *left, const struct Mem *right, - struct sql_num *a, struct sql_num *b) +int +mem_add(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (get_number(right, b) != 0) { + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_NUMBER; + return 0; + } + if (!mem_is_num(right)) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), "number"); return -1; } - if (get_number(left, a) != 0) { + if (!mem_is_num(left)) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), "number"); return -1; } - assert(a->type != 0 && b->type != 0); - if (a->type == b->type || ((a->type | b->type) & MEM_TYPE_DOUBLE) == 0) - return 0; - if (a->type == MEM_TYPE_DOUBLE) { - b->d = b->type == MEM_TYPE_INT ? (double)b->i : (double)b->u; - b->type = MEM_TYPE_DOUBLE; - return 0; - } - assert(b->type == MEM_TYPE_DOUBLE); - a->d = a->type == MEM_TYPE_INT ? (double)a->i : (double)a->u; - a->type = MEM_TYPE_DOUBLE; - return 0; -} - -int -mem_add(const struct Mem *left, const struct Mem *right, struct Mem *result) -{ - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) - return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) - return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - if (a.type == MEM_TYPE_DOUBLE) { - result->u.r = a.d + b.d; - result->type = MEM_TYPE_DOUBLE; - assert(result->flags == 0); + if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) { + double a; + double b; + mem_get_double(left, &a); + mem_get_double(right, &b); + mem_set_double(result, a + b); return 0; } - int64_t res; bool is_neg; - if (sql_add_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_add_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } int mem_sub(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_NUMBER; return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) + } + if (!mem_is_num(right)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), + "number"); return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - if (a.type == MEM_TYPE_DOUBLE) { - result->u.r = a.d - b.d; - result->type = MEM_TYPE_DOUBLE; - assert(result->flags == 0); + } + if (!mem_is_num(left)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), + "number"); + return -1; + } + if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) { + double a; + double b; + mem_get_double(left, &a); + mem_get_double(right, &b); + mem_set_double(result, a - b); return 0; } - int64_t res; bool is_neg; - if (sql_sub_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_sub_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } int mem_mul(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_NUMBER; return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) + } + if (!mem_is_num(right)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), + "number"); return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - if (a.type == MEM_TYPE_DOUBLE) { - result->u.r = a.d * b.d; - result->type = MEM_TYPE_DOUBLE; - assert(result->flags == 0); + } + if (!mem_is_num(left)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), + "number"); + return -1; + } + if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) { + double a; + double b; + mem_get_double(left, &a); + mem_get_double(right, &b); + mem_set_double(result, a * b); return 0; } - int64_t res; bool is_neg; - if (sql_mul_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_mul_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } int mem_div(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_NUMBER; return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) + } + if (!mem_is_num(right)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), + "number"); return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - if (a.type == MEM_TYPE_DOUBLE) { - if (b.d == 0.) { + } + if (!mem_is_num(left)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), + "number"); + return -1; + } + if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) { + double a; + double b; + mem_get_double(left, &a); + mem_get_double(right, &b); + if (b == 0.0) { diag_set(ClientError, ER_SQL_EXECUTE, "division by zero"); return -1; } - result->u.r = a.d / b.d; - result->type = MEM_TYPE_DOUBLE; - assert(result->flags == 0); + mem_set_double(result, a / b); return 0; } - - if (b.i == 0) { + if (right->u.u == 0) { diag_set(ClientError, ER_SQL_EXECUTE, "division by zero"); return -1; } int64_t res; bool is_neg; - if (sql_div_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_div_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } int mem_rem(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_INTEGER; return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) + } + if (!mem_is_int(right)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), + "integer"); return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - /* - * TODO: This operation works wrong when double d > INT64_MAX and - * d < UINT64_MAX. Also, there may be precision losses due to - * conversion integer to double and back. - */ - a.i = a.type == MEM_TYPE_DOUBLE ? (int64_t)a.d : a.i; - b.i = b.type == MEM_TYPE_DOUBLE ? (int64_t)b.d : b.i; - if (b.i == 0) { + } + if (!mem_is_int(left)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), + "integer"); + return -1; + } + if (right->u.u == 0) { diag_set(ClientError, ER_SQL_EXECUTE, "division by zero"); return -1; } int64_t res; bool is_neg; - if (sql_rem_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_rem_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } diff --git a/test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua b/test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua new file mode 100755 index 000000000..9ce595fe0 --- /dev/null +++ b/test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua @@ -0,0 +1,294 @@ +#!/usr/bin/env tarantool +local test = require("sqltester") +test:plan(35) + +-- +-- Make sure that arithmetic operations can only accept numeric values. Also, +-- operation '%' can accept only INTEGER and UNSIGNED values. +-- +test:do_execsql_test( + "gh-5756-1.1", + [[ + SELECT 9 + 2; + ]], { + 11 + }) + +test:do_execsql_test( + "gh-5756-1.2", + [[ + SELECT 9 + -2; + ]], { + 7 + }) + +test:do_execsql_test( + "gh-5756-1.3", + [[ + SELECT 9 + 2.0; + ]], { + 11 + }) + +test:do_catchsql_test( + "gh-5756-1.4", + [[ + SELECT 9 + '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to number" + }) + +test:do_catchsql_test( + "gh-5756-1.5", + [[ + SELECT 9 + x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to number" + }) + +test:do_catchsql_test( + "gh-5756-1.6", + [[ + SELECT 9 + true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to number" + }) + +test:do_catchsql_test( + "gh-5756-1.7", + [[ + SELECT 9 + CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to number" + }) + +test:do_execsql_test( + "gh-5756-2.1", + [[ + SELECT 9 - 2; + ]], { + 7 + }) + +test:do_execsql_test( + "gh-5756-2.2", + [[ + SELECT 9 - -2; + ]], { + 11 + }) + +test:do_execsql_test( + "gh-5756-2.3", + [[ + SELECT 9 - 2.0; + ]], { + 7 + }) + +test:do_catchsql_test( + "gh-5756-2.4", + [[ + SELECT 9 - '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to number" + }) + +test:do_catchsql_test( + "gh-5756-2.5", + [[ + SELECT 9 - x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to number" + }) + +test:do_catchsql_test( + "gh-5756-2.6", + [[ + SELECT 9 - true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to number" + }) + +test:do_catchsql_test( + "gh-5756-2.7", + [[ + SELECT 9 - CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to number" + }) + +test:do_execsql_test( + "gh-5756-3.1", + [[ + SELECT 9 * 2; + ]], { + 18 + }) + +test:do_execsql_test( + "gh-5756-3.2", + [[ + SELECT 9 * -2; + ]], { + -18 + }) + +test:do_execsql_test( + "gh-5756-3.3", + [[ + SELECT 9 * 2.0; + ]], { + 18 + }) + +test:do_catchsql_test( + "gh-5756-3.4", + [[ + SELECT 9 * '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to number" + }) + +test:do_catchsql_test( + "gh-5756-3.5", + [[ + SELECT 9 * x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to number" + }) + +test:do_catchsql_test( + "gh-5756-3.6", + [[ + SELECT 9 * true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to number" + }) + +test:do_catchsql_test( + "gh-5756-3.7", + [[ + SELECT 9 * CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to number" + }) + +test:do_execsql_test( + "gh-5756-4.1", + [[ + SELECT 9 / 2; + ]], { + 4 + }) + +test:do_execsql_test( + "gh-5756-4.2", + [[ + SELECT 9 / -2; + ]], { + -4 + }) + +test:do_execsql_test( + "gh-5756-4.3", + [[ + SELECT 9 / 2.0; + ]], { + 4.5 + }) + +test:do_catchsql_test( + "gh-5756-4.4", + [[ + SELECT 9 / '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to number" + }) + +test:do_catchsql_test( + "gh-5756-4.5", + [[ + SELECT 9 / x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to number" + }) + +test:do_catchsql_test( + "gh-5756-4.6", + [[ + SELECT 9 / true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to number" + }) + +test:do_catchsql_test( + "gh-5756-4.7", + [[ + SELECT 9 / CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to number" + }) + +test:do_execsql_test( + "gh-5756-5.1", + [[ + SELECT 9 % 2; + ]], { + 1 + }) + +test:do_execsql_test( + "gh-5756-5.2", + [[ + SELECT 9 % -2; + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-5756-5.3", + [[ + SELECT 9 % 2.0; + ]], { + 1, "Type mismatch: can not convert double(2.0) to integer" + }) + +test:do_catchsql_test( + "gh-5756-5.4", + [[ + SELECT 9 % '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to integer" + }) + +test:do_catchsql_test( + "gh-5756-5.5", + [[ + SELECT 9 % x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to integer" + }) + +test:do_catchsql_test( + "gh-5756-5.6", + [[ + SELECT 9 % true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to integer" + }) + +test:do_catchsql_test( + "gh-5756-5.7", + [[ + SELECT 9 % CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to integer" + }) + +test:finish_test() diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua index bcd8e665a..52c7945fe 100755 --- a/test/sql-tap/misc1.test.lua +++ b/test/sql-tap/misc1.test.lua @@ -68,7 +68,9 @@ test:do_test( cmd = cmd .. ")" test:execsql(cmd) end - return test:execsql("SELECT x50 FROM manycol ORDER BY x80+0") + return test:execsql([[ + SELECT x50 FROM manycol ORDER BY CAST(x80 AS INTEGER); + ]]) end, { -- <misc1-1.3.1> "50", "150", "250", "350", "450", "550", "650", "750", "850", "950", "1050" @@ -527,11 +529,12 @@ test:do_execsql_test( -- </misc1-10.6> }) +local cast = "CAST(CAST(x1 AS INTEGER) + 1 AS STRING)" test:do_test( "misc1-10.7", function() where = string.gsub(where, "x0=0", "x0=100") - return test:catchsql("UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where.."") + return test:catchsql("UPDATE manycol SET x1 = "..cast.." "..where..";") end, { -- <misc1-10.7> 0 @@ -553,7 +556,7 @@ test:do_execsql_test( -- } {0 {}} test:do_execsql_test( "misc1-10.9", - "UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where + "UPDATE manycol SET x1 = "..cast.." "..where --"UPDATE manycol SET x1=x1+1 $::where AND rowid>0" , {}) diff --git a/test/sql-tap/misc3.test.lua b/test/sql-tap/misc3.test.lua index 313484b5d..6e063a059 100755 --- a/test/sql-tap/misc3.test.lua +++ b/test/sql-tap/misc3.test.lua @@ -143,7 +143,7 @@ test:do_execsql_test( test:do_execsql_test( "misc3-2.6", [[ - SELECT '-2.0e-127' * '-0.5e27' + SELECT CAST('-2.0e-127' AS DOUBLE) * CAST('-0.5e27' AS DOUBLE); ]], { -- <misc3-2.6> 1e-100 @@ -153,7 +153,7 @@ test:do_execsql_test( test:do_execsql_test( "misc3-2.7", [[ - SELECT '+2.0e-127' * '-0.5e27' + SELECT CAST('+2.0e-127' AS DOUBLE) * CAST('-0.5e27' AS DOUBLE); ]], { -- <misc3-2.7> -1e-100 @@ -163,7 +163,7 @@ test:do_execsql_test( test:do_execsql_test( "misc3-2.8", [[ - SELECT 2.0e-27 * '+0.5e+127' + SELECT 2.0e-27 * CAST('+0.5e+127' AS DOUBLE); ]], { -- <misc3-2.8> 1e+100 @@ -173,7 +173,7 @@ test:do_execsql_test( test:do_execsql_test( "misc3-2.9", [[ - SELECT 2.0e-27 * '+0.000005e+132' + SELECT 2.0e-27 * CAST('+0.000005e+132' AS DOUBLE); ]], { -- <misc3-2.9> 1e+100 diff --git a/test/sql-tap/tkt-a8a0d2996a.test.lua b/test/sql-tap/tkt-a8a0d2996a.test.lua index 0dfa4653d..e97ff8c60 100755 --- a/test/sql-tap/tkt-a8a0d2996a.test.lua +++ b/test/sql-tap/tkt-a8a0d2996a.test.lua @@ -19,7 +19,7 @@ test:plan(12) -- -- ["set","testdir",[["file","dirname",["argv0"]]]] -- ["source",[["testdir"],"\/tester.tcl"]] -test:do_execsql_test( +test:do_catchsql_test( 1.0, [[ CREATE TABLE t(id INT PRIMARY KEY, x TEXT UNIQUE, y TEXT); @@ -27,98 +27,98 @@ test:do_execsql_test( SELECT typeof(x), typeof(y) FROM t WHERE 1=x+0 AND y=='1'; ]], { -- <1.0> - "string", "string" + 1, "Type mismatch: can not convert string('1') to number" -- </1.0> }) -test:do_execsql_test( +test:do_catchsql_test( 1.1, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x-0 AND y=='1'; ]], { -- <1.1> - "string", "string" + 1, "Type mismatch: can not convert string('1') to number" -- </1.1> }) -test:do_execsql_test( +test:do_catchsql_test( 1.2, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x*1 AND y=='1'; ]], { -- <1.2> - "string", "string" + 1, "Type mismatch: can not convert string('1') to number" -- </1.2> }) -test:do_execsql_test( +test:do_catchsql_test( 1.3, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x/1 AND y=='1'; ]], { -- <1.3> - "string", "string" + 1, "Type mismatch: can not convert string('1') to number" -- </1.3> }) -test:do_execsql_test( +test:do_catchsql_test( 1.4, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x%4 AND y=='1'; ]], { -- <1.4> - "string", "string" + 1, "Type mismatch: can not convert string('1') to integer" -- </1.4> }) -test:do_execsql_test( +test:do_catchsql_test( 3.0, [[ UPDATE t SET x='1.0'; SELECT typeof(x), typeof(y) FROM t WHERE 1=x+0 AND y=='1'; ]], { -- <3.0> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to number" -- </3.0> }) -test:do_execsql_test( +test:do_catchsql_test( 3.1, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x-0 AND y=='1'; ]], { -- <3.1> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to number" -- </3.1> }) -test:do_execsql_test( +test:do_catchsql_test( 3.2, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x*1 AND y=='1'; ]], { -- <3.2> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to number" -- </3.2> }) -test:do_execsql_test( +test:do_catchsql_test( 3.3, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x/1 AND y=='1'; ]], { -- <3.3> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to number" -- </3.3> }) -test:do_execsql_test( +test:do_catchsql_test( 3.4, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x%4 AND y=='1'; ]], { -- <3.4> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to integer" -- </3.4> }) @@ -132,13 +132,13 @@ test:do_execsql_test( -- </4.0> }) -test:do_execsql_test( +test:do_catchsql_test( 4.1, [[ SELECT '1.23e64'/'1.0000e+62'; ]], { -- <4.1> - 123.0 + 1, "Type mismatch: can not convert string('1.0000e+62') to number" -- </4.1> }) diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua index 70683a4fd..643c8f67d 100755 --- a/test/sql-tap/uuid.test.lua +++ b/test/sql-tap/uuid.test.lua @@ -1011,7 +1011,7 @@ test:do_catchsql_test( [[ SELECT u % 1 FROM t2; ]], { - 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number" + 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to integer" }) -- Check that bitwise operations work with UUIDs as intended. diff --git a/test/sql/boolean.result b/test/sql/boolean.result index 5594d92cb..4e69b8ef4 100644 --- a/test/sql/boolean.result +++ b/test/sql/boolean.result @@ -1218,22 +1218,22 @@ SELECT false / false; SELECT true % true; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT true % false; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT false % true; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT false % false; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a, true + a FROM t; @@ -1279,12 +1279,12 @@ SELECT a, false / a FROM t; SELECT a, true % a FROM t; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a, false % a FROM t; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a, a + true FROM t; | --- @@ -1329,12 +1329,12 @@ SELECT a, a / false FROM t; SELECT a, a % true FROM t; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT a, a % false FROM t; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a, a1, a + a1 FROM t, t6; @@ -1360,7 +1360,7 @@ SELECT a, a1, a / a1 FROM t, t6; SELECT a, a1, a % a1 FROM t, t6; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT ~true; @@ -2683,12 +2683,12 @@ SELECT false / 2; SELECT true % 2; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT false % 2; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT 2 + true; | --- @@ -2733,12 +2733,12 @@ SELECT 2 / false; SELECT 2 % true; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT 2 % false; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, a1 + 2 FROM t6 @@ -2764,7 +2764,7 @@ SELECT a1, a1 / 2 FROM t6 SELECT a1, a1 % 2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, 2 + a1 FROM t6 | --- @@ -2789,7 +2789,7 @@ SELECT a1, 2 / a1 FROM t6 SELECT a1, 2 % a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a2, a2 + 2 FROM t6 | --- @@ -2814,7 +2814,7 @@ SELECT a2, a2 / 2 FROM t6 SELECT a2, a2 % 2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT a2, 2 + a2 FROM t6 | --- @@ -2839,7 +2839,7 @@ SELECT a2, 2 / a2 FROM t6 SELECT a2, 2 % a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT b, true + b FROM t7; @@ -2885,12 +2885,12 @@ SELECT b, false / b FROM t7; SELECT b, true % b FROM t7; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT b, false % b FROM t7; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT b, b + true FROM t7; | --- @@ -2935,12 +2935,12 @@ SELECT b, b / false FROM t7; SELECT b, b % true FROM t7; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT b, b % false FROM t7; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, b, a1 + b FROM t6, t7; @@ -2966,7 +2966,7 @@ SELECT a1, b, a1 / b FROM t6, t7; SELECT a1, b, a1 % b FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, b, b + a1 FROM t6, t7; | --- @@ -2991,7 +2991,7 @@ SELECT a1, b, b / a1 FROM t6, t7; SELECT a1, b, b % a1 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a2, b, a2 + b FROM t6, t7; | --- @@ -3016,7 +3016,7 @@ SELECT a2, b, a2 / b FROM t6, t7; SELECT a2, b, a2 % b FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT a2, b, b + a2 FROM t6, t7; | --- @@ -3041,7 +3041,7 @@ SELECT a2, b, b / a2 FROM t6, t7; SELECT a2, b, b % a2 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT true & 2; @@ -4152,12 +4152,12 @@ SELECT false / 2.3; SELECT true % 2.3; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert double(2.3) to integer' | ... SELECT false % 2.3; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert double(2.3) to integer' | ... SELECT 2.3 + true; | --- @@ -4202,12 +4202,12 @@ SELECT 2.3 / false; SELECT 2.3 % true; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT 2.3 % false; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, a1 + 2.3 FROM t6 @@ -4233,7 +4233,7 @@ SELECT a1, a1 / 2.3 FROM t6 SELECT a1, a1 % 2.3 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert double(2.3) to integer' | ... SELECT a1, 2.3 + a1 FROM t6 | --- @@ -4258,7 +4258,7 @@ SELECT a1, 2.3 / a1 FROM t6 SELECT a1, 2.3 % a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a2, a2 + 2.3 FROM t6 | --- @@ -4283,7 +4283,7 @@ SELECT a2, a2 / 2.3 FROM t6 SELECT a2, a2 % 2.3 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert double(2.3) to integer' | ... SELECT a2, 2.3 + a2 FROM t6 | --- @@ -4308,7 +4308,7 @@ SELECT a2, 2.3 / a2 FROM t6 SELECT a2, 2.3 % a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT c, true + c FROM t8; @@ -4354,12 +4354,12 @@ SELECT c, false / c FROM t8; SELECT c, true % c FROM t8; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert double(4.56) to integer' | ... SELECT c, false % c FROM t8; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert double(4.56) to integer' | ... SELECT c, c + true FROM t8; | --- @@ -4404,12 +4404,12 @@ SELECT c, c / false FROM t8; SELECT c, c % true FROM t8; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT c, c % false FROM t8; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, c, a1 + c FROM t6, t8; @@ -4435,7 +4435,7 @@ SELECT a1, c, a1 / c FROM t6, t8; SELECT a1, c, a1 % c FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert double(4.56) to integer' | ... SELECT a1, c, c + a1 FROM t6, t8; | --- @@ -4460,7 +4460,7 @@ SELECT a1, c, c / a1 FROM t6, t8; SELECT a1, c, c % a1 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a2, c, a2 + c FROM t6, t8; | --- @@ -4485,7 +4485,7 @@ SELECT a2, c, a2 / c FROM t6, t8; SELECT a2, c, a2 % c FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert double(4.56) to integer' | ... SELECT a2, c, c + a2 FROM t6, t8; | --- @@ -4510,7 +4510,7 @@ SELECT a2, c, c / a2 FROM t6, t8; SELECT a2, c, c % a2 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT true > 2.3; diff --git a/test/sql/types.result b/test/sql/types.result index c1e1a8ef1..ceafaadc9 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -310,11 +310,8 @@ box.execute('SELECT 1 + 1.1;') ... box.execute('SELECT \'9223372036854\' + 1;') --- -- metadata: - - name: COLUMN_1 - type: integer - rows: - - [9223372036855] +- null +- 'Type mismatch: can not convert string(''9223372036854'') to number' ... -- Fix BOOLEAN bindings. box.execute('SELECT ?', {true}) -- 2.25.1
After this patch, arithmetic operations will only accept numeric values. For the "%" operation, the rules have become even stricter, now it accepts only INTEGER and UNSIGNED values. Part of #4470 Closes #5756 --- https://github.com/tarantool/tarantool/issues/5756 https://github.com/tarantool/tarantool/tree/imeevma/gh-5756-implicit-cast-in-arithmetic ...5756-remove-implicit-cast-in-arithmetic.md | 3 + src/box/sql/mem.c | 207 ++++++------ ...-5756-implicit-cast-in-arithmetic.test.lua | 294 ++++++++++++++++++ test/sql-tap/misc1.test.lua | 9 +- test/sql-tap/misc3.test.lua | 8 +- test/sql-tap/tkt-a8a0d2996a.test.lua | 44 +-- test/sql-tap/uuid.test.lua | 2 +- test/sql/boolean.result | 82 ++--- test/sql/types.result | 7 +- 9 files changed, 476 insertions(+), 180 deletions(-) create mode 100644 changelogs/unreleased/gh-5756-remove-implicit-cast-in-arithmetic.md create mode 100755 test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua diff --git a/changelogs/unreleased/gh-5756-remove-implicit-cast-in-arithmetic.md b/changelogs/unreleased/gh-5756-remove-implicit-cast-in-arithmetic.md new file mode 100644 index 000000000..4b21fed59 --- /dev/null +++ b/changelogs/unreleased/gh-5756-remove-implicit-cast-in-arithmetic.md @@ -0,0 +1,3 @@ +## bugfix/sql + +* All arithmetic operations can now only accept numeric values (gh-5756). diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c index e4ce233e0..299fa1898 100644 --- a/src/box/sql/mem.c +++ b/src/box/sql/mem.c @@ -1580,194 +1580,193 @@ get_number(const struct Mem *mem, struct sql_num *number) return -1; } -static int -arithmetic_prepare(const struct Mem *left, const struct Mem *right, - struct sql_num *a, struct sql_num *b) +int +mem_add(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (get_number(right, b) != 0) { + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_NUMBER; + return 0; + } + if (!mem_is_num(right)) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), "number"); return -1; } - if (get_number(left, a) != 0) { + if (!mem_is_num(left)) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), "number"); return -1; } - assert(a->type != 0 && b->type != 0); - if (a->type == b->type || ((a->type | b->type) & MEM_TYPE_DOUBLE) == 0) - return 0; - if (a->type == MEM_TYPE_DOUBLE) { - b->d = b->type == MEM_TYPE_INT ? (double)b->i : (double)b->u; - b->type = MEM_TYPE_DOUBLE; - return 0; - } - assert(b->type == MEM_TYPE_DOUBLE); - a->d = a->type == MEM_TYPE_INT ? (double)a->i : (double)a->u; - a->type = MEM_TYPE_DOUBLE; - return 0; -} - -int -mem_add(const struct Mem *left, const struct Mem *right, struct Mem *result) -{ - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) - return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) - return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - if (a.type == MEM_TYPE_DOUBLE) { - result->u.r = a.d + b.d; - result->type = MEM_TYPE_DOUBLE; - assert(result->flags == 0); + if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) { + double a; + double b; + mem_get_double(left, &a); + mem_get_double(right, &b); + mem_set_double(result, a + b); return 0; } - int64_t res; bool is_neg; - if (sql_add_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_add_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } int mem_sub(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_NUMBER; return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) + } + if (!mem_is_num(right)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), + "number"); return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - if (a.type == MEM_TYPE_DOUBLE) { - result->u.r = a.d - b.d; - result->type = MEM_TYPE_DOUBLE; - assert(result->flags == 0); + } + if (!mem_is_num(left)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), + "number"); + return -1; + } + if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) { + double a; + double b; + mem_get_double(left, &a); + mem_get_double(right, &b); + mem_set_double(result, a - b); return 0; } - int64_t res; bool is_neg; - if (sql_sub_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_sub_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } int mem_mul(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_NUMBER; return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) + } + if (!mem_is_num(right)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), + "number"); return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - if (a.type == MEM_TYPE_DOUBLE) { - result->u.r = a.d * b.d; - result->type = MEM_TYPE_DOUBLE; - assert(result->flags == 0); + } + if (!mem_is_num(left)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), + "number"); + return -1; + } + if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) { + double a; + double b; + mem_get_double(left, &a); + mem_get_double(right, &b); + mem_set_double(result, a * b); return 0; } - int64_t res; bool is_neg; - if (sql_mul_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_mul_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } int mem_div(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_NUMBER; return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) + } + if (!mem_is_num(right)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), + "number"); return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - if (a.type == MEM_TYPE_DOUBLE) { - if (b.d == 0.) { + } + if (!mem_is_num(left)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), + "number"); + return -1; + } + if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) { + double a; + double b; + mem_get_double(left, &a); + mem_get_double(right, &b); + if (b == 0.0) { diag_set(ClientError, ER_SQL_EXECUTE, "division by zero"); return -1; } - result->u.r = a.d / b.d; - result->type = MEM_TYPE_DOUBLE; - assert(result->flags == 0); + mem_set_double(result, a / b); return 0; } - - if (b.i == 0) { + if (right->u.u == 0) { diag_set(ClientError, ER_SQL_EXECUTE, "division by zero"); return -1; } int64_t res; bool is_neg; - if (sql_div_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_div_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } int mem_rem(const struct Mem *left, const struct Mem *right, struct Mem *result) { - if (try_return_null(left, right, result, FIELD_TYPE_NUMBER)) + if (mem_is_any_null(left, right)) { + mem_set_null(result); + result->field_type = FIELD_TYPE_INTEGER; return 0; - - struct sql_num a, b; - if (arithmetic_prepare(left, right, &a, &b) != 0) + } + if (!mem_is_int(right)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right), + "integer"); return -1; - - assert(a.type != MEM_TYPE_DOUBLE || a.type == b.type); - /* - * TODO: This operation works wrong when double d > INT64_MAX and - * d < UINT64_MAX. Also, there may be precision losses due to - * conversion integer to double and back. - */ - a.i = a.type == MEM_TYPE_DOUBLE ? (int64_t)a.d : a.i; - b.i = b.type == MEM_TYPE_DOUBLE ? (int64_t)b.d : b.i; - if (b.i == 0) { + } + if (!mem_is_int(left)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left), + "integer"); + return -1; + } + if (right->u.u == 0) { diag_set(ClientError, ER_SQL_EXECUTE, "division by zero"); return -1; } int64_t res; bool is_neg; - if (sql_rem_int(a.i, a.is_neg, b.i, b.is_neg, &res, &is_neg) != 0) { + if (sql_rem_int(left->u.i, left->type == MEM_TYPE_INT, right->u.i, + right->type == MEM_TYPE_INT, &res, &is_neg) != 0) { diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); return -1; } - result->u.i = res; - result->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT; - assert(result->flags == 0); + mem_set_int(result, res, is_neg); return 0; } diff --git a/test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua b/test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua new file mode 100755 index 000000000..9ce595fe0 --- /dev/null +++ b/test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua @@ -0,0 +1,294 @@ +#!/usr/bin/env tarantool +local test = require("sqltester") +test:plan(35) + +-- +-- Make sure that arithmetic operations can only accept numeric values. Also, +-- operation '%' can accept only INTEGER and UNSIGNED values. +-- +test:do_execsql_test( + "gh-5756-1.1", + [[ + SELECT 9 + 2; + ]], { + 11 + }) + +test:do_execsql_test( + "gh-5756-1.2", + [[ + SELECT 9 + -2; + ]], { + 7 + }) + +test:do_execsql_test( + "gh-5756-1.3", + [[ + SELECT 9 + 2.0; + ]], { + 11 + }) + +test:do_catchsql_test( + "gh-5756-1.4", + [[ + SELECT 9 + '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to number" + }) + +test:do_catchsql_test( + "gh-5756-1.5", + [[ + SELECT 9 + x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to number" + }) + +test:do_catchsql_test( + "gh-5756-1.6", + [[ + SELECT 9 + true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to number" + }) + +test:do_catchsql_test( + "gh-5756-1.7", + [[ + SELECT 9 + CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to number" + }) + +test:do_execsql_test( + "gh-5756-2.1", + [[ + SELECT 9 - 2; + ]], { + 7 + }) + +test:do_execsql_test( + "gh-5756-2.2", + [[ + SELECT 9 - -2; + ]], { + 11 + }) + +test:do_execsql_test( + "gh-5756-2.3", + [[ + SELECT 9 - 2.0; + ]], { + 7 + }) + +test:do_catchsql_test( + "gh-5756-2.4", + [[ + SELECT 9 - '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to number" + }) + +test:do_catchsql_test( + "gh-5756-2.5", + [[ + SELECT 9 - x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to number" + }) + +test:do_catchsql_test( + "gh-5756-2.6", + [[ + SELECT 9 - true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to number" + }) + +test:do_catchsql_test( + "gh-5756-2.7", + [[ + SELECT 9 - CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to number" + }) + +test:do_execsql_test( + "gh-5756-3.1", + [[ + SELECT 9 * 2; + ]], { + 18 + }) + +test:do_execsql_test( + "gh-5756-3.2", + [[ + SELECT 9 * -2; + ]], { + -18 + }) + +test:do_execsql_test( + "gh-5756-3.3", + [[ + SELECT 9 * 2.0; + ]], { + 18 + }) + +test:do_catchsql_test( + "gh-5756-3.4", + [[ + SELECT 9 * '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to number" + }) + +test:do_catchsql_test( + "gh-5756-3.5", + [[ + SELECT 9 * x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to number" + }) + +test:do_catchsql_test( + "gh-5756-3.6", + [[ + SELECT 9 * true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to number" + }) + +test:do_catchsql_test( + "gh-5756-3.7", + [[ + SELECT 9 * CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to number" + }) + +test:do_execsql_test( + "gh-5756-4.1", + [[ + SELECT 9 / 2; + ]], { + 4 + }) + +test:do_execsql_test( + "gh-5756-4.2", + [[ + SELECT 9 / -2; + ]], { + -4 + }) + +test:do_execsql_test( + "gh-5756-4.3", + [[ + SELECT 9 / 2.0; + ]], { + 4.5 + }) + +test:do_catchsql_test( + "gh-5756-4.4", + [[ + SELECT 9 / '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to number" + }) + +test:do_catchsql_test( + "gh-5756-4.5", + [[ + SELECT 9 / x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to number" + }) + +test:do_catchsql_test( + "gh-5756-4.6", + [[ + SELECT 9 / true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to number" + }) + +test:do_catchsql_test( + "gh-5756-4.7", + [[ + SELECT 9 / CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to number" + }) + +test:do_execsql_test( + "gh-5756-5.1", + [[ + SELECT 9 % 2; + ]], { + 1 + }) + +test:do_execsql_test( + "gh-5756-5.2", + [[ + SELECT 9 % -2; + ]], { + 1 + }) + +test:do_catchsql_test( + "gh-5756-5.3", + [[ + SELECT 9 % 2.0; + ]], { + 1, "Type mismatch: can not convert double(2.0) to integer" + }) + +test:do_catchsql_test( + "gh-5756-5.4", + [[ + SELECT 9 % '2'; + ]], { + 1, "Type mismatch: can not convert string('2') to integer" + }) + +test:do_catchsql_test( + "gh-5756-5.5", + [[ + SELECT 9 % x'32'; + ]], { + 1, "Type mismatch: can not convert varbinary(x'32') to integer" + }) + +test:do_catchsql_test( + "gh-5756-5.6", + [[ + SELECT 9 % true; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to integer" + }) + +test:do_catchsql_test( + "gh-5756-5.7", + [[ + SELECT 9 % CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to integer" + }) + +test:finish_test() diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua index bcd8e665a..52c7945fe 100755 --- a/test/sql-tap/misc1.test.lua +++ b/test/sql-tap/misc1.test.lua @@ -68,7 +68,9 @@ test:do_test( cmd = cmd .. ")" test:execsql(cmd) end - return test:execsql("SELECT x50 FROM manycol ORDER BY x80+0") + return test:execsql([[ + SELECT x50 FROM manycol ORDER BY CAST(x80 AS INTEGER); + ]]) end, { -- <misc1-1.3.1> "50", "150", "250", "350", "450", "550", "650", "750", "850", "950", "1050" @@ -527,11 +529,12 @@ test:do_execsql_test( -- </misc1-10.6> }) +local cast = "CAST(CAST(x1 AS INTEGER) + 1 AS STRING)" test:do_test( "misc1-10.7", function() where = string.gsub(where, "x0=0", "x0=100") - return test:catchsql("UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where.."") + return test:catchsql("UPDATE manycol SET x1 = "..cast.." "..where..";") end, { -- <misc1-10.7> 0 @@ -553,7 +556,7 @@ test:do_execsql_test( -- } {0 {}} test:do_execsql_test( "misc1-10.9", - "UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where + "UPDATE manycol SET x1 = "..cast.." "..where --"UPDATE manycol SET x1=x1+1 $::where AND rowid>0" , {}) diff --git a/test/sql-tap/misc3.test.lua b/test/sql-tap/misc3.test.lua index 313484b5d..6e063a059 100755 --- a/test/sql-tap/misc3.test.lua +++ b/test/sql-tap/misc3.test.lua @@ -143,7 +143,7 @@ test:do_execsql_test( test:do_execsql_test( "misc3-2.6", [[ - SELECT '-2.0e-127' * '-0.5e27' + SELECT CAST('-2.0e-127' AS DOUBLE) * CAST('-0.5e27' AS DOUBLE); ]], { -- <misc3-2.6> 1e-100 @@ -153,7 +153,7 @@ test:do_execsql_test( test:do_execsql_test( "misc3-2.7", [[ - SELECT '+2.0e-127' * '-0.5e27' + SELECT CAST('+2.0e-127' AS DOUBLE) * CAST('-0.5e27' AS DOUBLE); ]], { -- <misc3-2.7> -1e-100 @@ -163,7 +163,7 @@ test:do_execsql_test( test:do_execsql_test( "misc3-2.8", [[ - SELECT 2.0e-27 * '+0.5e+127' + SELECT 2.0e-27 * CAST('+0.5e+127' AS DOUBLE); ]], { -- <misc3-2.8> 1e+100 @@ -173,7 +173,7 @@ test:do_execsql_test( test:do_execsql_test( "misc3-2.9", [[ - SELECT 2.0e-27 * '+0.000005e+132' + SELECT 2.0e-27 * CAST('+0.000005e+132' AS DOUBLE); ]], { -- <misc3-2.9> 1e+100 diff --git a/test/sql-tap/tkt-a8a0d2996a.test.lua b/test/sql-tap/tkt-a8a0d2996a.test.lua index 0dfa4653d..e97ff8c60 100755 --- a/test/sql-tap/tkt-a8a0d2996a.test.lua +++ b/test/sql-tap/tkt-a8a0d2996a.test.lua @@ -19,7 +19,7 @@ test:plan(12) -- -- ["set","testdir",[["file","dirname",["argv0"]]]] -- ["source",[["testdir"],"\/tester.tcl"]] -test:do_execsql_test( +test:do_catchsql_test( 1.0, [[ CREATE TABLE t(id INT PRIMARY KEY, x TEXT UNIQUE, y TEXT); @@ -27,98 +27,98 @@ test:do_execsql_test( SELECT typeof(x), typeof(y) FROM t WHERE 1=x+0 AND y=='1'; ]], { -- <1.0> - "string", "string" + 1, "Type mismatch: can not convert string('1') to number" -- </1.0> }) -test:do_execsql_test( +test:do_catchsql_test( 1.1, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x-0 AND y=='1'; ]], { -- <1.1> - "string", "string" + 1, "Type mismatch: can not convert string('1') to number" -- </1.1> }) -test:do_execsql_test( +test:do_catchsql_test( 1.2, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x*1 AND y=='1'; ]], { -- <1.2> - "string", "string" + 1, "Type mismatch: can not convert string('1') to number" -- </1.2> }) -test:do_execsql_test( +test:do_catchsql_test( 1.3, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x/1 AND y=='1'; ]], { -- <1.3> - "string", "string" + 1, "Type mismatch: can not convert string('1') to number" -- </1.3> }) -test:do_execsql_test( +test:do_catchsql_test( 1.4, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x%4 AND y=='1'; ]], { -- <1.4> - "string", "string" + 1, "Type mismatch: can not convert string('1') to integer" -- </1.4> }) -test:do_execsql_test( +test:do_catchsql_test( 3.0, [[ UPDATE t SET x='1.0'; SELECT typeof(x), typeof(y) FROM t WHERE 1=x+0 AND y=='1'; ]], { -- <3.0> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to number" -- </3.0> }) -test:do_execsql_test( +test:do_catchsql_test( 3.1, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x-0 AND y=='1'; ]], { -- <3.1> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to number" -- </3.1> }) -test:do_execsql_test( +test:do_catchsql_test( 3.2, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x*1 AND y=='1'; ]], { -- <3.2> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to number" -- </3.2> }) -test:do_execsql_test( +test:do_catchsql_test( 3.3, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x/1 AND y=='1'; ]], { -- <3.3> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to number" -- </3.3> }) -test:do_execsql_test( +test:do_catchsql_test( 3.4, [[ SELECT typeof(x), typeof(y) FROM t WHERE 1=x%4 AND y=='1'; ]], { -- <3.4> - "string", "string" + 1, "Type mismatch: can not convert string('1.0') to integer" -- </3.4> }) @@ -132,13 +132,13 @@ test:do_execsql_test( -- </4.0> }) -test:do_execsql_test( +test:do_catchsql_test( 4.1, [[ SELECT '1.23e64'/'1.0000e+62'; ]], { -- <4.1> - 123.0 + 1, "Type mismatch: can not convert string('1.0000e+62') to number" -- </4.1> }) diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua index 70683a4fd..643c8f67d 100755 --- a/test/sql-tap/uuid.test.lua +++ b/test/sql-tap/uuid.test.lua @@ -1011,7 +1011,7 @@ test:do_catchsql_test( [[ SELECT u % 1 FROM t2; ]], { - 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number" + 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to integer" }) -- Check that bitwise operations work with UUIDs as intended. diff --git a/test/sql/boolean.result b/test/sql/boolean.result index 5594d92cb..4e69b8ef4 100644 --- a/test/sql/boolean.result +++ b/test/sql/boolean.result @@ -1218,22 +1218,22 @@ SELECT false / false; SELECT true % true; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT true % false; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT false % true; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT false % false; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a, true + a FROM t; @@ -1279,12 +1279,12 @@ SELECT a, false / a FROM t; SELECT a, true % a FROM t; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a, false % a FROM t; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a, a + true FROM t; | --- @@ -1329,12 +1329,12 @@ SELECT a, a / false FROM t; SELECT a, a % true FROM t; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT a, a % false FROM t; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a, a1, a + a1 FROM t, t6; @@ -1360,7 +1360,7 @@ SELECT a, a1, a / a1 FROM t, t6; SELECT a, a1, a % a1 FROM t, t6; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT ~true; @@ -2683,12 +2683,12 @@ SELECT false / 2; SELECT true % 2; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT false % 2; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT 2 + true; | --- @@ -2733,12 +2733,12 @@ SELECT 2 / false; SELECT 2 % true; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT 2 % false; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, a1 + 2 FROM t6 @@ -2764,7 +2764,7 @@ SELECT a1, a1 / 2 FROM t6 SELECT a1, a1 % 2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, 2 + a1 FROM t6 | --- @@ -2789,7 +2789,7 @@ SELECT a1, 2 / a1 FROM t6 SELECT a1, 2 % a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a2, a2 + 2 FROM t6 | --- @@ -2814,7 +2814,7 @@ SELECT a2, a2 / 2 FROM t6 SELECT a2, a2 % 2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT a2, 2 + a2 FROM t6 | --- @@ -2839,7 +2839,7 @@ SELECT a2, 2 / a2 FROM t6 SELECT a2, 2 % a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT b, true + b FROM t7; @@ -2885,12 +2885,12 @@ SELECT b, false / b FROM t7; SELECT b, true % b FROM t7; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT b, false % b FROM t7; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT b, b + true FROM t7; | --- @@ -2935,12 +2935,12 @@ SELECT b, b / false FROM t7; SELECT b, b % true FROM t7; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT b, b % false FROM t7; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, b, a1 + b FROM t6, t7; @@ -2966,7 +2966,7 @@ SELECT a1, b, a1 / b FROM t6, t7; SELECT a1, b, a1 % b FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, b, b + a1 FROM t6, t7; | --- @@ -2991,7 +2991,7 @@ SELECT a1, b, b / a1 FROM t6, t7; SELECT a1, b, b % a1 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a2, b, a2 + b FROM t6, t7; | --- @@ -3016,7 +3016,7 @@ SELECT a2, b, a2 / b FROM t6, t7; SELECT a2, b, a2 % b FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT a2, b, b + a2 FROM t6, t7; | --- @@ -3041,7 +3041,7 @@ SELECT a2, b, b / a2 FROM t6, t7; SELECT a2, b, b % a2 FROM t6, t7; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT true & 2; @@ -4152,12 +4152,12 @@ SELECT false / 2.3; SELECT true % 2.3; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert double(2.3) to integer' | ... SELECT false % 2.3; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert double(2.3) to integer' | ... SELECT 2.3 + true; | --- @@ -4202,12 +4202,12 @@ SELECT 2.3 / false; SELECT 2.3 % true; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT 2.3 % false; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, a1 + 2.3 FROM t6 @@ -4233,7 +4233,7 @@ SELECT a1, a1 / 2.3 FROM t6 SELECT a1, a1 % 2.3 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert double(2.3) to integer' | ... SELECT a1, 2.3 + a1 FROM t6 | --- @@ -4258,7 +4258,7 @@ SELECT a1, 2.3 / a1 FROM t6 SELECT a1, 2.3 % a1 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a2, a2 + 2.3 FROM t6 | --- @@ -4283,7 +4283,7 @@ SELECT a2, a2 / 2.3 FROM t6 SELECT a2, a2 % 2.3 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert double(2.3) to integer' | ... SELECT a2, 2.3 + a2 FROM t6 | --- @@ -4308,7 +4308,7 @@ SELECT a2, 2.3 / a2 FROM t6 SELECT a2, 2.3 % a2 FROM t6 | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT c, true + c FROM t8; @@ -4354,12 +4354,12 @@ SELECT c, false / c FROM t8; SELECT c, true % c FROM t8; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert double(4.56) to integer' | ... SELECT c, false % c FROM t8; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert double(4.56) to integer' | ... SELECT c, c + true FROM t8; | --- @@ -4404,12 +4404,12 @@ SELECT c, c / false FROM t8; SELECT c, c % true FROM t8; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT c, c % false FROM t8; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a1, c, a1 + c FROM t6, t8; @@ -4435,7 +4435,7 @@ SELECT a1, c, a1 / c FROM t6, t8; SELECT a1, c, a1 % c FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert double(4.56) to integer' | ... SELECT a1, c, c + a1 FROM t6, t8; | --- @@ -4460,7 +4460,7 @@ SELECT a1, c, c / a1 FROM t6, t8; SELECT a1, c, c % a1 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Type mismatch: can not convert boolean(FALSE) to integer' | ... SELECT a2, c, a2 + c FROM t6, t8; | --- @@ -4485,7 +4485,7 @@ SELECT a2, c, a2 / c FROM t6, t8; SELECT a2, c, a2 % c FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert double(4.56) to integer' | ... SELECT a2, c, c + a2 FROM t6, t8; | --- @@ -4510,7 +4510,7 @@ SELECT a2, c, c / a2 FROM t6, t8; SELECT a2, c, c % a2 FROM t6, t8; | --- | - null - | - 'Type mismatch: can not convert boolean(TRUE) to number' + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... SELECT true > 2.3; diff --git a/test/sql/types.result b/test/sql/types.result index c1e1a8ef1..ceafaadc9 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -310,11 +310,8 @@ box.execute('SELECT 1 + 1.1;') ... box.execute('SELECT \'9223372036854\' + 1;') --- -- metadata: - - name: COLUMN_1 - type: integer - rows: - - [9223372036855] +- null +- 'Type mismatch: can not convert string(''9223372036854'') to number' ... -- Fix BOOLEAN bindings. box.execute('SELECT ?', {true}) -- 2.25.1
Hello,
On 04 авг 11:36, imeevma@tarantool.org wrote:
> After this patch, arithmetic operations will only accept numeric values.
> For the "%" operation, the rules have become even stricter, now it
> accepts only INTEGER and UNSIGNED values.
>
> Part of #4470
> Closes #5756
> ---
> https://github.com/tarantool/tarantool/issues/5756
> https://github.com/tarantool/tarantool/tree/imeevma/gh-5756-implicit-cast-in-arithmetic
The patch LGTM.
--
Regards, Kirill Yukhin
[-- Attachment #1: Type: text/plain, Size: 54 bytes --] Hi all, QA LGTM -- Vitaliia Ioffe [-- Attachment #2: Type: text/html, Size: 291 bytes --]
Hello,
On 04 авг 11:36, imeevma@tarantool.org wrote:
> After this patch, arithmetic operations will only accept numeric values.
> For the "%" operation, the rules have become even stricter, now it
> accepts only INTEGER and UNSIGNED values.
>
> Part of #4470
> Closes #5756
> ---
> https://github.com/tarantool/tarantool/issues/5756
> https://github.com/tarantool/tarantool/tree/imeevma/gh-5756-implicit-cast-in-arithmetic
I've checked your patch into 2.7, 2.8 and master.
--
Regards, Kirill Yukhin