iproto.result

remote = require('net.box')
---
...
test_run = require('test_run').new()
---
...
engine = test_run:get_cfg('engine')
---
...
box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
---
...
box.sql.execute('create table test (id int primary key, a float, b text)')
---
...
space = box.space.TEST
---
...
space:replace{1, 2, '3'}
---
- [1, 2, '3']
...
space:replace{4, 5, '6'}
---
- [4, 5, '6']
...
space:replace{7, 8.5, '9'}
---
- [7, 8.5, '9']
...
box.sql.execute('select * from test')
---
- - [1, 2, '3']
  - [4, 5, '6']
  - [7, 8.5, '9']
...
box.schema.user.grant('guest','read,write,execute', 'universe')
---
...
box.schema.user.grant('guest', 'create', 'space')
---
...
cn = remote.connect(box.cfg.listen)
---
...
cn:ping()
---
- true
...
--
-- Static queries, with no parameters.
--
-- Simple select.
ret = cn:execute('select * from test')
---
...
ret
---
- metadata:
  - name: ID
    type: INTEGER
  - name: A
    type: NUMERIC
  - name: B
    type: TEXT
  rows:
  - [1, 2, '3']
  - [4, 5, '6']
  - [7, 8.5, '9']
...
type(ret.rows[1])
---
- cdata
...
-- Operation with rowcount result.
cn:execute('insert into test values (10, 11, NULL)')
---
- rowcount: 1
...
cn:execute('delete from test where a = 5')
---
- rowcount: 1
...
cn:execute('insert into test values (11, 12, NULL), (12, 12, NULL), (13, 12, NULL)')
---
- rowcount: 3
...
cn:execute('delete from test where a = 12')
---
- rowcount: 3
...
-- SQL errors.
cn:execute('insert into not_existing_table values ("kek")')
---
- error: 'Failed to execute SQL statement: no such table: NOT_EXISTING_TABLE'
...
cn:execute('insert qwerty gjsdjq  q  qwd qmq;; q;qwd;')
---
- error: 'Failed to execute SQL statement: near "qwerty": syntax error'
...
-- Empty result.
cn:execute('select id as identifier from test where a = 5;')
---
- metadata:
  - name: IDENTIFIER
    type: INTEGER
  rows: []
...
-- netbox API errors.
cn:execute(100)
---
- error: 'Failed to execute SQL statement: near "100": syntax error'
...
cn:execute('select 1', nil, {dry_run = true})
---
- error: execute does not support options
...
-- Empty request.
cn:execute('')
---
- error: 'Failed to execute SQL statement: syntax error: empty request'
...
cn:execute('   ;')
---
- error: 'Failed to execute SQL statement: syntax error: empty request'
...
--
-- Parmaeters bindig.
--
cn:execute('select * from test where id = ?', {1})
---
- metadata:
  - name: ID
    type: INTEGER
  - name: A
    type: NUMERIC
  - name: B
    type: TEXT
  rows:
  - [1, 2, '3']
...
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: 'Failed to execute SQL statement: too many SQL variables'
...
-- 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. SQLite 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)')
---
- rowcount: 1
...
box.space.TEST2.name
---
- TEST2
...
cn:execute('insert into test2 values (1, 1, 1, 1)')
---
- rowcount: 1
...
cn:execute('select * from test2')
---
- metadata:
  - name: ID
    type: INTEGER
  - name: A
    type: INTEGER
  - name: B
    type: INTEGER
  - name: C
    type: INTEGER
  rows:
  - [1, 1, 1, 1]
