Tarantool collate cases

Collate.lua

box.cfg{}

local fields = {
    {name = 'id', type = 'unsigned'},
    {name = 'firstname', type = 'string'},
    {name = 'lastname', type = 'string'},
    {name = 'state', type = 'integer'}
}

t = box.schema.space.create('collate_test_1', { engine = 'vinyl', format = fields })
t:create_index('id', { parts = { { 1 } } })
t:create_index('insens', {parts = {
    {'firstname', type = 'string', collation='unicode_ci'},
    {'lastname', type = 'string', collation='unicode_ci'},
    {'state', type = 'integer'}
}})

t:insert({1, "Иванов", "Петр", 1})
t:insert({2, "петров", "иван", 1})
t:insert({3, "СИДОРОВ", "иВан", 1})
t:insert({4, "СИДОРОВ", "иван", 0})

--box.execute([[explain query plan select * from "collate_test_1" where ("firstname"='петров' and "lastname" = 'иван' and "state" = 1) collate "unicode_ci"]])
-----
--- metadata:
--  - name: selectid
--    type: integer
--  - name: order
--    type: integer
--  - name: from
--    type: integer
--  - name: detail
--    type: text
--  rows:
--  - [0, 0, 0, 'SCAN TABLE collate_test_1 (~262144 rows)']

--box.execute([[explain query plan select * from "collate_test_1" where "firstname"='петров' and "lastname" = 'иван' collate "unicode_ci" and "state" = 1 ]])
-----
--- metadata:
--  - name: selectid
--    type: integer
--  - name: order
--    type: integer
--  - name: from
--    type: integer
--  - name: detail
--    type: text
--  rows:
--  - [0, 0, 0, 'SCAN TABLE collate_test_1 (~262144 rows)']

--box.execute([[explain query plan select * from "collate_test_1" where "firstname"='петров' collate "unicode_ci" and "lastname" = 'иван' collate "unicode_ci" and "state" = 1 ]])
-----
--- metadata:
--  - name: selectid
--    type: integer
--  - name: order
--    type: integer
--  - name: from
--    type: integer
--  - name: detail
--    type: text
--  rows:
--  - [0, 0, 0, 'SEARCH TABLE collate_test_1 USING COVERING INDEX insens (firstname=?
--      AND lastname=? AND state=?) (~1 row)']

--box.execute([[explain query plan select * from "collate_test_1" where "lastname" = 'иван' collate "unicode_ci" and "state" = 1 ]])
-----
--- metadata:
--  - name: selectid
--    type: integer
--  - name: order
--    type: integer
--  - name: from
--    type: integer
--  - name: detail
--    type: text
--  rows:
--  - [0, 0, 0, 'SCAN TABLE collate_test_1 (~262144 rows)']

-----
t = box.schema.space.create('collate_test_2', { engine = 'vinyl', format = fields })
t:create_index('id', { parts = { { 1 } } })
t:create_index('insens', {parts = {
    {'firstname', type = 'string', collation='unicode_ci'},
    {'lastname', type = 'string', collation='unicode'},
    {'state', type = 'integer'}
}})

--box.execute([[explain query plan select * from "collate_test_2" where "firstname"='петров' collate "unicode_ci" and "lastname" = 'иван' collate "unicode_ci" and "state" = 1 ]])
-----
--- metadata:
--  - name: selectid
--    type: integer
--  - name: order
--    type: integer
--  - name: from
--    type: integer
--  - name: detail
--    type: text
--  rows:
--  - [0, 0, 0, 'SEARCH TABLE collate_test_2 USING COVERING INDEX insens (firstname=?)
--      (~8 rows)']

-- box.execute([[explain query plan select * from "collate_test_2" where "firstname"='петров' collate "unicode_ci" and "lastname" = 'иван' collate "unicode" and "state" = 1 ]])
-----
--- metadata:
--  - name: selectid
--    type: integer
--  - name: order
--    type: integer
--  - name: from
--    type: integer
--  - name: detail
--    type: text
--  rows:
--  - [0, 0, 0, 'SEARCH TABLE collate_test_2 USING COVERING INDEX insens (firstname=?
--      AND lastname=? AND state=?) (~1 row)']

-----
t = box.schema.space.create('collate_test_3', { engine = 'vinyl', format = fields })
t:create_index('id', { parts = { { 1 } } })
t:create_index('insens', {parts = {
    {'firstname', type = 'string', collation='unicode_ci'},
}})
t:create_index('insens1', {parts = {
    {'lastname', type = 'string', collation='unicode_ci'},
}})

-- box.execute([[explain query plan select * from "collate_test_3" where "firstname"='петров' and "lastname" = 'иван' collate "unicode" ]])
-----
--- metadata:
--  - name: selectid
--    type: integer
--  - name: order
--    type: integer
--  - name: from
--    type: integer
--  - name: detail
--    type: text
--  rows:
--  - [0, 0, 0, 'SCAN TABLE collate_test_3 (~262144 rows)']

-- box.execute([[explain query plan select * from "collate_test_3" where "firstname"='петров' collate "unicode_ci" and "lastname" = 'иван' collate "unicode" ]])
-----
--- metadata:
--  - name: selectid
--    type: integer
--  - name: order
--    type: integer
--  - name: from
--    type: integer
--  - name: detail
--    type: text
--  rows:
--  - [0, 0, 0, 'SEARCH TABLE collate_test_3 USING COVERING INDEX insens (firstname=?)
--      (~1 row)']

-- box.execute([[explain query plan select * from "collate_test_3" where "firstname"='петров' collate "unicode_ci" and "lastname" = 'иван' collate "unicode_ci" ]])
-----
--- metadata:
--  - name: selectid
--    type: integer
--  - name: order
--    type: integer
--  - name: from
--    type: integer
--  - name: detail
--    type: text
--  rows:
--  - [0, 0, 0, 'SEARCH TABLE collate_test_3 USING COVERING INDEX insens (firstname=?)
--      (~1 row)']

t = box.schema.space.create('collate_test_4', { engine = 'vinyl', format = fields })
t:create_index('id', { parts = { { 1 } } })
t:create_index('insens', {parts = {
    {'firstname', type = 'string', collation='unicode_ci'},
}})
t:create_index('insens1', {parts = {
    {'lastname', type = 'string', collation='unicode'},
}})

-- box.execute([[explain query plan select * from "collate_test_4" where "firstname"='петров' collate "unicode_ci" and "lastname" = 'иван' collate "unicode" ]])
-----
--- metadata:
--  - name: selectid
--    type: integer
--  - name: order
--    type: integer
--  - name: from
--    type: integer
--  - name: detail
--    type: text
--  rows:
--  - [0, 0, 0, 'SEARCH TABLE collate_test_4 USING COVERING INDEX insens (firstname=?)
--      (~1 row)']

-- box.execute([[explain query plan select * from "collate_test_4" where "firstname"='петров' collate "unicode" and "lastname" = 'иван' collate "unicode_ci" ]])
-----
--- metadata:
--  - name: selectid
--    type: integer
--  - name: order
--    type: integer
--  - name: from
--    type: integer
--  - name: detail
--    type: text
--  rows:
--  - [0, 0, 0, 'SCAN TABLE collate_test_4 (~262144 rows)']