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 E369A2AB31 for ; Sat, 30 Mar 2019 13:08:01 -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 YXaxGxJqAXeE for ; Sat, 30 Mar 2019 13:08:01 -0400 (EDT) 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 turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 1D5052AB2E for ; Sat, 30 Mar 2019 13:08:01 -0400 (EDT) Subject: [tarantool-patches] Re: [PATCH v1 1/1] sql: parameter binding for box.execute() References: <884040abe819e8ec11f7877d15e5482d6a24139b.1553947026.git.imeevma@gmail.com> From: Vladislav Shpilevoy Message-ID: Date: Sat, 30 Mar 2019 20:07:58 +0300 MIME-Version: 1.0 In-Reply-To: <884040abe819e8ec11f7877d15e5482d6a24139b.1553947026.git.imeevma@gmail.com> Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 7bit 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: imeevma@tarantool.org Cc: tarantool-patches@freelists.org Hi! Thanks for the patch! See my 4 comments below, review fixes at the bottom of the email, and force pushed onto the branch. On 30/03/2019 15:01, imeevma@tarantool.org wrote: > This patch defines parameters binding for SQL statements executed > through box.execute(). > > Closes #3401 > --- > https://github.com/tarantool/tarantool/issues/3401 > https://github.com/tarantool/tarantool/tree/imeevma/gh-3401-add-binding-to-box_execute > > src/box/execute.c | 166 +++++++++++++++++++++++++++++++ > src/box/execute.h | 19 ++++ > src/box/lua/init.c | 13 ++- > test/sql/binding.result | 246 ++++++++++++++++++++++++++++++++++++++++++++++ > test/sql/binding.test.lua | 45 +++++++++ > 5 files changed, 487 insertions(+), 2 deletions(-) > create mode 100644 test/sql/binding.result > create mode 100644 test/sql/binding.test.lua > > diff --git a/src/box/execute.c b/src/box/execute.c > index 6b5f9d7..f02b32d 100644 > --- a/src/box/execute.c > +++ b/src/box/execute.c > @@ -325,6 +326,171 @@ sql_bind_list_decode(const char *data, struct sql_bind **out_bind) > return bind_count; > } > > + > +/** 1. Redundant empty line. > + * Decode a single bind column from Lua stack. > + * > + * @param L Lua stack. > + * @param[out] bind Bind to decode to. > + * @param idx Position of table with bind columns on Lua stack. > + * @param i Ordinal bind number. > + * > + * @retval 0 Success. > + * @retval -1 Memory or client error. > + */ > +static inline int > +lua_sql_bind_decode(struct lua_State *L, struct sql_bind *bind, int idx, int i) > + > diff --git a/test/sql/binding.result b/test/sql/binding.result > new file mode 100644 > index 0000000..877d6b0 > --- /dev/null > +++ b/test/sql/binding.result 2. Usually when we test a method, we name a test file with the method's name. Here it is 'bind'. bind.test.lua. > @@ -0,0 +1,246 @@ > +remote = require('net.box') 3. I understand your idea of testing that remote and local binds works equally. But it is a naive and not elastic way of providing that functionality. Every single update of that test file will be duplicated inside it making both review and tests writing a pure torture. Instead of setting a default engine, which is never used here by the way, you could use configuration files to run that test twice with different background - remote and local requests. Also, I see that you wrote your own new tests, but why? sql/iproto.test.lua already contains all the possible tests on bind parameters. > +--- > +... > +test_run = require('test_run').new() > +--- > +... > +engine = test_run:get_cfg('engine') > +--- > +... > +box.execute('pragma sql_default_engine=\''..engine..'\'') > +--- > +- rowcount: 0 > +... > +box.schema.user.grant('guest','read, write, execute', 'universe') > +--- > +... > +box.schema.user.grant('guest', 'create', 'space') > +--- > +... > +cn = remote.connect(box.cfg.listen) > +--- > +... > +-- gh-3401: sql.exequte arg substitution (parameter binding) 4. 'exequte' -> 'execute'. ============================================================== commit 0f19b3c8983e87fbb6b1cc92c322eebecc688491 Author: Vladislav Shpilevoy Date: Sat Mar 30 19:29:32 2019 +0300 review fixes diff --git a/src/box/execute.c b/src/box/execute.c index f02b32da1..d5f9e33d1 100644 --- a/src/box/execute.c +++ b/src/box/execute.c @@ -326,7 +326,6 @@ sql_bind_list_decode(const char *data, struct sql_bind **out_bind) return bind_count; } - /** * Decode a single bind column from Lua stack. * diff --git a/src/box/lua/init.c b/src/box/lua/init.c index cda878f9c..486dc08ee 100644 --- a/src/box/lua/init.c +++ b/src/box/lua/init.c @@ -275,7 +275,7 @@ lbox_execute(struct lua_State *L) struct port port; int top = lua_gettop(L); - if (! (top == 1 || top == 2) || ! lua_isstring(L, 1)) + if ((top != 1 && top != 2) || ! lua_isstring(L, 1)) return luaL_error(L, "Usage: box.execute(sqlstring[, params])"); const char *sql = lua_tolstring(L, 1, &length); diff --git a/test/sql/bind.result b/test/sql/bind.result new file mode 100644 index 000000000..acf23a52e --- /dev/null +++ b/test/sql/bind.result @@ -0,0 +1,296 @@ +netbox = require('net.box') +--- +... +test_run = require('test_run').new() +--- +... +box.execute('CREATE TABLE test (id INT PRIMARY KEY, a FLOAT, b TEXT)') +--- +- rowcount: 1 +... +box.space.TEST:replace{1, 2, '3'} +--- +- [1, 2, '3'] +... +box.space.TEST:replace{7, 8.5, '9'} +--- +- [7, 8.5, '9'] +... +box.space.TEST:replace{10, 11, box.NULL} +--- +- [10, 11, null] +... +remote = test_run:get_cfg('remote') == 'true' +--- +... +execute = nil +--- +... +test_run:cmd("setopt delimiter ';'") +--- +- true +... +if remote then + box.schema.user.grant('guest','read, write, execute', 'universe') + box.schema.user.grant('guest', 'create', 'space') + cn = netbox.connect(box.cfg.listen) + execute = function(...) return cn:execute(...) end +else + execute = box.execute +end; +--- +... +test_run:cmd("setopt delimiter ''"); +--- +- true +... +-- +-- gh-3401: box.execute parameter binding. +-- +parameters = {} +--- +... +parameters[1] = {} +--- +... +parameters[1][':value'] = 1 +--- +... +execute('SELECT * FROM test WHERE id = :value', parameters) +--- +- metadata: + - name: ID + type: INTEGER + - name: A + type: NUMERIC + - name: B + type: TEXT + rows: + - [1, 2, '3'] +... +execute('SELECT ?, ?, ?', {1, 2, 3}) +--- +- metadata: + - name: '?' + type: INTEGER + - name: '?' + type: INTEGER + - name: '?' + type: INTEGER + rows: + - [1, 2, 3] +... +parameters = {} +--- +... +parameters[1] = 10 +--- +... +parameters[2] = {} +--- +... +parameters[2]['@value2'] = 12 +--- +... +parameters[3] = {} +--- +... +parameters[3][':value1'] = 11 +--- +... +execute('SELECT ?, :value1, @value2', parameters) +--- +- metadata: + - name: '?' + type: INTEGER + - name: :value1 + type: INTEGER + - name: '@value2' + type: INTEGER + rows: + - [10, 11, 12] +... +parameters = {} +--- +... +parameters[1] = {} +--- +... +parameters[1][':value3'] = 1 +--- +... +parameters[2] = 2 +--- +... +parameters[3] = {} +--- +... +parameters[3][':value1'] = 3 +--- +... +parameters[4] = 4 +--- +... +parameters[5] = 5 +--- +... +parameters[6] = {} +--- +... +parameters[6]['@value2'] = 6 +--- +... +execute('SELECT :value3, ?, :value1, ?, ?, @value2, ?, :value3', parameters) +--- +- metadata: + - name: :value3 + type: INTEGER + - name: '?' + type: INTEGER + - name: :value1 + type: INTEGER + - name: '?' + type: INTEGER + - name: '?' + type: INTEGER + - name: '@value2' + type: INTEGER + - name: '?' + type: BOOLEAN + - name: :value3 + type: INTEGER + rows: + - [1, 2, 3, 4, 5, 6, null, 1] +... +-- Try not-integer types. +msgpack = require('msgpack') +--- +... +execute('SELECT ?, ?, ?, ?, ?', {'abc', -123.456, msgpack.NULL, true, false}) +--- +- metadata: + - name: '?' + type: TEXT + - name: '?' + type: NUMERIC + - name: '?' + type: BOOLEAN + - name: '?' + type: INTEGER + - name: '?' + type: INTEGER + rows: + - ['abc', -123.456, null, 1, 0] +... +-- Try to replace '?' in meta with something meaningful. +execute('SELECT ? AS kek, ? AS kek2', {1, 2}) +--- +- metadata: + - name: KEK + type: INTEGER + - name: KEK2 + type: INTEGER + rows: + - [1, 2] +... +-- Try to bind not existing name. +parameters = {} +--- +... +parameters[1] = {} +--- +... +parameters[1]['name'] = 300 +--- +... +execute('SELECT ? AS kek', parameters) +--- +- error: Parameter 'name' was not found in the statement +... +-- Try too many parameters in a statement. +sql = 'SELECT '..string.rep('?, ', box.schema.SQL_BIND_PARAMETER_MAX)..'?' +--- +... +execute(sql) +--- +- error: 'SQL bind parameter limit reached: 65000' +... +-- Try too many parameter values. +sql = 'SELECT ?' +--- +... +parameters = {} +--- +... +for i = 1, box.schema.SQL_BIND_PARAMETER_MAX + 1 do parameters[i] = i end +--- +... +execute(sql, parameters) +--- +- error: 'SQL bind parameter limit reached: 65001' +... +-- +-- Errors during parameters binding. +-- +-- Try value > INT64_MAX. sql can't bind it, since it has no +-- 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 +... +-- Bind incorrect parameters. +ok, err = pcall(execute, 'SELECT ?', { {1, 2, 3} }) +--- +... +ok +--- +- false +... +parameters = {} +--- +... +parameters[1] = {} +--- +... +parameters[1][100] = 200 +--- +... +ok, err = pcall(execute, 'SELECT ?', parameters) +--- +... +ok +--- +- false +... +parameters = {} +--- +... +parameters[1] = {} +--- +... +parameters[1][':value'] = {kek = 300} +--- +... +execute('SELECT :value', parameters) +--- +- error: Bind value type MAP for parameter ':value' is not supported +... +test_run:cmd("setopt delimiter ';'") +--- +- true +... +if remote then + cn:close() + box.schema.user.revoke('guest', 'read, write, execute', 'universe') + box.schema.user.revoke('guest', 'create', 'space') +end; +--- +... +test_run:cmd("setopt delimiter ''"); +--- +- true +... +box.execute('DROP TABLE test') +--- +- rowcount: 1 +... diff --git a/test/sql/bind.test.lua b/test/sql/bind.test.lua new file mode 100644 index 000000000..229207d3a --- /dev/null +++ b/test/sql/bind.test.lua @@ -0,0 +1,100 @@ +netbox = require('net.box') +test_run = require('test_run').new() + +box.execute('CREATE TABLE test (id INT PRIMARY KEY, a FLOAT, b TEXT)') +box.space.TEST:replace{1, 2, '3'} +box.space.TEST:replace{7, 8.5, '9'} +box.space.TEST:replace{10, 11, box.NULL} + +remote = test_run:get_cfg('remote') == 'true' +execute = nil +test_run:cmd("setopt delimiter ';'") +if remote then + box.schema.user.grant('guest','read, write, execute', 'universe') + box.schema.user.grant('guest', 'create', 'space') + cn = netbox.connect(box.cfg.listen) + execute = function(...) return cn:execute(...) end +else + execute = box.execute +end; +test_run:cmd("setopt delimiter ''"); +-- +-- gh-3401: box.execute parameter binding. +-- +parameters = {} +parameters[1] = {} +parameters[1][':value'] = 1 +execute('SELECT * FROM test WHERE id = :value', parameters) +execute('SELECT ?, ?, ?', {1, 2, 3}) +parameters = {} +parameters[1] = 10 +parameters[2] = {} +parameters[2]['@value2'] = 12 +parameters[3] = {} +parameters[3][':value1'] = 11 +execute('SELECT ?, :value1, @value2', parameters) + +parameters = {} +parameters[1] = {} +parameters[1][':value3'] = 1 +parameters[2] = 2 +parameters[3] = {} +parameters[3][':value1'] = 3 +parameters[4] = 4 +parameters[5] = 5 +parameters[6] = {} +parameters[6]['@value2'] = 6 +execute('SELECT :value3, ?, :value1, ?, ?, @value2, ?, :value3', parameters) + +-- Try not-integer types. +msgpack = require('msgpack') +execute('SELECT ?, ?, ?, ?, ?', {'abc', -123.456, msgpack.NULL, true, false}) + +-- Try to replace '?' in meta with something meaningful. +execute('SELECT ? AS kek, ? AS kek2', {1, 2}) + +-- Try to bind not existing name. +parameters = {} +parameters[1] = {} +parameters[1]['name'] = 300 +execute('SELECT ? AS kek', parameters) + +-- Try too many parameters in a statement. +sql = 'SELECT '..string.rep('?, ', box.schema.SQL_BIND_PARAMETER_MAX)..'?' +execute(sql) + +-- Try too many parameter values. +sql = 'SELECT ?' +parameters = {} +for i = 1, box.schema.SQL_BIND_PARAMETER_MAX + 1 do parameters[i] = i end +execute(sql, parameters) + +-- +-- Errors during parameters binding. +-- +-- Try value > INT64_MAX. sql can't bind it, since it has no +-- suitable method in its bind API. +execute('SELECT ? AS big_uint', {0xefffffffffffffff}) +-- Bind incorrect parameters. +ok, err = pcall(execute, 'SELECT ?', { {1, 2, 3} }) +ok +parameters = {} +parameters[1] = {} +parameters[1][100] = 200 +ok, err = pcall(execute, 'SELECT ?', parameters) +ok + +parameters = {} +parameters[1] = {} +parameters[1][':value'] = {kek = 300} +execute('SELECT :value', parameters) + +test_run:cmd("setopt delimiter ';'") +if remote then + cn:close() + box.schema.user.revoke('guest', 'read, write, execute', 'universe') + box.schema.user.revoke('guest', 'create', 'space') +end; +test_run:cmd("setopt delimiter ''"); + +box.execute('DROP TABLE test') diff --git a/test/sql/binding.result b/test/sql/binding.result deleted file mode 100644 index 877d6b00b..000000000 --- a/test/sql/binding.result +++ /dev/null @@ -1,246 +0,0 @@ -remote = require('net.box') ---- -... -test_run = require('test_run').new() ---- -... -engine = test_run:get_cfg('engine') ---- -... -box.execute('pragma sql_default_engine=\''..engine..'\'') ---- -- rowcount: 0 -... -box.schema.user.grant('guest','read, write, execute', 'universe') ---- -... -box.schema.user.grant('guest', 'create', 'space') ---- -... -cn = remote.connect(box.cfg.listen) ---- -... --- gh-3401: sql.exequte arg substitution (parameter binding) -binding_values = {} ---- -... -binding_values[1] = 123 ---- -... -binding_values[2] = {} ---- -... -binding_values[2]['@value2'] = 45 ---- -... -binding_values[3] = {} ---- -... -binding_values[3][':value1'] = 67 ---- -... -box.execute('SELECT ?, ?, ?', {111, 22, 3}) ---- -- metadata: - - name: '?' - type: INTEGER - - name: '?' - type: INTEGER - - name: '?' - type: INTEGER - rows: - - [111, 22, 3] -... -box.execute('SELECT $1, $1, ?, $1, ?, $3, $2', {111, 22, 3}) ---- -- metadata: - - name: $1 - type: INTEGER - - name: $1 - type: INTEGER - - name: '?' - type: INTEGER - - name: $1 - type: INTEGER - - name: '?' - type: BOOLEAN - - name: $3 - type: BOOLEAN - - name: $2 - type: BOOLEAN - rows: - - [111, 111, 22, 111, 3, 3, 22] -... -box.execute('SELECT $3, ?', {111, 22, 3}) ---- -- metadata: - - name: $3 - type: INTEGER - - name: '?' - type: INTEGER - rows: - - [3, null] -... -box.execute('SELECT :value1, @value2', binding_values) ---- -- metadata: - - name: :value1 - type: INTEGER - - name: '@value2' - type: INTEGER - rows: - - [67, 45] -... -box.execute('SELECT ?, $1, :value1, @value2', binding_values) ---- -- metadata: - - name: '?' - type: INTEGER - - name: $1 - type: INTEGER - - name: :value1 - type: INTEGER - - name: '@value2' - type: BOOLEAN - rows: - - [123, 123, 67, 45] -... -box.execute('SELECT $1, ?, $3, :value1, @value2', binding_values) ---- -- metadata: - - name: $1 - type: INTEGER - - name: '?' - type: BOOLEAN - - name: $3 - type: BOOLEAN - - name: :value1 - type: INTEGER - - name: '@value2' - type: INTEGER - rows: - - [123, null, null, 67, 45] -... -box.execute('SELECT ?', {111, 22, 3}) ---- -- error: Bind value for parameter 2 is out of range for type INTEGER -... -box.execute('SELECT $1', {111, 22, 3}) ---- -- error: Bind value for parameter 2 is out of range for type INTEGER -... -box.execute('SELECT $1, $2, $1', {111, 22, 3}) ---- -- error: Bind value for parameter 3 is out of range for type INTEGER -... -box.execute('SELECT @value2', binding_values) ---- -- error: Parameter ':value1' was not found in the statement -... -cn:execute('SELECT ?, ?, ?', {111, 22, 3}) ---- -- metadata: - - name: '?' - type: INTEGER - - name: '?' - type: INTEGER - - name: '?' - type: INTEGER - rows: - - [111, 22, 3] -... -cn:execute('SELECT $1, $1, ?, $1, ?, $3, $2', {111, 22, 3}) ---- -- metadata: - - name: $1 - type: INTEGER - - name: $1 - type: INTEGER - - name: '?' - type: INTEGER - - name: $1 - type: INTEGER - - name: '?' - type: BOOLEAN - - name: $3 - type: BOOLEAN - - name: $2 - type: BOOLEAN - rows: - - [111, 111, 22, 111, 3, 3, 22] -... -cn:execute('SELECT $3, ?', {111, 22, 3}) ---- -- metadata: - - name: $3 - type: INTEGER - - name: '?' - type: INTEGER - rows: - - [3, null] -... -cn:execute('SELECT :value1, @value2', binding_values) ---- -- metadata: - - name: :value1 - type: INTEGER - - name: '@value2' - type: INTEGER - rows: - - [67, 45] -... -cn:execute('SELECT ?, $1, :value1, @value2', binding_values) ---- -- metadata: - - name: '?' - type: INTEGER - - name: $1 - type: INTEGER - - name: :value1 - type: INTEGER - - name: '@value2' - type: BOOLEAN - rows: - - [123, 123, 67, 45] -... -cn:execute('SELECT $1, ?, $3, :value1, @value2', binding_values) ---- -- metadata: - - name: $1 - type: INTEGER - - name: '?' - type: BOOLEAN - - name: $3 - type: BOOLEAN - - name: :value1 - type: INTEGER - - name: '@value2' - type: INTEGER - rows: - - [123, null, null, 67, 45] -... -cn:execute('SELECT ?', {111, 22, 3}) ---- -- error: Bind value for parameter 2 is out of range for type INTEGER -... -cn:execute('SELECT $1', {111, 22, 3}) ---- -- error: Bind value for parameter 2 is out of range for type INTEGER -... -cn:execute('SELECT $1, $2, $1', {111, 22, 3}) ---- -- error: Bind value for parameter 3 is out of range for type INTEGER -... -cn:execute('SELECT @value2', binding_values) ---- -- error: Parameter ':value1' was not found in the statement -... -cn:close() ---- -... -box.schema.user.revoke('guest', 'read, write, execute', 'universe') ---- -... -box.schema.user.revoke('guest', 'create', 'space') ---- -... diff --git a/test/sql/binding.test.lua b/test/sql/binding.test.lua deleted file mode 100644 index 577b12f42..000000000 --- a/test/sql/binding.test.lua +++ /dev/null @@ -1,45 +0,0 @@ -remote = require('net.box') -test_run = require('test_run').new() -engine = test_run:get_cfg('engine') -box.execute('pragma sql_default_engine=\''..engine..'\'') - -box.schema.user.grant('guest','read, write, execute', 'universe') -box.schema.user.grant('guest', 'create', 'space') -cn = remote.connect(box.cfg.listen) - --- gh-3401: sql.exequte arg substitution (parameter binding) -binding_values = {} -binding_values[1] = 123 -binding_values[2] = {} -binding_values[2]['@value2'] = 45 -binding_values[3] = {} -binding_values[3][':value1'] = 67 - -box.execute('SELECT ?, ?, ?', {111, 22, 3}) -box.execute('SELECT $1, $1, ?, $1, ?, $3, $2', {111, 22, 3}) -box.execute('SELECT $3, ?', {111, 22, 3}) -box.execute('SELECT :value1, @value2', binding_values) -box.execute('SELECT ?, $1, :value1, @value2', binding_values) -box.execute('SELECT $1, ?, $3, :value1, @value2', binding_values) - -box.execute('SELECT ?', {111, 22, 3}) -box.execute('SELECT $1', {111, 22, 3}) -box.execute('SELECT $1, $2, $1', {111, 22, 3}) -box.execute('SELECT @value2', binding_values) - -cn:execute('SELECT ?, ?, ?', {111, 22, 3}) -cn:execute('SELECT $1, $1, ?, $1, ?, $3, $2', {111, 22, 3}) -cn:execute('SELECT $3, ?', {111, 22, 3}) -cn:execute('SELECT :value1, @value2', binding_values) -cn:execute('SELECT ?, $1, :value1, @value2', binding_values) -cn:execute('SELECT $1, ?, $3, :value1, @value2', binding_values) - -cn:execute('SELECT ?', {111, 22, 3}) -cn:execute('SELECT $1', {111, 22, 3}) -cn:execute('SELECT $1, $2, $1', {111, 22, 3}) -cn:execute('SELECT @value2', binding_values) - -cn:close() - -box.schema.user.revoke('guest', 'read, write, execute', 'universe') -box.schema.user.revoke('guest', 'create', 'space') diff --git a/test/sql/engine.cfg b/test/sql/engine.cfg index 5ac445108..284c42082 100644 --- a/test/sql/engine.cfg +++ b/test/sql/engine.cfg @@ -5,6 +5,10 @@ "sql-debug.test.lua": { "memtx": {"engine": "memtx"} }, + "bind.test.lua": { + "remote": {"remote": "true"}, + "local": {"remote": "false"} + }, "*": { "memtx": {"engine": "memtx"}, "vinyl": {"engine": "vinyl"} diff --git a/test/sql/iproto.result b/test/sql/iproto.result index 18e305276..112225bb6 100644 --- a/test/sql/iproto.result +++ b/test/sql/iproto.result @@ -198,231 +198,6 @@ cn:execute('select * from test limit 1 offset ?', {'Hello'}) --- - error: Only positive integers are allowed in the OFFSET clause ... --- --- Parameters binding. --- -parameters = {} ---- -... -parameters[1] = {} ---- -... -parameters[1][':value'] = 1 ---- -... -cn:execute('select * from test where id = :value', parameters) ---- -- metadata: - - name: ID - type: INTEGER - - name: A - type: NUMERIC - - name: B - type: TEXT - rows: - - [1, 2, '3'] -... -cn:execute('select ?, ?, ?', {1, 2, 3}) ---- -- metadata: - - name: '?' - type: INTEGER - - name: '?' - type: INTEGER - - name: '?' - type: INTEGER - rows: - - [1, 2, 3] -... -parameters = {} ---- -... -parameters[1] = 10 ---- -... -parameters[2] = {} ---- -... -parameters[2]['@value2'] = 12 ---- -... -parameters[3] = {} ---- -... -parameters[3][':value1'] = 11 ---- -... -cn:execute('select ?, :value1, @value2', parameters) ---- -- metadata: - - name: '?' - type: INTEGER - - name: :value1 - type: INTEGER - - name: '@value2' - type: INTEGER - rows: - - [10, 11, 12] -... -parameters = {} ---- -... -parameters[1] = {} ---- -... -parameters[1][':value3'] = 1 ---- -... -parameters[2] = 2 ---- -... -parameters[3] = {} ---- -... -parameters[3][':value1'] = 3 ---- -... -parameters[4] = 4 ---- -... -parameters[5] = 5 ---- -... -parameters[6] = {} ---- -... -parameters[6]['@value2'] = 6 ---- -... -cn:execute('select :value3, ?, :value1, ?, ?, @value2, ?, :value3', parameters) ---- -- metadata: - - name: :value3 - type: INTEGER - - name: '?' - type: INTEGER - - name: :value1 - type: INTEGER - - name: '?' - type: INTEGER - - name: '?' - type: INTEGER - - name: '@value2' - type: INTEGER - - name: '?' - type: BOOLEAN - - name: :value3 - type: INTEGER - rows: - - [1, 2, 3, 4, 5, 6, null, 1] -... --- Try not-integer types. -msgpack = require('msgpack') ---- -... -cn:execute('select ?, ?, ?, ?, ?', {'abc', -123.456, msgpack.NULL, true, false}) ---- -- metadata: - - name: '?' - type: TEXT - - name: '?' - type: NUMERIC - - name: '?' - type: BOOLEAN - - name: '?' - type: INTEGER - - name: '?' - type: INTEGER - rows: - - ['abc', -123.456, null, 1, 0] -... --- Try to replace '?' in meta with something meaningful. -cn:execute('select ? as kek, ? as kek2', {1, 2}) ---- -- metadata: - - name: KEK - type: INTEGER - - name: KEK2 - type: INTEGER - rows: - - [1, 2] -... --- Try to bind not existing name. -parameters = {} ---- -... -parameters[1] = {} ---- -... -parameters[1]['name'] = 300 ---- -... -cn:execute('select ? as kek', parameters) ---- -- error: Parameter 'name' was not found in the statement -... --- Try too many parameters in a statement. -sql = 'select '..string.rep('?, ', box.schema.SQL_BIND_PARAMETER_MAX)..'?' ---- -... -cn:execute(sql) ---- -- error: 'SQL bind parameter limit reached: 65000' -... --- Try too many parameter values. -sql = 'select ?' ---- -... -parameters = {} ---- -... -for i = 1, box.schema.SQL_BIND_PARAMETER_MAX + 1 do parameters[i] = i end ---- -... -cn:execute(sql, parameters) ---- -- error: 'SQL bind parameter limit reached: 65001' -... --- --- Errors during parameters binding. --- --- Try value > INT64_MAX. sql can't bind it, since it has no --- suitable method in its bind API. -cn:execute('select ? as big_uint', {0xefffffffffffffff}) ---- -- error: Bind value for parameter 1 is out of range for type INTEGER -... --- Bind incorrect parameters. -cn:execute('select ?', { {1, 2, 3} }) ---- -- error: Bind value type ARRAY for parameter 1 is not supported -... -parameters = {} ---- -... -parameters[1] = {} ---- -... -parameters[1][100] = 200 ---- -... -cn:execute('select ?', parameters) ---- -- error: Invalid MsgPack - SQL bind parameter -... -parameters = {} ---- -... -parameters[1] = {} ---- -... -parameters[1][':value'] = {kek = 300} ---- -... -cn:execute('select :value', parameters) ---- -- error: Bind value type MAP for parameter ':value' is not supported -... -- gh-2608 SQL iproto DDL cn:execute('create table test2(id int primary key, a int, b int, c int)') --- diff --git a/test/sql/iproto.test.lua b/test/sql/iproto.test.lua index 6a6c24ad9..fc27ca620 100644 --- a/test/sql/iproto.test.lua +++ b/test/sql/iproto.test.lua @@ -58,75 +58,6 @@ cn:execute('select * from test limit 1 offset ?', {-2}) cn:execute('select * from test limit 1 offset ?', {2.7}) cn:execute('select * from test limit 1 offset ?', {'Hello'}) --- --- Parameters binding. --- -parameters = {} -parameters[1] = {} -parameters[1][':value'] = 1 -cn:execute('select * from test where id = :value', parameters) -cn:execute('select ?, ?, ?', {1, 2, 3}) -parameters = {} -parameters[1] = 10 -parameters[2] = {} -parameters[2]['@value2'] = 12 -parameters[3] = {} -parameters[3][':value1'] = 11 -cn:execute('select ?, :value1, @value2', parameters) - -parameters = {} -parameters[1] = {} -parameters[1][':value3'] = 1 -parameters[2] = 2 -parameters[3] = {} -parameters[3][':value1'] = 3 -parameters[4] = 4 -parameters[5] = 5 -parameters[6] = {} -parameters[6]['@value2'] = 6 -cn:execute('select :value3, ?, :value1, ?, ?, @value2, ?, :value3', parameters) - --- Try not-integer types. -msgpack = require('msgpack') -cn:execute('select ?, ?, ?, ?, ?', {'abc', -123.456, msgpack.NULL, true, false}) - --- Try to replace '?' in meta with something meaningful. -cn:execute('select ? as kek, ? as kek2', {1, 2}) - --- Try to bind not existing name. -parameters = {} -parameters[1] = {} -parameters[1]['name'] = 300 -cn:execute('select ? as kek', parameters) - --- Try too many parameters in a statement. -sql = 'select '..string.rep('?, ', box.schema.SQL_BIND_PARAMETER_MAX)..'?' -cn:execute(sql) - --- Try too many parameter values. -sql = 'select ?' -parameters = {} -for i = 1, box.schema.SQL_BIND_PARAMETER_MAX + 1 do parameters[i] = i end -cn:execute(sql, parameters) - --- --- Errors during parameters binding. --- --- Try value > INT64_MAX. sql can't bind it, since it has no --- suitable method in its bind API. -cn:execute('select ? as big_uint', {0xefffffffffffffff}) --- Bind incorrect parameters. -cn:execute('select ?', { {1, 2, 3} }) -parameters = {} -parameters[1] = {} -parameters[1][100] = 200 -cn:execute('select ?', parameters) - -parameters = {} -parameters[1] = {} -parameters[1][':value'] = {kek = 300} -cn:execute('select :value', parameters) - -- gh-2608 SQL iproto DDL cn:execute('create table test2(id int primary key, a int, b int, c int)') box.space.TEST2.name