From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng1.m.smailru.net (smtpng1.m.smailru.net [94.100.181.251]) (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 E7290430D56 for ; Thu, 7 Nov 2019 12:30:19 +0300 (MSK) Date: Thu, 7 Nov 2019 12:30:17 +0300 From: Mergen Imeev Message-ID: <20191107093017.GA13486@tarantool.org> References: <7361e30e0857bf3c4d05b72713bbdf2596a1452f.1572693924.git.imeevma@gmail.com> <0a281b27-407a-9ab6-6d75-1564142a9bf9@tarantool.org> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: <0a281b27-407a-9ab6-6d75-1564142a9bf9@tarantool.org> Subject: Re: [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: Vladislav Shpilevoy Cc: tarantool-patches@dev.tarantool.org Thank you for review! My answers, diff between versions and new patch below. On Sat, Nov 02, 2019 at 06:49:58PM +0100, Vladislav Shpilevoy wrote: > Thanks for the patch! > > See 5 comments below. > > On 02/11/2019 12:30, imeevma@tarantool.org wrote: > > 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/tree/imeevma/gh-4233-fix-number-field-type-in-sql > > > https://github.com/tarantool/tarantool/issues/4233 > > https://github.com/tarantool/tarantool/issues/4463 > > > > 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 > > @@ -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); > > 1. Please, put a whitespace after '!='. > Fixed. > > switch( pOp->opcode) { > > case OP_Add: rB += rA; break; > > case OP_Subtract: rB -= rA; break; > > 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) > > +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) { > > 2. I think you can safely assume, that long long unsigned == uint64_t. > As well as long long int == int64_t. > Thanks, was fixed due to changes. > > + 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; > > + } > > 3. strtoull and strtoll are very mean functions. It is easy to > screw their results: > > tarantool> box.execute("SELECT CAST(' 9223372036854774800 ' AS NUMBER);") > --- > - metadata: > - name: CAST(' 9223372036854774800 ' AS NUMBER) > type: number > rows: > - [9223372036854774784] > ... > > tarantool> box.execute("SELECT CAST(' 9223372036854774800' AS NUMBER);") > --- > - metadata: > - name: CAST(' 9223372036854774800' AS NUMBER) > type: number > rows: > - [9223372036854774800] > ... > > Here presence of whitespaces in the end of a number string affects the > cast result. So you can't actually treat tail[0] == 0 as a sign of > success. > > One another example: > > tarantool> box.execute("SELECT CAST('' AS NUMBER);") > --- > - metadata: > - name: CAST('' AS NUMBER) > type: number > rows: > - [0] > ... > > You can find how we work with them in expr_code_int(), sql_atoi64(), > lbox_tonumber64(). > Thanks. I understood that sql_atoi64() is actually capable of all I need in this issue. Replaced sql_mem_to_number() by sqlVdbeMemNumerify(). > > + double value_d; > > + if (sqlAtoF(mem->z, &value_d, mem->n) == 0) > > + return -1; > > 4. Why not 'strtod'? > Some tests fail if the DOUBLE is cast to STRING and then that STRING casts back to DOUBLE. It looks like the SQL functions that casts STRING to DOUBLE and DOUBLE to STRING are somehow synchronized. > > + mem->u.r = value_d; > > + MemSetTypeFlag(mem, MEM_Real); > > + return 0; > > +} > > + > > 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 > > @@ -65,5 +65,107 @@ for _, enc in ipairs({"utf8"}) do > > +test:do_execsql_test( > > + "numcast-2.7", > > + [[ > > + SELECT CAST('101 ' AS NUMBER) / 10, CAST(' 101' AS NUMBER) / 10; > > + ]], { > > + 10.1, 10 > > 5. So this is actually incorrect. Why a number of whitespaces in the end > affects the result type? Fixed. Diff: >From 920653b9649503ff0dafe5587247db02115a013e Mon Sep 17 00:00:00 2001 From: Mergen Imeev Date: Thu, 7 Nov 2019 12:00:46 +0300 Subject: [PATCH] Review fix diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index 61e6487..2ceee1a 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -1657,7 +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); + assert(((type1 | type2) & MEM_Real) != 0); switch( pOp->opcode) { case OP_Add: rB += rA; break; case OP_Subtract: rB -= rA; break; 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 6bfd4c0..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 @@ -596,34 +579,6 @@ 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. @@ -636,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); @@ -754,10 +707,7 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type) return -1; return 0; case FIELD_TYPE_NUMBER: - if ((pMem->flags & - (MEM_Real | MEM_Int | MEM_UInt | MEM_Null)) != 0) - return 0; - return sql_mem_to_number(pMem); + return sqlVdbeMemNumerify(pMem); case FIELD_TYPE_VARBINARY: if ((pMem->flags & MEM_Blob) != 0) return 0; diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua index 1355bad..582344e 100755 --- a/test/sql-tap/numcast.test.lua +++ b/test/sql-tap/numcast.test.lua @@ -133,7 +133,7 @@ test:do_execsql_test( [[ SELECT CAST('101 ' AS NUMBER) / 10, CAST(' 101' AS NUMBER) / 10; ]], { - 10.1, 10 + 10, 10 }) test:do_execsql_test( New patch: >From c00062c46fe603bdc4cacfa26635b8b66ddde849 Mon Sep 17 00:00:00 2001 From: Mergen Imeev Date: Sat, 26 Oct 2019 17:27:53 +0300 Subject: [PATCH] sql: make NUMBER to be union of SQL numeric types 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 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);") ---