From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 725FE401A42 for ; Sat, 2 Nov 2019 14:30:13 +0300 (MSK) From: imeevma@tarantool.org Date: Sat, 2 Nov 2019 14:30:12 +0300 Message-Id: <7361e30e0857bf3c4d05b72713bbdf2596a1452f.1572693924.git.imeevma@gmail.com> Subject: [Tarantool-patches] [PATCH v1 1/1] sql: make NUMBER to be union of SQL numeric types List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: v.shpilevoy@tarantool.org Cc: tarantool-patches@freelists.org, tarantool-patches@dev.tarantool.org This patch makes number to be union of UNSIGNED, INTEGER and DOUBLE numeric types. The first two types represented by Tarantool UNSIGNED and INTEGER types. Currently there is not DOUBLE type in Tarantool. Closes #4233 Closes #4463 --- https://github.com/tarantool/tarantool/branches https://github.com/tarantool/tarantool/issues/4233 https://github.com/tarantool/tarantool/issues/4463 src/box/sql/vdbe.c | 15 +---- src/box/sql/vdbemem.c | 63 ++++++++++----------- test/sql-tap/cast.test.lua | 32 +++++------ test/sql-tap/e_select1.test.lua | 2 +- test/sql-tap/numcast.test.lua | 104 ++++++++++++++++++++++++++++++++++- test/sql-tap/select3.test.lua | 2 +- test/sql-tap/sort.test.lua | 12 ++-- test/sql-tap/tkt-91e2e8ba6f.test.lua | 12 ++-- test/sql/integer-overflow.result | 2 +- test/sql/types.result | 2 +- 10 files changed, 168 insertions(+), 78 deletions(-) diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index 03c63d8..61e6487 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -1586,7 +1586,6 @@ case OP_Subtract: /* same as TK_MINUS, in1, in2, out3 */ case OP_Multiply: /* same as TK_STAR, in1, in2, out3 */ case OP_Divide: /* same as TK_SLASH, in1, in2, out3 */ case OP_Remainder: { /* same as TK_REM, in1, in2, out3 */ - char bIntint; /* Started out as two integer operands */ u32 flags; /* Combined MEM_* flags from both inputs */ u16 type1; /* Numeric type of left operand */ u16 type2; /* Numeric type of right operand */ @@ -1609,7 +1608,6 @@ case OP_Remainder: { /* same as TK_REM, in1, in2, out3 */ bool is_lhs_neg = pIn1->flags & MEM_Int; bool is_rhs_neg = pIn2->flags & MEM_Int; bool is_res_neg; - bIntint = 1; switch( pOp->opcode) { case OP_Add: { if (sql_add_int(iA, is_lhs_neg, iB, is_rhs_neg, @@ -1649,7 +1647,6 @@ case OP_Remainder: { /* same as TK_REM, in1, in2, out3 */ } mem_set_int(pOut, iB, is_res_neg); } else { - bIntint = 0; if (sqlVdbeRealValue(pIn1, &rA) != 0) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, sql_value_to_diag_str(pIn1), "numeric"); @@ -1660,6 +1657,7 @@ case OP_Remainder: { /* same as TK_REM, in1, in2, out3 */ sql_value_to_diag_str(pIn2), "numeric"); goto abort_due_to_error; } + assert(((type1 | type2) & MEM_Real) !=0); switch( pOp->opcode) { case OP_Add: rB += rA; break; case OP_Subtract: rB -= rA; break; @@ -1685,9 +1683,6 @@ case OP_Remainder: { /* same as TK_REM, in1, in2, out3 */ } pOut->u.r = rB; MemSetTypeFlag(pOut, MEM_Real); - if (((type1|type2)&MEM_Real)==0 && !bIntint) { - mem_apply_integer_type(pOut); - } } break; @@ -2746,14 +2741,6 @@ case OP_Column: { default_val_mem != NULL) { sqlVdbeMemShallowCopy(pDest, default_val_mem, MEM_Static); } - if ((pDest->flags & (MEM_Int | MEM_UInt)) != 0) { - if (field_type == FIELD_TYPE_NUMBER) { - if ((pDest->flags & MEM_Int) != 0) - sqlVdbeMemSetDouble(pDest, pDest->u.i); - else - sqlVdbeMemSetDouble(pDest, pDest->u.u); - } - } pDest->field_type = field_type; op_column_out: REGISTER_TRACE(p, pOp->p3, pDest); diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c index 2d37b62..6bfd4c0 100644 --- a/src/box/sql/vdbemem.c +++ b/src/box/sql/vdbemem.c @@ -596,6 +596,34 @@ sqlVdbeMemRealify(Mem * pMem) return 0; } +static int +sql_mem_to_number(struct Mem *mem) +{ + assert((mem->flags & (MEM_Int | MEM_UInt | MEM_Real | MEM_Null)) == 0); + if ((mem->flags & (MEM_Blob | MEM_Str)) == 0) + return -1; + assert(mem->z[mem->n] == '\0'); + char *tail = NULL; + long long unsigned int value_u = strtoull(mem->z, &tail, 10); + if (tail[0] == '\0' && value_u <= UINT64_MAX) { + mem->u.u = value_u; + MemSetTypeFlag(mem, MEM_UInt); + return 0; + } + long long int value_i = strtoll(mem->z, &tail, 10); + if (tail[0] == '\0' && value_i <= INT64_MAX && value_i >= INT64_MIN) { + mem->u.i = value_i; + MemSetTypeFlag(mem, MEM_Int); + return 0; + } + double value_d; + if (sqlAtoF(mem->z, &value_d, mem->n) == 0) + return -1; + mem->u.r = value_d; + MemSetTypeFlag(mem, MEM_Real); + return 0; +} + /* * Convert pMem so that it has types MEM_Real or MEM_Int or both. * Invalidate any prior representations. @@ -676,22 +704,6 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type) assert(type < field_type_MAX); if (pMem->flags & MEM_Null) return 0; - if ((pMem->flags & MEM_Blob) != 0 && type == FIELD_TYPE_NUMBER) { - bool is_neg; - if (sql_atoi64(pMem->z, (int64_t *) &pMem->u.i, &is_neg, - pMem->n) == 0) { - MemSetTypeFlag(pMem, MEM_Real); - if (is_neg) - pMem->u.r = pMem->u.i; - else - pMem->u.r = pMem->u.u; - return 0; - } - if (sqlAtoF(pMem->z, &pMem->u.r, pMem->n) == 0) - return -1; - MemSetTypeFlag(pMem, MEM_Real); - return 0; - } switch (type) { case FIELD_TYPE_SCALAR: return 0; @@ -742,7 +754,10 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type) return -1; return 0; case FIELD_TYPE_NUMBER: - return sqlVdbeMemRealify(pMem); + if ((pMem->flags & + (MEM_Real | MEM_Int | MEM_UInt | MEM_Null)) != 0) + return 0; + return sql_mem_to_number(pMem); case FIELD_TYPE_VARBINARY: if ((pMem->flags & MEM_Blob) != 0) return 0; @@ -1820,26 +1835,12 @@ mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var) if (var->flags & MEM_Null) { mpstream_encode_nil(stream); } else if (var->flags & MEM_Real) { - /* - * We can't pass to INT iterator float - * value. Hence, if floating point value - * lacks fractional component, we can - * encode it as INT and successfully - * pass to INT iterator. - */ - i = var->u.r; - if (i == var->u.r && i < 0) - goto encode_int; - if (i == var->u.r && i >= 0) - goto encode_uint; mpstream_encode_double(stream, var->u.r); } else if (var->flags & MEM_Int) { i = var->u.i; -encode_int: mpstream_encode_int(stream, i); } else if (var->flags & MEM_UInt) { i = var->u.u; -encode_uint: mpstream_encode_uint(stream, i); } else if (var->flags & MEM_Str) { mpstream_encode_strn(stream, var->z, var->n); diff --git a/test/sql-tap/cast.test.lua b/test/sql-tap/cast.test.lua index 23229db..43c155a 100755 --- a/test/sql-tap/cast.test.lua +++ b/test/sql-tap/cast.test.lua @@ -628,14 +628,14 @@ test:do_execsql_test( SELECT CAST(9223372036854774800 AS NUMBER) ]], { -- - 9223372036854774784 + 9223372036854774800LL -- }) test:do_execsql_test( "cast-3.3", "SELECT CAST(9223372036854774800 AS NUMBER)", - {9.22337203685477e+18}) + {9223372036854774800LL}) test:do_execsql_test( "cast-3.4", @@ -643,7 +643,7 @@ test:do_execsql_test( SELECT CAST(CAST(9223372036854774800 AS NUMBER) AS integer) ]], { -- - 9223372036854774784LL + 9223372036854774800LL -- }) @@ -663,14 +663,14 @@ test:do_execsql_test( SELECT CAST(-9223372036854774800 AS NUMBER) ]], { -- - -9223372036854774784 + -9223372036854774800LL -- }) test:do_execsql_test( "cast-3.7", "SELECT CAST(-9223372036854774800 AS NUMBER)", - {-9.22337203685477e+18}) + {-9223372036854774800LL}) test:do_execsql_test( "cast-3.8", @@ -678,7 +678,7 @@ test:do_execsql_test( SELECT CAST(CAST(-9223372036854774800 AS NUMBER) AS integer) ]], { -- - -9223372036854774784LL + -9223372036854774800LL -- }) @@ -695,7 +695,7 @@ test:do_execsql_test( test:do_execsql_test( "cast-3.12", [[ - SELECT CAST('9223372036854774800' AS NUMBER) + SELECT CAST('9223372036854774800.' AS NUMBER) ]], { -- 9223372036854774784 @@ -705,12 +705,12 @@ test:do_execsql_test( test:do_execsql_test( "cast-3.13", "SELECT CAST(9223372036854774800 AS NUMBER)", - {9.22337203685477e+18}) + {9223372036854774800LL}) test:do_execsql_test( "cast-3.14", [[ - SELECT CAST(CAST('9223372036854774800' AS NUMBER) AS integer) + SELECT CAST(CAST('9223372036854774800.' AS NUMBER) AS integer) ]], { -- 9223372036854774784LL @@ -732,7 +732,7 @@ test:do_execsql_test( test:do_execsql_test( "cast-3.16", [[ - SELECT CAST('-9223372036854774800' AS NUMBER) + SELECT CAST('-9223372036854774800.' AS NUMBER) ]], { -- -9223372036854774784 @@ -741,13 +741,13 @@ test:do_execsql_test( test:do_execsql_test( "cast-3.17", - "SELECT CAST('-9223372036854774800' AS NUMBER)", + "SELECT CAST('-9223372036854774800.' AS NUMBER)", {-9.22337203685477e+18}) test:do_execsql_test( "cast-3.18", [[ - SELECT CAST(CAST('-9223372036854774800' AS NUMBER) AS integer) + SELECT CAST(CAST('-9223372036854774800.' AS NUMBER) AS integer) ]], { -- -9223372036854774784LL @@ -770,7 +770,7 @@ if true then --test:execsql("PRAGMA encoding")[1][1]=="UTF-8" then test:do_execsql_test( "cast-3.22", [[ - SELECT CAST(x'39323233333732303336383534373734383030' AS NUMBER) + SELECT CAST(x'393232333337323033363835343737343830302E' AS NUMBER) ]], { -- 9223372036854774784 @@ -778,7 +778,7 @@ if true then --test:execsql("PRAGMA encoding")[1][1]=="UTF-8" then }) test:do_execsql_test( "cast-3.23", - "SELECT CAST(x'39323233333732303336383534373734383030' AS NUMBER)", + "SELECT CAST(x'393232333337323033363835343737343830302E' AS NUMBER)", {9.22337203685477e+18}) test:do_execsql_test( @@ -788,7 +788,7 @@ if true then --test:execsql("PRAGMA encoding")[1][1]=="UTF-8" then AS integer) ]], { -- - 9223372036854774784LL + 9223372036854774800LL -- }) end @@ -796,7 +796,7 @@ end test:do_execsql_test( "case-3.25", [[ - SELECT CAST(x'3138343436373434303733373039353531363135' AS NUMBER); + SELECT CAST(x'31383434363734343037333730393535313631352E' AS NUMBER); ]], { 1.844674407371e+19 } ) test:do_execsql_test( diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua index a0a6716..d9fec77 100755 --- a/test/sql-tap/e_select1.test.lua +++ b/test/sql-tap/e_select1.test.lua @@ -760,7 +760,7 @@ test:do_execsql_test( INSERT INTO z1 VALUES(6, 63, '0', -26); INSERT INTO z2 VALUES(1, NULL, 21); - INSERT INTO z2 VALUES(2, 36, 6); + INSERT INTO z2 VALUES(2, 36.0, 6.0); INSERT INTO z3 VALUES(1, 123.21, 123.12); INSERT INTO z3 VALUES(2, 49.17, -67); diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua index 9f72485..1355bad 100755 --- a/test/sql-tap/numcast.test.lua +++ b/test/sql-tap/numcast.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(11) +test:plan(22) --!./tcltestrunner.lua -- 2013 March 20 @@ -65,5 +65,107 @@ for _, enc in ipairs({"utf8"}) do end end +-- +-- gh-4233: Make sure that NUMBER can contain UNSIGNED, INTEGER +-- and DOUBLE and is not automatically converted to DOUBLE. +-- +test:do_execsql_test( + "numcast-2.1", + [[ + CREATE TABLE t1 (id INT PRIMARY KEY, n NUMBER); + INSERT INTO t1 VALUES (1, 9223372036854775807); + INSERT INTO t1 VALUES (2, -9223372036854775807); + INSERT INTO t1 VALUES (3, 9223372036854775807.1); + SELECT n, n/100 FROM t1; + ]], { + 9223372036854775807ULL, 92233720368547758ULL, + -9223372036854775807LL, -92233720368547758LL, + 9223372036854775808, 92233720368547758.08 + }) + +test:do_execsql_test( + "numcast-2.2", + [[ + CREATE TABLE t2(a NUMBER primary key); + INSERT INTO t2 VALUES(-56); + INSERT INTO t2 VALUES(44.0); + INSERT INTO t2 VALUES(46); + INSERT INTO t2 VALUES(56.0); + SELECT (a + 25) / 50 FROM t2; + ]], { + 0,1.38,1,1.62 + }) + +test:do_execsql_test( + "numcast-2.3", + [[ + SELECT CAST(x'3131313131313131313131313131313131313131' AS NUMBER); + ]], { + 11111111111111111111ULL + }) + +test:do_execsql_test( + "numcast-2.4", + [[ + SELECT CAST(x'31313131313131313131313131313131313131312E' AS NUMBER); + ]], { + 11111111111111110656 + }) + +test:do_execsql_test( + "numcast-2.5", + [[ + SELECT CAST('11111111111111111111' AS NUMBER); + ]], { + 11111111111111111111ULL + }) + +test:do_execsql_test( + "numcast-2.6", + [[ + SELECT CAST('101' AS NUMBER) / 10, CAST('101.' AS NUMBER) / 10; + ]], { + 10, 10.1 + }) + +test:do_execsql_test( + "numcast-2.7", + [[ + SELECT CAST('101 ' AS NUMBER) / 10, CAST(' 101' AS NUMBER) / 10; + ]], { + 10.1, 10 + }) + +test:do_execsql_test( + "numcast-2.8", + [[ + SELECT (1 + 0) / 3, (1 + 0.) / 3, (1 + 0) / 3.; + ]], { + 0, 0.33333333333333, 0.33333333333333 + }) + +test:do_execsql_test( + "numcast-2.9", + [[ + SELECT (1 - 0) / 3, (1 - 0.) / 3, (1 - 0) / 3.; + ]], { + 0, 0.33333333333333, 0.33333333333333 + }) + +test:do_execsql_test( + "numcast-2.10", + [[ + SELECT (1 * 1) / 3, (1 * 1.) / 3, (1 * 1) / 3.; + ]], { + 0, 0.33333333333333, 0.33333333333333 + }) + +test:do_execsql_test( + "numcast-2.11", + [[ + SELECT (1 / 1) / 3, (1 / 1.) / 3, (1 / 1) / 3.; + ]], { + 0, 0.33333333333333, 0.33333333333333 + }) test:finish_test() diff --git a/test/sql-tap/select3.test.lua b/test/sql-tap/select3.test.lua index 19f853d..f2637db 100755 --- a/test/sql-tap/select3.test.lua +++ b/test/sql-tap/select3.test.lua @@ -386,7 +386,7 @@ test:do_execsql_test("select3-8.1", [[ SELECT typeof(sum(a3)) FROM a; ]], { -- - "number" + "integer" -- }) diff --git a/test/sql-tap/sort.test.lua b/test/sql-tap/sort.test.lua index e156414..36074d6 100755 --- a/test/sql-tap/sort.test.lua +++ b/test/sql-tap/sort.test.lua @@ -243,7 +243,7 @@ test:do_execsql_test( SELECT v FROM t1 ORDER BY v; ]], { -- - "x-123.0", "x-2.15", "x-2b", "x-3.141592653", "x-4221.0", "x0.0013442", "x1.6", "x11.0" + "x-123.0", "x-2.15", "x-2b", "x-3.141592653", "x-4221.0", "x0.0013442", "x1.6", "x11" -- }) @@ -253,7 +253,7 @@ test:do_execsql_test( SELECT v FROM t1 ORDER BY substr(v,2,999); ]], { -- - "x-123.0", "x-2.15", "x-2b", "x-3.141592653", "x-4221.0", "x0.0013442", "x1.6", "x11.0" + "x-123.0", "x-2.15", "x-2b", "x-3.141592653", "x-4221.0", "x0.0013442", "x1.6", "x11" -- }) @@ -263,7 +263,7 @@ test:do_execsql_test( SELECT v FROM t1 ORDER BY substr(v,2,999) DESC; ]], { -- - "x11.0", "x1.6", "x0.0013442", "x-4221.0", "x-3.141592653", "x-2b", "x-2.15", "x-123.0" + "x11", "x1.6", "x0.0013442", "x-4221.0", "x-3.141592653", "x-2b", "x-2.15", "x-123.0" -- }) @@ -381,7 +381,7 @@ test:do_execsql_test( SELECT v FROM t1 ORDER BY 1; ]], { -- - "x-123.0", "x-2.15", "x-2b", "x-3.141592653", "x-4.0e9", "x-4221.0", "x0.0013442", "x01234567890123456789", "x1.6", "x11.0", "x2.7", "x5.0e10" + "x-123.0", "x-2.15", "x-2b", "x-3.141592653", "x-4.0e9", "x-4221.0", "x0.0013442", "x01234567890123456789", "x1.6", "x11", "x2.7", "x5.0e10" -- }) @@ -391,7 +391,7 @@ test:do_execsql_test( SELECT v FROM t1 ORDER BY 1 DESC; ]], { -- - "x5.0e10", "x2.7", "x11.0", "x1.6", "x01234567890123456789", "x0.0013442", "x-4221.0", "x-4.0e9", "x-3.141592653", "x-2b", "x-2.15", "x-123.0" + "x5.0e10", "x2.7", "x11", "x1.6", "x01234567890123456789", "x0.0013442", "x-4221.0", "x-4.0e9", "x-3.141592653", "x-2b", "x-2.15", "x-123.0" -- }) @@ -401,7 +401,7 @@ test:do_execsql_test( SELECT substr(v,2,99) FROM t1 ORDER BY 1; ]], { -- - "-123.0","-2.15","-2b","-3.141592653","-4.0e9","-4221.0","0.0013442","01234567890123456789","1.6","11.0","2.7","5.0e10" + "-123.0","-2.15","-2b","-3.141592653","-4.0e9","-4221.0","0.0013442","01234567890123456789","1.6","11","2.7","5.0e10" -- }) diff --git a/test/sql-tap/tkt-91e2e8ba6f.test.lua b/test/sql-tap/tkt-91e2e8ba6f.test.lua index 7622f75..b12b6e0 100755 --- a/test/sql-tap/tkt-91e2e8ba6f.test.lua +++ b/test/sql-tap/tkt-91e2e8ba6f.test.lua @@ -35,7 +35,7 @@ test:do_execsql_test( SELECT x/10, y/10 FROM t1; ]], { -- <1.2> - 1, 1.1 + 1, 1 -- }) @@ -45,7 +45,7 @@ test:do_execsql_test( SELECT x/10, y/10 FROM (SELECT * FROM t1); ]], { -- <1.3> - 1, 1.1 + 1, 1 -- }) @@ -55,7 +55,7 @@ test:do_execsql_test( SELECT x/10, y/10 FROM (SELECT * FROM t1 LIMIT 5 OFFSET 0); ]], { -- <1.4> - 1, 1.1 + 1, 1 -- }) @@ -65,7 +65,7 @@ test:do_execsql_test( SELECT x/10, y/10 FROM (SELECT * FROM t1 LIMIT 5 OFFSET 0) LIMIT 5 OFFSET 0; ]], { -- <1.5> - 1, 1.1 + 1, 1 -- }) @@ -77,7 +77,7 @@ test:do_execsql_test( LIMIT 5 OFFSET 0; ]], { -- <1.6> - 1, 1.1 + 1, 1 -- }) @@ -92,7 +92,7 @@ test:do_execsql_test( SELECT a.x/10, a.y/10 FROM v1 AS a, t1 AS b WHERE a.x = b.x LIMIT 5 OFFSET 0; ]], { -- <1.7> - 1, 1.1 + 1, 1 -- }) diff --git a/test/sql/integer-overflow.result b/test/sql/integer-overflow.result index 223ba02..1849314 100644 --- a/test/sql/integer-overflow.result +++ b/test/sql/integer-overflow.result @@ -171,7 +171,7 @@ box.execute("SELECT * FROM t;") - name: A type: number rows: - - [1, 1.844674407371e+19] + - [1, 18446744073709551615] - [2, -1] ... box.space.T:drop() diff --git a/test/sql/types.result b/test/sql/types.result index 37350f0..81dd169 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -1020,7 +1020,7 @@ box.execute("SELECT CAST(18446744073709551615 AS NUMBER);") - name: CAST(18446744073709551615 AS NUMBER) type: number rows: - - [1.844674407371e+19] + - [18446744073709551615] ... box.execute("SELECT CAST(18446744073709551615 AS TEXT);") --- -- 2.7.4