SQL: Tests failure research

We are working towards 100% pass rate in SQLite tests.

Occasionally we encounter obscure issues that aren't fixed immediately due to non-trivial amount of effort required. We record our findings here for future reference.

aggnested-3.0

Feature request submitted #2263

alter-*

Original test is alter.test, not alter1.test There're lots of commented cases in the file. Need to review and uncomment if possible.

alter-12.4

#2308.

alter-4.1,2

Commented. Need AUTOINCREMENT feature #2263

alter-12.4,5

Not sure, we can and will implement db close TCL API. @mejedi could you pls clarify?

autoindex1-*

This SQLite feature won't be supported in nearest future.

between-*

All issues here connected w/ removal of default column rowid. With rowid removed, traversal is performed using different index.

bigrow1-4.2 and rest

See #2273

bind-*

See #2274

close-*

We're not going to support db close/open statements at all, so this test might be excluded.

count-*

Fails in this tests depend on #2284.

coveridxscan-3.1+

#2308.

e_create-table-3.11+

Key part count limit reached.

e_select-2.[1,2,3,4].2.28d, -2.4.20,22,25

Submitted #2293.

NB: these tests failed on sql branch as well.

enc3-*

Submitted #2295.

eqp-8.1.3

Submitted #2296.

eval-2.[3,4]

These tests relies on ordering by rowid. Also they use x as PK (wasn't originally), so duplicates on x prohibited. Probably we'll comment the test.

func-29.[1,3,5]

Same as alter-12.4. Tests use db close/open TCL statements.

func5-*

UTF system table encoding issue #2295.

in1-13.12, in3-3.2

Issue submitted #2300

in5-2.5.[1,2,3]

Use PK instead of rowid changed generated program. So, it doesn't use OpenEphemeral anymore. Cases commented.

index2

Issue submitted #2289.

index5-1.2

Test checks VFS functionality. No relation to Tarantool.

index6-6.0,9.2

Issue submitted #2311.

indexedby-5.4

Removal of rowid makes query solvable.

insert5-*

Need AUTOINCREMENT feature #2263.

like-9.*

db open/close feature.

like-5.3,13,21-24, -11.3,5,6,9

This and subsequent tests fail due to the missing NOCASE collation.

like3-3.0

This and subsequent tests fail due to the missing NOCASE collation.

quote-1.0

#2126.

misc3-5.1,2

Issue submitted #2325.

select1-15.3

#2300.

select6-1,2.7,8,9 -3.2

#2331.

selectA-*

Due to the missing NOCASE collation.

substr-*

#2263.

table-14.2

#2321.

tkt-2ea2425d34-1.1

#2295.

tkt-b72787b1

Issue submitted #2302.

tkt-80e031a00f-*

Issue related to #2284.

tkt-3a77c9714e-*

Need AUTOINCREMENT feature #2263.

transitive1-410

Issue submitted #2306.

trigger2-1.6+.*

Issue submitted #2319.

trigger1-*

Issue submitted #2320.

types-2.4.2

Issue submitted #2307.

SQL:

  CREATE TABLE t3(x TEXT PRIMARY KEY COLLATE nocase);
  INSERT INTO t3(x) VALUES('aaa'),('abc'),('abd'),('abe'),('acz');
  INSERT INTO t3(x) SELECT CAST(x AS blob) FROM t3;
  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x;

Expected: ['abc' 'abd' 'abe' X'616263' X'616264' X'616265']

Got: ['aaa' 'abc' 'abd' 'abe' X'616263' X'616264' X'616265']

Root cause: LIKE 'ab%' translates into OP_IdxGE 'AB', i.e. position the cursor on the first entry >= 'AB'. NOCASE implies 'aaa' < 'AB', but in the BINARY collation, which is the only one we have for now, 'aaa' > 'AB'.

unique.test.lua

See https://github.com/tarantool/tarantool/issues/2255

test/sql-tap related questions

with2.test.lua-4.[5..7]

SQLite limit SQLITE_LIMIT_COLUMN is not set at all. Need to investigate if we want to port it to Tarantool. Tests will be commented until that time.

with2.test.lua-5.[1..8]

xferopt optimization status in Tarantool is unknown, need to investigate and find a way to export variable to Lua if needed.