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

134 lines
3.1 KiB
Plaintext

PRAGMA encoding=UTF8;
CREATE TABLE t1(a,b TEXT COLLATE nocase);
INSERT INTO t1(a,b)
VALUES(1,'abc'),
(2,'ABX'),
(3,'BCD'),
(4,x'616263'),
(5,x'414258'),
(6,x'424344');
CREATE INDEX t1ba ON t1(b,a);
SELECT a, b FROM t1 WHERE b LIKE 'aB%' ORDER BY +a;
SELECT a, b FROM t1 WHERE +b LIKE 'aB%' ORDER BY +a;
CREATE TABLE t2(a, b TEXT);
INSERT INTO t2 SELECT a, b FROM t1;
CREATE INDEX t2ba ON t2(b,a);
SELECT a, b FROM t2 WHERE b GLOB 'ab*' ORDER BY +a;
SELECT a, b FROM t2 WHERE +b GLOB 'ab*' ORDER BY +a;
SELECT a, b FROM t2 WHERE b>=x'6162' AND b GLOB 'ab*'
SELECT a, b FROM t2 WHERE +b>=x'6162' AND +b GLOB 'ab*'
SELECT a, b FROM t2 WHERE b GLOB 'ab*' AND b>=x'6162'
SELECT a, b FROM t2 WHERE +b GLOB 'ab*' AND +b>=x'6162'
CREATE TABLE t3(x TEXT PRIMARY KEY COLLATE nocase);
INSERT INTO t3(x) VALUES('aaa'),('abc'),('abd'),('abe'),('acz');
INSERT INTO t3(x) SELECT CAST(x AS blob) FROM t3;
SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x;
SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x DESC;
SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x DESC;
SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x ASC;
SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x ASC;
CREATE TABLE t4(x TEXT COLLATE nocase);
CREATE INDEX t4x ON t4(x DESC);
INSERT INTO t4(x) SELECT x FROM t3;
SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x;
SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x DESC;
SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x DESC;
SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x ASC;
SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x ASC;
CREATE TABLE t5a(x INT UNIQUE COLLATE nocase);
INSERT INTO t5a(x) VALUES('/abc'),(123),(-234);
SELECT x FROM t5a WHERE x LIKE '/%';
SELECT x FROM t5a WHERE x LIKE '/a%';
SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';
SELECT x FROM t5a WHERE x LIKE '^-2%' ESCAPE '^';
CREATE TABLE t5b(x INT UNIQUE COLLATE binary);
INSERT INTO t5b(x) VALUES('/abc'),(123),(-234);
SELECT x FROM t5b WHERE x GLOB '/*';
SELECT x FROM t5b WHERE x GLOB '/a*';
CREATE TABLE t5c (c0 REAL);
CREATE INDEX t5c_0 ON t5c(c0 COLLATE NOCASE);
INSERT INTO t5c(rowid, c0) VALUES (99,'+/');
SELECT * FROM t5c WHERE (c0 LIKE '+/');
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE);
INSERT INTO t0(c0) VALUES ('./');
SELECT * FROM t0 WHERE t0.c0 LIKE './';
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE);
INSERT INTO t0(c0) VALUES ('.1%');
SELECT * FROM t0 WHERE t0.c0 LIKE '.1%';
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 UNIQUE);
INSERT INTO t0(c0) VALUES(-1);
SELECT * FROM t0 WHERE t0.c0 GLOB '-*';
SELECT t0.c0 GLOB '-*' FROM t0;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(path TEXT COLLATE nocase PRIMARY KEY,a,b,c) WITHOUT ROWID;
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(path TEXT,x,y,z);
CREATE INDEX t2path ON t2(path COLLATE nocase);
CREATE INDEX t2path2 ON t2(path);