0
0
mirror of https://github.com/tursodatabase/libsql.git synced 2024-12-16 12:38:46 +00:00
ad hoc ad79ef48a2
libsql wal tests (#1408)
* add tests

* add bench
2024-05-24 13:59:17 +00:00

126 lines
2.7 KiB
Plaintext

CREATE TABLE t1(a,b,c);
INSERT INTO t1(a,b,c)
VALUES(1,2,3),(7,8,9),(4,5,6),(10,11,12),(4,8,12),(1,11,111);
CREATE INDEX t1a ON t1(a);
CREATE INDEX t1b ON t1(b);
ANALYZE;
DELETE FROM sqlite_stat1;
INSERT INTO sqlite_stat1(tbl,idx,stat)
VALUES('t1','t1a','12345 2'),('t1','t1b','12345 4');
ANALYZE sqlite_master;
SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
EXPLAIN QUERY PLAN
SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
SELECT c FROM t1 ORDER BY a;
EXPLAIN QUERY PLAN
SELECT c FROM t1 ORDER BY a;
EXPLAIN QUERY PLAN
SELECT c FROM t1 ORDER BY a;
UPDATE sqlite_stat1 SET stat='12345 2 unordered' WHERE idx='t1a';
ANALYZE sqlite_master;
SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
EXPLAIN QUERY PLAN
SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
SELECT c FROM t1 ORDER BY a;
EXPLAIN QUERY PLAN
SELECT c FROM t1 ORDER BY a;
EXPLAIN QUERY PLAN
SELECT c FROM t1 ORDER BY a;
UPDATE sqlite_stat1 SET stat='12345 2 whatever=5 unordered xyzzy=11'
WHERE idx='t1a';
ANALYZE sqlite_master;
SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
EXPLAIN QUERY PLAN
SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
SELECT c FROM t1 ORDER BY a;
EXPLAIN QUERY PLAN
SELECT c FROM t1 ORDER BY a;
EXPLAIN QUERY PLAN
SELECT c FROM t1 ORDER BY a;
DROP INDEX t1a;
CREATE INDEX t1ab ON t1(a,b);
CREATE INDEX t1ca ON t1(c,a);
DELETE FROM sqlite_stat1;
INSERT INTO sqlite_stat1(tbl,idx,stat)
VALUES('t1','t1ab','12345 3 2 sz=10'),('t1','t1ca','12345 3 2 sz=20');
ANALYZE sqlite_master;
SELECT count(a) FROM t1;
EXPLAIN QUERY PLAN
SELECT count(a) FROM t1;
DELETE FROM sqlite_stat1;
INSERT INTO sqlite_stat1(tbl,idx,stat)
VALUES('t1','t1ab','12345 3 2 sz=20'),('t1','t1ca','12345 3 2 sz=10');
ANALYZE sqlite_master;
SELECT count(a) FROM t1;
EXPLAIN QUERY PLAN
SELECT count(a) FROM t1;
DROP TABLE IF EXISTS t44;
CREATE TABLE t44(a PRIMARY KEY);
INSERT INTO sqlite_stat1 VALUES('t44',null,'sz=0');
ANALYZE sqlite_master;
SELECT 0 FROM t44 WHERE a IN(1,2,3);
DELETE FROM sqlite_stat1;
INSERT INTO sqlite_stat1(tbl,idx,stat)
VALUES('t1','t1ab','12345 3 2 x=5 sz=10 y=10'),
('t1','t1ca','12345 3 2 whatever sz=20 junk');
ANALYZE sqlite_master;
SELECT count(a) FROM t1;
EXPLAIN QUERY PLAN
SELECT count(a) FROM t1;
DELETE FROM sqlite_stat1;
INSERT INTO sqlite_stat1(tbl,idx,stat)
VALUES('t1','t1ca','12345 3 2 x=5 sz=10 y=10'),
('t1','t1ab','12345 3 2 whatever sz=20 junk');
ANALYZE sqlite_master;
SELECT count(a) FROM t1;
EXPLAIN QUERY PLAN
SELECT count(a) FROM t1;