From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0E10E31040 for ; Fri, 7 Jun 2019 11:37:53 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id YrjxJc7BUgyF for ; Fri, 7 Jun 2019 11:37:52 -0400 (EDT) Received: from smtpng2.m.smailru.net (smtpng2.m.smailru.net [94.100.179.3]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 66C2E310FC for ; Fri, 7 Jun 2019 11:37:52 -0400 (EDT) From: Nikita Pettik Subject: [tarantool-patches] [PATCH 5/6] sql: introduce extended range for INTEGER type Date: Fri, 7 Jun 2019 18:37:45 +0300 Message-Id: In-Reply-To: References: In-Reply-To: References: Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: tarantool-patches@freelists.org Cc: v.shpilevoy@tarantool.org, Nikita Pettik This patch allows to operate on integer values in range [2^63, 2^64 - 1] It means that: - One can use literals from 9223372036854775808 to 18446744073709551615 - One can pass values from mentioned range to bindings - One can insert and select values from mentioned range Support of built-in functions and operators has been introduced in previous patches. Closes #3810 Part of #4015 --- src/box/bind.c | 13 +- src/box/bind.h | 1 + src/box/errcode.h | 2 +- src/box/execute.c | 2 +- src/box/lua/execute.c | 9 +- src/box/lua/lua_sql.c | 2 +- src/box/sql/build.c | 4 +- src/box/sql/expr.c | 7 +- src/box/sql/pragma.c | 2 +- src/box/sql/sqlInt.h | 6 + src/box/sql/util.c | 2 - src/box/sql/vdbe.c | 2 +- src/box/sql/vdbe.h | 3 +- src/box/sql/vdbeapi.c | 23 +- src/box/sql/vdbeaux.c | 14 +- test/sql-tap/func.test.lua | 2 +- test/sql-tap/hexlit.test.lua | 2 +- test/sql-tap/sql-errors.test.lua | 4 +- test/sql/bind.result | 46 ++- test/sql/bind.test.lua | 4 + test/sql/gh-2347-max-int-literals.result | 39 --- test/sql/gh-2347-max-int-literals.test.lua | 11 - test/sql/integer-overflow.result | 62 +++- test/sql/integer-overflow.test.lua | 7 + test/sql/iproto.result | 6 +- test/sql/types.result | 503 +++++++++++++++++++++++++++++ test/sql/types.test.lua | 98 ++++++ 27 files changed, 761 insertions(+), 115 deletions(-) delete mode 100644 test/sql/gh-2347-max-int-literals.result delete mode 100644 test/sql/gh-2347-max-int-literals.test.lua diff --git a/src/box/bind.c b/src/box/bind.c index f15915377..9e952857f 100644 --- a/src/box/bind.c +++ b/src/box/bind.c @@ -69,13 +69,8 @@ sql_bind_decode(struct sql_bind *bind, int i, const char **packet) switch (type) { case MP_UINT: { uint64_t n = mp_decode_uint(packet); - if (n > INT64_MAX) { - diag_set(ClientError, ER_SQL_BIND_VALUE, - sql_bind_name(bind), "INTEGER"); - return -1; - } - bind->i64 = (int64_t) n; - bind->bytes = sizeof(bind->i64); + bind->u64 = n; + bind->bytes = sizeof(bind->u64); break; } case MP_INT: @@ -173,9 +168,11 @@ sql_bind_column(struct sql_stmt *stmt, const struct sql_bind *p, } switch (p->type) { case MP_INT: - case MP_UINT: rc = sql_bind_int64(stmt, pos, p->i64); break; + case MP_UINT: + rc = sql_bind_uint64(stmt, pos, p->u64); + break; case MP_BOOL: rc = sql_bind_boolean(stmt, pos, p->b); break; diff --git a/src/box/bind.h b/src/box/bind.h index d9823431e..568c558f3 100644 --- a/src/box/bind.h +++ b/src/box/bind.h @@ -64,6 +64,7 @@ struct sql_bind { bool b; double d; int64_t i64; + uint64_t u64; /** For string or blob. */ const char *s; }; diff --git a/src/box/errcode.h b/src/box/errcode.h index f4aba2f54..417029adb 100644 --- a/src/box/errcode.h +++ b/src/box/errcode.h @@ -242,7 +242,7 @@ struct errcode_record { /*187 */_(ER_SQL_ANALYZE_ARGUMENT, "ANALYZE statement argument %s is not a base table") \ /*188 */_(ER_SQL_COLUMN_COUNT_MAX, "Failed to create space '%s': space column count %d exceeds the limit (%d)") \ /*189 */_(ER_HEX_LITERAL_MAX, "Hex literal %s%s length %d exceeds the supported limit (%d)") \ - /*190 */_(ER_INT_LITERAL_MAX, "Integer literal %s exceeds the supported range %lld - %lld") \ + /*190 */_(ER_INT_LITERAL_MAX, "Integer literal %s exceeds the supported range %lld - %llu") \ /*191 */_(ER_SQL_PARSER_LIMIT, "%s %d exceeds the limit (%d)") \ /*192 */_(ER_INDEX_DEF_UNSUPPORTED, "%s are prohibited in an index definition") \ /*193 */_(ER_CK_DEF_UNSUPPORTED, "%s are prohibited in a CHECK constraint definition") \ diff --git a/src/box/execute.c b/src/box/execute.c index f5aead391..2e76724a6 100644 --- a/src/box/execute.c +++ b/src/box/execute.c @@ -146,7 +146,7 @@ sql_column_to_messagepack(struct sql_stmt *stmt, int i, break; } case MP_UINT: { - uint64_t n = sql_column_int64(stmt, i); + uint64_t n = sql_column_uint64(stmt, i); size = mp_sizeof_uint(n); char *pos = (char *) region_alloc(region, size); if (pos == NULL) diff --git a/src/box/lua/execute.c b/src/box/lua/execute.c index 239aba47b..eb6516096 100644 --- a/src/box/lua/execute.c +++ b/src/box/lua/execute.c @@ -140,12 +140,9 @@ lua_sql_bind_decode(struct lua_State *L, struct sql_bind *bind, int idx, int i) return -1; switch (field.type) { case MP_UINT: - if ((uint64_t) field.ival > INT64_MAX) { - diag_set(ClientError, ER_SQL_BIND_VALUE, - sql_bind_name(bind), "INTEGER"); - return -1; - } - FALLTHROUGH; + bind->u64 = field.ival; + bind->bytes = sizeof(bind->u64); + break; case MP_INT: bind->i64 = field.ival; bind->bytes = sizeof(bind->i64); diff --git a/src/box/lua/lua_sql.c b/src/box/lua/lua_sql.c index 59ea260bf..89e72e6d2 100644 --- a/src/box/lua/lua_sql.c +++ b/src/box/lua/lua_sql.c @@ -61,7 +61,7 @@ lua_sql_call(sql_context *pCtx, int nVal, sql_value **apVal) { luaL_pushint64(L, sql_value_int64(param)); break; case MP_UINT: - luaL_pushuint64(L, sql_value_int64(param)); + luaL_pushuint64(L, sql_value_uint64(param)); break; case MP_DOUBLE: lua_pushnumber(L, sql_value_double(param)); diff --git a/src/box/sql/build.c b/src/box/sql/build.c index e2353d8cc..75d6579ca 100644 --- a/src/box/sql/build.c +++ b/src/box/sql/build.c @@ -918,10 +918,10 @@ emitNewSysSequenceRecord(Parse *pParse, int reg_seq_id, const char *seq_name) /* 5. Minimum */ sqlVdbeAddOp4Dup8(v, OP_Int64, 0, first_col + 5, 0, - (unsigned char*)&min_usigned_long_long, P4_INT64); + (unsigned char*)&min_usigned_long_long, P4_UINT64); /* 6. Maximum */ sqlVdbeAddOp4Dup8(v, OP_Int64, 0, first_col + 6, 0, - (unsigned char*)&max_usigned_long_long, P4_INT64); + (unsigned char*)&max_usigned_long_long, P4_UINT64); /* 7. Start */ sqlVdbeAddOp2(v, OP_Integer, 1, first_col + 7); diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c index 158631416..4da297a5f 100644 --- a/src/box/sql/expr.c +++ b/src/box/sql/expr.c @@ -1203,7 +1203,7 @@ sqlExprAssignVarNumber(Parse * pParse, Expr * pExpr, u32 n) testcase(i == 1); testcase(i == SQL_BIND_PARAMETER_MAX - 1); testcase(i == SQL_BIND_PARAMETER_MAX); - if (i < 1) { + if (is_neg || i < 1) { diag_set(ClientError, ER_SQL_PARSER_GENERIC, "Index of binding slots must start "\ "from 1"); @@ -3367,12 +3367,13 @@ expr_code_int(struct Parse *parse, struct Expr *expr, bool is_neg, if (rc != 0) { int_overflow: diag_set(ClientError, ER_INT_LITERAL_MAX, z, INT64_MIN, - INT64_MAX); + UINT64_MAX); parse->is_aborted = true; return; } } - sqlVdbeAddOp4Dup8(v, OP_Int64, 0, mem, 0, (u8 *)&value, P4_INT64); + sqlVdbeAddOp4Dup8(v, OP_Int64, 0, mem, 0, (u8 *) &value, + is_neg ? P4_INT64 : P4_UINT64); } /* diff --git a/src/box/sql/pragma.c b/src/box/sql/pragma.c index 53524b617..18b8f351b 100644 --- a/src/box/sql/pragma.c +++ b/src/box/sql/pragma.c @@ -134,7 +134,7 @@ static void returnSingleInt(Vdbe * v, i64 value) { sqlVdbeAddOp4Dup8(v, OP_Int64, 0, 1, 0, (const u8 *)&value, - P4_INT64); + value < 0 ? P4_INT64 : P4_UINT64); sqlVdbeAddOp2(v, OP_ResultRow, 1, 1); } diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h index 4697e3003..89b7356d8 100644 --- a/src/box/sql/sqlInt.h +++ b/src/box/sql/sqlInt.h @@ -594,6 +594,9 @@ sql_column_boolean(struct sql_stmt *stmt, int column); sql_int64 sql_column_int64(sql_stmt *, int iCol); +uint64_t +sql_column_uint64(struct sql_stmt *stmt, int column); + const unsigned char * sql_column_text(sql_stmt *, int iCol); @@ -908,6 +911,9 @@ sql_bind_int(sql_stmt *, int, int); int sql_bind_int64(sql_stmt *, int, sql_int64); +int +sql_bind_uint64(struct sql_stmt *stmt, int i, uint64_t value); + int sql_bind_null(sql_stmt *, int); diff --git a/src/box/sql/util.c b/src/box/sql/util.c index ee6a83ad5..3af2d6d1f 100644 --- a/src/box/sql/util.c +++ b/src/box/sql/util.c @@ -611,8 +611,6 @@ sql_atoi64(const char *z, int64_t *val, bool *is_neg, int length) *val = strtoll(z, &end, 10); } else { uint64_t u_val = strtoull(z, &end, 10); - if (u_val > INT64_MAX) - return -1; *val = u_val; } /* No digits were found, e.g. an empty string. */ diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index 9c28b9131..dae9f903d 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -1108,7 +1108,7 @@ case OP_Bool: { /* out2 */ case OP_Int64: { /* out2 */ pOut = out2Prerelease(p, pOp); assert(pOp->p4.pI64!=0); - mem_set_int(pOut, *pOp->p4.pI64, *pOp->p4.pI64 < 0); + mem_set_int(pOut, *pOp->p4.pI64, pOp->p4type == P4_INT64); break; } diff --git a/src/box/sql/vdbe.h b/src/box/sql/vdbe.h index f9bb96f09..b37daa897 100644 --- a/src/box/sql/vdbe.h +++ b/src/box/sql/vdbe.h @@ -70,7 +70,7 @@ struct VdbeOp { int i; /* Integer value if p4type==P4_INT32 */ void *p; /* Generic pointer */ char *z; /* Pointer to data for string (char array) types */ - i64 *pI64; /* Used when p4type is P4_INT64 */ + i64 *pI64; /* Used when p4type is P4_INT64/UINT64 */ double *pReal; /* Used when p4type is P4_REAL */ FuncDef *pFunc; /* Used when p4type is P4_FUNCDEF */ sql_context *pCtx; /* Used when p4type is P4_FUNCCTX */ @@ -127,6 +127,7 @@ struct SubProgram { #define P4_TRANSIENT 0 /* P4 is a pointer to a transient string */ #define P4_REAL (-9) /* P4 is a 64-bit floating point value */ #define P4_INT64 (-10) /* P4 is a 64-bit signed integer */ +#define P4_UINT64 (-8) /* P4 is a 64-bit signed integer */ #define P4_INT32 (-11) /* P4 is a 32-bit signed integer */ #define P4_INTARRAY (-12) /* P4 is a vector of 32-bit integers */ #define P4_SUBPROGRAM (-13) /* P4 is a pointer to a SubProgram structure */ diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c index 752efeecb..751aead70 100644 --- a/src/box/sql/vdbeapi.c +++ b/src/box/sql/vdbeapi.c @@ -1027,6 +1027,14 @@ sql_column_int64(sql_stmt * pStmt, int i) return val; } +uint64_t +sql_column_uint64(sql_stmt * pStmt, int i) +{ + sql_int64 val = sql_value_uint64(columnMem(pStmt, i)); + columnMallocFailure(pStmt); + return val; +} + const unsigned char * sql_column_text(sql_stmt * pStmt, int i) { @@ -1387,9 +1395,22 @@ sql_bind_int64(sql_stmt * pStmt, int i, sql_int64 iValue) int rc; Vdbe *p = (Vdbe *) pStmt; rc = vdbeUnbind(p, i); + assert(iValue < 0); if (rc == SQL_OK) { rc = sql_bind_type(p, i, "INTEGER"); - mem_set_int(&p->aVar[i - 1], iValue, iValue < 0); + mem_set_int(&p->aVar[i - 1], iValue, true); + } + return rc; +} + +int +sql_bind_uint64(struct sql_stmt *stmt, int i, uint64_t value) +{ + struct Vdbe *p = (struct Vdbe *) stmt; + int rc = vdbeUnbind(p, i); + if (rc == SQL_OK) { + rc = sql_bind_type(p, i, "UNSIGNED"); + mem_set_int(&p->aVar[i - 1], value, false); } return rc; } diff --git a/src/box/sql/vdbeaux.c b/src/box/sql/vdbeaux.c index 5a71e1801..7ca2d89d8 100644 --- a/src/box/sql/vdbeaux.c +++ b/src/box/sql/vdbeaux.c @@ -386,8 +386,8 @@ sqlVdbeAddOp4(Vdbe * p, /* Add the opcode to this VM */ } /* - * Add an opcode that includes the p4 value with a P4_INT64 or - * P4_REAL type. + * Add an opcode that includes the p4 value with a P4_INT64/UINT64 + * or P4_REAL type. */ int sqlVdbeAddOp4Dup8(Vdbe * p, /* Add the opcode to this VM */ @@ -863,6 +863,7 @@ freeP4(sql * db, int p4type, void *p4) } case P4_REAL: case P4_INT64: + case P4_UINT64: case P4_DYNAMIC: case P4_INTARRAY:{ sqlDbFree(db, p4); @@ -1355,6 +1356,10 @@ displayP4(Op * pOp, char *zTemp, int nTemp) sqlXPrintf(&x, "%lld", *pOp->p4.pI64); break; } + case P4_UINT64: { + sqlXPrintf(&x, "%llu", (uint64_t)*pOp->p4.pI64); + break; + } case P4_INT32:{ sqlXPrintf(&x, "%d", pOp->p4.i); break; @@ -3735,11 +3740,6 @@ vdbe_decode_msgpack_into_mem(const char *buf, struct Mem *mem, uint32_t *len) } case MP_UINT: { uint64_t v = mp_decode_uint(&buf); - if (v > INT64_MAX) { - diag_set(ClientError, ER_SQL_EXECUTE, - "integer is overflowed"); - return -1; - } mem->u.u = v; mem->flags = MEM_UInt; break; diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua index 9cf9c2f67..217b137a4 100755 --- a/test/sql-tap/func.test.lua +++ b/test/sql-tap/func.test.lua @@ -919,7 +919,7 @@ test:do_execsql_test( UNION ALL SELECT -9223372036854775807) ]], { -- - "number" + "unsigned" -- }) diff --git a/test/sql-tap/hexlit.test.lua b/test/sql-tap/hexlit.test.lua index 288d823d9..220aa9117 100755 --- a/test/sql-tap/hexlit.test.lua +++ b/test/sql-tap/hexlit.test.lua @@ -91,7 +91,7 @@ hexlit1(160, "0X1000000000000000", 1152921504606846976LL) hexlit1(161, "0x2000000000000000", 2305843009213693952LL) hexlit1(162, "0X4000000000000000", 4611686018427387904LL) hexlit1(163, "0x8000000000000000", -9223372036854775808LL) -hexlit1(164, "0XFFFFFFFFFFFFFFFF", -1) +hexlit1(164, "0XFFFFFFFFFFFFFFFF", 18446744073709551615LL) for n = 1, 0x10 -1, 1 do hexlit1("200."..n..".1", "0X"..string.format("%03X",n), n) hexlit1("200."..n..".2", "0x"..string.format("%03X",n), n) diff --git a/test/sql-tap/sql-errors.test.lua b/test/sql-tap/sql-errors.test.lua index 9357c406b..4201899fd 100755 --- a/test/sql-tap/sql-errors.test.lua +++ b/test/sql-tap/sql-errors.test.lua @@ -139,10 +139,10 @@ test:do_catchsql_test( test:do_catchsql_test( "sql-errors-1.13", [[ - SELECT 9223372036854775808; + SELECT 18446744073709551616; ]], { -- - 1,"Integer literal 9223372036854775808 exceeds the supported range -9223372036854775808 - 9223372036854775807" + 1,"Integer literal 18446744073709551616 exceeds the supported range -9223372036854775808 - 18446744073709551615" -- }) diff --git a/test/sql/bind.result b/test/sql/bind.result index 076bf8319..209daff85 100644 --- a/test/sql/bind.result +++ b/test/sql/bind.result @@ -72,11 +72,11 @@ execute('SELECT ?, ?, ?', {1, 2, 3}) --- - metadata: - name: '?' - type: INTEGER + type: UNSIGNED - name: '?' - type: INTEGER + type: UNSIGNED - name: '?' - type: INTEGER + type: UNSIGNED rows: - [1, 2, 3] ... @@ -102,11 +102,11 @@ execute('SELECT ?, :value1, @value2', parameters) --- - metadata: - name: '?' - type: INTEGER + type: UNSIGNED - name: :value1 - type: INTEGER + type: UNSIGNED - name: '@value2' - type: INTEGER + type: UNSIGNED rows: - [10, 11, 12] ... @@ -144,21 +144,21 @@ execute('SELECT :value3, ?, :value1, ?, ?, @value2, ?, :value3', parameters) --- - metadata: - name: :value3 - type: INTEGER + type: UNSIGNED - name: '?' - type: INTEGER + type: UNSIGNED - name: :value1 - type: INTEGER + type: UNSIGNED - name: '?' - type: INTEGER + type: UNSIGNED - name: '?' - type: INTEGER + type: UNSIGNED - name: '@value2' - type: INTEGER + type: UNSIGNED - name: '?' type: boolean - name: :value3 - type: INTEGER + type: UNSIGNED rows: - [1, 2, 3, 4, 5, 6, null, 1] ... @@ -187,9 +187,9 @@ execute('SELECT ? AS kek, ? AS kek2', {1, 2}) --- - metadata: - name: KEK - type: INTEGER + type: UNSIGNED - name: KEK2 - type: INTEGER + type: UNSIGNED rows: - [1, 2] ... @@ -236,7 +236,11 @@ execute(sql, parameters) -- suitable method in its bind API. execute('SELECT ? AS big_uint', {0xefffffffffffffff}) --- -- error: Bind value for parameter 1 is out of range for type INTEGER +- metadata: + - name: BIG_UINT + type: UNSIGNED + rows: + - [17293822569102704640] ... -- Bind incorrect parameters. ok, err = pcall(execute, 'SELECT ?', { {1, 2, 3} }) @@ -275,6 +279,16 @@ execute('SELECT :value', parameters) --- - error: Bind value type MAP for parameter ':value' is not supported ... +-- gh-3810: bind values of integer in range up to 2^64 - 1. +-- +execute('SELECT ? ', {18446744073709551615ULL}) +--- +- metadata: + - name: '?' + type: UNSIGNED + rows: + - [18446744073709551615] +... test_run:cmd("setopt delimiter ';'") --- - true diff --git a/test/sql/bind.test.lua b/test/sql/bind.test.lua index 229207d3a..ea71e7fe7 100644 --- a/test/sql/bind.test.lua +++ b/test/sql/bind.test.lua @@ -89,6 +89,10 @@ parameters[1] = {} parameters[1][':value'] = {kek = 300} execute('SELECT :value', parameters) +-- gh-3810: bind values of integer in range up to 2^64 - 1. +-- +execute('SELECT ? ', {18446744073709551615ULL}) + test_run:cmd("setopt delimiter ';'") if remote then cn:close() diff --git a/test/sql/gh-2347-max-int-literals.result b/test/sql/gh-2347-max-int-literals.result deleted file mode 100644 index e6c4d9992..000000000 --- a/test/sql/gh-2347-max-int-literals.result +++ /dev/null @@ -1,39 +0,0 @@ -test_run = require('test_run').new() ---- -... -engine = test_run:get_cfg('engine') ---- -... -box.execute('pragma sql_default_engine=\''..engine..'\'') ---- -- row_count: 0 -... -box.cfg{} ---- -... -box.execute("select (9223372036854775807)") ---- -- metadata: - - name: (9223372036854775807) - type: integer - rows: - - [9223372036854775807] -... -box.execute("select (-9223372036854775808)") ---- -- metadata: - - name: (-9223372036854775808) - type: integer - rows: - - [-9223372036854775808] -... -box.execute("select (9223372036854775808)") ---- -- error: Integer literal 9223372036854775808 exceeds the supported range -9223372036854775808 - - 9223372036854775807 -... -box.execute("select (-9223372036854775809)") ---- -- error: Integer literal -9223372036854775809 exceeds the supported range -9223372036854775808 - - 9223372036854775807 -... diff --git a/test/sql/gh-2347-max-int-literals.test.lua b/test/sql/gh-2347-max-int-literals.test.lua deleted file mode 100644 index 8331f0333..000000000 --- a/test/sql/gh-2347-max-int-literals.test.lua +++ /dev/null @@ -1,11 +0,0 @@ -test_run = require('test_run').new() -engine = test_run:get_cfg('engine') -box.execute('pragma sql_default_engine=\''..engine..'\'') - -box.cfg{} - -box.execute("select (9223372036854775807)") -box.execute("select (-9223372036854775808)") - -box.execute("select (9223372036854775808)") -box.execute("select (-9223372036854775809)") diff --git a/test/sql/integer-overflow.result b/test/sql/integer-overflow.result index 40962ac5c..915914eac 100644 --- a/test/sql/integer-overflow.result +++ b/test/sql/integer-overflow.result @@ -40,28 +40,67 @@ box.execute('SELECT (9223372036854775807 + 9223372036854775807 + 2);') --- - error: 'Failed to execute SQL statement: integer is overflowed' ... +box.execute('SELECT 18446744073709551615 * 2;') +--- +- error: 'Failed to execute SQL statement: integer is overflowed' +... +box.execute('SELECT (-9223372036854775807 * (-2));') +--- +- metadata: + - name: (-9223372036854775807 * (-2)) + type: integer + rows: + - [18446744073709551614] +... -- Literals are checked right after parsing. -- box.execute('SELECT 9223372036854775808;') --- -- error: Integer literal 9223372036854775808 exceeds the supported range -9223372036854775808 - - 9223372036854775807 +- metadata: + - name: '9223372036854775808' + type: integer + rows: + - [9223372036854775808] ... box.execute('SELECT -9223372036854775809;') --- - error: Integer literal -9223372036854775809 exceeds the supported range -9223372036854775808 - - 9223372036854775807 + - 18446744073709551615 ... box.execute('SELECT 9223372036854775808 - 1;') --- -- error: Integer literal 9223372036854775808 exceeds the supported range -9223372036854775808 - - 9223372036854775807 +- metadata: + - name: 9223372036854775808 - 1 + type: integer + rows: + - [9223372036854775807] +... +box.execute('SELECT 18446744073709551615;') +--- +- metadata: + - name: '18446744073709551615' + type: integer + rows: + - [18446744073709551615] +... +box.execute('SELECT 18446744073709551616;') +--- +- error: Integer literal 18446744073709551616 exceeds the supported range -9223372036854775808 + - 18446744073709551615 ... -- Test that CAST may also leads to overflow. -- box.execute('SELECT CAST(\'9223372036854775808\' AS INTEGER);') --- -- error: 'Type mismatch: can not convert 9223372036854775808 to integer' +- metadata: + - name: CAST('9223372036854775808' AS INTEGER) + type: integer + rows: + - [9223372036854775808] +... +box.execute('SELECT CAST(\'18446744073709551616\' AS INTEGER);') +--- +- error: 'Type mismatch: can not convert 18446744073709551616 to integer' ... -- Due to inexact represantation of large integers in terms of -- floating point numbers, numerics with value < INT64_MAX @@ -85,9 +124,18 @@ box.space.T:insert({9223372036854775809}) --- - [9223372036854775808] ... +box.space.T:insert({18446744073709551615ULL}) +--- +- [18446744073709551615] +... box.execute('SELECT * FROM t;') --- -- error: 'Failed to execute SQL statement: integer is overflowed' +- metadata: + - name: ID + type: integer + rows: + - [9223372036854775808] + - [18446744073709551615] ... box.space.T:drop() --- diff --git a/test/sql/integer-overflow.test.lua b/test/sql/integer-overflow.test.lua index 7727f368c..246465da0 100644 --- a/test/sql/integer-overflow.test.lua +++ b/test/sql/integer-overflow.test.lua @@ -11,15 +11,21 @@ box.execute('SELECT (-9223372036854775808 / -1);') box.execute('SELECT (-9223372036854775808 - 1);') box.execute('SELECT (9223372036854775807 + 1);') box.execute('SELECT (9223372036854775807 + 9223372036854775807 + 2);') +box.execute('SELECT 18446744073709551615 * 2;') +box.execute('SELECT (-9223372036854775807 * (-2));') -- Literals are checked right after parsing. -- box.execute('SELECT 9223372036854775808;') box.execute('SELECT -9223372036854775809;') box.execute('SELECT 9223372036854775808 - 1;') +box.execute('SELECT 18446744073709551615;') +box.execute('SELECT 18446744073709551616;') + -- Test that CAST may also leads to overflow. -- box.execute('SELECT CAST(\'9223372036854775808\' AS INTEGER);') +box.execute('SELECT CAST(\'18446744073709551616\' AS INTEGER);') -- Due to inexact represantation of large integers in terms of -- floating point numbers, numerics with value < INT64_MAX -- have INT64_MAX + 1 value in integer representation: @@ -33,5 +39,6 @@ box.execute('SELECT CAST(9223372036854775807.0 AS INTEGER);') -- box.execute('CREATE TABLE t (id INT PRIMARY KEY);') box.space.T:insert({9223372036854775809}) +box.space.T:insert({18446744073709551615ULL}) box.execute('SELECT * FROM t;') box.space.T:drop() diff --git a/test/sql/iproto.result b/test/sql/iproto.result index e734872b2..10f42d936 100644 --- a/test/sql/iproto.result +++ b/test/sql/iproto.result @@ -368,11 +368,11 @@ cn:execute('select $2, $1, $3', parameters) --- - metadata: - name: $2 - type: INTEGER + type: UNSIGNED - name: $1 - type: INTEGER + type: UNSIGNED - name: $3 - type: INTEGER + type: UNSIGNED rows: - [22, 11, 33] ... diff --git a/test/sql/types.result b/test/sql/types.result index 4670fd38a..e12515345 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -963,3 +963,506 @@ box.execute('SELECT ?', {true}) rows: - [true] ... +-- gh-3810: range of integer is extended up to 2^64 - 1. +-- +box.execute("SELECT 18446744073709551615 > 18446744073709551614;") +--- +- metadata: + - name: 18446744073709551615 > 18446744073709551614 + type: boolean + rows: + - [true] +... +box.execute("SELECT 18446744073709551615 = 18446744073709551615;") +--- +- metadata: + - name: 18446744073709551615 = 18446744073709551615 + type: boolean + rows: + - [true] +... +box.execute("SELECT 18446744073709551615 > -9223372036854775808;") +--- +- metadata: + - name: 18446744073709551615 > -9223372036854775808 + type: boolean + rows: + - [true] +... +box.execute("SELECT -1 < 18446744073709551615;") +--- +- metadata: + - name: -1 < 18446744073709551615 + type: boolean + rows: + - [true] +... +box.execute("SELECT 18446744073709551610 - 18446744073709551615;") +--- +- metadata: + - name: 18446744073709551610 - 18446744073709551615 + type: integer + rows: + - [-5] +... +box.execute("SELECT 18446744073709551615 = null;") +--- +- metadata: + - name: 18446744073709551615 = null + type: boolean + rows: + - [null] +... +box.execute("SELECT 18446744073709551615 = 18446744073709551615.0;") +--- +- metadata: + - name: 18446744073709551615 = 18446744073709551615.0 + type: boolean + rows: + - [false] +... +box.execute("SELECT 18446744073709551615.0 > 18446744073709551615") +--- +- metadata: + - name: 18446744073709551615.0 > 18446744073709551615 + type: boolean + rows: + - [true] +... +box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)") +--- +- metadata: + - name: 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0) + type: boolean + rows: + - [true] +... +box.execute("SELECT 1 LIMIT 18446744073709551615;") +--- +- metadata: + - name: '1' + type: integer + rows: + - [1] +... +box.execute("SELECT 1 LIMIT 1 OFFSET 18446744073709551615;") +--- +- metadata: + - name: '1' + type: integer + rows: [] +... +box.execute("SELECT CAST('18446744073' || '709551616' AS INTEGER);") +--- +- error: 'Type mismatch: can not convert 18446744073709551616 to integer' +... +box.execute("SELECT CAST('18446744073' || '709551615' AS INTEGER);") +--- +- metadata: + - name: CAST('18446744073' || '709551615' AS INTEGER) + type: integer + rows: + - [18446744073709551615] +... +box.execute("SELECT 18446744073709551610 + 5;") +--- +- metadata: + - name: 18446744073709551610 + 5 + type: integer + rows: + - [18446744073709551615] +... +box.execute("SELECT 18446744073709551615 * 1;") +--- +- metadata: + - name: 18446744073709551615 * 1 + type: integer + rows: + - [18446744073709551615] +... +box.execute("SELECT 1 / 18446744073709551615;") +--- +- metadata: + - name: 1 / 18446744073709551615 + type: integer + rows: + - [0] +... +box.execute("SELECT 18446744073709551615 / 18446744073709551615;") +--- +- metadata: + - name: 18446744073709551615 / 18446744073709551615 + type: integer + rows: + - [1] +... +box.execute("SELECT 18446744073709551615 / -9223372036854775808;") +--- +- metadata: + - name: 18446744073709551615 / -9223372036854775808 + type: integer + rows: + - [-1] +... +box.execute("SELECT 0 - 18446744073709551610;") +--- +- error: 'Failed to execute SQL statement: integer is overflowed' +... +box.execute("CREATE TABLE t (id INT PRIMARY KEY, i INT);") +--- +- row_count: 1 +... +box.execute("INSERT INTO t VALUES (1, 18446744073709551615);") +--- +- row_count: 1 +... +box.execute("INSERT INTO t VALUES (2, 18446744073709551614);") +--- +- row_count: 1 +... +box.execute("INSERT INTO t VALUES (3, 18446744073709551613)") +--- +- row_count: 1 +... +box.execute("SELECT i FROM t;") +--- +- metadata: + - name: I + type: integer + rows: + - [18446744073709551615] + - [18446744073709551614] + - [18446744073709551613] +... +box.execute("SELECT i FROM t WHERE i = 18446744073709551615;") +--- +- metadata: + - name: I + type: integer + rows: + - [18446744073709551615] +... +box.execute("SELECT i FROM t WHERE i BETWEEN 18446744073709551613 AND 18446744073709551615;") +--- +- metadata: + - name: I + type: integer + rows: + - [18446744073709551615] + - [18446744073709551614] + - [18446744073709551613] +... +box.execute("SELECT i FROM t ORDER BY i;") +--- +- metadata: + - name: I + type: integer + rows: + - [18446744073709551613] + - [18446744073709551614] + - [18446744073709551615] +... +box.execute("SELECT i FROM t ORDER BY -i;") +--- +- error: 'Failed to execute SQL statement: integer is overflowed' +... +box.execute("SELECT i FROM t ORDER BY i LIMIT 1;") +--- +- metadata: + - name: I + type: integer + rows: + - [18446744073709551613] +... +-- Test that built-in functions are capable of handling unsigneds. +-- +box.execute("DELETE FROM t WHERE i > 18446744073709551613;") +--- +- row_count: 2 +... +box.execute("INSERT INTO t VALUES (1, 1);") +--- +- row_count: 1 +... +box.execute("INSERT INTO t VALUES (2, -1);") +--- +- row_count: 1 +... +box.execute("SELECT sum(i) FROM t;") +--- +- metadata: + - name: sum(i) + type: number + rows: + - [18446744073709551613] +... +box.execute("SELECT avg(i) FROM t;") +--- +- metadata: + - name: avg(i) + type: number + rows: + - [6148914691236516864] +... +box.execute("SELECT total(i) FROM t;") +--- +- metadata: + - name: total(i) + type: number + rows: + - [1.844674407371e+19] +... +box.execute("SELECT min(i) FROM t;") +--- +- metadata: + - name: min(i) + type: scalar + rows: + - [-1] +... +box.execute("SELECT max(i) FROM t;") +--- +- metadata: + - name: max(i) + type: scalar + rows: + - [18446744073709551613] +... +box.execute("SELECT count(i) FROM t;") +--- +- metadata: + - name: count(i) + type: integer + rows: + - [3] +... +box.execute("SELECT group_concat(i) FROM t;") +--- +- metadata: + - name: group_concat(i) + type: string + rows: + - ['1,-1,18446744073709551613'] +... +box.execute("DELETE FROM t WHERE i < 18446744073709551613;") +--- +- row_count: 2 +... +box.execute("SELECT lower(i) FROM t;") +--- +- metadata: + - name: lower(i) + type: string + rows: + - ['18446744073709551613'] +... +box.execute("SELECT upper(i) FROM t;") +--- +- metadata: + - name: upper(i) + type: string + rows: + - ['18446744073709551613'] +... +box.execute("SELECT abs(i) FROM t;") +--- +- metadata: + - name: abs(i) + type: number + rows: + - [18446744073709551613] +... +box.execute("SELECT typeof(i) FROM t;") +--- +- metadata: + - name: typeof(i) + type: string + rows: + - ['unsigned'] +... +box.execute("SELECT quote(i) FROM t;") +--- +- metadata: + - name: quote(i) + type: string + rows: + - [18446744073709551613] +... +box.execute("SELECT min(-1, i) FROM t;") +--- +- metadata: + - name: min(-1, i) + type: scalar + rows: + - [-1] +... +box.execute("SELECT quote(i) FROM t;") +--- +- metadata: + - name: quote(i) + type: string + rows: + - [18446744073709551613] +... +box.execute("CREATE INDEX i ON t(i);") +--- +- row_count: 1 +... +box.execute("SELECT i FROM t WHERE i = 18446744073709551613;") +--- +- metadata: + - name: I + type: integer + rows: + - [18446744073709551613] +... +box.execute("SELECT i FROM t WHERE i >= 18446744073709551613 ORDER BY i;") +--- +- metadata: + - name: I + type: integer + rows: + - [18446744073709551613] +... +box.execute("UPDATE t SET i = 18446744073709551615 WHERE i = 18446744073709551613;") +--- +- row_count: 1 +... +box.execute("SELECT i FROM t;") +--- +- metadata: + - name: I + type: integer + rows: + - [18446744073709551615] +... +-- Test constraints functionality. +-- +box.execute("CREATE TABLE parent (id INT PRIMARY KEY, a INT UNIQUE);") +--- +- row_count: 1 +... +box.execute("INSERT INTO parent VALUES (1, 18446744073709551613);") +--- +- row_count: 1 +... +box.space.T:truncate() +--- +... +box.execute("ALTER TABLE t ADD CONSTRAINT fk1 FOREIGN KEY (i) REFERENCES parent (a);") +--- +- row_count: 1 +... +box.execute("INSERT INTO t VALUES (1, 18446744073709551615);") +--- +- error: 'Failed to execute SQL statement: FOREIGN KEY constraint failed' +... +box.execute("INSERT INTO parent VALUES (2, 18446744073709551615);") +--- +- row_count: 1 +... +box.execute("INSERT INTO t VALUES (1, 18446744073709551615);") +--- +- row_count: 1 +... +box.execute("ALTER TABLE t DROP CONSTRAINT fk1;") +--- +- row_count: 1 +... +box.space.PARENT:drop() +--- +... +box.space.T:drop() +--- +... +box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a INT CHECK (a > 18446744073709551612));") +--- +- row_count: 1 +... +box.execute("INSERT INTO t1 VALUES (1, 18446744073709551611);") +--- +- error: 'Failed to execute SQL statement: CHECK constraint failed: T1' +... +box.execute("INSERT INTO t1 VALUES (1, -1);") +--- +- error: 'Failed to execute SQL statement: CHECK constraint failed: T1' +... +box.space.T1:drop() +--- +... +box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a INT DEFAULT 18446744073709551615);") +--- +- row_count: 1 +... +box.execute("INSERT INTO t1 (id) VALUES (1);") +--- +- row_count: 1 +... +box.space.T1:select() +--- +- - [1, 18446744073709551615] +... +box.space.T1:drop() +--- +... +-- Test that autoincrement accepts only max 2^63 - 1 . +-- +box.execute("CREATE TABLE t1 (id INT PRIMARY KEY AUTOINCREMENT);") +--- +- row_count: 1 +... +box.execute("INSERT INTO t1 VALUES (18446744073709551615);") +--- +- row_count: 1 +... +box.execute("INSERT INTO t1 VALUES (NULL);") +--- +- autoincrement_ids: + - 1 + row_count: 1 +... +box.space.T1:drop() +--- +... +-- Test CAST facilities. +-- +box.execute("SELECT CAST(18446744073709551615 AS FLOAT);") +--- +- metadata: + - name: CAST(18446744073709551615 AS FLOAT) + type: number + rows: + - [1.844674407371e+19] +... +box.execute("SELECT CAST(18446744073709551615 AS TEXT);") +--- +- metadata: + - name: CAST(18446744073709551615 AS TEXT) + type: string + rows: + - ['18446744073709551615'] +... +box.execute("SELECT CAST(18446744073709551615 AS SCALAR);") +--- +- metadata: + - name: CAST(18446744073709551615 AS SCALAR) + type: scalar + rows: + - [18446744073709551615] +... +box.execute("SELECT CAST(18446744073709551615 AS BOOLEAN);") +--- +- metadata: + - name: CAST(18446744073709551615 AS BOOLEAN) + type: boolean + rows: + - [true] +... +box.execute("SELECT CAST('18446744073709551615' AS INTEGER);") +--- +- metadata: + - name: CAST('18446744073709551615' AS INTEGER) + type: integer + rows: + - [18446744073709551615] +... diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua index ae1a0ab72..ccdd1f3d4 100644 --- a/test/sql/types.test.lua +++ b/test/sql/types.test.lua @@ -234,3 +234,101 @@ box.execute('SELECT \'9223372036854\' + 1;') -- Fix BOOLEAN bindings. box.execute('SELECT ?', {true}) + +-- gh-3810: range of integer is extended up to 2^64 - 1. +-- +box.execute("SELECT 18446744073709551615 > 18446744073709551614;") +box.execute("SELECT 18446744073709551615 = 18446744073709551615;") +box.execute("SELECT 18446744073709551615 > -9223372036854775808;") +box.execute("SELECT -1 < 18446744073709551615;") +box.execute("SELECT 18446744073709551610 - 18446744073709551615;") +box.execute("SELECT 18446744073709551615 = null;") +box.execute("SELECT 18446744073709551615 = 18446744073709551615.0;") +box.execute("SELECT 18446744073709551615.0 > 18446744073709551615") +box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)") +box.execute("SELECT 1 LIMIT 18446744073709551615;") +box.execute("SELECT 1 LIMIT 1 OFFSET 18446744073709551615;") +box.execute("SELECT CAST('18446744073' || '709551616' AS INTEGER);") +box.execute("SELECT CAST('18446744073' || '709551615' AS INTEGER);") +box.execute("SELECT 18446744073709551610 + 5;") +box.execute("SELECT 18446744073709551615 * 1;") +box.execute("SELECT 1 / 18446744073709551615;") +box.execute("SELECT 18446744073709551615 / 18446744073709551615;") +box.execute("SELECT 18446744073709551615 / -9223372036854775808;") +box.execute("SELECT 0 - 18446744073709551610;") +box.execute("CREATE TABLE t (id INT PRIMARY KEY, i INT);") +box.execute("INSERT INTO t VALUES (1, 18446744073709551615);") +box.execute("INSERT INTO t VALUES (2, 18446744073709551614);") +box.execute("INSERT INTO t VALUES (3, 18446744073709551613)") +box.execute("SELECT i FROM t;") +box.execute("SELECT i FROM t WHERE i = 18446744073709551615;") +box.execute("SELECT i FROM t WHERE i BETWEEN 18446744073709551613 AND 18446744073709551615;") +box.execute("SELECT i FROM t ORDER BY i;") +box.execute("SELECT i FROM t ORDER BY -i;") +box.execute("SELECT i FROM t ORDER BY i LIMIT 1;") +-- Test that built-in functions are capable of handling unsigneds. +-- +box.execute("DELETE FROM t WHERE i > 18446744073709551613;") +box.execute("INSERT INTO t VALUES (1, 1);") +box.execute("INSERT INTO t VALUES (2, -1);") +box.execute("SELECT sum(i) FROM t;") +box.execute("SELECT avg(i) FROM t;") +box.execute("SELECT total(i) FROM t;") +box.execute("SELECT min(i) FROM t;") +box.execute("SELECT max(i) FROM t;") +box.execute("SELECT count(i) FROM t;") +box.execute("SELECT group_concat(i) FROM t;") + +box.execute("DELETE FROM t WHERE i < 18446744073709551613;") +box.execute("SELECT lower(i) FROM t;") +box.execute("SELECT upper(i) FROM t;") +box.execute("SELECT abs(i) FROM t;") +box.execute("SELECT typeof(i) FROM t;") +box.execute("SELECT quote(i) FROM t;") +box.execute("SELECT min(-1, i) FROM t;") +box.execute("SELECT quote(i) FROM t;") + +box.execute("CREATE INDEX i ON t(i);") +box.execute("SELECT i FROM t WHERE i = 18446744073709551613;") +box.execute("SELECT i FROM t WHERE i >= 18446744073709551613 ORDER BY i;") + +box.execute("UPDATE t SET i = 18446744073709551615 WHERE i = 18446744073709551613;") +box.execute("SELECT i FROM t;") + +-- Test constraints functionality. +-- +box.execute("CREATE TABLE parent (id INT PRIMARY KEY, a INT UNIQUE);") +box.execute("INSERT INTO parent VALUES (1, 18446744073709551613);") +box.space.T:truncate() +box.execute("ALTER TABLE t ADD CONSTRAINT fk1 FOREIGN KEY (i) REFERENCES parent (a);") +box.execute("INSERT INTO t VALUES (1, 18446744073709551615);") +box.execute("INSERT INTO parent VALUES (2, 18446744073709551615);") +box.execute("INSERT INTO t VALUES (1, 18446744073709551615);") +box.execute("ALTER TABLE t DROP CONSTRAINT fk1;") +box.space.PARENT:drop() +box.space.T:drop() + +box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a INT CHECK (a > 18446744073709551612));") +box.execute("INSERT INTO t1 VALUES (1, 18446744073709551611);") +box.execute("INSERT INTO t1 VALUES (1, -1);") +box.space.T1:drop() + +box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a INT DEFAULT 18446744073709551615);") +box.execute("INSERT INTO t1 (id) VALUES (1);") +box.space.T1:select() +box.space.T1:drop() + +-- Test that autoincrement accepts only max 2^63 - 1 . +-- +box.execute("CREATE TABLE t1 (id INT PRIMARY KEY AUTOINCREMENT);") +box.execute("INSERT INTO t1 VALUES (18446744073709551615);") +box.execute("INSERT INTO t1 VALUES (NULL);") +box.space.T1:drop() + +-- Test CAST facilities. +-- +box.execute("SELECT CAST(18446744073709551615 AS FLOAT);") +box.execute("SELECT CAST(18446744073709551615 AS TEXT);") +box.execute("SELECT CAST(18446744073709551615 AS SCALAR);") +box.execute("SELECT CAST(18446744073709551615 AS BOOLEAN);") +box.execute("SELECT CAST('18446744073709551615' AS INTEGER);") -- 2.15.1