test_run = require('test_run').new()
---
...
engine = test_run:get_cfg('engine')
---
...
_ = box.space._session_settings:update('sql_default_engine', {{'=', 2, engine}})
---
...
-- create space
box.execute("CREATE TABLE foobar (foo INT PRIMARY KEY, bar TEXT)")
---
- row_count: 1
...
-- prepare data
box.execute("INSERT INTO foobar VALUES (1, 'foo')")
---
- row_count: 1
...
box.execute("INSERT INTO foobar VALUES (2, 'bar')")
---
- row_count: 1
...
box.execute("INSERT INTO foobar VALUES (1000, 'foobar')")
---
- row_count: 1
...
box.execute("INSERT INTO foobar VALUES (1, 'duplicate')")
---
- null
- Duplicate key exists in unique index 'pk_unnamed_FOOBAR_1' in space 'FOOBAR'
...
-- simple select
box.execute("SELECT bar, foo, 42, 'awesome' FROM foobar")
---
- metadata:
  - name: BAR
    type: string
  - name: FOO
    type: integer
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: string
  rows:
  - ['foo', 1, 42, 'awesome']
  - ['bar', 2, 42, 'awesome']
  - ['foobar', 1000, 42, 'awesome']
...
box.execute("SELECT bar, foo, 42, 'awesome' FROM foobar LIMIT 2")
---
- metadata:
  - name: BAR
    type: string
  - name: FOO
    type: integer
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: string
  rows:
  - ['foo', 1, 42, 'awesome']
  - ['bar', 2, 42, 'awesome']
...
box.execute("SELECT bar, foo, 42, 'awesome' FROM foobar WHERE foo=2")
---
- metadata:
  - name: BAR
    type: string
  - name: FOO
    type: integer
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: string
  rows:
  - ['bar', 2, 42, 'awesome']
...
box.execute("SELECT bar, foo, 42, 'awesome' FROM foobar WHERE foo>2")
---
- metadata:
  - name: BAR
    type: string
  - name: FOO
    type: integer
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: string
  rows:
  - ['foobar', 1000, 42, 'awesome']
...
box.execute("SELECT bar, foo, 42, 'awesome' FROM foobar WHERE foo>=2")
---
- metadata:
  - name: BAR
    type: string
  - name: FOO
    type: integer
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: string
  rows:
  - ['bar', 2, 42, 'awesome']
  - ['foobar', 1000, 42, 'awesome']
...
box.execute("SELECT bar, foo, 42, 'awesome' FROM foobar WHERE foo=10000")
---
- metadata:
  - name: BAR
    type: string
  - name: FOO
    type: integer
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: string
  rows: []
...
box.execute("SELECT bar, foo, 42, 'awesome' FROM foobar WHERE foo>10000")
---
- metadata:
  - name: BAR
    type: string
  - name: FOO
    type: integer
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: string
  rows: []
...
box.execute("SELECT bar, foo, 42, 'awesome' FROM foobar WHERE foo<2")
---
- metadata:
  - name: BAR
    type: string
  - name: FOO
    type: integer
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: string
  rows:
  - ['foo', 1, 42, 'awesome']
...
box.execute("SELECT bar, foo, 42, 'awesome' FROM foobar WHERE foo<2.001")
---
- metadata:
  - name: BAR
    type: string
  - name: FOO
    type: integer
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: string
  rows:
  - ['foo', 1, 42, 'awesome']
  - ['bar', 2, 42, 'awesome']
...
box.execute("SELECT bar, foo, 42, 'awesome' FROM foobar WHERE foo<=2")
---
- metadata:
  - name: BAR
    type: string
  - name: FOO
    type: integer
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: string
  rows:
  - ['foo', 1, 42, 'awesome']
  - ['bar', 2, 42, 'awesome']
...
box.execute("SELECT bar, foo, 42, 'awesome' FROM foobar WHERE foo<100")
---
- metadata:
  - name: BAR
    type: string
  - name: FOO
    type: integer
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: string
  rows:
  - ['foo', 1, 42, 'awesome']
  - ['bar', 2, 42, 'awesome']
...
box.execute("SELECT bar, foo, 42, 'awesome' FROM foobar WHERE bar='foo'")
---
- metadata:
  - name: BAR
    type: string
  - name: FOO
    type: integer
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: string
  rows:
  - ['foo', 1, 42, 'awesome']
...
box.execute("SELECT count(*) FROM foobar")
---
- metadata:
  - name: COLUMN_1
    type: integer
  rows:
  - [3]
