remote = require('net.box')
---
...
test_run = require('test_run').new()
---
...
engine = test_run:get_cfg('engine')
---
...
_ = box.space._session_settings:update('sql_default_engine', {{'=', 2, engine}})
---
...
box.execute('create table test (id int primary key, a NUMBER, b text)')
---
- row_count: 1
...
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.execute('select * from test')
---
- metadata:
  - name: ID
    type: integer
  - name: A
    type: number
  - name: B
    type: string
  rows:
  - [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: number
  - name: B
    type: string
  rows:
  - [1, 2, '3']
  - [4, 5, '6']
  - [7, 8.5, '9']
...
type(ret.rows[1])
---
- cdata
...
-- Operation with row_count result.
cn:execute('insert into test values (10, 11, NULL)')
---
- row_count: 1
...
cn:execute('delete from test where a = 5')
---
- row_count: 1
...
cn:execute('insert into test values (11, 12, NULL), (12, 12, NULL), (13, 12, NULL)')
---
- row_count: 3
...
cn:execute('delete from test where a = 12')
---
- row_count: 3
...
-- SQL errors.
cn:execute('insert into not_existing_table values ("kek")')
---
- error: Space 'NOT_EXISTING_TABLE' does not exist
...
cn:execute('insert qwerty gjsdjq  q  qwd qmq;; q;qwd;')
---
- error: Syntax error at line 1 near 'qwerty'
...
-- 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: Prepared statement with id 100 does not exist
...
cn:execute('select 1', nil, {dry_run = true})
---
- error: execute does not support options
...
-- Empty request.
cn:execute('')
---
- error: Failed to execute an empty SQL statement
...
cn:execute('   ;')
---
- error: Failed to execute an empty SQL statement
...
--
-- gh-3467: allow only positive integers under limit clause.
--
cn:execute('select * from test where id = ?', {1})
---
- metadata:
  - name: ID
    type: integer
  - name: A
    type: number
  - name: B
    type: string
  rows:
  - [1, 2, '3']
...
cn:execute('select * from test limit ?', {2})
---
- metadata:
  - name: ID
    type: integer
  - name: A
    type: number
  - name: B
    type: string
  rows:
  - [1, 2, '3']
  - [7, 8.5, '9']
...
cn:execute('select * from test limit ?', {-2})
---
- error: 'Failed to execute SQL statement: Only positive integers are allowed in the
    LIMIT clause'
...
cn:execute('select * from test limit ?', {2.7})
---
- error: 'Failed to execute SQL statement: Only positive integers are allowed in the
    LIMIT clause'
...
cn:execute('select * from test limit ?', {'Hello'})
---
- error: 'Failed to execute SQL statement: Only positive integers are allowed in the
    LIMIT clause'
...
cn:execute('select * from test limit 1 offset ?', {2})
---
- metadata:
  - name: ID
    type: integer
  - name: A
    type: number
  - name: B
    type: string
  rows:
  - [10, 11, null]
...
cn:execute('select * from test limit 1 offset ?', {-2})
---
- error: 'Failed to execute SQL statement: Only positive integers are allowed in the
    OFFSET clause'
...
cn:execute('select * from test limit 1 offset ?', {2.7})
---
- error: 'Failed to execute SQL statement: Only positive integers are allowed in the
    OFFSET clause'
...
cn:execute('select * from test limit 1 offset ?', {'Hello'})
---
- error: 'Failed to execute SQL statement: Only positive integers are allowed in the
    OFFSET clause'
...
-- gh-2608 SQL iproto DDL
cn:execute('create table test2(id int primary key, a int, b int, c int)')
---
- row_count: 1
...
box.space.TEST2.name
---
- TEST2
...
cn:execute('insert into test2 values (1, 1, 1, 1)')
---
- row_count: 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)')
---
- row_count: 1
...
#box.space.TEST2.index
---
- 1
...
cn:execute('drop table test2')
---
- row_count: 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)')
---
- row_count: 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)')
---
- row_count: 3
...
cn:execute('create table if not exists test3(id int primary key)')
---
- row_count: 0
...
-- Test CREATE VIEW [IF NOT EXISTS] and
--      DROP   VIEW [IF EXISTS].
cn:execute('create view test3_view(id) as select id from test3')
---
- row_count: 1
...
cn:execute('create view if not exists test3_view(id) as select id from test3')
---
- row_count: 0
...
cn:execute('drop view test3_view')
---
- row_count: 1
...
cn:execute('drop view if exists test3_view')
---
- row_count: 0
...
-- Test CREATE INDEX [IF NOT EXISTS] and
--      DROP   INDEX [IF EXISTS].
cn:execute('create index test3_sec on test3(a, b)')
---
- row_count: 1
...
cn:execute('create index if not exists test3_sec on test3(a, b)')
---
- row_count: 0
...
cn:execute('drop index test3_sec on test3')
---
- row_count: 1
...
cn:execute('drop index if exists test3_sec on test3')
---
- row_count: 0
...
-- Test CREATE TRIGGER [IF NOT EXISTS] and
--      DROP   TRIGGER [IF EXISTS].
cn:execute('CREATE TRIGGER trig INSERT ON test3 FOR EACH ROW BEGIN SELECT * FROM test3; END;')
---
- row_count: 1
...
cn:execute('CREATE TRIGGER if not exists trig INSERT ON test3 FOR EACH ROW BEGIN SELECT * FROM test3; END;')
---
- row_count: 0
...
cn:execute('drop trigger trig')
---
- row_count: 1
...
cn:execute('drop trigger if exists trig')
---
- row_count: 0
...
-- Test DROP TABLE [IF EXISTS].
-- Create more indexes, triggers and _truncate tuple.
cn:execute('create index idx1 on test3(a)')
---
- row_count: 1
...
cn:execute('create index idx2 on test3(b)')
---
- row_count: 1
...
box.space.TEST3:truncate()
---
...
cn:execute('CREATE TRIGGER trig INSERT ON test3 FOR EACH ROW BEGIN SELECT * FROM test3; END;')
---
- row_count: 1
...
cn:execute('insert into test3 values (1, 1, 1), (2, 2, 2), (3, 3, 3)')
---
- row_count: 3
...
cn:execute('drop table test3')
---
- row_count: 1
...
cn:execute('drop table if exists test3')
---
- row_count: 0
...
--
-- gh-2948: sql: remove unnecessary templates for binding
-- parameters.
--
cn:execute('select ?1, ?2, ?3', {1, 2, 3})
---
- error: Syntax error at line 1 near '?1'
...
cn:execute('select $name, $name2', {1, 2})
---
- error: Index of binding slots must start from 1
...
parameters = {}
---
...
parameters[1] = 11
---
...
parameters[2] = 22
---
...
parameters[3] = 33
---
...
cn:execute('select $2, $1, $3', parameters)
---
- metadata:
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: integer
  - name: COLUMN_3
    type: integer
  rows:
  - [22, 11, 33]
