diff options
Diffstat (limited to 'test/skipscan1.test')
-rw-r--r-- | test/skipscan1.test | 250 |
1 files changed, 250 insertions, 0 deletions
diff --git a/test/skipscan1.test b/test/skipscan1.test new file mode 100644 index 0000000..8150b01 --- /dev/null +++ b/test/skipscan1.test @@ -0,0 +1,250 @@ +# 2013-11-13 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# This file implements tests of the "skip-scan" query strategy. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_execsql_test skipscan1-1.1 { + CREATE TABLE t1(a TEXT, b INT, c INT, d INT); + CREATE INDEX t1abc ON t1(a,b,c); + INSERT INTO t1 VALUES('abc',123,4,5); + INSERT INTO t1 VALUES('abc',234,5,6); + INSERT INTO t1 VALUES('abc',234,6,7); + INSERT INTO t1 VALUES('abc',345,7,8); + INSERT INTO t1 VALUES('def',567,8,9); + INSERT INTO t1 VALUES('def',345,9,10); + INSERT INTO t1 VALUES('bcd',100,6,11); + + /* Fake the sqlite_stat1 table so that the query planner believes + ** the table contains thousands of rows and that the first few + ** columns are not selective. */ + ANALYZE; + DELETE FROM sqlite_stat1; + INSERT INTO sqlite_stat1 VALUES('t1','t1abc','10000 5000 2000 10'); + ANALYZE sqlite_master; +} {} + +# Simple queries that leave the first one or two columns of the +# index unconstrainted. +# +do_execsql_test skipscan1-1.2 { + SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a; +} {abc 345 7 8 | def 345 9 10 |} +do_execsql_test skipscan1-1.2eqp { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a; +} {/* USING INDEX t1abc (ANY(a) AND b=?)*/} +do_execsql_test skipscan1-1.2sort { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a; +} {~/*ORDER BY*/} + +do_execsql_test skipscan1-1.3 { + SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a DESC; +} {def 345 9 10 | abc 345 7 8 |} +do_execsql_test skipscan1-1.3eqp { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a; +} {/* USING INDEX t1abc (ANY(a) AND b=?)*/} +do_execsql_test skipscan1-1.3sort { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a; +} {~/*ORDER BY*/} + +do_execsql_test skipscan1-1.4 { + SELECT a,b,c,d,'|' FROM t1 WHERE c=6 ORDER BY a, b, c; +} {abc 234 6 7 | bcd 100 6 11 |} +do_execsql_test skipscan1-1.4eqp { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t1 WHERE c=6 ORDER BY a, b, c; +} {/* USING INDEX t1abc (ANY(a) AND ANY(b) AND c=?)*/} +do_execsql_test skipscan1-1.4sort { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t1 WHERE c=6 ORDER BY a, b, c; +} {~/*ORDER BY*/} + +do_execsql_test skipscan1-1.5 { + SELECT a,b,c,d,'|' FROM t1 WHERE c IN (6,7) ORDER BY a, b, c; +} {abc 234 6 7 | abc 345 7 8 | bcd 100 6 11 |} +do_execsql_test skipscan1-1.5eqp { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t1 WHERE c IN (6,7) ORDER BY a, b, c; +} {/* USING INDEX t1abc (ANY(a) AND ANY(b) AND c=?)*/} +do_execsql_test skipscan1-1.5sort { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t1 WHERE c IN (6,7) ORDER BY a, b, c; +} {~/*ORDER BY*/} + +do_execsql_test skipscan1-1.6 { + SELECT a,b,c,d,'|' FROM t1 WHERE c BETWEEN 6 AND 7 ORDER BY a, b, c; +} {abc 234 6 7 | abc 345 7 8 | bcd 100 6 11 |} +do_execsql_test skipscan1-1.6eqp { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t1 WHERE c BETWEEN 6 AND 7 ORDER BY a, b, c; +} {/* USING INDEX t1abc (ANY(a) AND ANY(b) AND c>? AND c<?)*/} +do_execsql_test skipscan1-1.6sort { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t1 WHERE c BETWEEN 6 AND 7 ORDER BY a, b, c; +} {~/*ORDER BY*/} + +do_execsql_test skipscan1-1.7 { + SELECT a,b,c,d,'|' FROM t1 WHERE b IN (234, 345) AND c BETWEEN 6 AND 7 + ORDER BY a, b; +} {abc 234 6 7 | abc 345 7 8 |} +do_execsql_test skipscan1-1.7eqp { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t1 WHERE b IN (234, 345) AND c BETWEEN 6 AND 7 + ORDER BY a, b; +} {/* USING INDEX t1abc (ANY(a) AND b=? AND c>? AND c<?)*/} +do_execsql_test skipscan1-1.7sort { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t1 WHERE b IN (234, 345) AND c BETWEEN 6 AND 7 + ORDER BY a, b; +} {~/*ORDER BY*/} + + +# Joins +# +do_execsql_test skipscan1-1.51 { + CREATE TABLE t1j(x TEXT, y INTEGER); + INSERT INTO t1j VALUES('one',1),('six',6),('ninty-nine',99); + INSERT INTO sqlite_stat1 VALUES('t1j',null,'3'); + ANALYZE sqlite_master; + SELECT x, a, b, c, d, '|' FROM t1j, t1 WHERE c=y ORDER BY +a; +} {six abc 234 6 7 | six bcd 100 6 11 |} +do_execsql_test skipscan1-1.51eqp { + EXPLAIN QUERY PLAN + SELECT x, a, b, c, d, '|' FROM t1j, t1 WHERE c=y ORDER BY +a; +} {/* INDEX t1abc (ANY(a) AND ANY(b) AND c=?)*/} + +do_execsql_test skipscan1-1.52 { + SELECT x, a, b, c, d, '|' FROM t1j LEFT JOIN t1 ON c=y ORDER BY +y, +a; +} {one {} {} {} {} | six abc 234 6 7 | six bcd 100 6 11 | ninty-nine {} {} {} {} |} +do_execsql_test skipscan1-1.52eqp { + EXPLAIN QUERY PLAN + SELECT x, a, b, c, d, '|' FROM t1j LEFT JOIN t1 ON c=y ORDER BY +y, +a; +} {/* INDEX t1abc (ANY(a) AND ANY(b) AND c=?)*/} + +do_execsql_test skipscan1-2.1 { + CREATE TABLE t2(a TEXT, b INT, c INT, d INT, + PRIMARY KEY(a,b,c)); + INSERT INTO t2 SELECT * FROM t1; + + /* Fake the sqlite_stat1 table so that the query planner believes + ** the table contains thousands of rows and that the first few + ** columns are not selective. */ + ANALYZE; + UPDATE sqlite_stat1 SET stat='10000 5000 2000 10' WHERE idx NOT NULL; + ANALYZE sqlite_master; +} {} + +do_execsql_test skipscan1-2.2 { + SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a; +} {abc 345 7 8 | def 345 9 10 |} +do_execsql_test skipscan1-2.2eqp { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a; +} {/* USING INDEX sqlite_autoindex_t2_1 (ANY(a) AND b=?)*/} +do_execsql_test skipscan1-2.2sort { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a; +} {~/*ORDER BY*/} + + +do_execsql_test skipscan1-3.1 { + CREATE TABLE t3(a TEXT, b INT, c INT, d INT, + PRIMARY KEY(a,b,c)) WITHOUT ROWID; + INSERT INTO t3 SELECT * FROM t1; + + /* Fake the sqlite_stat1 table so that the query planner believes + ** the table contains thousands of rows and that the first few + ** columns are not selective. */ + ANALYZE; + UPDATE sqlite_stat1 SET stat='10000 5000 2000 10' WHERE idx NOT NULL; + ANALYZE sqlite_master; +} {} + +do_execsql_test skipscan1-3.2 { + SELECT a,b,c,d,'|' FROM t3 WHERE b=345 ORDER BY a; +} {abc 345 7 8 | def 345 9 10 |} +do_execsql_test skipscan1-3.2eqp { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t3 WHERE b=345 ORDER BY a; +} {/* PRIMARY KEY (ANY(a) AND b=?)*/} +do_execsql_test skipscan1-3.2sort { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t3 WHERE b=345 ORDER BY a; +} {~/*ORDER BY*/} + +# Ticket 520070ec7fbaac: Array overrun in the skip-scan optimization +# 2013-12-22 +# +do_execsql_test skipscan1-4.1 { + CREATE TABLE t4(a,b,c,d,e,f,g,h,i); + CREATE INDEX t4all ON t4(a,b,c,d,e,f,g,h); + INSERT INTO t4 VALUES(1,2,3,4,5,6,7,8,9); + ANALYZE; + DELETE FROM sqlite_stat1; + INSERT INTO sqlite_stat1 + VALUES('t4','t4all','655360 163840 40960 10240 2560 640 160 40 10'); + ANALYZE sqlite_master; + SELECT i FROM t4 WHERE a=1; + SELECT i FROM t4 WHERE b=2; + SELECT i FROM t4 WHERE c=3; + SELECT i FROM t4 WHERE d=4; + SELECT i FROM t4 WHERE e=5; + SELECT i FROM t4 WHERE f=6; + SELECT i FROM t4 WHERE g=7; + SELECT i FROM t4 WHERE h=8; +} {9 9 9 9 9 9 9 9} + +# Make sure skip-scan cost computation in the query planner takes into +# account the fact that the seek must occur multiple times. +# +# Prior to 2014-03-10, the costs were computed incorrectly which would +# cause index t5i2 to be used instead of t5i1 on the skipscan1-5.3. +# +do_execsql_test skipscan1-5.1 { + CREATE TABLE t5( + id INTEGER PRIMARY KEY, + loc TEXT, + lang INTEGER, + utype INTEGER, + xa INTEGER, + xd INTEGER, + xh INTEGER + ); + CREATE INDEX t5i1 on t5(loc, xh, xa, utype, lang); + CREATE INDEX t5i2 ON t5(xd,loc,utype,lang); + EXPLAIN QUERY PLAN + SELECT xh, loc FROM t5 WHERE loc >= 'M' AND loc < 'N'; +} {/.*COVERING INDEX t5i1 .*/} +do_execsql_test skipscan1-5.2 { + ANALYZE; + DELETE FROM sqlite_stat1; + DROP TABLE IF EXISTS sqlite_stat4; + DROP TABLE IF EXISTS sqlite_stat3; + INSERT INTO sqlite_stat1 VALUES('t5','t5i1','2702931 3 2 2 2 2'); + INSERT INTO sqlite_stat1 VALUES('t5','t5i2','2702931 686 2 2 2'); + ANALYZE sqlite_master; +} {} +db cache flush +do_execsql_test skipscan1-5.3 { + EXPLAIN QUERY PLAN + SELECT xh, loc FROM t5 WHERE loc >= 'M' AND loc < 'N'; +} {/.*COVERING INDEX t5i1 .*/} + + + +finish_test |