...
box.execute("SELECT count(*) FROM foobar WHERE bar='foo'")
---
- metadata:
  - name: COLUMN_1
    type: integer
  rows:
  - [1]
...
box.execute("SELECT bar, foo, 42, 'awesome' FROM foobar ORDER BY bar")
---
- metadata:
  - name: BAR
    type: string
  - name: FOO
    type: integer
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: string
  rows:
  - ['bar', 2, 42, 'awesome']
  - ['foo', 1, 42, 'awesome']
  - ['foobar', 1000, 42, 'awesome']
...
box.execute("SELECT bar, foo, 42, 'awesome' FROM foobar ORDER BY bar DESC")
---
- metadata:
  - name: BAR
    type: string
  - name: FOO
    type: integer
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: string
  rows:
  - ['foobar', 1000, 42, 'awesome']
  - ['foo', 1, 42, 'awesome']
  - ['bar', 2, 42, 'awesome']
...
-- updates
box.execute("REPLACE INTO foobar VALUES (1, 'cacodaemon')")
---
- row_count: 2
...
box.execute("SELECT COUNT(*) FROM foobar WHERE foo=1")
---
- metadata:
  - name: COLUMN_1
    type: integer
  rows:
  - [1]
...
box.execute("SELECT COUNT(*) FROM foobar WHERE bar='cacodaemon'")
---
- metadata:
  - name: COLUMN_1
    type: integer
  rows:
  - [1]
...
box.execute("DELETE FROM foobar WHERE bar='cacodaemon'")
---
- row_count: 1
...
box.execute("SELECT COUNT(*) FROM foobar WHERE bar='cacodaemon'")
---
- metadata:
  - name: COLUMN_1
    type: integer
  rows:
  - [0]
...
-- multi-index
-- create space
box.execute("CREATE TABLE barfoo (bar TEXT, foo NUMBER PRIMARY KEY)")
---
- row_count: 1
...
box.execute("CREATE UNIQUE INDEX barfoo2 ON barfoo(bar)")
---
- row_count: 1
...
-- prepare data
box.execute("INSERT INTO barfoo VALUES ('foo', 1)")
---
- row_count: 1
...
box.execute("INSERT INTO barfoo VALUES ('bar', 2)")
---
- row_count: 1
...
box.execute("INSERT INTO barfoo VALUES ('foobar', 1000)")
---
- row_count: 1
...
-- prove barfoo2 was created
box.execute("INSERT INTO barfoo VALUES ('xfoo', 1)")
---
- null
- Duplicate key exists in unique index 'pk_unnamed_BARFOO_1' in space 'BARFOO'
...
box.execute("SELECT foo, bar FROM barfoo")
---
- metadata:
  - name: FOO
    type: number
  - name: BAR
    type: string
  rows:
  - [1, 'foo']
  - [2, 'bar']
  - [1000, 'foobar']
...
box.execute("SELECT foo, bar FROM barfoo WHERE foo==2")
---
- metadata:
  - name: FOO
    type: number
  - name: BAR
    type: string
  rows:
  - [2, 'bar']
...
box.execute("SELECT foo, bar FROM barfoo WHERE bar=='foobar'")
---
- metadata:
  - name: FOO
    type: number
  - name: BAR
    type: string
  rows:
  - [1000, 'foobar']
...
box.execute("SELECT foo, bar FROM barfoo WHERE foo>=2")
---
- metadata:
  - name: FOO
    type: number
  - name: BAR
    type: string
  rows:
  - [2, 'bar']
  - [1000, 'foobar']
...
box.execute("SELECT foo, bar FROM barfoo WHERE foo<=2")
---
- metadata:
  - name: FOO
    type: number
  - name: BAR
    type: string
  rows:
  - [1, 'foo']
  - [2, 'bar']
...
-- cleanup
box.execute("DROP INDEX barfoo2 ON barfoo")
---
- row_count: 1
...
box.execute("DROP TABLE foobar")
---
- row_count: 1
...
box.execute("DROP TABLE barfoo")
---
- row_count: 1
...
-- attempt to create a table lacking PRIMARY KEY
box.execute("CREATE TABLE without_rowid_lacking_primary_key(x SCALAR)")
---
- null
- 'Failed to create space ''WITHOUT_ROWID_LACKING_PRIMARY_KEY'': PRIMARY KEY missing'
...
-- create a table with implicit indices (used to SEGFAULT)
box.execute("CREATE TABLE implicit_indices(a INT PRIMARY KEY,b INT,c INT,d TEXT UNIQUE)")
---
- row_count: 1
...
box.execute("DROP TABLE implicit_indices")
---
- row_count: 1
...