mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-15 07:29:41 +00:00
328 lines
7.4 KiB
Plaintext
328 lines
7.4 KiB
Plaintext
|
|
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;
|
|
|