SELECT count(*) FROM t1;
DROP INDEX IF EXISTS bad1;
DROP INDEX t2a1;
UPDATE t2 SET a=b, b=b+10000;
SELECT b FROM t2 WHERE a=15;
CREATE INDEX t2a2 ON t2(a) WHERE a<100 OR a>200;
SELECT b FROM t2 WHERE a=15;
PRAGMA integrity_check;
EXPLAIN QUERY PLAN
SELECT b FROM t2 WHERE a=15;
SELECT b FROM t2 WHERE a=15 AND a<100;
EXPLAIN QUERY PLAN
SELECT b FROM t2 WHERE a=15 AND a<100;
SELECT b FROM t2 WHERE a=515 AND a>200;
EXPLAIN QUERY PLAN
SELECT b FROM t2 WHERE a=515 AND a>200;
CREATE TABLE t3(a,b);
INSERT INTO t3 SELECT value, value FROM nums WHERE value<200;
UPDATE t3 SET a=999 WHERE b%5!=0;
CREATE UNIQUE INDEX t3a ON t3(a) WHERE a<>999;
SELECT count(*) FROM t3 WHERE a=999;
VACUUM;
PRAGMA integrity_check;
CREATE INDEX t3b ON t3(b) WHERE xyzzy.t3.b BETWEEN 5 AND 10;
/* ^^^^^-- ignored */
ANALYZE;
SELECT count(*) FROM t3 WHERE t3.b BETWEEN 5 AND 10;
SELECT stat+0 FROM sqlite_stat1 WHERE idx='t3b';
CREATE TABLE t6(a,b);
CREATE UNIQUE INDEX t6ab ON t1(a,b);
CREATE INDEX t6b ON t6(b) WHERE b=1;
INSERT INTO t6(a,b) VALUES(123,456);
SELECT * FROM t6;
UPDATE OR REPLACE t6 SET b=789;
SELECT * FROM t6;
PRAGMA integrity_check;
CREATE TABLE t7a(x);
CREATE TABLE t7b(y);
INSERT INTO t7a(x) VALUES(1);
CREATE INDEX t7ax ON t7a(x) WHERE x=99;
PRAGMA automatic_index=OFF;
SELECT * FROM t7a LEFT JOIN t7b ON (x=99) ORDER BY x;
INSERT INTO t7b(y) VALUES(2);
SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
INSERT INTO t7a(x) VALUES(99);
SELECT * FROM t7a LEFT JOIN t7b ON (x=99) ORDER BY x;
SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
EXPLAIN QUERY PLAN
SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
CREATE TABLE t8a(a,b);
CREATE TABLE t8b(x,y);
CREATE INDEX i8c ON t8b(y) WHERE x = 'value';
INSERT INTO t8a VALUES(1, 'one');
INSERT INTO t8a VALUES(2, 'two');
INSERT INTO t8a VALUES(3, 'three');
INSERT INTO t8b VALUES('value', 1);
INSERT INTO t8b VALUES('dummy', 2);
INSERT INTO t8b VALUES('value', 3);
INSERT INTO t8b VALUES('dummy', 4);
SELECT * FROM t8a LEFT JOIN t8b ON (x = 'value' AND y = a)
CREATE TABLE t9(a int, b int, c int);
CREATE INDEX t9ca ON t9(c,a) WHERE a in (10,12,20);
INSERT INTO t9 VALUES(1,1,9),(10,2,35),(11,15,82),(20,19,5),(NULL,7,3);
UPDATE t9 SET b=c WHERE a in (10,12,20);
SELECT a,b,c,'|' FROM t9 ORDER BY a;
DROP TABLE t9;
CREATE TABLE t9(a int, b int, c int, PRIMARY KEY(a)) WITHOUT ROWID;
CREATE INDEX t9ca ON t9(c,a) WHERE a in (10,12,20);
INSERT INTO t9 VALUES(1,1,9),(10,2,35),(11,15,82),(20,19,5);
UPDATE t9 SET b=c WHERE a in (10,12,20);
SELECT a,b,c,'|' FROM t9 ORDER BY a;
CREATE TABLE t10(a,b,c,d,e INTEGER PRIMARY KEY);
INSERT INTO t10 VALUES
(1,2,3,4,5),
(2,3,4,5,6),
(3,4,5,6,7),
(1,2,3,8,9);
CREATE INDEX t10x ON t10(d) WHERE a=1 AND b=2 AND c=3;
SELECT e FROM t10 WHERE a=1 AND b=2 AND c=3 ORDER BY d;
EXPLAIN QUERY PLAN
SELECT e FROM t10 WHERE a=1 AND b=2 AND c=3 ORDER BY d;
SELECT e FROM t10 WHERE c=3 AND 2=b AND a=1 ORDER BY d DESC;
EXPLAIN QUERY PLAN
SELECT e FROM t10 WHERE c=3 AND 2=b AND a=1 ORDER BY d DESC;
SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC;
EXPLAIN QUERY PLAN
SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC;
CREATE TABLE t11(a,b,c);
CREATE INDEX t11x ON t11(a) WHERE b<>99;
EXPLAIN QUERY PLAN SELECT a FROM t11 WHERE b<>99;
EXPLAIN QUERY PLAN SELECT a FROM t11 WHERE b<>99 AND c<>98;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1(a,b);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t1 VALUES(2,2);
CREATE TABLE t2(x);
INSERT INTO t2 VALUES(1);
INSERT INTO t2 VALUES(2);
SELECT 'one', * FROM t2 WHERE x NOT IN (SELECT a FROM t1);
CREATE INDEX t1a ON t1(a) WHERE b=1;
SELECT 'two', * FROM t2 WHERE x NOT IN (SELECT a FROM t1);
SELECT x FROM t2 WHERE x IN (SELECT a FROM t1) ORDER BY +x;
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0);
CREATE INDEX index_0 ON t0(c0) WHERE c0 NOT NULL;
INSERT INTO t0(c0) VALUES (NULL);
SELECT * FROM t0 WHERE c0 OR 1;
CREATE TABLE IF NOT EXISTS t0 (c0, c1);
CREATE INDEX IF NOT EXISTS i0 ON t0(c0, c1) WHERE c0 NOT NULL;
INSERT INTO t0(c0, c1) VALUES(NULL, 'row');
SELECT * FROM t0 WHERE t0.c0 IS NOT 1;
SELECT * FROM t0 WHERE CASE c0 WHEN 0 THEN 0 ELSE 1 END;
DROP TABLE t0;
CREATE TABLE t0(c0);
INSERT INTO t0(c0) VALUES (NULL);
CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL;
SELECT 1 FROM t0 WHERE (t0.c0 IS FALSE) IS FALSE;
SELECT 1 FROM t0 WHERE (t0.c0 IS FALSE) BETWEEN FALSE AND TRUE;
SELECT 1 FROM t0 WHERE TRUE BETWEEN (t0.c0 IS FALSE) AND TRUE;
SELECT 1 FROM t0 WHERE FALSE BETWEEN FALSE AND (t0.c0 IS FALSE);
SELECT 1 FROM t0 WHERE (c0 IS FALSE) IN (FALSE);
DROP TABLE t0;
CREATE TABLE t0(c0 COLLATE NOCASE, c1);
CREATE INDEX i0 ON t0(0) WHERE c0 >= c1;
INSERT INTO t0 VALUES('a', 'B');
SELECT c1 <= c0, c0 >= c1 FROM t0;
SELECT 2 FROM t0 WHERE c0 >= c1;
SELECT 3 FROM t0 WHERE c1 <= c0;
CREATE TABLE t0(c0);
CREATE INDEX i0 ON t0(0) WHERE c0 GLOB c0;
INSERT INTO t0 VALUES (0);
CREATE UNIQUE INDEX i1 ON t0(0);
PRAGMA integrity_check;
CREATE UNIQUE INDEX i2 ON t0(0);
REPLACE INTO t0 VALUES(0);
PRAGMA integrity_check;
SELECT COUNT(*) FROM t0 WHERE t0.c0 GLOB t0.c0;
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES(10,10);
CREATE UNIQUE INDEX t1b ON t1(b) WHERE a>NULL;
SELECT * FROM t1 WHERE a IS NOT NULL;
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1(a) VALUES(2);
CREATE TABLE t2(c INT);
CREATE INDEX i0 ON t2(c) WHERE c=3;
CREATE TABLE t3(d INT);
INSERT INTO t3 VALUES(1);
SELECT * FROM t2 RIGHT JOIN t3 ON d<>0 LEFT JOIN t1 ON c=3 WHERE t1.a<>0;