# 2017 April 11 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix indexexpr2 do_execsql_test 1 { CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two'); INSERT INTO t1 VALUES(3, 'three'); CREATE INDEX i1 ON t1(b || 'x'); } do_execsql_test 1.1 { SELECT 'TWOX' == (b || 'x') FROM t1 WHERE (b || 'x')>'onex' } {0 0} do_execsql_test 1.2 { SELECT 'TWOX' == (b || 'x') COLLATE nocase FROM t1 WHERE (b || 'x')>'onex' } {0 1} do_execsql_test 2.0 { CREATE INDEX i2 ON t1(a+1); } do_execsql_test 2.1 { SELECT a+1, quote(a+1) FROM t1 ORDER BY 1; } {2 2 3 3 4 4} #------------------------------------------------------------------------- # At one point SQLite was incorrectly using indexes on expressions to # optimize ORDER BY and GROUP BY clauses even when the collation # sequences of the query and index did not match (ticket [e20dd54ab0e4]). # The following tests - 3.* - attempt to verify that this has been fixed. # reset_db do_execsql_test 3.1.0 { CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a, b); } {} do_eqp_test 3.1.1 { SELECT b FROM t1 WHERE b IS NOT NULL AND a IS NULL GROUP BY b COLLATE nocase ORDER BY b COLLATE nocase; } {/USE TEMP B-TREE FOR GROUP BY/} do_execsql_test 3.2.0 { CREATE TABLE t2(x); INSERT INTO t2 VALUES('.ABC'); INSERT INTO t2 VALUES('.abcd'); INSERT INTO t2 VALUES('.defg'); INSERT INTO t2 VALUES('.DEF'); } {} do_execsql_test 3.2.1 { SELECT x FROM t2 ORDER BY substr(x, 2) COLLATE nocase; } { .ABC .abcd .DEF .defg } do_execsql_test 3.2.2 { CREATE INDEX i2 ON t2( substr(x, 2) ); SELECT x FROM t2 ORDER BY substr(x, 2) COLLATE nocase; } { .ABC .abcd .DEF .defg } do_execsql_test 3.3.0 { CREATE TABLE t3(x); } ifcapable json1 { do_eqp_test 3.3.1 { SELECT json_extract(x, '$.b') FROM t2 WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL GROUP BY json_extract(x, '$.b') COLLATE nocase ORDER BY json_extract(x, '$.b') COLLATE nocase; } { 0 0 0 {SCAN TABLE t2} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} } do_execsql_test 3.3.2 { CREATE INDEX i3 ON t3(json_extract(x, '$.a'), json_extract(x, '$.b')); } {} do_eqp_test 3.3.3 { SELECT json_extract(x, '$.b') FROM t3 WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL GROUP BY json_extract(x, '$.b') COLLATE nocase ORDER BY json_extract(x, '$.b') COLLATE nocase; } { 0 0 0 {SEARCH TABLE t3 USING INDEX i3 (=?)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} } } do_execsql_test 3.4.0 { CREATE TABLE t4(a, b); INSERT INTO t4 VALUES('.ABC', 1); INSERT INTO t4 VALUES('.abc', 2); INSERT INTO t4 VALUES('.ABC', 3); INSERT INTO t4 VALUES('.abc', 4); } do_execsql_test 3.4.1 { SELECT * FROM t4 WHERE substr(a, 2) = 'abc' COLLATE NOCASE ORDER BY substr(a, 2), b; } { .ABC 1 .ABC 3 .abc 2 .abc 4 } do_execsql_test 3.4.2 { CREATE INDEX i4 ON t4( substr(a, 2) COLLATE NOCASE, b ); SELECT * FROM t4 WHERE substr(a, 2) = 'abc' COLLATE NOCASE ORDER BY substr(a, 2), b; } { .ABC 1 .ABC 3 .abc 2 .abc 4 } do_execsql_test 3.4.3 { DROP INDEX i4; UPDATE t4 SET a = printf('%s%d',a,b); SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; } {.ABC1 1 .abc2 2 .ABC3 3 .abc4 4} do_execsql_test 3.4.4 { SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE binary; } {.ABC1 1 .ABC3 3 .abc2 2 .abc4 4} do_execsql_test 3.4.5 { CREATE INDEX i4 ON t4( Substr(a,-2) COLLATE nocase ); SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; } {.ABC1 1 .abc2 2 .ABC3 3 .abc4 4} do_execsql_test 3.4.5eqp { EXPLAIN QUERY PLAN SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; } {/SCAN TABLE t4 USING INDEX i4/} do_execsql_test 3.4.6 { SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE binary; } {.ABC1 1 .ABC3 3 .abc2 2 .abc4 4} finish_test