mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-15 20:20:20 +00:00
134 lines
3.1 KiB
Plaintext
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);
|
|
|