Index: test/skipscan1.test ================================================================== --- test/skipscan1.test +++ test/skipscan1.test @@ -342,7 +342,35 @@ do_execsql_test skipscan1-9.3 { EXPLAIN QUERY PLAN SELECT * FROM t9a WHERE b IN (SELECT x FROM t9b WHERE y!=5); } {/{SCAN TABLE t9a}/} optimization_control db skip-scan 1 + +do_execsql_test skipscan1-2.1 { + CREATE TABLE t6(a TEXT, b INT, c INT, d INT); + CREATE INDEX t6abc ON t6(a,b,c); + INSERT INTO t6 VALUES('abc',123,4,5); + + ANALYZE; + DELETE FROM sqlite_stat1; + INSERT INTO sqlite_stat1 VALUES('t6','t6abc','10000 5000 2000 10'); + ANALYZE sqlite_master; + DELETE FROM t6; +} {} + +do_execsql_test skipscan1-2.2eqp { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t6 WHERE d<>99 AND b=345 ORDER BY a; +} {/* USING INDEX t6abc (ANY(a) AND b=?)*/} +do_execsql_test skipscan1-2.2 { + SELECT a,b,c,d,'|' FROM t6 WHERE d<>99 AND b=345 ORDER BY a; +} {} + +do_execsql_test skipscan1-2.3eqp { + EXPLAIN QUERY PLAN + SELECT a,b,c,d,'|' FROM t6 WHERE d<>99 AND b=345 ORDER BY a DESC; +} {/* USING INDEX t6abc (ANY(a) AND b=?)*/} +do_execsql_test skipscan1-2.3 { + SELECT a,b,c,d,'|' FROM t6 WHERE d<>99 AND b=345 ORDER BY a DESC; +} {} finish_test Index: test/where.test ================================================================== --- test/where.test +++ test/where.test @@ -1436,6 +1436,107 @@ CREATE TABLE t2(x INTEGER PRIMARY KEY, y INT); INSERT INTO t2(y) VALUES(2),(3); SELECT * FROM t1, t2 WHERE a=y AND y=3; } {3 2 3} +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test where-24.0 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b); + INSERT INTO t1 VALUES(1, 'one'); + INSERT INTO t1 VALUES(2, 'two'); + INSERT INTO t1 VALUES(3, 'three'); + INSERT INTO t1 VALUES(4, 'four'); +} + +foreach {tn sql res} { + 1 "SELECT b FROM t1" {one two three four} + 2 "SELECT b FROM t1 WHERE a<4" {one two three} + 3 "SELECT b FROM t1 WHERE a>1" {two three four} + 4 "SELECT b FROM t1 WHERE a>1 AND a<4" {two three} + + 5 "SELECT b FROM t1 WHERE a>? AND a<4" {} + 6 "SELECT b FROM t1 WHERE a>1 AND a? AND a=? AND a<=4" {} + 8 "SELECT b FROM t1 WHERE a>=1 AND a<=?" {} + 9 "SELECT b FROM t1 WHERE a>=? AND a<=?" {} +} { + set rev [list] + foreach r $res { set rev [concat $r $rev] } + + do_execsql_test where-24.$tn.1 "$sql" $res + do_execsql_test where-24.$tn.2 "$sql ORDER BY rowid" $res + do_execsql_test where-24.$tn.3 "$sql ORDER BY rowid DESC" $rev + + do_execsql_test where-24-$tn.4 " + BEGIN; + DELETE FROM t1; + $sql; + $sql ORDER BY rowid; + $sql ORDER BY rowid DESC; + ROLLBACK; + " +} + +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test where-25.0 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + CREATE UNIQUE INDEX i1 ON t1(c); + INSERT INTO t1 VALUES(1, 'one', 'i'); + INSERT INTO t1 VALUES(2, 'two', 'ii'); + + CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c); + CREATE UNIQUE INDEX i2 ON t2(c); + INSERT INTO t2 VALUES(1, 'one', 'i'); + INSERT INTO t2 VALUES(2, 'two', 'ii'); + INSERT INTO t2 VALUES(3, 'three', 'iii'); + + PRAGMA writable_schema = 1; + UPDATE sqlite_master SET rootpage = ( + SELECT rootpage FROM sqlite_master WHERE name = 'i2' + ) WHERE name = 'i1'; +} +db close +sqlite3 db test.db +do_catchsql_test where-25.1 { + DELETE FROM t1 WHERE c='iii' +} {1 {database disk image is malformed}} +do_catchsql_test where-25.2 { + INSERT INTO t1 VALUES(4, 'four', 'iii') + ON CONFLICT(c) DO UPDATE SET b=NULL +} {1 {database disk image is malformed}} + +reset_db +do_execsql_test where-25.3 { + CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID; + CREATE UNIQUE INDEX i1 ON t1(c); + INSERT INTO t1 VALUES(1, 'one', 'i'); + INSERT INTO t1 VALUES(2, 'two', 'ii'); + + CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c); + CREATE UNIQUE INDEX i2 ON t2(c); + INSERT INTO t2 VALUES(1, 'one', 'i'); + INSERT INTO t2 VALUES(2, 'two', 'ii'); + INSERT INTO t2 VALUES(3, 'three', 'iii'); + + PRAGMA writable_schema = 1; + UPDATE sqlite_master SET rootpage = ( + SELECT rootpage FROM sqlite_master WHERE name = 'i2' + ) WHERE name = 'i1'; +} +db close +sqlite3 db test.db +do_catchsql_test where-25.4 { + SELECT * FROM t1 WHERE c='iii' +} {0 {}} +do_catchsql_test where-25.5 { + INSERT INTO t1 VALUES(4, 'four', 'iii') + ON CONFLICT(c) DO UPDATE SET b=NULL +} {1 {corrupt database}} + finish_test +