1# 2015-08-31 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. The 12# focus of this file is testing indexes on expressions. 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18do_execsql_test indexexpr1-100 { 19 CREATE TABLE t1(a,b,c); 20 INSERT INTO t1(a,b,c) 21 /* 123456789 123456789 123456789 123456789 123456789 123456789 */ 22 VALUES('In_the_beginning_was_the_Word',1,1), 23 ('and_the_Word_was_with_God',1,2), 24 ('and_the_Word_was_God',1,3), 25 ('The_same_was_in_the_beginning_with_God',2,1), 26 ('All_things_were_made_by_him',3,1), 27 ('and_without_him_was_not_any_thing_made_that_was_made',3,2); 28 CREATE INDEX t1a1 ON t1(substr(a,1,12)); 29} {} 30do_execsql_test indexexpr1-110 { 31 SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c; 32} {1 2 | 1 3 |} 33do_execsql_test indexexpr1-110eqp { 34 EXPLAIN QUERY PLAN 35 SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c; 36} {/USING INDEX t1a1/} 37do_execsql_test indexexpr1-120 { 38 SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c; 39} {1 2 | 1 3 |} 40do_execsql_test indexexpr1-120eqp { 41 EXPLAIN QUERY PLAN 42 SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c; 43} {/USING INDEX t1a1/} 44 45do_execsql_test indexexpr1-130 { 46 CREATE INDEX t1ba ON t1(b,substr(a,2,3),c); 47 SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c; 48} {2 3} 49do_execsql_test indexexpr1-130eqp { 50 EXPLAIN QUERY PLAN 51 SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c; 52} {/USING INDEX t1ba/} 53 54do_execsql_test indexexpr1-140 { 55 SELECT rowid, substr(a,b,3), '|' FROM t1 ORDER BY 2; 56} {1 In_ | 2 and | 3 and | 6 d_w | 4 he_ | 5 l_t |} 57do_execsql_test indexexpr1-141 { 58 CREATE INDEX t1abx ON t1(substr(a,b,3)); 59 SELECT rowid FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +rowid; 60} {1 2 3} 61do_execsql_test indexexpr1-141eqp { 62 EXPLAIN QUERY PLAN 63 SELECT rowid FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +rowid; 64} {/USING INDEX t1abx/} 65do_execsql_test indexexpr1-142 { 66 SELECT rowid FROM t1 WHERE +substr(a,b,3)<='and' ORDER BY +rowid; 67} {1 2 3} 68do_execsql_test indexexpr1-150 { 69 SELECT rowid FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz') 70 ORDER BY +rowid; 71} {2 3 5} 72do_execsql_test indexexpr1-150eqp { 73 EXPLAIN QUERY PLAN 74 SELECT rowid FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz') 75 ORDER BY +rowid; 76} {/USING INDEX t1abx/} 77 78do_execsql_test indexexpr1-160 { 79 ALTER TABLE t1 ADD COLUMN d; 80 UPDATE t1 SET d=length(a); 81 CREATE INDEX t1a2 ON t1(SUBSTR(a, 27, 3)) WHERE d>=29; 82 SELECT rowid, b, c FROM t1 83 WHERE substr(a,27,3)=='ord' AND d>=29; 84} {1 1 1} 85do_execsql_test indexexpr1-160eqp { 86 EXPLAIN QUERY PLAN 87 SELECT rowid, b, c FROM t1 88 WHERE substr(a,27,3)=='ord' AND d>=29; 89} {/USING INDEX t1a2/} 90 91 92do_execsql_test indexexpr1-200 { 93 DROP TABLE t1; 94 CREATE TABLE t1(id ANY PRIMARY KEY, a,b,c) WITHOUT ROWID; 95 INSERT INTO t1(id,a,b,c) 96 VALUES(1,'In_the_beginning_was_the_Word',1,1), 97 (2,'and_the_Word_was_with_God',1,2), 98 (3,'and_the_Word_was_God',1,3), 99 (4,'The_same_was_in_the_beginning_with_God',2,1), 100 (5,'All_things_were_made_by_him',3,1), 101 (6,'and_without_him_was_not_any_thing_made_that_was_made',3,2); 102 CREATE INDEX t1a1 ON t1(substr(a,1,12)); 103} {} 104do_execsql_test indexexpr1-210 { 105 SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c; 106} {1 2 | 1 3 |} 107do_execsql_test indexexpr1-210eqp { 108 EXPLAIN QUERY PLAN 109 SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c; 110} {/USING INDEX t1a1/} 111do_execsql_test indexexpr1-220 { 112 SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c; 113} {1 2 | 1 3 |} 114do_execsql_test indexexpr1-220eqp { 115 EXPLAIN QUERY PLAN 116 SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c; 117} {/USING INDEX t1a1/} 118 119do_execsql_test indexexpr1-230 { 120 CREATE INDEX t1ba ON t1(b,substr(a,2,3),c); 121 SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c; 122} {2 3} 123do_execsql_test indexexpr1-230eqp { 124 EXPLAIN QUERY PLAN 125 SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c; 126} {/USING INDEX t1ba/} 127 128do_execsql_test indexexpr1-240 { 129 SELECT id, substr(a,b,3), '|' FROM t1 ORDER BY 2; 130} {1 In_ | 2 and | 3 and | 6 d_w | 4 he_ | 5 l_t |} 131do_execsql_test indexexpr1-241 { 132 CREATE INDEX t1abx ON t1(substr(a,b,3)); 133 SELECT id FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +id; 134} {1 2 3} 135do_execsql_test indexexpr1-241eqp { 136 EXPLAIN QUERY PLAN 137 SELECT id FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +id; 138} {/USING INDEX t1abx/} 139do_execsql_test indexexpr1-242 { 140 SELECT id FROM t1 WHERE +substr(a,b,3)<='and' ORDER BY +id; 141} {1 2 3} 142do_execsql_test indexexpr1-250 { 143 SELECT id FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz') 144 ORDER BY +id; 145} {2 3 5} 146do_execsql_test indexexpr1-250eqp { 147 EXPLAIN QUERY PLAN 148 SELECT id FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz') 149 ORDER BY +id; 150} {/USING INDEX t1abx/} 151 152do_execsql_test indexexpr1-260 { 153 ALTER TABLE t1 ADD COLUMN d; 154 UPDATE t1 SET d=length(a); 155 CREATE INDEX t1a2 ON t1(SUBSTR(a, 27, 3)) WHERE d>=29; 156 SELECT id, b, c FROM t1 157 WHERE substr(a,27,3)=='ord' AND d>=29; 158} {1 1 1} 159do_execsql_test indexexpr1-260eqp { 160 EXPLAIN QUERY PLAN 161 SELECT id, b, c FROM t1 162 WHERE substr(a,27,3)=='ord' AND d>=29; 163} {/USING INDEX t1a2/} 164 165 166do_catchsql_test indexexpr1-300 { 167 CREATE TABLE t2(a,b,c); 168 CREATE INDEX t2x1 ON t2(a,b+random()); 169} {1 {non-deterministic functions prohibited in index expressions}} 170do_catchsql_test indexexpr1-301 { 171 CREATE INDEX t2x1 ON t2(a+julianday('now')); 172} {1 {non-deterministic functions prohibited in index expressions}} 173do_catchsql_test indexexpr1-310 { 174 CREATE INDEX t2x2 ON t2(a,b+(SELECT 15)); 175} {1 {subqueries prohibited in index expressions}} 176do_catchsql_test indexexpr1-320 { 177 CREATE TABLE e1(x,y,UNIQUE(y,substr(x,1,5))); 178} {1 {expressions prohibited in PRIMARY KEY and UNIQUE constraints}} 179do_catchsql_test indexexpr1-330 { 180 CREATE TABLE e1(x,y,PRIMARY KEY(y,substr(x,1,5))); 181} {1 {expressions prohibited in PRIMARY KEY and UNIQUE constraints}} 182do_catchsql_test indexexpr1-331 { 183 CREATE TABLE e1(x,y,PRIMARY KEY(y,substr(x,1,5))) WITHOUT ROWID; 184} {1 {expressions prohibited in PRIMARY KEY and UNIQUE constraints}} 185do_catchsql_test indexexpr1-340 { 186 CREATE TABLE e1(x,y,FOREIGN KEY(substr(y,1,5)) REFERENCES t1); 187} {1 {near "(": syntax error}} 188 189do_execsql_test indexexpr1-400 { 190 CREATE TABLE t3(a,b,c); 191 WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<30) 192 INSERT INTO t3(a,b,c) 193 SELECT x, printf('ab%04xyz',x), random() FROM c; 194 CREATE UNIQUE INDEX t3abc ON t3(CAST(a AS text), b, substr(c,1,3)); 195 SELECT a FROM t3 WHERE CAST(a AS text)<='10' ORDER BY +a; 196} {1 10} 197do_catchsql_test indexexpr1-410 { 198 INSERT INTO t3 SELECT * FROM t3 WHERE rowid=10; 199} {1 {UNIQUE constraint failed: index 't3abc'}} 200 201do_execsql_test indexexpr1-500 { 202 CREATE TABLE t5(a); 203 CREATE TABLE cnt(x); 204 WITH RECURSIVE 205 c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5) 206 INSERT INTO cnt(x) SELECT x FROM c; 207 INSERT INTO t5(a) SELECT printf('abc%03dxyz',x) FROM cnt; 208 CREATE INDEX t5ax ON t5( substr(a,4,3) ); 209} {} 210do_execsql_test indexexpr1-510 { 211 -- The use of the "k" alias in the WHERE clause is technically 212 -- illegal, but SQLite allows it for historical reasons. In this 213 -- test and the next, verify that "k" can be used by the t5ax index 214 SELECT substr(a,4,3) AS k FROM cnt, t5 WHERE k=printf('%03d',x); 215} {001 002 003 004 005} 216do_execsql_test indexexpr1-510eqp { 217 EXPLAIN QUERY PLAN 218 SELECT substr(a,4,3) AS k FROM cnt, t5 WHERE k=printf('%03d',x); 219} {/USING INDEX t5ax/} 220 221# Skip-scan on an indexed expression 222# 223do_execsql_test indexexpr1-600 { 224 DROP TABLE IF EXISTS t4; 225 CREATE TABLE t4(a,b,c,d,e,f,g,h,i); 226 CREATE INDEX t4all ON t4(a,b,c<d,e,f,i,h); 227 INSERT INTO t4 VALUES(1,2,3,4,5,6,7,8,9); 228 ANALYZE; 229 DELETE FROM sqlite_stat1; 230 INSERT INTO sqlite_stat1 231 VALUES('t4','t4all','600000 160000 40000 10000 2000 600 100 40 10'); 232 ANALYZE sqlite_master; 233 SELECT i FROM t4 WHERE e=5; 234} {9} 235 236 237 238 239finish_test 240