...
cn:execute('select * from test where id = :1', {1})
---
- metadata:
  - name: ID
    type: integer
  - name: A
    type: number
  - name: B
    type: string
  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: number
  - name: B
    type: string
...
box.execute('drop table test')
---
- row_count: 1
...
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)')
---
- row_count: 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()
---
- row_count: 1
...
future2:wait_result()
---
- null
- Duplicate key exists in unique index 'pk_unnamed_TEST_1' in space 'TEST'
...
future3:wait_result()
---
- row_count: 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.execute('drop table test')
---
- row_count: 1
...
-- gh-2618 Return generated columns after INSERT in IPROTO.
-- Return all ids generated in current INSERT statement.
box.execute('create table test (id integer primary key autoincrement, a integer)')
---
- row_count: 1
...
cn = remote.connect(box.cfg.listen)
---
...
cn:execute('insert into test values (1, 1)')
---
- row_count: 1
...
cn:execute('insert into test values (null, 2)')
---
- autoincrement_ids:
  - 2
  row_count: 1
...
cn:execute('update test set a = 11 where id == 1')
---
- row_count: 1
...
cn:execute('insert into test values (100, 1), (null, 1), (120, 1), (null, 1)')
---
- autoincrement_ids:
  - 101
  - 121
  row_count: 4
...
cn:execute('insert into test values (null, 1), (null, 1), (null, 1), (null, 1), (null, 1)')
---
- autoincrement_ids:
  - 122
  - 123
  - 124
  - 125
  - 126
  row_count: 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
  row_count: 1
...
box.execute('create table test3 (id int primary key autoincrement)')
---
- row_count: 1
...
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
  row_count: 4
