1# 2017 April 11 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. 12# 13 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set testprefix indexexpr2 17 18do_execsql_test 1 { 19 CREATE TABLE t1(a, b); 20 INSERT INTO t1 VALUES(1, 'one'); 21 INSERT INTO t1 VALUES(2, 'two'); 22 INSERT INTO t1 VALUES(3, 'three'); 23 24 CREATE INDEX i1 ON t1(b || 'x'); 25} 26 27do_execsql_test 1.1 { 28 SELECT 'TWOX' == (b || 'x') FROM t1 WHERE (b || 'x')>'onex' 29} {0 0} 30 31do_execsql_test 1.2 { 32 SELECT 'TWOX' == (b || 'x') COLLATE nocase FROM t1 WHERE (b || 'x')>'onex' 33} {0 1} 34 35do_execsql_test 2.0 { 36 CREATE INDEX i2 ON t1(a+1); 37} 38 39do_execsql_test 2.1 { 40 SELECT a+1, quote(a+1) FROM t1 ORDER BY 1; 41} {2 2 3 3 4 4} 42 43#------------------------------------------------------------------------- 44# At one point SQLite was incorrectly using indexes on expressions to 45# optimize ORDER BY and GROUP BY clauses even when the collation 46# sequences of the query and index did not match (ticket [e20dd54ab0e4]). 47# The following tests - 3.* - attempt to verify that this has been fixed. 48# 49 50reset_db 51do_execsql_test 3.1.0 { 52 CREATE TABLE t1(a, b); 53 CREATE INDEX i1 ON t1(a, b); 54} {} 55 56do_eqp_test 3.1.1 { 57 SELECT b FROM t1 WHERE b IS NOT NULL AND a IS NULL 58 GROUP BY b COLLATE nocase 59 ORDER BY b COLLATE nocase; 60} {/USE TEMP B-TREE FOR GROUP BY/} 61 62do_execsql_test 3.2.0 { 63 CREATE TABLE t2(x); 64 65 INSERT INTO t2 VALUES('.ABC'); 66 INSERT INTO t2 VALUES('.abcd'); 67 INSERT INTO t2 VALUES('.defg'); 68 INSERT INTO t2 VALUES('.DEF'); 69} {} 70 71do_execsql_test 3.2.1 { 72 SELECT x FROM t2 ORDER BY substr(x, 2) COLLATE nocase; 73} { 74 .ABC .abcd .DEF .defg 75} 76 77do_execsql_test 3.2.2 { 78 CREATE INDEX i2 ON t2( substr(x, 2) ); 79 SELECT x FROM t2 ORDER BY substr(x, 2) COLLATE nocase; 80} { 81 .ABC .abcd .DEF .defg 82} 83 84do_execsql_test 3.3.0 { 85 CREATE TABLE t3(x); 86} 87 88ifcapable json1 { 89 do_eqp_test 3.3.1 { 90 SELECT json_extract(x, '$.b') FROM t2 91 WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL 92 GROUP BY json_extract(x, '$.b') COLLATE nocase 93 ORDER BY json_extract(x, '$.b') COLLATE nocase; 94 } { 95 0 0 0 {SCAN TABLE t2} 96 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 97 } 98 99 do_execsql_test 3.3.2 { 100 CREATE INDEX i3 ON t3(json_extract(x, '$.a'), json_extract(x, '$.b')); 101 } {} 102 103 do_eqp_test 3.3.3 { 104 SELECT json_extract(x, '$.b') FROM t3 105 WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL 106 GROUP BY json_extract(x, '$.b') COLLATE nocase 107 ORDER BY json_extract(x, '$.b') COLLATE nocase; 108 } { 109 0 0 0 {SEARCH TABLE t3 USING INDEX i3 (<expr>=?)} 110 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 111 } 112} 113 114do_execsql_test 3.4.0 { 115 CREATE TABLE t4(a, b); 116 INSERT INTO t4 VALUES('.ABC', 1); 117 INSERT INTO t4 VALUES('.abc', 2); 118 INSERT INTO t4 VALUES('.ABC', 3); 119 INSERT INTO t4 VALUES('.abc', 4); 120} 121 122do_execsql_test 3.4.1 { 123 SELECT * FROM t4 124 WHERE substr(a, 2) = 'abc' COLLATE NOCASE 125 ORDER BY substr(a, 2), b; 126} { 127 .ABC 1 .ABC 3 .abc 2 .abc 4 128} 129 130do_execsql_test 3.4.2 { 131 CREATE INDEX i4 ON t4( substr(a, 2) COLLATE NOCASE, b ); 132 SELECT * FROM t4 133 WHERE substr(a, 2) = 'abc' COLLATE NOCASE 134 ORDER BY substr(a, 2), b; 135} { 136 .ABC 1 .ABC 3 .abc 2 .abc 4 137} 138 139do_execsql_test 3.4.3 { 140 DROP INDEX i4; 141 UPDATE t4 SET a = printf('%s%d',a,b); 142 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; 143} {.ABC1 1 .abc2 2 .ABC3 3 .abc4 4} 144do_execsql_test 3.4.4 { 145 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE binary; 146} {.ABC1 1 .ABC3 3 .abc2 2 .abc4 4} 147 148do_execsql_test 3.4.5 { 149 CREATE INDEX i4 ON t4( Substr(a,-2) COLLATE nocase ); 150 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; 151} {.ABC1 1 .abc2 2 .ABC3 3 .abc4 4} 152do_execsql_test 3.4.5eqp { 153 EXPLAIN QUERY PLAN 154 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; 155} {/SCAN TABLE t4 USING INDEX i4/} 156do_execsql_test 3.4.6 { 157 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE binary; 158} {.ABC1 1 .ABC3 3 .abc2 2 .abc4 4} 159 160 161finish_test 162