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.

like3-3.0

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

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'.