...
box.execute('drop table test')
---
- row_count: 1
...
s:drop()
---
...
sq:drop()
---
...
box.execute('drop table test3')
---
- row_count: 1
...
--
-- Ensure that FK inside CREATE TABLE does not affect row_count.
--
cn:execute('create table test (id integer primary key, a integer)')
---
- row_count: 1
...
cn:execute('create table test2 (id integer primary key, ref integer references test(id))')
---
- row_count: 1
...
cn:execute('drop table test2')
---
- row_count: 1
...
--
-- Ensure that REPLACE is accounted twice in row_count. As delete +
-- insert.
--
cn:execute('insert into test values(1, 1)')
---
- row_count: 1
...
cn:execute('insert or replace into test values(1, 2)')
---
- row_count: 2
...
cn:execute('drop table test')
---
- row_count: 1
...
-- 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: integer
  - name: x
    type: any
  rows:
  - [1, [1, 2, 3]]
  - [2, {'a': 3}]
...
s:drop()
---
...
-- Too many autogenerated ids leads to SEGFAULT.
cn = remote.connect(box.cfg.listen)
---
...
box.execute('CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT)')
---
- row_count: 1
...
for i = 0, 1000 do cn:execute("INSERT INTO t1 VALUES (null)") end
---
...
_ = cn:execute("INSERT INTO t1 SELECT NULL from t1")
---
...
box.execute('DROP TABLE t1')
---
- row_count: 1
...
cn:close()
---
...
-- gh-3832: Some statements do not return column type
box.execute('CREATE TABLE t1(id INTEGER PRIMARY KEY)')
---
- row_count: 1
...
cn = remote.connect(box.cfg.listen)
---
...
-- PRAGMA:
res = cn:execute("PRAGMA table_info(t1)")
---
...
res.metadata
---
- - name: cid
    type: integer
  - name: name
    type: text
  - name: type
    type: text
  - name: notnull
    type: integer
  - name: dflt_value
    type: text
  - name: pk
    type: integer
...
-- EXPLAIN
res = cn:execute("EXPLAIN SELECT 1")
---
...
res.metadata
---
- - name: addr
    type: integer
  - name: opcode
    type: text
  - name: p1
    type: integer
  - name: p2
    type: integer
  - name: p3
    type: integer
  - name: p4
    type: text
  - name: p5
    type: text
  - name: comment
    type: text
...
res = cn:execute("EXPLAIN QUERY PLAN SELECT COUNT(*) FROM t1")
---
...
res.metadata
---
- - name: selectid
    type: integer
  - name: order
    type: integer
  - name: from
    type: integer
  - name: detail
    type: text
...
-- Make sure that built-in functions have a right returning type.
--
cn:execute("SELECT zeroblob(1);")
---
- metadata:
  - name: COLUMN_1
    type: varbinary
  rows:
  - ["\0"]
...
-- randomblob() returns different results each time, so check only
-- type in meta.
--
res = cn:execute("SELECT randomblob(1);")
---
...
res.metadata
---
- - name: COLUMN_1
    type: varbinary
...
-- Type set during compilation stage, and since min/max are accept
-- arguments of all scalar type, we can't say nothing more than
-- SCALAR.
--
cn:execute("SELECT LEAST(1, 2, 3);")
---
- metadata:
  - name: COLUMN_1
    type: scalar
  rows:
  - [1]
...
cn:execute("SELECT GREATEST(1, 2, 3);")
---
- metadata:
  - name: COLUMN_1
    type: scalar
  rows:
  - [3]
...
cn:close()
---
...
box.execute('DROP TABLE t1')
---
- row_count: 1
...
box.schema.user.revoke('guest', 'read,write,execute', 'universe')
---
...
box.schema.user.revoke('guest', 'create', 'space')
---
...
space = nil
---
...
--
-- gh-4756: PREPARE and EXECUTE statistics should be present in box.stat()
--
p = box.stat().PREPARE.total
---
...
e = box.stat().EXECUTE.total
---
...
s = box.prepare([[ SELECT ?; ]])
---
...
s:execute({42})
---
- metadata:
  - name: COLUMN_1
    type: integer
  rows:
  - [42]
...
box.execute('SELECT 1;')
---
- metadata:
  - name: COLUMN_1
    type: integer
  rows:
  - [1]
...
res, err = box.unprepare(s)
---
...
assert(box.stat().PREPARE.total == p + 1)
---
- true
...
assert(box.stat().EXECUTE.total == e + 2)
---
- true
...
-- Cleanup xlog
box.snapshot()
---
- ok
...