...
cn:execute('create index test2_a_b_index on test2(a, b)')
---
- rowcount: 1
...
#box.space.TEST2.index
---
- 1
...
cn:execute('drop table test2')
---
- rowcount: 1
...
box.space.TEST2
---
- null
...
-- gh-2617 DDL row_count either 0 or 1.
-- Test CREATE [IF NOT EXISTS] TABLE.
cn:execute('create table test3(id int primary key, a int, b int)')
---
- rowcount: 1
...
-- Rowcount = 1, although two tuples were created:
-- for _space and for _index.
cn:execute('insert into test3 values (1, 1, 1), (2, 2, 2), (3, 3, 3)')
---
- rowcount: 3
...
cn:execute('create table if not exists test3(id int primary key)')
---
- rowcount: 0
...
-- Test CREATE VIEW [IF NOT EXISTS] and
--      DROP   VIEW [IF EXISTS].
cn:execute('create view test3_view(id) as select id from test3')
---
- rowcount: 1
...
cn:execute('create view if not exists test3_view(id) as select id from test3')
---
- rowcount: 0
...
cn:execute('drop view test3_view')
---
- rowcount: 1
...
cn:execute('drop view if exists test3_view')
---
- rowcount: 0
...
-- Test CREATE INDEX [IF NOT EXISTS] and
--      DROP   INDEX [IF EXISTS].
cn:execute('create index test3_sec on test3(a, b)')
---
- rowcount: 1
...
cn:execute('create index if not exists test3_sec on test3(a, b)')
---
- rowcount: 0
...
cn:execute('drop index test3_sec on test3')
---
- rowcount: 1
...
cn:execute('drop index if exists test3_sec on test3')
---
- rowcount: 0
...
-- Test CREATE TRIGGER [IF NOT EXISTS] and
--      DROP   TRIGGER [IF EXISTS].
cn:execute('create trigger trig INSERT ON test3 BEGIN SELECT * FROM test3; END;')
---
- rowcount: 1
...
cn:execute('create trigger if not exists trig INSERT ON test3 BEGIN SELECT * FROM test3; END;')
---
- rowcount: 0
...
cn:execute('drop trigger trig')
---
- rowcount: 1
...
cn:execute('drop trigger if exists trig')
---
- rowcount: 0
...
-- Test DROP TABLE [IF EXISTS].
-- Create more indexes, triggers and _truncate tuple.
cn:execute('create index idx1 on test3(a)')
---
- rowcount: 1
...
cn:execute('create index idx2 on test3(b)')
---
- rowcount: 1
...
box.space.TEST3:truncate()
---
...
cn:execute('create trigger trig INSERT ON test3 BEGIN SELECT * FROM test3; END;')
---
- rowcount: 1
...
cn:execute('insert into test3 values (1, 1, 1), (2, 2, 2), (3, 3, 3)')
---
- rowcount: 3
...
cn:execute('drop table test3')
---
- rowcount: 1
...
cn:execute('drop table if exists test3')
---
- rowcount: 0
...
--
-- gh-2948: sql: remove unnecessary templates for binding
-- parameters.
--
cn:execute('select ?1, ?2, ?3', {1, 2, 3})
---
- error: 'Failed to execute SQL statement: near "?1": syntax error'
...
cn:execute('select $name, $name2', {1, 2})
---
- error: 'Failed to execute SQL statement: variable number must be between $1 and
    $65000'
...
parameters = {}
---
...
parameters[1] = 11
---
...
parameters[2] = 22
---
...
parameters[3] = 33
---
...
cn:execute('select $2, $1, $3', parameters)
---
- metadata:
  - name: $2
    type: INTEGER
  - name: $1
    type: INTEGER
  - name: $3
    type: INTEGER
  rows:
  - [22, 11, 33]
...
cn:execute('select * from test where id = :1', {1})
---
- metadata:
  - name: ID
    type: INTEGER
  - name: A
    type: NUMERIC
  - name: B
    type: TEXT
  rows:
  - [1, 2, '3']
...
-- gh-2602 obuf_alloc breaks the tuple in different slabs
_ = space:replace{1, 1, string.rep('a', 4 * 1024 * 1024)}
---
...
res = cn:execute('select * from test')
---
...
res.metadata
---
- - name: ID
    type: INTEGER
  - name: A
    type: NUMERIC
  - name: B
    type: TEXT
