AryaWu/sqlite
0
1# 2013-11-042#3# The author disclaims copyright to this source code. In place of4# 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#12# Test cases for partial indices in WITHOUT ROWID tables13#14 15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19ifcapable !vtab {20 finish_test21 return22}23 24# Capture the output of a pragma in a TEMP table.25#26proc capture_pragma {db tabname sql} {27 $db eval "DROP TABLE IF EXISTS temp.$tabname"28 set once 129 $db eval $sql x {30 if {$once} {31 set once 032 set ins "INSERT INTO $tabname VALUES"33 set crtab "CREATE TEMP TABLE $tabname "34 set sep "("35 foreach col $x(*) {36 append ins ${sep}\$x($col)37 append crtab ${sep}\"$col\"38 set sep ,39 }40 append ins )41 append crtab )42 $db eval $crtab43 }44 $db eval $ins45 }46}47 48 49load_static_extension db wholenumber;50do_test index7-1.1 {51 # Able to parse and manage partial indices52 execsql {53 CREATE TABLE t1(a,b,c PRIMARY KEY) WITHOUT rowid;54 CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL;55 CREATE INDEX t1b ON t1(b) WHERE b>10;56 CREATE VIRTUAL TABLE nums USING wholenumber;57 INSERT INTO t1(a,b,c)58 SELECT CASE WHEN value%3!=0 THEN value END, value, value59 FROM nums WHERE value<=20;60 SELECT count(a), count(b) FROM t1;61 PRAGMA integrity_check;62 }63} {14 20 ok}64 65# (The "partial" column of the PRAGMA index_list output is...)66# EVIDENCE-OF: R-34457-09668 "1" if the index is a partial index and "0"67# if not.68#69do_test index7-1.1a {70 capture_pragma db out {PRAGMA index_list(t1)}71 db eval {SELECT "name", "partial", '|' FROM out ORDER BY "name"}72} {sqlite_autoindex_t1_1 0 | t1a 1 | t1b 1 |}73 74# Make sure the count(*) optimization works correctly with75# partial indices. Ticket [a5c8ed66cae16243be6] 2013-10-03.76#77do_execsql_test index7-1.1.1 {78 SELECT count(*) FROM t1;79} {20}80 81# Error conditions during parsing...82#83do_test index7-1.2 {84 catchsql {85 CREATE INDEX bad1 ON t1(a,b) WHERE x IS NOT NULL;86 }87} {1 {no such column: x}}88do_test index7-1.3 {89 catchsql {90 CREATE INDEX bad1 ON t1(a,b) WHERE EXISTS(SELECT * FROM t1);91 }92} {1 {subqueries prohibited in partial index WHERE clauses}}93do_test index7-1.4 {94 catchsql {95 CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1;96 }97} {1 {parameters prohibited in partial index WHERE clauses}}98do_test index7-1.5 {99 catchsql {100 CREATE INDEX bad1 ON t1(a,b) WHERE a!=random();101 }102} {1 {non-deterministic functions prohibited in partial index WHERE clauses}}103do_test index7-1.6 {104 catchsql {105 CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%';106 }107} {0 {}}108do_execsql_test index7-1.7 {109 INSERT INTO t1(a,b,c)110 VALUES('abcde',1,101),('abdef',2,102),('xyz',3,103),('abcz',4,104);111 SELECT c FROM t1 WHERE a NOT LIKE 'abc%' AND a=7 ORDER BY +b;112} {7}113do_execsql_test index7-1.7eqp {114 EXPLAIN QUERY PLAN115 SELECT b FROM t1 WHERE a NOT LIKE 'abc%' AND a=7 ORDER BY +b;116} {/SEARCH t1 USING COVERING INDEX bad1 /}117do_execsql_test index7-1.8 {118 DELETE FROM t1 WHERE c>=101;119 DROP INDEX IF EXISTS bad1;120} {}121 122do_test index7-1.10 {123 execsql {124 ANALYZE;125 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;126 PRAGMA integrity_check;127 }128} {t1 {20 1} t1a {14 1} t1b {10 1} ok}129 130# STAT1 shows the partial indices have a reduced number of131# rows.132#133do_test index7-1.11 {134 execsql {135 UPDATE t1 SET a=b;136 ANALYZE;137 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;138 PRAGMA integrity_check;139 }140} {t1 {20 1} t1a {20 1} t1b {10 1} ok}141 142do_test index7-1.11b {143 execsql {144 UPDATE t1 SET a=NULL WHERE b%3!=0;145 UPDATE t1 SET b=b+100;146 ANALYZE;147 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;148 PRAGMA integrity_check;149 }150} {t1 {20 1} t1a {6 1} t1b {20 1} ok}151 152do_test index7-1.12 {153 execsql {154 UPDATE t1 SET a=CASE WHEN b%3!=0 THEN b END;155 UPDATE t1 SET b=b-100;156 ANALYZE;157 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;158 PRAGMA integrity_check;159 }160} {t1 {20 1} t1a {13 1} t1b {10 1} ok}161 162do_test index7-1.13 {163 execsql {164 DELETE FROM t1 WHERE b BETWEEN 8 AND 12;165 ANALYZE;166 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;167 PRAGMA integrity_check;168 }169} {t1 {15 1} t1a {10 1} t1b {8 1} ok}170 171do_test index7-1.14 {172 execsql {173 REINDEX;174 ANALYZE;175 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;176 PRAGMA integrity_check;177 }178} {t1 {15 1} t1a {10 1} t1b {8 1} ok}179 180do_test index7-1.15 {181 execsql {182 CREATE INDEX t1c ON t1(c);183 ANALYZE;184 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;185 PRAGMA integrity_check;186 }187} {t1 {15 1} t1a {10 1} t1b {8 1} t1c {15 1} ok}188 189# Queries use partial indices at appropriate times.190#191do_test index7-2.1 {192 execsql {193 CREATE TABLE t2(a,b PRIMARY KEY) without rowid;194 INSERT INTO t2(a,b) SELECT value, value FROM nums WHERE value<1000;195 UPDATE t2 SET a=NULL WHERE b%5==0;196 CREATE INDEX t2a1 ON t2(a) WHERE a IS NOT NULL;197 SELECT count(*) FROM t2 WHERE a IS NOT NULL;198 }199} {800}200do_test index7-2.2 {201 execsql {202 EXPLAIN QUERY PLAN203 SELECT * FROM t2 WHERE a=5;204 }205} {/(SCAN|SEARCH) t2 USING COVERING INDEX t2a1 /}206ifcapable stat4 {207 do_test index7-2.3stat4 {208 execsql {209 EXPLAIN QUERY PLAN210 SELECT * FROM t2 WHERE a IS NOT NULL;211 }212 } {/(SCAN|SEARCH) t2 USING COVERING INDEX t2a1 /}213} else {214 do_test index7-2.3stat4 {215 execsql {216 EXPLAIN QUERY PLAN217 SELECT * FROM t2 WHERE a IS NOT NULL AND a>0;218 }219 } {/(SCAN|SEARCH) t2 USING COVERING INDEX t2a1 /}220}221do_test index7-2.4 {222 execsql {223 EXPLAIN QUERY PLAN224 SELECT * FROM t2 WHERE a IS NULL;225 }226} {~/INDEX t2a1/}227 228do_execsql_test index7-2.101 {229 DROP INDEX t2a1;230 UPDATE t2 SET a=b, b=b+10000;231 SELECT b FROM t2 WHERE a=15;232} {10015}233do_execsql_test index7-2.102 {234 CREATE INDEX t2a2 ON t2(a) WHERE a<100 OR a>200;235 SELECT b FROM t2 WHERE a=15;236 PRAGMA integrity_check;237} {10015 ok}238do_execsql_test index7-2.102eqp {239 EXPLAIN QUERY PLAN240 SELECT b FROM t2 WHERE a=15;241} {~/.*INDEX t2a2.*/}242do_execsql_test index7-2.103 {243 SELECT b FROM t2 WHERE a=15 AND a<100;244} {10015}245do_execsql_test index7-2.103eqp {246 EXPLAIN QUERY PLAN247 SELECT b FROM t2 WHERE a=15 AND a<100;248} {/.*INDEX t2a2.*/}249do_execsql_test index7-2.104 {250 SELECT b FROM t2 WHERE a=515 AND a>200;251} {10515}252do_execsql_test index7-2.104eqp {253 EXPLAIN QUERY PLAN254 SELECT b FROM t2 WHERE a=515 AND a>200;255} {/.*INDEX t2a2.*/}256 257# Partial UNIQUE indices258#259do_execsql_test index7-3.1 {260 CREATE TABLE t3(a,b PRIMARY KEY) without rowid;261 INSERT INTO t3 SELECT value, value FROM nums WHERE value<200;262 UPDATE t3 SET a=999 WHERE b%5!=0;263 CREATE UNIQUE INDEX t3a ON t3(a) WHERE a<>999;264} {}265do_test index7-3.2 {266 # unable to insert a duplicate row a-value that is not 999.267 catchsql {268 INSERT INTO t3(a,b) VALUES(150, 'test1');269 }270} {1 {UNIQUE constraint failed: t3.a}}271do_test index7-3.3 {272 # can insert multiple rows with a==999 because such rows are not273 # part of the unique index.274 catchsql {275 INSERT INTO t3(a,b) VALUES(999, 'test1'), (999, 'test2');276 }277} {0 {}}278do_execsql_test index7-3.4 {279 SELECT count(*) FROM t3 WHERE a=999;280} {162}281integrity_check index7-3.5282 283do_execsql_test index7-4.0 {284 VACUUM;285 PRAGMA integrity_check;286} {ok}287 288# Silently ignore database name qualifiers in partial indices.289#290do_execsql_test index7-5.0 {291 CREATE INDEX t3b ON t3(b) WHERE xyzzy.t3.b BETWEEN 5 AND 10;292 /* ^^^^^-- ignored */293 ANALYZE;294 SELECT count(*) FROM t3 WHERE t3.b BETWEEN 5 AND 10;295 SELECT stat+0 FROM sqlite_stat1 WHERE idx='t3b';296} {6 6}297 298# Verify that the problem identified by ticket [98d973b8f5] has been fixed.299#300do_execsql_test index7-6.1 {301 CREATE TABLE t5(a, b);302 CREATE TABLE t4(c, d);303 INSERT INTO t5 VALUES(1, 'xyz');304 INSERT INTO t4 VALUES('abc', 'not xyz');305 SELECT * FROM (SELECT * FROM t5 WHERE a=1 AND b='xyz'), t4 WHERE c='abc';306} {307 1 xyz abc {not xyz}308}309do_execsql_test index7-6.2 {310 CREATE INDEX i4 ON t4(c) WHERE d='xyz';311 SELECT * FROM (SELECT * FROM t5 WHERE a=1 AND b='xyz'), t4 WHERE c='abc';312} {313 1 xyz abc {not xyz}314}315do_execsql_test index7-6.3 {316 CREATE VIEW v4 AS SELECT * FROM t4;317 INSERT INTO t4 VALUES('def', 'xyz');318 SELECT * FROM v4 WHERE d='xyz' AND c='def'319} {320 def xyz321}322do_eqp_test index7-6.4 {323 SELECT * FROM v4 WHERE d='xyz' AND c='def'324} {SEARCH t4 USING INDEX i4 (c=?)}325 326do_catchsql_test index7-6.5 {327 CREATE INDEX t5a ON t5(a) WHERE a=#1;328} {1 {near "#1": syntax error}}329 330do_execsql_test index7-7.0 {331 CREATE TABLE t6(x, y);332 INSERT INTO t6 VALUES(1, 1);333 INSERT INTO t6 VALUES(0, 0);334 SELECT * FROM t6 WHERE y IS TRUE ORDER BY x;335} {1 1}336 337do_execsql_test index7-7.1 {338 CREATE INDEX i6 ON t6(x) WHERE y IS NOT TRUE;339 SELECT * FROM t6 WHERE y IS TRUE ORDER BY x;340} {1 1}341 342# 2020-05-27. tag-20200527-1.343# Incomplete stat1 information on a table with few rows should still use the344# index.345reset_db346do_execsql_test index7-8.1 {347 CREATE TABLE t1(x INTEGER PRIMARY KEY, y);348 CREATE INDEX t1y ON t1(y) WHERE y IS NOT NULL;349 INSERT INTO t1(x) VALUES(1),(2);350 ANALYZE;351 EXPLAIN QUERY PLAN SELECT 1 FROM t1 WHERE y=5;352} {/SEARCH t1 USING COVERING INDEX t1y/}353 354 355finish_test356