mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-15 19:09:41 +00:00
ad79ef48a2
* add tests * add bench
181 lines
4.7 KiB
Plaintext
181 lines
4.7 KiB
Plaintext
|
|
CREATE TABLE t181(a);
|
|
CREATE TABLE t182(b,c);
|
|
INSERT INTO t181 VALUES(1);
|
|
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL;
|
|
|
|
|
|
SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY c IS NULL;
|
|
|
|
|
|
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
|
|
|
|
|
|
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c;
|
|
|
|
|
|
SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY c;
|
|
|
|
|
|
INSERT INTO t181 VALUES(1),(1),(1),(1);
|
|
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
|
|
|
|
|
|
SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY +c;
|
|
|
|
|
|
INSERT INTO t181 VALUES(2);
|
|
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a;
|
|
|
|
|
|
INSERT INTO t181 VALUES(2);
|
|
SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +a, +c IS NULL;
|
|
|
|
|
|
CREATE TABLE t191(a INT UNIQUE NOT NULL, b INT UNIQUE NOT NULL,c,d);
|
|
CREATE INDEX t191a ON t1(a);
|
|
CREATE INDEX t191b ON t1(b);
|
|
CREATE TABLE t192(x INTEGER PRIMARY KEY,y INT, z INT);
|
|
|
|
EXPLAIN QUERY PLAN
|
|
SELECT t191.rowid FROM t192, t191 WHERE (a=y OR b=y) AND x=?1;
|
|
|
|
|
|
CREATE TABLE t201(x);
|
|
CREATE TABLE t202(y, z);
|
|
INSERT INTO t201 VALUES('key');
|
|
INSERT INTO t202 VALUES('key', -1);
|
|
CREATE INDEX t202i ON t202(y, ifnull(z, 0));
|
|
SELECT count(*) FROM t201 LEFT JOIN t202 ON (x=y) WHERE ifnull(z, 0) >=0;
|
|
|
|
|
|
CREATE TABLE t12(a, b, c);
|
|
CREATE TABLE t13(x);
|
|
CREATE INDEX t12ab ON t12(b, a);
|
|
CREATE INDEX t12ac ON t12(c, a);
|
|
|
|
INSERT INTO t12 VALUES(4, 0, 1);
|
|
INSERT INTO t12 VALUES(4, 1, 0);
|
|
INSERT INTO t12 VALUES(5, 0, 1);
|
|
INSERT INTO t12 VALUES(5, 1, 0);
|
|
|
|
INSERT INTO t13 VALUES(1), (2), (3), (4);
|
|
|
|
|
|
SELECT * FROM t12 WHERE
|
|
a = (SELECT * FROM (SELECT count(*) FROM t13 LIMIT 5) ORDER BY 1 LIMIT 10)
|
|
AND (b=1 OR c=1);
|
|
|
|
|
|
CREATE TABLE t1(a INT);
|
|
CREATE INDEX t1a ON t1(a);
|
|
INSERT INTO t1(a) VALUES(NULL),(NULL),(42),(NULL),(NULL);
|
|
CREATE TABLE t2(dummy INT);
|
|
SELECT count(*) FROM t1 LEFT JOIN t2 ON a IS NOT NULL;
|
|
|
|
|
|
DROP TABLE IF EXISTS t1;
|
|
DROP TABLE IF EXISTS t2;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY);
|
|
INSERT INTO t1(a) VALUES(1),(2),(3);
|
|
CREATE TABLE t2(x INTEGER PRIMARY KEY, y INT);
|
|
INSERT INTO t2(y) VALUES(2),(3);
|
|
SELECT * FROM t1, t2 WHERE a=y AND y=3;
|
|
|
|
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
|
|
INSERT INTO t1 VALUES(1, 'one');
|
|
INSERT INTO t1 VALUES(2, 'two');
|
|
INSERT INTO t1 VALUES(3, 'three');
|
|
INSERT INTO t1 VALUES(4, 'four');
|
|
|
|
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
|
|
CREATE UNIQUE INDEX i1 ON t1(c);
|
|
INSERT INTO t1 VALUES(1, 'one', 'i');
|
|
INSERT INTO t1 VALUES(2, 'two', 'ii');
|
|
|
|
CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c);
|
|
CREATE UNIQUE INDEX i2 ON t2(c);
|
|
INSERT INTO t2 VALUES(1, 'one', 'i');
|
|
INSERT INTO t2 VALUES(2, 'two', 'ii');
|
|
INSERT INTO t2 VALUES(3, 'three', 'iii');
|
|
|
|
PRAGMA writable_schema = 1;
|
|
UPDATE sqlite_schema SET rootpage = (
|
|
SELECT rootpage FROM sqlite_schema WHERE name = 'i2'
|
|
) WHERE name = 'i1';
|
|
|
|
|
|
CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID;
|
|
CREATE UNIQUE INDEX i1 ON t1(c);
|
|
INSERT INTO t1 VALUES(1, 'one', 'i');
|
|
INSERT INTO t1 VALUES(2, 'two', 'ii');
|
|
|
|
CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c);
|
|
CREATE UNIQUE INDEX i2 ON t2(c);
|
|
INSERT INTO t2 VALUES(1, 'one', 'i');
|
|
INSERT INTO t2 VALUES(2, 'two', 'ii');
|
|
INSERT INTO t2 VALUES(3, 'three', 'iii');
|
|
|
|
PRAGMA writable_schema = 1;
|
|
UPDATE sqlite_schema SET rootpage = (
|
|
SELECT rootpage FROM sqlite_schema WHERE name = 'i2'
|
|
) WHERE name = 'i1';
|
|
|
|
|
|
CREATE TABLE t0(c0 INTEGER PRIMARY KEY, c1 TEXT);
|
|
INSERT INTO t0(c0, c1) VALUES (1, 'a');
|
|
CREATE TABLE t1(c0 INT PRIMARY KEY, c1 TEXT);
|
|
INSERT INTO t1(c0, c1) VALUES (1, 'a');
|
|
SELECT * FROM t0 WHERE '-1' BETWEEN 0 AND t0.c0;
|
|
|
|
|
|
SELECT * FROM t1 WHERE '-1' BETWEEN 0 AND t1.c0;
|
|
|
|
|
|
SELECT * FROM t0 WHERE '-1'>=0 AND '-1'<=t0.c0;
|
|
|
|
|
|
SELECT * FROM t1 WHERE '-1'>=0 AND '-1'<=t1.c0;
|
|
|
|
|
|
SELECT '-1' BETWEEN 0 AND t0.c0 FROM t0;
|
|
|
|
|
|
SELECT '-1' BETWEEN 0 AND t1.c0 FROM t1;
|
|
|
|
|
|
SELECT '-1'>=0 AND '-1'<=t0.c0 FROM t0;
|
|
|
|
|
|
SELECT '-1'>=0 AND '-1'<=t1.c0 FROM t1;
|
|
|
|
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY);
|
|
INSERT INTO t1(a) VALUES(9223372036854775807);
|
|
SELECT 1 FROM t1 WHERE a>=(9223372036854775807+1);
|
|
|
|
|
|
SELECT a>=9223372036854775807+1 FROM t1;
|
|
|
|
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);
|
|
CREATE INDEX t1b ON t1(b,b,b,b,b,b,b,b,b,b,b,b,b);
|
|
INSERT INTO t1(a,b) VALUES(1,1),(15,2),(19,5);
|
|
UPDATE t1 SET b=999 WHERE a IN (SELECT 15) AND b IN (1,2);
|
|
SELECT * FROM t1;
|
|
|
|
|
|
SELECT DISTINCT 'xyz' FROM pragma_cache_size
|
|
WHERE rowid OR abs(0)
|
|
ORDER BY
|
|
1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
|
|
1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
|
|
1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
|
|
1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
|
|
1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
|
|
1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
|
|
1, 1, 1, 1;
|
|
|