...
box.sql.execute('drop table test')
---
...
cn:close()
---
...
--
-- gh-3107: async netbox.
--
cn = remote.connect(box.cfg.listen)
---
...
cn:execute('create table test (id integer primary key, a integer, b integer)')
---
- rowcount: 1
...
future1 = cn:execute('insert into test values (1, 1, 1)', nil, nil, {is_async = true})
---
...
future2 = cn:execute('insert into test values (1, 2, 2)', nil, nil, {is_async = true})
---
...
future3 = cn:execute('insert into test values (2, 2, 2), (3, 3, 3)', nil, nil, {is_async = true})
---
...
future1:wait_result()
---
- rowcount: 1
...
future2:wait_result()
---
- null
- 'Failed to execute SQL statement: Duplicate key exists in unique index ''pk_unnamed_TEST_1''
  in space ''TEST'''
...
future3:wait_result()
---
- rowcount: 2
...
future4 = cn:execute('select * from test', nil, nil, {is_async = true})
---
...
future4:wait_result()
---
- metadata:
  - name: ID
    type: INTEGER
  - name: A
    type: INTEGER
  - name: B
    type: INTEGER
  rows:
  - [1, 1, 1]
  - [2, 2, 2]
  - [3, 3, 3]
...
cn:close()
---
...
box.sql.execute('drop table test')
---
...
-- gh-2618 Return generated columns after INSERT in IPROTO.
-- Return all ids generated in current INSERT statement.
box.sql.execute('create table test (id integer primary key autoincrement, a integer)')
---
...
cn = remote.connect(box.cfg.listen)
---
...
cn:execute('insert into test values (1, 1)')
---
- rowcount: 1
...
cn:execute('insert into test values (null, 2)')
---
- autoincrement_ids:
  - 2
  rowcount: 1
...
cn:execute('update test set a = 11 where id == 1')
---
- rowcount: 1
...
cn:execute('insert into test values (100, 1), (null, 1), (120, 1), (null, 1)')
---
- autoincrement_ids:
  - 101
  - 121
  rowcount: 4
...
cn:execute('insert into test values (null, 1), (null, 1), (null, 1), (null, 1), (null, 1)')
---
- autoincrement_ids:
  - 122
  - 123
  - 124
  - 125
  - 126
  rowcount: 5
...
cn:execute('select * from test')
---
- metadata:
  - name: ID
    type: INTEGER
  - name: A
    type: INTEGER
  rows:
  - [1, 11]
  - [2, 2]
  - [100, 1]
  - [101, 1]
  - [120, 1]
  - [121, 1]
  - [122, 1]
  - [123, 1]
  - [124, 1]
  - [125, 1]
  - [126, 1]
...
s = box.schema.create_space('test2', {engine = engine})
---
...
sq = box.schema.sequence.create('test2')
---
...
pk = s:create_index('pk', {sequence = 'test2'})
---
...
function push_id() s:replace{box.NULL} s:replace{box.NULL} end
---
...
_ = box.space.TEST:on_replace(push_id)
---
...
cn:execute('insert into test values (null, 1)')
---
- autoincrement_ids:
  - 127
  - 1
  - 2
  rowcount: 1
...
box.sql.execute('create table test3 (id int primary key autoincrement)')
---
...
box.schema.sequence.alter('TEST3', {min=-10000, step=-10})
---
...
cn:execute('insert into TEST3 values (null), (null), (null), (null)')
---
- autoincrement_ids:
  - 1
  - -9
  - -19
  - -29
  rowcount: 4
...
box.sql.execute('drop table test')
---
...
s:drop()
---
...
sq:drop()
---
...
box.sql.execute('drop table test3')
---
...
--
-- Ensure that FK inside CREATE TABLE does not affect rowcount.
--
cn:execute('create table test (id integer primary key, a integer)')
---
- rowcount: 1
...
cn:execute('create table test2 (id integer primary key, ref integer references test(id))')
---
- rowcount: 1
...
cn:execute('drop table test2')
---
- rowcount: 1
...
--
-- Ensure that REPLACE is accounted twice in rowcount. As delete +
-- insert.
--
cn:execute('insert into test values(1, 1)')
---
- rowcount: 1
...
cn:execute('insert or replace into test values(1, 2)')
---
- rowcount: 2
...
cn:execute('drop table test')
---
- rowcount: 1
...
cn:close()
---
...
--
-- Some commands, e.g. EXPLAIN, could lead to segfault because it
-- does not send column data type, but looks like DQL. Netbox had
-- been trying to decode type for any DQL.
--
cn = remote.connect(box.cfg.listen)
---
...
-- Segmentation fault when EXPLAIN executed using net.box.
_ = cn:execute("EXPLAIN SELECT 1;")
---
...
-- Segmentation fault when PRAGMA TABLE_INFO() executed using
-- net.box.
box.sql.execute('CREATE TABLE test (id INT PRIMARY KEY)')
---
...
cn:execute("PRAGMA TABLE_INFO(test);")
---
- metadata:
  - name: cid
    type: UNKNOWN
  - name: name
    type: UNKNOWN
  - name: type
    type: UNKNOWN
  - name: notnull
    type: UNKNOWN
  - name: dflt_value
    type: UNKNOWN
  - name: pk
    type: UNKNOWN
  rows:
  - [0, 'ID', 'integer', 1, null, 1]
...
box.sql.execute('DROP TABLE test')
---
...
-- SELECT returns unpacked msgpack.
format = {{name = 'id', type = 'integer'}, {name = 'x', type = 'any'}}
---
...
s = box.schema.space.create('test', {format=format})
---
...
i1 = s:create_index('i1', {parts = {1, 'int'}})
---
...
s:insert({1, {1,2,3}})
---
- [1, [1, 2, 3]]
...
s:insert({2, {a = 3}})
---
- [2, {'a': 3}]
...
cn:execute('select * from "test"')
---
- metadata:
  - name: id
    type: UNKNOWN
  - name: x
    type: UNKNOWN
  rows:
  - [1, [1, 2, 3]]
  - [2, {'a': 3}]
...
s:drop()
---
...
-- Too many autogenerated ids leads to SEGFAULT.
cn = remote.connect(box.cfg.listen)
---
...
box.sql.execute('CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT)')
---
...
for i = 0, 1000 do cn:execute("INSERT INTO t1 VALUES (null)") end
---
...
_ = cn:execute("INSERT INTO t1 SELECT NULL from t1")
---
...
box.sql.execute('DROP TABLE t1')
---
...
cn:close()
---
...
box.schema.user.revoke('guest', 'read,write,execute', 'universe')
---
...
box.schema.user.revoke('guest', 'create', 'space')
---
...
space = nil
---
...
-- Cleanup xlog
box.snapshot()
---
- ok
...