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 D92DD430D56 for ; Thu, 7 Nov 2019 15:13:46 +0300 (MSK) From: imeevma@tarantool.org Date: Thu, 7 Nov 2019 15:13:45 +0300 Message-Id: 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: korablev@tarantool.org Cc: tarantool-patches@dev.tarantool.org, v.shpilevoy@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/issues/4233 https://github.com/tarantool/tarantool/tree/imeevma/gh-4233-fix-number-field-type-in-sql src/box/sql/vdbe.c | 15 +---- src/box/sql/vdbeInt.h | 1 - src/box/sql/vdbemem.c | 57 ++----------------- 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 +- 11 files changed, 140 insertions(+), 101 deletions(-) diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index 03c63d8..2ceee1a 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/vdbeInt.h b/src/box/sql/vdbeInt.h index 0f32b4c..3c5b9cc 100644 --- a/src/box/sql/vdbeInt.h +++ b/src/box/sql/vdbeInt.h @@ -512,7 +512,6 @@ int sqlVdbeRealValue(Mem *, double *); int mem_value_bool(const struct Mem *mem, bool *b); -int mem_apply_integer_type(Mem *); int sqlVdbeMemRealify(Mem *); int sqlVdbeMemNumerify(Mem *); int sqlVdbeMemCast(Mem *, enum field_type type); diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c index 2d37b62..6d99efe 100644 --- a/src/box/sql/vdbemem.c +++ b/src/box/sql/vdbemem.c @@ -534,23 +534,6 @@ mem_value_bool(const struct Mem *mem, bool *b) } /* - * The MEM structure is already a MEM_Real. Try to also make it a - * MEM_Int if we can. - */ -int -mem_apply_integer_type(Mem *pMem) -{ - int rc; - i64 ix; - assert(pMem->flags & MEM_Real); - assert(EIGHT_BYTE_ALIGNMENT(pMem)); - - if ((rc = doubleToInt64(pMem->u.r, (int64_t *) &ix)) == 0) - mem_set_int(pMem, ix, pMem->u.r <= -1); - return rc; -} - -/* * Convert pMem to type integer. Invalidate any prior representations. */ int @@ -608,18 +591,16 @@ int sqlVdbeMemNumerify(Mem * pMem) { if ((pMem->flags & (MEM_Int | MEM_UInt | MEM_Real | MEM_Null)) == 0) { - assert((pMem->flags & (MEM_Blob | MEM_Str)) != 0); + if ((pMem->flags & (MEM_Blob | MEM_Str)) == 0) + return -1; bool is_neg; int64_t i; if (sql_atoi64(pMem->z, &i, &is_neg, pMem->n) == 0) { mem_set_int(pMem, i, is_neg); } else { - double v; - if (sqlVdbeRealValue(pMem, &v)) + if (sqlAtoF(pMem->z, &pMem->u.r, pMem->n) == 0) return -1; - pMem->u.r = v; MemSetTypeFlag(pMem, MEM_Real); - mem_apply_integer_type(pMem); } } assert((pMem->flags & (MEM_Int | MEM_UInt | MEM_Real | MEM_Null)) != 0); @@ -676,22 +657,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 +707,7 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type) return -1; return 0; case FIELD_TYPE_NUMBER: - return sqlVdbeMemRealify(pMem); + return sqlVdbeMemNumerify(pMem); case FIELD_TYPE_VARBINARY: if ((pMem->flags & MEM_Blob) != 0) return 0; @@ -1820,26 +1785,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..582344e 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, 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