CREATE TABLE t1(a TEXT, b TEXT);
INSERT INTO t1 VALUES('(0)', '(0)');
INSERT INTO t1 VALUES('(1)', '(1)');
INSERT INTO t1 VALUES('(2)', '(2)');
INSERT INTO t1 VALUES('(3)', '(3)');
INSERT INTO t1 VALUES('(4)', '(4)');
CREATE INDEX i1 ON t1(a, b);
ANALYZE;
SELECT tbl,idx,nEq,nLt,nDLt,test_decode(sample) FROM sqlite_stat4;
SELECT tbl,idx,nEq,nLt,nDLt,s(sample) FROM sqlite_stat4;
CREATE TABLE t1(a, b, c);
INSERT INTO t1 VALUES('some text', 14, NULL);
INSERT INTO t1 VALUES(22.0, NULL, x'656667');
CREATE INDEX i1 ON t1(a, b, c);
ANALYZE;
SELECT test_decode(sample) FROM sqlite_stat4;
CREATE TABLE t2(a, b);
CREATE INDEX i2 ON t2(a, b);
BEGIN;
SELECT count(*) FROM t2 GROUP BY a;
ANALYZE;
SELECT lindex(nEq, 0) FROM sqlite_stat4;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
INSERT INTO t1 VALUES(1, 1, 'one-a');
INSERT INTO t1 VALUES(11, 1, 'one-b');
INSERT INTO t1 VALUES(21, 1, 'one-c');
INSERT INTO t1 VALUES(31, 1, 'one-d');
INSERT INTO t1 VALUES(41, 1, 'one-e');
INSERT INTO t1 VALUES(51, 1, 'one-f');
INSERT INTO t1 VALUES(61, 1, 'one-g');
INSERT INTO t1 VALUES(71, 1, 'one-h');
INSERT INTO t1 VALUES(81, 1, 'one-i');
INSERT INTO t1 VALUES(91, 1, 'one-j');
INSERT INTO t1 SELECT a+1,2,'two' || substr(c,4) FROM t1;
INSERT INTO t1 SELECT a+2,3,'three'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
INSERT INTO t1 SELECT a+3,4,'four'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
INSERT INTO t1 SELECT a+4,5,'five'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
INSERT INTO t1 SELECT a+5,6,'six'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
CREATE INDEX t1b ON t1(b);
ANALYZE;
SELECT c FROM t1 WHERE b=3 AND a BETWEEN 30 AND 60;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a, b, c);
CREATE INDEX i1 ON t1(c, b, a);
SELECT
neq,
lrange(nlt, 0, 2),
lrange(ndlt, 0, 2),
lrange(test_decode(sample), 0, 2)
FROM sqlite_stat4
ORDER BY rowid LIMIT 16;
SELECT
neq,
lrange(nlt, 0, 2),
lrange(ndlt, 0, 2),
lrange(test_decode(sample), 0, 1)
FROM sqlite_stat4
ORDER BY rowid DESC LIMIT 2;
SELECT count(DISTINCT c) FROM t1 WHERE c<201
SELECT count(DISTINCT c) FROM t1 WHERE c<200
SELECT count(*) FROM sqlite_stat4
WHERE lindex(test_decode(sample), 3) IN
('34', '68', '102', '136', '170', '204', '238', '272')
SELECT test_decode(sample) FROM sqlite_stat4;
PRAGMA encoding = 'utf-16';
CREATE TABLE t0(v);
ANALYZE;
CREATE TABLE t1(a, b);
CREATE INDEX i1 ON t1(a);
CREATE INDEX i2 ON t1(b);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(2, 2);
INSERT INTO t1 VALUES(3, 3);
INSERT INTO t1 VALUES(4, 4);
INSERT INTO t1 VALUES(5, 5);
ANALYZE;
PRAGMA writable_schema = 1;
CREATE TEMP TABLE x1 AS
SELECT tbl,idx,neq,nlt,ndlt,sample FROM sqlite_stat4
ORDER BY (rowid%5), rowid;
DELETE FROM sqlite_stat4;
INSERT INTO sqlite_stat4 SELECT * FROM x1;
PRAGMA writable_schema = 0;
ANALYZE sqlite_master;
SELECT * FROM t1 WHERE a = 'abc';
CREATE TABLE t1(a, b);
CREATE INDEX i1 ON t1(a, b);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(2, 2);
INSERT INTO t1 VALUES(3, 3);
INSERT INTO t1 VALUES(4, 4);
INSERT INTO t1 VALUES(5, 5);
ANALYZE;
UPDATE sqlite_stat4 SET sample = X'' WHERE rowid = 1;
ANALYZE sqlite_master;
UPDATE sqlite_stat4 SET sample = X'FFFF';
ANALYZE sqlite_master;
SELECT * FROM t1 WHERE a = 1;
ANALYZE;
UPDATE sqlite_stat4 SET neq = '0 0 0';
ANALYZE sqlite_master;
SELECT * FROM t1 WHERE a = 1;
ANALYZE;
UPDATE sqlite_stat4 SET ndlt = '0 0 0';
ANALYZE sqlite_master;
SELECT * FROM t1 WHERE a = 3;
ANALYZE;
UPDATE sqlite_stat4 SET nlt = '0 0 0';
ANALYZE sqlite_master;
SELECT * FROM t1 WHERE a = 5;
CREATE TABLE t1(x TEXT);
CREATE INDEX i1 ON t1(x);
INSERT INTO t1 VALUES('1');
INSERT INTO t1 VALUES('2');
INSERT INTO t1 VALUES('3');
INSERT INTO t1 VALUES('4');
ANALYZE;
SELECT * FROM t1 WHERE x = 3;
CREATE TABLE t1(a, b, c, d, e);
CREATE INDEX i1 ON t1(a, b, c, d);
CREATE INDEX i2 ON t1(e);
DROP TABLE IF EXISTS t3;
CREATE TABLE t3(a, b);
CREATE INDEX t3a ON t3(a);
CREATE INDEX t3b ON t3(b);
DROP TABLE IF EXISTS t3;
CREATE TABLE t3(x, a, b);
CREATE INDEX t3a ON t3(x, a);
CREATE INDEX t3b ON t3(x, b);
SELECT lrange(test_decode(sample), 0, 1) AS s FROM sqlite_stat4
WHERE lindex(s, 1)=='1' ORDER BY rowid
CREATE TABLE x1(a, b, UNIQUE(a, b));
INSERT INTO x1 VALUES(1, 2);
INSERT INTO x1 VALUES(3, 4);
INSERT INTO x1 VALUES(5, 6);
ANALYZE;
INSERT INTO sqlite_stat4 VALUES(NULL, NULL, NULL, NULL, NULL, NULL);
SELECT * FROM x1
INSERT INTO sqlite_stat4 VALUES(42, 42, 42, 42, 42, 42);
SELECT * FROM x1
UPDATE sqlite_stat1 SET stat = NULL;
SELECT * FROM x1
ANALYZE;
UPDATE sqlite_stat1 SET tbl = 'no such tbl';
SELECT * FROM x1
ANALYZE;
UPDATE sqlite_stat4 SET neq = NULL, nlt=NULL, ndlt=NULL;
SELECT * FROM x1
ANALYZE;
UPDATE sqlite_stat1 SET stat = stat || ' unordered';
SELECT * FROM x1
CREATE INDEX i2 ON t1(c, d);
ANALYZE main.i2;
ANALYZE
CREATE TABLE t2(a, b);
CREATE INDEX i2 ON t2(a);
CREATE TABLE t3(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID;
SELECT * FROM t3;
WITH r(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM r WHERE x<=100
)
INSERT INTO t3 SELECT
CASE WHEN (x>45 AND x<96) THEN 'B' ELSE 'A' END, /* Column "a" */
x, /* Column "b" */
CASE WHEN (x<51) THEN 'one' ELSE 'two' END, /* Column "c" */
x /* Column "d" */
FROM r;
CREATE INDEX i3 ON t3(c);
CREATE INDEX i4 ON t3(d);
ANALYZE;
CREATE TABLE t4(
a COLLATE nocase, b, c,
d, e, f,
PRIMARY KEY(c, b, a)
) WITHOUT ROWID;
CREATE INDEX i41 ON t4(e);
CREATE INDEX i42 ON t4(f);
WITH data(a, b, c, d, e, f) AS (
SELECT int_to_char(0), 'xyz', 'zyx', '*', 0, 0
UNION ALL
SELECT
int_to_char(f+1), b, c, d, (e+1) % 2, f+1
FROM data WHERE f<1024
)
INSERT INTO t4 SELECT a, b, c, d, e, f FROM data;
ANALYZE;
CREATE TABLE t5(c, d, b, e, a, PRIMARY KEY(a, b, c)) WITHOUT ROWID;
WITH data(a, b, c, d, e) AS (
SELECT 'z', 'y', 0, 0, 0
UNION ALL
SELECT
a, CASE WHEN b='y' THEN 'n' ELSE 'y' END, c+1, e/250, e+1
FROM data
WHERE e<1000
)
INSERT INTO t5(a, b, c, d, e) SELECT * FROM data;
CREATE INDEX t5d ON t5(d);
CREATE INDEX t5e ON t5(e);
ANALYZE;
CREATE TABLE t6(a, b);
WITH ints(i,j) AS (
SELECT 1,1 UNION ALL SELECT i+1,j+1 FROM ints WHERE i<100
) INSERT INTO t6 SELECT * FROM ints;
CREATE INDEX aa ON t6(a);
CREATE INDEX bb ON t6(b);
ANALYZE;
SELECT count(*) FROM t1 WHERE x = 10000 AND y < 50;
SELECT count(*) FROM t1 WHERE z = 444;
BEGIN;
CREATE TABLE t1(x, y, z);
CREATE INDEX i1 ON t1(x, y);
CREATE INDEX i2 ON t1(z);
WITH
cnt(y) AS (SELECT 0 UNION ALL SELECT y+1 FROM cnt WHERE y<99),
letters(x) AS (
SELECT 'A' UNION SELECT 'B' UNION SELECT 'C' UNION SELECT 'D'
)
INSERT INTO t1(x, y) SELECT x, y FROM letters, cnt;
WITH
letters(x) AS (
SELECT 'A' UNION SELECT 'B' UNION SELECT 'C' UNION SELECT 'D'
)
INSERT INTO t1(x, y) SELECT x, 70 FROM letters;
WITH
cnt(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM cnt WHERE i<9999)
INSERT INTO t1(x, y) SELECT i, i FROM cnt;
UPDATE t1 SET z = (rowid / 95);
ANALYZE;
COMMIT;