libsql/libsql-wal/tests/assets/fixtures/index6.test

  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;