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

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

94 lines
2.1 KiB
Plaintext

CREATE TABLE t1(a COLLATE nocase);
INSERT INTO t1 VALUES('one');
INSERT INTO t1 VALUES('ONE');
SELECT count(*) FROM t1 WHERE a COLLATE BINARY IN (SELECT DISTINCT a FROM t1)
CREATE TABLE t3(a, b);
INSERT INTO t3 VALUES(1, 1);
INSERT INTO t3 VALUES(1, 2);
INSERT INTO t3 VALUES(1, 3);
INSERT INTO t3 VALUES(2, 4);
INSERT INTO t3 VALUES(2, 5);
INSERT INTO t3 VALUES(2, 6);
INSERT INTO t3 VALUES(3, 7);
INSERT INTO t3 VALUES(3, 8);
INSERT INTO t3 VALUES(3, 9);
SELECT count(*) FROM t3 WHERE b IN (SELECT DISTINCT a FROM t3 LIMIT 5);
SELECT count(*) FROM t3 WHERE b IN (SELECT a FROM t3 LIMIT 5);
CREATE TABLE x1(a);
CREATE TABLE x2(b);
INSERT INTO x1 VALUES(1), (1), (2);
INSERT INTO x2 VALUES(1), (2);
SELECT count(*) FROM x2 WHERE b IN (SELECT DISTINCT a FROM x1 LIMIT 2);
CREATE TABLE y1(a, b);
CREATE TABLE y2(c);
INSERT INTO y1 VALUES(1, 'one');
INSERT INTO y1 VALUES('two', 'two');
INSERT INTO y1 VALUES(3, 'three');
INSERT INTO y2 VALUES('one');
INSERT INTO y2 VALUES('two');
INSERT INTO y2 VALUES('three');
SELECT a FROM y1 WHERE b NOT IN (SELECT a FROM y2);
SELECT a FROM y1 WHERE b IN (SELECT a FROM y2);
CREATE INDEX y2c ON y2(c);
SELECT a FROM y1 WHERE b NOT IN (SELECT a FROM y2);
SELECT a FROM y1 WHERE b IN (SELECT a FROM y2);
CREATE TABLE n1(a INTEGER PRIMARY KEY, b VARCHAR(500));
CREATE UNIQUE INDEX n1a ON n1(a);
SELECT count(*) FROM n1 WHERE a IN (1, 2, 3)
SELECT count(*) FROM n1 WHERE a IN (SELECT +a FROM n1)
INSERT INTO n1 VALUES(1, NULL), (2, NULL), (3, NULL);
SELECT count(*) FROM n1 WHERE a IN (1, 2, 3)
SELECT count(*) FROM n1 WHERE a IN (SELECT +a FROM n1)
CREATE TABLE t9(a INTEGER PRIMARY KEY);
INSERT INTO t9 VALUES (44), (45);
SELECT * FROM t9 WHERE a IN (44, 45, 44, 45)
CREATE TABLE t0(c0);
CREATE VIEW v0(c0) AS SELECT LOWER(CAST('1e500' AS TEXT)) FROM t0;
INSERT INTO t0(c0) VALUES (NULL);
SELECT lower('1e500') FROM t0 WHERE rowid NOT IN (0, 0, lower('1e500'));
SELECT lower('1e500') FROM t0 WHERE rowid != lower('1e500');