000001 # 2013-11-04
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 #
000012 # Test cases for partial indices in WITHOUT ROWID tables
000013 #
000014
000015
000016 set testdir [file dirname $argv0]
000017 source $testdir/tester.tcl
000018
000019 ifcapable !vtab {
000020 finish_test
000021 return
000022 }
000023
000024 # Capture the output of a pragma in a TEMP table.
000025 #
000026 proc capture_pragma {db tabname sql} {
000027 $db eval "DROP TABLE IF EXISTS temp.$tabname"
000028 set once 1
000029 $db eval $sql x {
000030 if {$once} {
000031 set once 0
000032 set ins "INSERT INTO $tabname VALUES"
000033 set crtab "CREATE TEMP TABLE $tabname "
000034 set sep "("
000035 foreach col $x(*) {
000036 append ins ${sep}\$x($col)
000037 append crtab ${sep}\"$col\"
000038 set sep ,
000039 }
000040 append ins )
000041 append crtab )
000042 $db eval $crtab
000043 }
000044 $db eval $ins
000045 }
000046 }
000047
000048
000049 load_static_extension db wholenumber;
000050 do_test index7-1.1 {
000051 # Able to parse and manage partial indices
000052 execsql {
000053 CREATE TABLE t1(a,b,c PRIMARY KEY) WITHOUT rowid;
000054 CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL;
000055 CREATE INDEX t1b ON t1(b) WHERE b>10;
000056 CREATE VIRTUAL TABLE nums USING wholenumber;
000057 INSERT INTO t1(a,b,c)
000058 SELECT CASE WHEN value%3!=0 THEN value END, value, value
000059 FROM nums WHERE value<=20;
000060 SELECT count(a), count(b) FROM t1;
000061 PRAGMA integrity_check;
000062 }
000063 } {14 20 ok}
000064
000065 # (The "partial" column of the PRAGMA index_list output is...)
000066 # EVIDENCE-OF: R-34457-09668 "1" if the index is a partial index and "0"
000067 # if not.
000068 #
000069 do_test index7-1.1a {
000070 capture_pragma db out {PRAGMA index_list(t1)}
000071 db eval {SELECT "name", "partial", '|' FROM out ORDER BY "name"}
000072 } {sqlite_autoindex_t1_1 0 | t1a 1 | t1b 1 |}
000073
000074 # Make sure the count(*) optimization works correctly with
000075 # partial indices. Ticket [a5c8ed66cae16243be6] 2013-10-03.
000076 #
000077 do_execsql_test index7-1.1.1 {
000078 SELECT count(*) FROM t1;
000079 } {20}
000080
000081 # Error conditions during parsing...
000082 #
000083 do_test index7-1.2 {
000084 catchsql {
000085 CREATE INDEX bad1 ON t1(a,b) WHERE x IS NOT NULL;
000086 }
000087 } {1 {no such column: x}}
000088 do_test index7-1.3 {
000089 catchsql {
000090 CREATE INDEX bad1 ON t1(a,b) WHERE EXISTS(SELECT * FROM t1);
000091 }
000092 } {1 {subqueries prohibited in partial index WHERE clauses}}
000093 do_test index7-1.4 {
000094 catchsql {
000095 CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1;
000096 }
000097 } {1 {parameters prohibited in partial index WHERE clauses}}
000098 do_test index7-1.5 {
000099 catchsql {
000100 CREATE INDEX bad1 ON t1(a,b) WHERE a!=random();
000101 }
000102 } {1 {non-deterministic functions prohibited in partial index WHERE clauses}}
000103 do_test index7-1.6 {
000104 catchsql {
000105 CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%';
000106 }
000107 } {0 {}}
000108 do_execsql_test index7-1.7 {
000109 INSERT INTO t1(a,b,c)
000110 VALUES('abcde',1,101),('abdef',2,102),('xyz',3,103),('abcz',4,104);
000111 SELECT c FROM t1 WHERE a NOT LIKE 'abc%' AND a=7 ORDER BY +b;
000112 } {7}
000113 do_execsql_test index7-1.7eqp {
000114 EXPLAIN QUERY PLAN
000115 SELECT b FROM t1 WHERE a NOT LIKE 'abc%' AND a=7 ORDER BY +b;
000116 } {/SEARCH t1 USING COVERING INDEX bad1 /}
000117 do_execsql_test index7-1.8 {
000118 DELETE FROM t1 WHERE c>=101;
000119 DROP INDEX IF EXISTS bad1;
000120 } {}
000121
000122 do_test index7-1.10 {
000123 execsql {
000124 ANALYZE;
000125 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
000126 PRAGMA integrity_check;
000127 }
000128 } {t1 {20 1} t1a {14 1} t1b {10 1} ok}
000129
000130 # STAT1 shows the partial indices have a reduced number of
000131 # rows.
000132 #
000133 do_test index7-1.11 {
000134 execsql {
000135 UPDATE t1 SET a=b;
000136 ANALYZE;
000137 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
000138 PRAGMA integrity_check;
000139 }
000140 } {t1 {20 1} t1a {20 1} t1b {10 1} ok}
000141
000142 do_test index7-1.11b {
000143 execsql {
000144 UPDATE t1 SET a=NULL WHERE b%3!=0;
000145 UPDATE t1 SET b=b+100;
000146 ANALYZE;
000147 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
000148 PRAGMA integrity_check;
000149 }
000150 } {t1 {20 1} t1a {6 1} t1b {20 1} ok}
000151
000152 do_test index7-1.12 {
000153 execsql {
000154 UPDATE t1 SET a=CASE WHEN b%3!=0 THEN b END;
000155 UPDATE t1 SET b=b-100;
000156 ANALYZE;
000157 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
000158 PRAGMA integrity_check;
000159 }
000160 } {t1 {20 1} t1a {13 1} t1b {10 1} ok}
000161
000162 do_test index7-1.13 {
000163 execsql {
000164 DELETE FROM t1 WHERE b BETWEEN 8 AND 12;
000165 ANALYZE;
000166 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
000167 PRAGMA integrity_check;
000168 }
000169 } {t1 {15 1} t1a {10 1} t1b {8 1} ok}
000170
000171 do_test index7-1.14 {
000172 execsql {
000173 REINDEX;
000174 ANALYZE;
000175 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
000176 PRAGMA integrity_check;
000177 }
000178 } {t1 {15 1} t1a {10 1} t1b {8 1} ok}
000179
000180 do_test index7-1.15 {
000181 execsql {
000182 CREATE INDEX t1c ON t1(c);
000183 ANALYZE;
000184 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
000185 PRAGMA integrity_check;
000186 }
000187 } {t1 {15 1} t1a {10 1} t1b {8 1} t1c {15 1} ok}
000188
000189 # Queries use partial indices at appropriate times.
000190 #
000191 do_test index7-2.1 {
000192 execsql {
000193 CREATE TABLE t2(a,b PRIMARY KEY) without rowid;
000194 INSERT INTO t2(a,b) SELECT value, value FROM nums WHERE value<1000;
000195 UPDATE t2 SET a=NULL WHERE b%5==0;
000196 CREATE INDEX t2a1 ON t2(a) WHERE a IS NOT NULL;
000197 SELECT count(*) FROM t2 WHERE a IS NOT NULL;
000198 }
000199 } {800}
000200 do_test index7-2.2 {
000201 execsql {
000202 EXPLAIN QUERY PLAN
000203 SELECT * FROM t2 WHERE a=5;
000204 }
000205 } {/(SCAN|SEARCH) t2 USING COVERING INDEX t2a1 /}
000206 ifcapable stat4 {
000207 do_test index7-2.3stat4 {
000208 execsql {
000209 EXPLAIN QUERY PLAN
000210 SELECT * FROM t2 WHERE a IS NOT NULL;
000211 }
000212 } {/(SCAN|SEARCH) t2 USING COVERING INDEX t2a1 /}
000213 } else {
000214 do_test index7-2.3stat4 {
000215 execsql {
000216 EXPLAIN QUERY PLAN
000217 SELECT * FROM t2 WHERE a IS NOT NULL AND a>0;
000218 }
000219 } {/(SCAN|SEARCH) t2 USING COVERING INDEX t2a1 /}
000220 }
000221 do_test index7-2.4 {
000222 execsql {
000223 EXPLAIN QUERY PLAN
000224 SELECT * FROM t2 WHERE a IS NULL;
000225 }
000226 } {~/INDEX t2a1/}
000227
000228 do_execsql_test index7-2.101 {
000229 DROP INDEX t2a1;
000230 UPDATE t2 SET a=b, b=b+10000;
000231 SELECT b FROM t2 WHERE a=15;
000232 } {10015}
000233 do_execsql_test index7-2.102 {
000234 CREATE INDEX t2a2 ON t2(a) WHERE a<100 OR a>200;
000235 SELECT b FROM t2 WHERE a=15;
000236 PRAGMA integrity_check;
000237 } {10015 ok}
000238 do_execsql_test index7-2.102eqp {
000239 EXPLAIN QUERY PLAN
000240 SELECT b FROM t2 WHERE a=15;
000241 } {~/.*INDEX t2a2.*/}
000242 do_execsql_test index7-2.103 {
000243 SELECT b FROM t2 WHERE a=15 AND a<100;
000244 } {10015}
000245 do_execsql_test index7-2.103eqp {
000246 EXPLAIN QUERY PLAN
000247 SELECT b FROM t2 WHERE a=15 AND a<100;
000248 } {/.*INDEX t2a2.*/}
000249 do_execsql_test index7-2.104 {
000250 SELECT b FROM t2 WHERE a=515 AND a>200;
000251 } {10515}
000252 do_execsql_test index7-2.104eqp {
000253 EXPLAIN QUERY PLAN
000254 SELECT b FROM t2 WHERE a=515 AND a>200;
000255 } {/.*INDEX t2a2.*/}
000256
000257 # Partial UNIQUE indices
000258 #
000259 do_execsql_test index7-3.1 {
000260 CREATE TABLE t3(a,b PRIMARY KEY) without rowid;
000261 INSERT INTO t3 SELECT value, value FROM nums WHERE value<200;
000262 UPDATE t3 SET a=999 WHERE b%5!=0;
000263 CREATE UNIQUE INDEX t3a ON t3(a) WHERE a<>999;
000264 } {}
000265 do_test index7-3.2 {
000266 # unable to insert a duplicate row a-value that is not 999.
000267 catchsql {
000268 INSERT INTO t3(a,b) VALUES(150, 'test1');
000269 }
000270 } {1 {UNIQUE constraint failed: t3.a}}
000271 do_test index7-3.3 {
000272 # can insert multiple rows with a==999 because such rows are not
000273 # part of the unique index.
000274 catchsql {
000275 INSERT INTO t3(a,b) VALUES(999, 'test1'), (999, 'test2');
000276 }
000277 } {0 {}}
000278 do_execsql_test index7-3.4 {
000279 SELECT count(*) FROM t3 WHERE a=999;
000280 } {162}
000281 integrity_check index7-3.5
000282
000283 do_execsql_test index7-4.0 {
000284 VACUUM;
000285 PRAGMA integrity_check;
000286 } {ok}
000287
000288 # Silently ignore database name qualifiers in partial indices.
000289 #
000290 do_execsql_test index7-5.0 {
000291 CREATE INDEX t3b ON t3(b) WHERE xyzzy.t3.b BETWEEN 5 AND 10;
000292 /* ^^^^^-- ignored */
000293 ANALYZE;
000294 SELECT count(*) FROM t3 WHERE t3.b BETWEEN 5 AND 10;
000295 SELECT stat+0 FROM sqlite_stat1 WHERE idx='t3b';
000296 } {6 6}
000297
000298 # Verify that the problem identified by ticket [98d973b8f5] has been fixed.
000299 #
000300 do_execsql_test index7-6.1 {
000301 CREATE TABLE t5(a, b);
000302 CREATE TABLE t4(c, d);
000303 INSERT INTO t5 VALUES(1, 'xyz');
000304 INSERT INTO t4 VALUES('abc', 'not xyz');
000305 SELECT * FROM (SELECT * FROM t5 WHERE a=1 AND b='xyz'), t4 WHERE c='abc';
000306 } {
000307 1 xyz abc {not xyz}
000308 }
000309 do_execsql_test index7-6.2 {
000310 CREATE INDEX i4 ON t4(c) WHERE d='xyz';
000311 SELECT * FROM (SELECT * FROM t5 WHERE a=1 AND b='xyz'), t4 WHERE c='abc';
000312 } {
000313 1 xyz abc {not xyz}
000314 }
000315 do_execsql_test index7-6.3 {
000316 CREATE VIEW v4 AS SELECT * FROM t4;
000317 INSERT INTO t4 VALUES('def', 'xyz');
000318 SELECT * FROM v4 WHERE d='xyz' AND c='def'
000319 } {
000320 def xyz
000321 }
000322 do_eqp_test index7-6.4 {
000323 SELECT * FROM v4 WHERE d='xyz' AND c='def'
000324 } {SEARCH t4 USING INDEX i4 (c=?)}
000325
000326 do_catchsql_test index7-6.5 {
000327 CREATE INDEX t5a ON t5(a) WHERE a=#1;
000328 } {1 {near "#1": syntax error}}
000329
000330 do_execsql_test index7-7.0 {
000331 CREATE TABLE t6(x, y);
000332 INSERT INTO t6 VALUES(1, 1);
000333 INSERT INTO t6 VALUES(0, 0);
000334 SELECT * FROM t6 WHERE y IS TRUE ORDER BY x;
000335 } {1 1}
000336
000337 do_execsql_test index7-7.1 {
000338 CREATE INDEX i6 ON t6(x) WHERE y IS NOT TRUE;
000339 SELECT * FROM t6 WHERE y IS TRUE ORDER BY x;
000340 } {1 1}
000341
000342 # 2020-05-27. tag-20200527-1.
000343 # Incomplete stat1 information on a table with few rows should still use the
000344 # index.
000345 reset_db
000346 do_execsql_test index7-8.1 {
000347 CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
000348 CREATE INDEX t1y ON t1(y) WHERE y IS NOT NULL;
000349 INSERT INTO t1(x) VALUES(1),(2);
000350 ANALYZE;
000351 EXPLAIN QUERY PLAN SELECT 1 FROM t1 WHERE y=5;
000352 } {/SEARCH t1 USING COVERING INDEX t1y/}
000353
000354
000355 finish_test