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 } [string map {"\n " \n} { 95 QUERY PLAN 96 |--SCAN TABLE t2 97 `--USE TEMP B-TREE FOR GROUP BY 98 }] 99 100 do_execsql_test 3.3.2 { 101 CREATE INDEX i3 ON t3(json_extract(x, '$.a'), json_extract(x, '$.b')); 102 } {} 103 104 do_eqp_test 3.3.3 { 105 SELECT json_extract(x, '$.b') FROM t3 106 WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL 107 GROUP BY json_extract(x, '$.b') COLLATE nocase 108 ORDER BY json_extract(x, '$.b') COLLATE nocase; 109 } [string map {"\n " \n} { 110 QUERY PLAN 111 |--SEARCH TABLE t3 USING INDEX i3 (<expr>=?) 112 `--USE TEMP B-TREE FOR GROUP BY 113 }] 114} 115 116do_execsql_test 3.4.0 { 117 CREATE TABLE t4(a, b); 118 INSERT INTO t4 VALUES('.ABC', 1); 119 INSERT INTO t4 VALUES('.abc', 2); 120 INSERT INTO t4 VALUES('.ABC', 3); 121 INSERT INTO t4 VALUES('.abc', 4); 122} 123 124do_execsql_test 3.4.1 { 125 SELECT * FROM t4 126 WHERE substr(a, 2) = 'abc' COLLATE NOCASE 127 ORDER BY substr(a, 2), b; 128} { 129 .ABC 1 .ABC 3 .abc 2 .abc 4 130} 131 132do_execsql_test 3.4.2 { 133 CREATE INDEX i4 ON t4( substr(a, 2) COLLATE NOCASE, b ); 134 SELECT * FROM t4 135 WHERE substr(a, 2) = 'abc' COLLATE NOCASE 136 ORDER BY substr(a, 2), b; 137} { 138 .ABC 1 .ABC 3 .abc 2 .abc 4 139} 140 141do_execsql_test 3.4.3 { 142 DROP INDEX i4; 143 UPDATE t4 SET a = printf('%s%d',a,b); 144 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; 145} {.ABC1 1 .abc2 2 .ABC3 3 .abc4 4} 146do_execsql_test 3.4.4 { 147 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE binary; 148} {.ABC1 1 .ABC3 3 .abc2 2 .abc4 4} 149 150do_execsql_test 3.4.5 { 151 CREATE INDEX i4 ON t4( Substr(a,-2) COLLATE nocase ); 152 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; 153} {.ABC1 1 .abc2 2 .ABC3 3 .abc4 4} 154do_execsql_test 3.4.5eqp { 155 EXPLAIN QUERY PLAN 156 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; 157} {/SCAN TABLE t4 USING INDEX i4/} 158do_execsql_test 3.4.6 { 159 SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE binary; 160} {.ABC1 1 .ABC3 3 .abc2 2 .abc4 4} 161 162 163finish_test 164