1839 lines
64 KiB
Markdown
1839 lines
64 KiB
Markdown
## Summary
|
|
<!--Explain briefly what goes wrong and explain why you believe this is a bug and not the intended behavior of SQLite (if it is not a crash).-->**No review yet**
|
|
|
|
## Minimized query
|
|
|
|
```sql
|
|
PRAGMA locking_mode = 'NORMAL';
|
|
ATTACH DATABASE ':memory:' AS aux40;
|
|
PRAGMA journal_size_limit = 1000000;
|
|
.width -(-(1)) 2 3
|
|
PRAGMA table_list("users");
|
|
.timeout json_set(NULL, '$.new_key', NULL)
|
|
PRAGMA parser_trace = OFF;
|
|
-- zeroblob({v})
|
|
|
|
.stats off
|
|
SAVEPOINT sp800;
|
|
SAVEPOINT sp1369;
|
|
ATTACH DATABASE ':memory:' AS aux20;
|
|
PRAGMA short_column_names = YES;
|
|
.progress CHARINT('Inf', '+1 day')
|
|
.shell pwd
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b INTEGER,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (('a' || ''),ceiling(x'c5cb906e1c8cc13d197d23f8b3cc1209'),NULL), ('a',-zeroblob(','),-'%'), ('b',CAST(abs(NULL) AS INT8),4.5);
|
|
SELECT a,b,c,
|
|
SUM(c) OVER (
|
|
PARTITION BY a
|
|
ORDER BY b
|
|
) AS d
|
|
FROM T;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (like('_1%', 1, '_'), 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
PRAGMA writable_schema = ON;
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
.quit
|
|
BEGIN DEFERRED;
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES ('a',100.0), ('b',-200.5), ('c',9e999);
|
|
SELECT a,b,
|
|
LEAD(b,1) OVER (ORDER BY b DESC) AS c,
|
|
NTILE(2) OVER (ORDER BY b DESC) AS d FROM T;
|
|
PRAGMA cache_spill = NO;
|
|
PRAGMA vdbe_listing = inf;
|
|
PRAGMA empty_result_callbacks = 0;
|
|
.timeout
|
|
CREATE TABLE [p1](a PRIMARY KEY, b UNIQUE);
|
|
CREATE TABLE "c1"(x, y, z REFERENCES p1(c));
|
|
CREATE TABLE c2(x, y, z, w REFERENCES p1(b));
|
|
ALTER TABLE [c1] DROP COLUMN z;
|
|
ALTER TABLE c2 DROP COLUMN z;
|
|
SELECT sql FROM sqlite_schema WHERE NOT NOT NOT (name IN ('c1', 'c2')) ;
|
|
COMMIT TRANSACTION;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE +x<31) SELECT x FROM cnt WHERE +x > 15;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_c1_2917 AS SELECT z FROM c1;
|
|
SELECT * FROM c2 AS a NATURAL JOIN c1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM p1;
|
|
UPDATE c2 SET z = NULL WHERE rowid = 1;
|
|
CREATE INDEX IF NOT EXISTS idx_c2_3327 ON c2(lower(x)) WHERE x > 0;
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE y IS NOT NULL), SUM(rowid) FILTER (WHERE y > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(y) FILTER (WHERE y > 0 AND y < 100), COUNT(*) FILTER (WHERE typeof(y) = "text") FROM c1;
|
|
REINDEX p1;
|
|
ALTER TABLE c2 RENAME COLUMN w TO w_r831;
|
|
CREATE TABLE T1 (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15) UNIQUE,
|
|
C BIGINT
|
|
);
|
|
CREATE TABLE T2 (
|
|
X VARCHAR(20) PRIMARY KEY,
|
|
A VARCHAR(10),
|
|
FOREIGN KEY (A) REFERENCES T1(A)
|
|
);
|
|
INSERT INTO T1 VALUES ('a', 'p', 9999999999999);
|
|
INSERT INTO T1 VALUES ('b', 'q', -9999999999999);
|
|
SELECT 0x0ACB29BM205381;
|
|
SELECT sqlite_compileoption_used('æçå¤©ä½ æ¯æä¹äº');
|
|
SELECT 1 >> -999999999999999999999999999;
|
|
SELECT sqlite_compileoption_get($1);
|
|
SELECT sqlite_version();
|
|
INSERT INTO T2 VALUES ('m', 'a');
|
|
INSERT INTO T2 VALUES ('n', 'b');
|
|
SELECT T2.X, T1.C FROM T2 INNER JOIN T1 ON T2.A = T1.A WHERE T1.C > -1000000000000;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM c2 t1 LEFT JOIN c2 t2 ON t1.w = (SELECT w FROM c2 WHERE w = t1.w);
|
|
ALTER TABLE p1 DROP COLUMN b;
|
|
ANALYZE;
|
|
SELECT COUNT(*) FROM T2;
|
|
INSERT INTO T1 DEFAULT VALUES;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
SELECT * FROM t1 AS a RIGHT JOIN c2 AS b ON a.rowid = b.rowid;
|
|
DETACH DATABASE aux20;
|
|
RELEASE sp1369;
|
|
SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM T;
|
|
DROP VIEW IF EXISTS T1;
|
|
INSERT INTO p1 DEFAULT VALUES;
|
|
|
|
SELECT COUNT(*) FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM c2;
|
|
ALTER TABLE c1 RENAME COLUMN z TO z_r2716;
|
|
SELECT * FROM c2;
|
|
DROP INDEX IF EXISTS p1;
|
|
RELEASE sp800;
|
|
|
|
SELECT COUNT(*) FROM c1;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
ANALYZE;
|
|
INSERT INTO c1 SELECT * FROM c1;
|
|
INSERT INTO p1 VALUES (NULL, NULL);
|
|
DROP TABLE IF EXISTS T2;
|
|
|
|
SELECT COUNT(*) FROM T1;
|
|
REINDEX;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T2_6563 AFTER UPDATE OF A ON T2 BEGIN INSERT INTO T2(A) VALUES (NULL); END;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T_8673 AS SELECT a FROM T;
|
|
WITH cte AS (SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T1 WHERE A > 0)) SELECT * FROM cte;
|
|
ALTER TABLE T2 RENAME COLUMN X TO X_r1036;
|
|
ALTER TABLE T ADD COLUMN extra_3319 SMALLINT DEFAULT (random());
|
|
SELECT * FROM c1;
|
|
INSERT INTO T2 SELECT * FROM T2;
|
|
CREATE TABLE T1 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
CREATE TABLE T2 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
INSERT INTO T1 VALUES (1,'a'),(2,'b');
|
|
INSERT INTO T2 VALUES (1,'c'),(1,'d');
|
|
SELECT T1.b,
|
|
(
|
|
SELECT GROUP_CONCAT(b,',')
|
|
FROM T2
|
|
WHERE T2.a=T1.a
|
|
) AS c
|
|
FROM T1;
|
|
INSERT INTO c2 DEFAULT VALUES;
|
|
SELECT * FROM T2;
|
|
.timeout 999999
|
|
PRAGMA parser_trace = OFF;
|
|
-- zeroblob({v})
|
|
|
|
.stats off
|
|
SAVEPOINT sp800;
|
|
SAVEPOINT sp1369;
|
|
ATTACH DATABASE ':memory:' AS aux20;
|
|
PRAGMA short_column_names = YES;
|
|
.progress datetime('Inf', '+1 day')
|
|
.shell pwd
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b INTEGER,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (('a' || ''),ceiling(x'c5cb906e1c8cc13d197d23f8b3cc1209'),NULL), ('a',-zeroblob(','),-'%'), ('b',CAST(3.141592653589793 AS INT8),4.5);
|
|
SELECT a,b,c,
|
|
SUM(c) OVER (
|
|
PARTITION BY a
|
|
ORDER BY b
|
|
) AS d
|
|
FROM T;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (like('_1%', 1, '_'), 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
PRAGMA writable_schema = ON;
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
.quit
|
|
BEGIN DEFERRED;
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES ('a',100.0), ('b',-200.5), ('c',9e999);
|
|
SELECT a,b,
|
|
LEAD(b,1) OVER (ORDER BY b DESC) AS c,
|
|
NTILE(2) OVER (ORDER BY b DESC) AS d FROM T;
|
|
PRAGMA cache_spill = NO;
|
|
PRAGMA vdbe_listing = inf;
|
|
PRAGMA empty_result_callbacks = 0;
|
|
.timeout
|
|
CREATE TABLE [p1](a PRIMARY KEY, b UNIQUE);
|
|
CREATE TABLE c1(x, y, z REFERENCES p1(c));
|
|
CREATE TABLE c2(x, y, z, w REFERENCES p1(b));
|
|
ALTER TABLE c1 DROP COLUMN z;
|
|
ALTER TABLE c2 DROP COLUMN z;
|
|
SELECT sql FROM sqlite_schema WHERE name IN ('c1', 'c2');
|
|
COMMIT TRANSACTION;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE +x<31) SELECT x FROM cnt WHERE +x > 15;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_c1_2917 AS SELECT z FROM c1;
|
|
SELECT * FROM c2 AS a FULL OUTER JOIN c1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM p1;
|
|
UPDATE c2 SET z = NULL WHERE rowid = 1;
|
|
CREATE INDEX IF NOT EXISTS idx_c2_3327 ON c2(lower(x)) WHERE x > 0;
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE y IS NOT NULL), SUM(rowid) FILTER (WHERE y > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(y) FILTER (WHERE y > 0 AND y < 100), COUNT(*) FILTER (WHERE typeof(y) = "text") FROM c1;
|
|
REINDEX p1;
|
|
ALTER TABLE c2 RENAME COLUMN w TO w_r831;
|
|
CREATE TABLE T1 (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15) UNIQUE,
|
|
C BIGINT
|
|
);
|
|
CREATE TABLE T2 (
|
|
X VARCHAR(20) PRIMARY KEY,
|
|
A VARCHAR(10),
|
|
FOREIGN KEY (A) REFERENCES T1(A)
|
|
);
|
|
INSERT INTO T1 VALUES ('a', 'p', 9999999999999);
|
|
INSERT INTO T1 VALUES ('b', 'q', -9999999999999);
|
|
SELECT 0x0ACB29BM205381;
|
|
SELECT sqlite_compileoption_used('æçå¤©ä½ æ¯æä¹äº');
|
|
SELECT 1 >> -999999999999999999999999999;
|
|
SELECT sqlite_compileoption_get($1);
|
|
SELECT sqlite_version();
|
|
INSERT INTO T2 VALUES ('m', 'a');
|
|
INSERT INTO T2 VALUES ('n', 'b');
|
|
SELECT T2.X, T1.C FROM T2 INNER JOIN T1 ON T2.A = T1.A WHERE T1.C > -1000000000000;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM c2 t1 LEFT JOIN c2 t2 ON t1.w = (SELECT w FROM c2 WHERE w = t1.w);
|
|
ALTER TABLE p1 DROP COLUMN b;
|
|
ANALYZE;
|
|
SELECT COUNT(*) FROM T2;
|
|
INSERT INTO T1 DEFAULT VALUES;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
SELECT * FROM t1 AS a RIGHT JOIN c2 AS b ON a.rowid = b.rowid;
|
|
DETACH DATABASE aux20;
|
|
RELEASE sp1369;
|
|
SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM T;
|
|
DROP VIEW IF EXISTS T1;
|
|
INSERT INTO p1 DEFAULT VALUES;
|
|
|
|
SELECT COUNT(*) FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM c2;
|
|
ALTER TABLE c1 RENAME COLUMN z TO z_r2716;
|
|
SELECT * FROM c2;
|
|
DROP INDEX IF EXISTS p1;
|
|
RELEASE sp800;
|
|
|
|
SELECT COUNT(*) FROM c1;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
ANALYZE;
|
|
INSERT INTO c1 SELECT * FROM c1;
|
|
INSERT INTO p1 VALUES (NULL, NULL);
|
|
DROP TABLE IF EXISTS T2;
|
|
|
|
SELECT COUNT(*) FROM T1;
|
|
REINDEX;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T2_6563 AFTER UPDATE OF A ON T2 BEGIN INSERT INTO T2(A) VALUES (NULL); END;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T_8673 AS SELECT a FROM T;
|
|
WITH cte AS (SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T1 WHERE A > 0)) SELECT * FROM cte;
|
|
ALTER TABLE T2 RENAME COLUMN X TO X_r1036;
|
|
ALTER TABLE T ADD COLUMN extra_3319 SMALLINT DEFAULT (random());
|
|
SELECT * FROM c1;
|
|
INSERT INTO T2 SELECT * FROM T2;
|
|
CREATE TABLE T1 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
CREATE TABLE T2 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
INSERT INTO T1 VALUES (1,'a'),(2,'b');
|
|
INSERT INTO T2 VALUES (1,'c'),(1,'d');
|
|
SELECT T1.b,
|
|
(
|
|
SELECT GROUP_CONCAT(b,',')
|
|
FROM T2
|
|
WHERE T2.a=T1.a
|
|
) AS c
|
|
FROM T1;
|
|
INSERT INTO c2 DEFAULT VALUES;
|
|
SELECT * FROM T2;
|
|
.timeout 999999
|
|
PRAGMA parser_trace = OFF;
|
|
-- zeroblob({v})
|
|
|
|
.stats off
|
|
SAVEPOINT sp800;
|
|
SAVEPOINT sp1369;
|
|
ATTACH DATABASE ':memory:' AS aux20;
|
|
PRAGMA short_column_names = YES;
|
|
.progress datetime('Inf', '+1 day')
|
|
.shell pwd
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b INTEGER,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (('a' || ''),ceiling(x'c5cb906e1c8cc13d197d23f8b3cc1209'),NULL), ('a',-zeroblob(','),-'%'), ('b',CAST(3.141592653589793 AS INT8),4.5);
|
|
SELECT a,b,c,
|
|
SUM(c) OVER (
|
|
PARTITION BY a
|
|
ORDER BY b
|
|
) AS d
|
|
FROM T;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (like('_1%', 1, '_'), 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
PRAGMA writable_schema = ON;
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
.quit
|
|
BEGIN DEFERRED;
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES ('a',100.0), ('b',-200.5), ('c',9e999);
|
|
SELECT a,b,
|
|
LEAD(b,1) OVER (ORDER BY b DESC) AS c,
|
|
NTILE(2) OVER (ORDER BY b DESC) AS d FROM T;
|
|
PRAGMA cache_spill = NO;
|
|
PRAGMA vdbe_listing = inf;
|
|
PRAGMA empty_result_callbacks = 0;
|
|
.timeout
|
|
CREATE TABLE [p1](a PRIMARY KEY, b UNIQUE);
|
|
CREATE TABLE c1(x, y, z REFERENCES p1(c));
|
|
CREATE TABLE c2(x, y, z, w REFERENCES p1(b));
|
|
ALTER TABLE c1 DROP COLUMN z;
|
|
ALTER TABLE c2 DROP COLUMN z;
|
|
SELECT sql FROM sqlite_schema WHERE name IN ('c1', 'c2');
|
|
COMMIT TRANSACTION;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE +x<31) SELECT x FROM cnt WHERE +x > 15;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_c1_2917 AS SELECT z FROM c1;
|
|
SELECT * FROM c2 AS a FULL OUTER JOIN c1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM p1;
|
|
UPDATE c2 SET z = NULL WHERE rowid = 1;
|
|
CREATE INDEX IF NOT EXISTS idx_c2_3327 ON c2(lower(x)) WHERE x > 0;
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE y IS NOT NULL), SUM(rowid) FILTER (WHERE y > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(y) FILTER (WHERE y > 0 AND y < 100), COUNT(*) FILTER (WHERE typeof(y) = "text") FROM c1;
|
|
REINDEX p1;
|
|
ALTER TABLE c2 RENAME COLUMN w TO w_r831;
|
|
CREATE TABLE T1 (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15) UNIQUE,
|
|
C BIGINT
|
|
);
|
|
CREATE TABLE T2 (
|
|
X VARCHAR(20) PRIMARY KEY,
|
|
A VARCHAR(10),
|
|
FOREIGN KEY (A) REFERENCES T1(A)
|
|
);
|
|
INSERT INTO T1 VALUES ('a', 'p', 9999999999999);
|
|
INSERT INTO T1 VALUES ('b', 'q', -9999999999999);
|
|
SELECT 0x0ACB29BM205381;
|
|
SELECT sqlite_compileoption_used('æçå¤©ä½ æ¯æä¹äº');
|
|
SELECT 1 >> -999999999999999999999999999;
|
|
SELECT sqlite_compileoption_get($1);
|
|
SELECT sqlite_version();
|
|
INSERT INTO T2 VALUES ('m', 'a');
|
|
INSERT INTO T2 VALUES ('n', 'b');
|
|
SELECT T2.X, T1.C FROM T2 INNER JOIN T1 ON T2.A = T1.A WHERE T1.C > -1000000000000;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM c2 t1 LEFT JOIN c2 t2 ON t1.w = (SELECT w FROM c2 WHERE w = t1.w);
|
|
ALTER TABLE p1 DROP COLUMN b;
|
|
ANALYZE;
|
|
SELECT COUNT(*) FROM T2;
|
|
INSERT INTO T1 DEFAULT VALUES;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
SELECT * FROM t1 AS a RIGHT JOIN c2 AS b ON a.rowid = b.rowid;
|
|
DETACH DATABASE aux20;
|
|
RELEASE sp1369;
|
|
SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM T;
|
|
DROP VIEW IF EXISTS T1;
|
|
INSERT INTO p1 DEFAULT VALUES;
|
|
|
|
SELECT COUNT(*) FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM c2;
|
|
ALTER TABLE c1 RENAME COLUMN z TO z_r2716;
|
|
SELECT * FROM c2;
|
|
DROP INDEX IF EXISTS p1;
|
|
RELEASE sp800;
|
|
|
|
SELECT COUNT(*) FROM c1;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
ANALYZE;
|
|
INSERT INTO c1 SELECT * FROM c1;
|
|
INSERT INTO p1 VALUES (NULL, NULL);
|
|
DROP TABLE IF EXISTS T2;
|
|
|
|
SELECT COUNT(*) FROM T1;
|
|
REINDEX;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T2_6563 AFTER UPDATE OF A ON T2 BEGIN INSERT INTO T2(A) VALUES (NULL); END;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T_8673 AS SELECT a FROM T;
|
|
WITH cte AS (SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T1 WHERE A > 0)) SELECT * FROM cte;
|
|
ALTER TABLE T2 RENAME COLUMN X TO X_r1036;
|
|
ALTER TABLE T ADD COLUMN extra_3319 SMALLINT DEFAULT (random());
|
|
SELECT * FROM c1;
|
|
INSERT INTO T2 SELECT * FROM T2;
|
|
CREATE TABLE T1 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
CREATE TABLE T2 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
INSERT INTO T1 VALUES (1,'a'),(2,'b');
|
|
INSERT INTO T2 VALUES (1,'c'),(1,'d');
|
|
SELECT T1.b,
|
|
(
|
|
SELECT GROUP_CONCAT(b,',')
|
|
FROM T2
|
|
WHERE T2.a=T1.a
|
|
) AS c
|
|
FROM T1;
|
|
INSERT INTO c2 DEFAULT VALUES;
|
|
SELECT * FROM T2;
|
|
|
|
ALTER TABLE T RENAME TO T_r4684;
|
|
PRAGMA mmap_size = 50000000;
|
|
CREATE TEMP VIEW IF NOT EXISTS v_c2_7233 AS SELECT y FROM c2;
|
|
.timeout json_set(NULL, '$.new_key', NULL)
|
|
PRAGMA parser_trace = OFF;
|
|
-- zeroblob({v})
|
|
|
|
.stats off
|
|
SAVEPOINT sp800;
|
|
SAVEPOINT sp1369;
|
|
ATTACH DATABASE ':memory:' AS aux20;
|
|
PRAGMA short_column_names = YES;
|
|
.progress CHARINT('Inf', '+1 day')
|
|
.shell pwd
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b INTEGER,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (('a' || ''),ceiling(x'c5cb906e1c8cc13d197d23f8b3cc1209'),NULL), ('a',-zeroblob(','),-'%'), ('b',CAST(abs(NULL) AS INT8),4.5);
|
|
SELECT a,b,c,
|
|
SUM(c) OVER (
|
|
PARTITION BY a
|
|
ORDER BY b
|
|
) AS d
|
|
FROM T;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (like('_1%', 1, '_'), 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
PRAGMA writable_schema = ON;
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
.quit
|
|
BEGIN DEFERRED;
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES ('a',100.0), ('b',-200.5), ('c',9e999);
|
|
SELECT a,b,
|
|
LEAD(b,1) OVER (ORDER BY b DESC) AS c,
|
|
NTILE(2) OVER (ORDER BY b DESC) AS d FROM T;
|
|
PRAGMA cache_spill = NO;
|
|
PRAGMA vdbe_listing = inf;
|
|
PRAGMA empty_result_callbacks = 0;
|
|
.timeout
|
|
CREATE TABLE [p1](a PRIMARY KEY, b UNIQUE);
|
|
CREATE TABLE "c1"(x, y, z REFERENCES p1(c));
|
|
CREATE TABLE c2(x, y, z, w REFERENCES p1(b));
|
|
ALTER TABLE c1 DROP COLUMN z;
|
|
ALTER TABLE c2 DROP COLUMN z;
|
|
SELECT sql FROM sqlite_schema WHERE name IN ('c1', 'c2');
|
|
COMMIT TRANSACTION;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE +x<31) SELECT x FROM cnt WHERE +x > 15;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_c1_2917 AS SELECT z FROM c1;
|
|
SELECT * FROM c2 AS a FULL OUTER JOIN c1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM p1;
|
|
UPDATE c2 SET z = NULL WHERE rowid = 1;
|
|
CREATE INDEX IF NOT EXISTS idx_c2_3327 ON c2(lower(x)) WHERE x > 0;
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE y IS NOT NULL), SUM(rowid) FILTER (WHERE y > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(y) FILTER (WHERE y > 0 AND y < 100), COUNT(*) FILTER (WHERE typeof(y) = "text") FROM c1;
|
|
REINDEX p1;
|
|
ALTER TABLE c2 RENAME COLUMN w TO w_r831;
|
|
CREATE TABLE T1 (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15) UNIQUE,
|
|
C BIGINT
|
|
);
|
|
CREATE TABLE T2 (
|
|
X VARCHAR(20) PRIMARY KEY,
|
|
A VARCHAR(10),
|
|
FOREIGN KEY (A) REFERENCES T1(A)
|
|
);
|
|
INSERT INTO T1 VALUES ('a', 'p', 9999999999999);
|
|
INSERT INTO T1 VALUES ('b', 'q', -9999999999999);
|
|
SELECT 0x0ACB29BM205381;
|
|
SELECT sqlite_compileoption_used('æçå¤©ä½ æ¯æä¹äº');
|
|
SELECT 1 >> -999999999999999999999999999;
|
|
SELECT sqlite_compileoption_get($1);
|
|
SELECT sqlite_version();
|
|
INSERT INTO T2 VALUES ('m', 'a');
|
|
INSERT INTO T2 VALUES ('n', 'b');
|
|
SELECT T2.X, T1.C FROM T2 INNER JOIN T1 ON T2.A = T1.A WHERE T1.C > -1000000000000;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM c2 t1 LEFT JOIN c2 t2 ON t1.w = (SELECT w FROM c2 WHERE w = t1.w);
|
|
ALTER TABLE p1 DROP COLUMN b;
|
|
ANALYZE;
|
|
SELECT COUNT(*) FROM T2;
|
|
INSERT INTO T1 DEFAULT VALUES;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
SELECT * FROM t1 AS a RIGHT JOIN c2 AS b ON a.rowid = b.rowid;
|
|
DETACH DATABASE aux20;
|
|
RELEASE sp1369;
|
|
SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM T;
|
|
DROP VIEW IF EXISTS T1;
|
|
INSERT INTO p1 DEFAULT VALUES;
|
|
|
|
SELECT COUNT(*) FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM c2;
|
|
ALTER TABLE c1 RENAME COLUMN z TO z_r2716;
|
|
SELECT * FROM c2;
|
|
DROP INDEX IF EXISTS p1;
|
|
RELEASE sp800;
|
|
|
|
SELECT COUNT(*) FROM c1;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
ANALYZE;
|
|
INSERT INTO c1 SELECT * FROM c1;
|
|
INSERT INTO p1 VALUES (NULL, NULL);
|
|
DROP TABLE IF EXISTS T2;
|
|
|
|
SELECT COUNT(*) FROM T1;
|
|
REINDEX;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T2_6563 AFTER UPDATE OF A ON T2 BEGIN INSERT INTO T2(A) VALUES (NULL); END;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T_8673 AS SELECT a FROM T;
|
|
WITH cte AS (SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T1 WHERE A > 0)) SELECT * FROM cte;
|
|
ALTER TABLE T2 RENAME COLUMN X TO X_r1036;
|
|
ALTER TABLE T ADD COLUMN extra_3319 SMALLINT DEFAULT (random());
|
|
SELECT * FROM c1;
|
|
INSERT INTO T2 SELECT * FROM T2;
|
|
CREATE TABLE T1 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
CREATE TABLE T2 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
INSERT INTO T1 VALUES (1,'a'),(2,'b');
|
|
INSERT INTO T2 VALUES (1,'c'),(1,'d');
|
|
SELECT T1.b,
|
|
(
|
|
SELECT GROUP_CONCAT(b,',')
|
|
FROM T2
|
|
WHERE T2.a=T1.a
|
|
) AS c
|
|
FROM T1;
|
|
INSERT INTO c2 DEFAULT VALUES;
|
|
SELECT * FROM T2;
|
|
.timeout 999999
|
|
PRAGMA parser_trace = OFF;
|
|
-- zeroblob({v})
|
|
|
|
.stats off
|
|
SAVEPOINT sp800;
|
|
SAVEPOINT sp1369;
|
|
ATTACH DATABASE ':memory:' AS aux20;
|
|
PRAGMA short_column_names = YES;
|
|
.progress datetime('Inf', '+1 day')
|
|
.shell pwd
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b INTEGER,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (('a' || ''),ceiling(x'c5cb906e1c8cc13d197d23f8b3cc1209'),NULL), ('a',-zeroblob(','),-'%'), ('b',CAST(3.141592653589793 AS INT8),4.5);
|
|
SELECT a,b,c,
|
|
SUM(c) OVER (
|
|
PARTITION BY a
|
|
ORDER BY b
|
|
) AS d
|
|
FROM T;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (like('_1%', 1, '_'), 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
PRAGMA writable_schema = ON;
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
.quit
|
|
BEGIN DEFERRED;
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES ('a',100.0), ('b',-200.5), ('c',9e999);
|
|
SELECT a,b,
|
|
LEAD(b,1) OVER (ORDER BY b DESC) AS c,
|
|
NTILE(2) OVER (ORDER BY b DESC) AS d FROM T;
|
|
PRAGMA cache_spill = NO;
|
|
PRAGMA vdbe_listing = inf;
|
|
PRAGMA empty_result_callbacks = 0;
|
|
.timeout
|
|
CREATE TABLE [p1](a PRIMARY KEY, b UNIQUE);
|
|
CREATE TABLE c1(x, y, z REFERENCES p1(c));
|
|
CREATE TABLE c2(x, y, z, w REFERENCES p1(b));
|
|
ALTER TABLE c1 DROP COLUMN z;
|
|
ALTER TABLE c2 DROP COLUMN z;
|
|
SELECT sql FROM sqlite_schema WHERE name IN ('c1', 'c2');
|
|
COMMIT TRANSACTION;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE +x<31) SELECT x FROM cnt WHERE +x > 15;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_c1_2917 AS SELECT z FROM c1;
|
|
SELECT * FROM c2 AS a FULL OUTER JOIN c1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM p1;
|
|
UPDATE c2 SET z = NULL WHERE rowid = 1;
|
|
CREATE INDEX IF NOT EXISTS idx_c2_3327 ON c2(lower(x)) WHERE x > 0;
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE y IS NOT NULL), SUM(rowid) FILTER (WHERE y > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(y) FILTER (WHERE y > 0 AND y < 100), COUNT(*) FILTER (WHERE typeof(y) = "text") FROM c1;
|
|
REINDEX p1;
|
|
ALTER TABLE c2 RENAME COLUMN w TO w_r831;
|
|
CREATE TABLE T1 (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15) UNIQUE,
|
|
C BIGINT
|
|
);
|
|
CREATE TABLE T2 (
|
|
X VARCHAR(20) PRIMARY KEY,
|
|
A VARCHAR(10),
|
|
FOREIGN KEY (A) REFERENCES T1(A)
|
|
);
|
|
INSERT INTO T1 VALUES ('a', 'p', 9999999999999);
|
|
INSERT INTO T1 VALUES ('b', 'q', -9999999999999);
|
|
SELECT 0x0ACB29BM205381;
|
|
SELECT sqlite_compileoption_used('æçå¤©ä½ æ¯æä¹äº');
|
|
SELECT 1 >> -999999999999999999999999999;
|
|
SELECT sqlite_compileoption_get($1);
|
|
SELECT sqlite_version();
|
|
INSERT INTO T2 VALUES ('m', 'a');
|
|
INSERT INTO T2 VALUES ('n', 'b');
|
|
SELECT T2.X, T1.C FROM T2 INNER JOIN T1 ON T2.A = T1.A WHERE T1.C > -1000000000000;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM c2 t1 LEFT JOIN c2 t2 ON t1.w = (SELECT w FROM c2 WHERE w = t1.w);
|
|
ALTER TABLE p1 DROP COLUMN b;
|
|
ANALYZE;
|
|
SELECT COUNT(*) FROM T2;
|
|
INSERT INTO T1 DEFAULT VALUES;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
SELECT * FROM t1 AS a RIGHT JOIN c2 AS b ON a.rowid = b.rowid;
|
|
DETACH DATABASE aux20;
|
|
RELEASE sp1369;
|
|
SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM T;
|
|
DROP VIEW IF EXISTS T1;
|
|
INSERT INTO p1 DEFAULT VALUES;
|
|
|
|
SELECT COUNT(*) FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM c2;
|
|
ALTER TABLE c1 RENAME COLUMN z TO z_r2716;
|
|
SELECT * FROM c2;
|
|
DROP INDEX IF EXISTS p1;
|
|
RELEASE sp800;
|
|
|
|
SELECT COUNT(*) FROM c1;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
ANALYZE;
|
|
INSERT INTO c1 SELECT * FROM c1;
|
|
INSERT INTO p1 VALUES (NULL, NULL);
|
|
DROP TABLE IF EXISTS T2;
|
|
|
|
SELECT COUNT(*) FROM T1;
|
|
REINDEX;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T2_6563 AFTER UPDATE OF A ON T2 BEGIN INSERT INTO T2(A) VALUES (NULL); END;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T_8673 AS SELECT a FROM T;
|
|
WITH cte AS (SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T1 WHERE A > 0)) SELECT * FROM cte;
|
|
ALTER TABLE T2 RENAME COLUMN X TO X_r1036;
|
|
ALTER TABLE T ADD COLUMN extra_3319 SMALLINT DEFAULT (random());
|
|
SELECT * FROM c1;
|
|
INSERT INTO T2 SELECT * FROM T2;
|
|
CREATE TABLE T1 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
CREATE TABLE T2 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
INSERT INTO T1 VALUES (1,'a'),(2,'b');
|
|
INSERT INTO T2 VALUES (1,'c'),(1,'d');
|
|
SELECT T1.b,
|
|
(
|
|
SELECT GROUP_CONCAT(b,',')
|
|
FROM T2
|
|
WHERE T2.a=T1.a
|
|
) AS c
|
|
FROM T1;
|
|
INSERT INTO c2 DEFAULT VALUES;
|
|
SELECT * FROM T2;
|
|
.timeout 999999
|
|
PRAGMA parser_trace = OFF;
|
|
-- zeroblob({v})
|
|
|
|
.stats off
|
|
SAVEPOINT sp800;
|
|
SAVEPOINT sp1369;
|
|
ATTACH DATABASE ':memory:' AS aux20;
|
|
PRAGMA short_column_names = YES;
|
|
.progress datetime('Inf', '+1 day')
|
|
.shell pwd
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b INTEGER,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (('a' || ''),ceiling(x'c5cb906e1c8cc13d197d23f8b3cc1209'),NULL), ('a',-zeroblob(','),-'%'), ('b',CAST(3.141592653589793 AS INT8),4.5);
|
|
SELECT a,b,c,
|
|
SUM(c) OVER (
|
|
PARTITION BY a
|
|
ORDER BY b
|
|
) AS d
|
|
FROM T;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (like('_1%', 1, '_'), 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
PRAGMA writable_schema = ON;
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
.quit
|
|
BEGIN DEFERRED;
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES ('a',100.0), ('b',-200.5), ('c',9e999);
|
|
SELECT a,b,
|
|
LEAD(b,1) OVER (ORDER BY b DESC) AS c,
|
|
NTILE(2) OVER (ORDER BY b DESC) AS d FROM T;
|
|
PRAGMA cache_spill = NO;
|
|
PRAGMA vdbe_listing = inf;
|
|
PRAGMA empty_result_callbacks = 0;
|
|
.timeout
|
|
CREATE TABLE [p1](a PRIMARY KEY, b UNIQUE);
|
|
CREATE TABLE c1(x, y, z REFERENCES p1(c));
|
|
CREATE TABLE c2(x, y, z, w REFERENCES p1(b));
|
|
ALTER TABLE c1 DROP COLUMN z;
|
|
ALTER TABLE c2 DROP COLUMN z;
|
|
SELECT sql FROM sqlite_schema WHERE name IN ('c1', 'c2');
|
|
COMMIT TRANSACTION;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE +x<31) SELECT x FROM cnt WHERE +x > 15;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_c1_2917 AS SELECT z FROM c1;
|
|
SELECT * FROM c2 AS a FULL OUTER JOIN c1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM p1;
|
|
UPDATE c2 SET z = NULL WHERE rowid = 1;
|
|
CREATE INDEX IF NOT EXISTS idx_c2_3327 ON c2(lower(x)) WHERE x > 0;
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE y IS NOT NULL), SUM(rowid) FILTER (WHERE y > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(y) FILTER (WHERE y > 0 AND y < 100), COUNT(*) FILTER (WHERE typeof(y) = "text") FROM c1;
|
|
REINDEX p1;
|
|
ALTER TABLE c2 RENAME COLUMN w TO w_r831;
|
|
CREATE TABLE T1 (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15) UNIQUE,
|
|
C BIGINT
|
|
);
|
|
CREATE TABLE T2 (
|
|
X VARCHAR(20) PRIMARY KEY,
|
|
A VARCHAR(10),
|
|
FOREIGN KEY (A) REFERENCES T1(A)
|
|
);
|
|
INSERT INTO T1 VALUES ('a', 'p', 9999999999999);
|
|
INSERT INTO T1 VALUES ('b', 'q', -9999999999999);
|
|
SELECT 0x0ACB29BM205381;
|
|
SELECT sqlite_compileoption_used('æçå¤©ä½ æ¯æä¹äº');
|
|
SELECT 1 >> -999999999999999999999999999;
|
|
SELECT sqlite_compileoption_get($1);
|
|
SELECT sqlite_version();
|
|
INSERT INTO T2 VALUES ('m', 'a');
|
|
INSERT INTO T2 VALUES ('n', 'b');
|
|
SELECT T2.X, T1.C FROM T2 INNER JOIN T1 ON T2.A = T1.A WHERE T1.C > -1000000000000;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM c2 t1 LEFT JOIN c2 t2 ON t1.w = (SELECT w FROM c2 WHERE w = t1.w);
|
|
ALTER TABLE p1 DROP COLUMN b;
|
|
ANALYZE;
|
|
SELECT COUNT(*) FROM T2;
|
|
INSERT INTO T1 DEFAULT VALUES;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
SELECT * FROM t1 AS a RIGHT JOIN c2 AS b ON a.rowid = b.rowid;
|
|
DETACH DATABASE aux20;
|
|
RELEASE sp1369;
|
|
SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM T;
|
|
DROP VIEW IF EXISTS T1;
|
|
INSERT INTO p1 DEFAULT VALUES;
|
|
|
|
SELECT COUNT(*) FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM c2;
|
|
ALTER TABLE c1 RENAME COLUMN z TO z_r2716;
|
|
SELECT * FROM c2;
|
|
DROP INDEX IF EXISTS p1;
|
|
RELEASE sp800;
|
|
|
|
SELECT COUNT(*) FROM c1;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
ANALYZE;
|
|
INSERT INTO c1 SELECT * FROM c1;
|
|
INSERT INTO p1 VALUES (NULL, NULL);
|
|
DROP TABLE IF EXISTS T2;
|
|
|
|
SELECT COUNT(*) FROM T1;
|
|
REINDEX;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T2_6563 AFTER UPDATE OF A ON T2 BEGIN INSERT INTO T2(A) VALUES (NULL); END;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T_8673 AS SELECT a FROM T;
|
|
WITH cte AS (SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T1 WHERE A > 0)) SELECT * FROM cte;
|
|
ALTER TABLE T2 RENAME COLUMN X TO X_r1036;
|
|
ALTER TABLE T ADD COLUMN extra_3319 SMALLINT DEFAULT (random());
|
|
SELECT * FROM c1;
|
|
INSERT INTO T2 SELECT * FROM T2;
|
|
CREATE TABLE T1 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
CREATE TABLE T2 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
INSERT INTO T1 VALUES (1,'a'),(2,'b');
|
|
INSERT INTO T2 VALUES (1,'c'),(1,'d');
|
|
SELECT T1.b,
|
|
(
|
|
SELECT GROUP_CONCAT(b,',')
|
|
FROM T2
|
|
WHERE T2.a=T1.a
|
|
) AS c
|
|
FROM T1;
|
|
INSERT INTO c2 DEFAULT VALUES;
|
|
SELECT * FROM T2;
|
|
|
|
ALTER TABLE T RENAME TO T_r4684;
|
|
PRAGMA mmap_size = 50000000;
|
|
CREATE TEMP VIEW IF NOT EXISTS v_c2_7233 AS SELECT y FROM c2;
|
|
.timeout json_set(NULL, '$.new_key', NULL)
|
|
PRAGMA parser_trace = OFF;
|
|
-- zeroblob({v})
|
|
|
|
.stats off
|
|
SAVEPOINT sp800;
|
|
SAVEPOINT sp1369;
|
|
ATTACH DATABASE ':memory:' AS aux20;
|
|
PRAGMA short_column_names = YES;
|
|
.progress CHARINT('Inf', '+1 day')
|
|
.shell pwd
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b INTEGER,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (('a' || ''),ceiling(x'c5cb906e1c8cc13d197d23f8b3cc1209'),NULL), ('a',-zeroblob(','),-'%'), ('b',CAST(abs(NULL) AS INT8),4.5);
|
|
SELECT a,b,c,
|
|
SUM(c) OVER (
|
|
PARTITION BY a
|
|
ORDER BY b
|
|
) AS d
|
|
FROM T;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (like('_1%', 1, '_'), 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
PRAGMA writable_schema = ON;
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
.quit
|
|
BEGIN DEFERRED;
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES ('a',100.0), ('b',-200.5), ('c',9e999);
|
|
SELECT a,b,
|
|
LEAD(b,1) OVER (ORDER BY b DESC) AS c,
|
|
NTILE(2) OVER (ORDER BY b DESC) AS d FROM T;
|
|
PRAGMA cache_spill = NO;
|
|
PRAGMA vdbe_listing = inf;
|
|
PRAGMA empty_result_callbacks = 0;
|
|
.timeout
|
|
CREATE TABLE [p1](a PRIMARY KEY, b UNIQUE);
|
|
CREATE TABLE "c1"(x, y, z REFERENCES p1(c));
|
|
CREATE TABLE c2(x, y, z, w REFERENCES p1(b));
|
|
ALTER TABLE c1 DROP COLUMN z;
|
|
ALTER TABLE c2 DROP COLUMN z;
|
|
SELECT sql FROM sqlite_schema WHERE name IN ('c1', 'c2');
|
|
COMMIT TRANSACTION;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE +x<31) SELECT x FROM cnt WHERE +x > 15;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_c1_2917 AS SELECT z FROM c1;
|
|
SELECT * FROM c2 AS a FULL OUTER JOIN c1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM p1;
|
|
UPDATE c2 SET z = NULL WHERE rowid = 1;
|
|
CREATE INDEX IF NOT EXISTS idx_c2_3327 ON c2(lower(x)) WHERE x > 0;
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE y IS NOT NULL), SUM(rowid) FILTER (WHERE y > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(y) FILTER (WHERE y > 0 AND y < 100), COUNT(*) FILTER (WHERE typeof(y) = "text") FROM c1;
|
|
REINDEX p1;
|
|
ALTER TABLE c2 RENAME COLUMN w TO w_r831;
|
|
CREATE TABLE T1 (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15) UNIQUE,
|
|
C BIGINT
|
|
);
|
|
CREATE TABLE T2 (
|
|
X VARCHAR(20) PRIMARY KEY,
|
|
A VARCHAR(10),
|
|
FOREIGN KEY (A) REFERENCES T1(A)
|
|
);
|
|
INSERT INTO T1 VALUES ('a', 'p', 9999999999999);
|
|
INSERT INTO T1 VALUES ('b', 'q', -9999999999999);
|
|
SELECT 0x0ACB29BM205381;
|
|
SELECT sqlite_compileoption_used('æçå¤©ä½ æ¯æä¹äº');
|
|
SELECT 1 >> -999999999999999999999999999;
|
|
SELECT sqlite_compileoption_get($1);
|
|
SELECT sqlite_version();
|
|
INSERT INTO T2 VALUES ('m', 'a');
|
|
INSERT INTO T2 VALUES ('n', 'b');
|
|
SELECT T2.X, T1.C FROM T2 INNER JOIN T1 ON T2.A = T1.A WHERE T1.C > -1000000000000;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM c2 t1 LEFT JOIN c2 t2 ON t1.w = (SELECT w FROM c2 WHERE w = t1.w);
|
|
ALTER TABLE p1 DROP COLUMN b;
|
|
ANALYZE;
|
|
SELECT COUNT(*) FROM T2;
|
|
INSERT INTO T1 DEFAULT VALUES;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
SELECT * FROM t1 AS a RIGHT JOIN c2 AS b ON a.rowid = b.rowid;
|
|
DETACH DATABASE aux20;
|
|
RELEASE sp1369;
|
|
SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM T;
|
|
DROP VIEW IF EXISTS T1;
|
|
INSERT INTO p1 DEFAULT VALUES;
|
|
|
|
SELECT COUNT(*) FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM c2;
|
|
ALTER TABLE c1 RENAME COLUMN z TO z_r2716;
|
|
SELECT * FROM c2;
|
|
DROP INDEX IF EXISTS p1;
|
|
RELEASE sp800;
|
|
|
|
SELECT COUNT(*) FROM c1;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
ANALYZE;
|
|
INSERT INTO c1 SELECT * FROM c1;
|
|
INSERT INTO p1 VALUES (NULL, NULL);
|
|
DROP TABLE IF EXISTS T2;
|
|
|
|
SELECT COUNT(*) FROM T1;
|
|
REINDEX;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T2_6563 AFTER UPDATE OF A ON T2 BEGIN INSERT INTO T2(A) VALUES (NULL); END;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T_8673 AS SELECT a FROM T;
|
|
WITH cte AS (SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T1 WHERE A > 0)) SELECT * FROM cte;
|
|
ALTER TABLE T2 RENAME COLUMN X TO X_r1036;
|
|
ALTER TABLE T ADD COLUMN extra_3319 SMALLINT DEFAULT (random());
|
|
SELECT * FROM c1;
|
|
INSERT INTO T2 SELECT * FROM T2;
|
|
CREATE TABLE T1 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
CREATE TABLE T2 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
INSERT INTO T1 VALUES (1,'a'),(2,'b');
|
|
INSERT INTO T2 VALUES (1,'c'),(1,'d');
|
|
SELECT T1.b,
|
|
(
|
|
SELECT GROUP_CONCAT(b,',')
|
|
FROM T2
|
|
WHERE T2.a=T1.a
|
|
) AS c
|
|
FROM T1;
|
|
INSERT INTO c2 DEFAULT VALUES;
|
|
SELECT * FROM T2;
|
|
.timeout 999999
|
|
PRAGMA parser_trace = OFF;
|
|
-- zeroblob({v})
|
|
|
|
.stats off
|
|
SAVEPOINT sp800;
|
|
SAVEPOINT sp1369;
|
|
ATTACH DATABASE ':memory:' AS aux20;
|
|
PRAGMA short_column_names = YES;
|
|
.progress datetime('Inf', '+1 day')
|
|
.shell pwd
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b INTEGER,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (('a' || ''),ceiling(x'c5cb906e1c8cc13d197d23f8b3cc1209'),NULL), ('a',-zeroblob(','),-'%'), ('b',CAST(3.141592653589793 AS INT8),4.5);
|
|
SELECT a,b,c,
|
|
SUM(c) OVER (
|
|
PARTITION BY a
|
|
ORDER BY b
|
|
) AS d
|
|
FROM T;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (like('_1%', 1, '_'), 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
PRAGMA writable_schema = ON;
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
.quit
|
|
BEGIN DEFERRED;
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES ('a',100.0), ('b',-200.5), ('c',9e999);
|
|
SELECT a,b,
|
|
LEAD(b,1) OVER (ORDER BY b DESC) AS c,
|
|
NTILE(2) OVER (ORDER BY b DESC) AS d FROM T;
|
|
PRAGMA cache_spill = NO;
|
|
PRAGMA vdbe_listing = inf;
|
|
PRAGMA empty_result_callbacks = 0;
|
|
.timeout
|
|
CREATE TABLE [p1](a PRIMARY KEY, b UNIQUE);
|
|
CREATE TABLE c1(x, y, z REFERENCES p1(c));
|
|
CREATE TABLE c2(x, y, z, w REFERENCES p1(b));
|
|
ALTER TABLE c1 DROP COLUMN z;
|
|
ALTER TABLE c2 DROP COLUMN z;
|
|
SELECT sql FROM sqlite_schema WHERE name IN ('c1', 'c2');
|
|
COMMIT TRANSACTION;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE +x<31) SELECT x FROM cnt WHERE +x > 15;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_c1_2917 AS SELECT z FROM c1;
|
|
SELECT * FROM c2 AS a FULL OUTER JOIN c1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM p1;
|
|
UPDATE c2 SET z = NULL WHERE rowid = 1;
|
|
CREATE INDEX IF NOT EXISTS idx_c2_3327 ON c2(lower(x)) WHERE x > 0;
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE y IS NOT NULL), SUM(rowid) FILTER (WHERE y > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(y) FILTER (WHERE y > 0 AND y < 100), COUNT(*) FILTER (WHERE typeof(y) = "text") FROM c1;
|
|
REINDEX p1;
|
|
ALTER TABLE c2 RENAME COLUMN w TO w_r831;
|
|
CREATE TABLE T1 (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15) UNIQUE,
|
|
C BIGINT
|
|
);
|
|
CREATE TABLE T2 (
|
|
X VARCHAR(20) PRIMARY KEY,
|
|
A VARCHAR(10),
|
|
FOREIGN KEY (A) REFERENCES T1(A)
|
|
);
|
|
INSERT INTO T1 VALUES ('a', 'p', 9999999999999);
|
|
INSERT INTO T1 VALUES ('b', 'q', -9999999999999);
|
|
SELECT 0x0ACB29BM205381;
|
|
SELECT sqlite_compileoption_used('æçå¤©ä½ æ¯æä¹äº');
|
|
SELECT 1 >> -999999999999999999999999999;
|
|
SELECT sqlite_compileoption_get($1);
|
|
SELECT sqlite_version();
|
|
INSERT INTO T2 VALUES ('m', 'a');
|
|
INSERT INTO T2 VALUES ('n', 'b');
|
|
SELECT T2.X, T1.C FROM T2 INNER JOIN T1 ON T2.A = T1.A WHERE T1.C > -1000000000000;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM c2 t1 LEFT JOIN c2 t2 ON t1.w = (SELECT w FROM c2 WHERE w = t1.w);
|
|
ALTER TABLE p1 DROP COLUMN b;
|
|
ANALYZE;
|
|
SELECT COUNT(*) FROM T2;
|
|
INSERT INTO T1 DEFAULT VALUES;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
SELECT * FROM t1 AS a RIGHT JOIN c2 AS b ON a.rowid = b.rowid;
|
|
DETACH DATABASE aux20;
|
|
RELEASE sp1369;
|
|
SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM T;
|
|
DROP VIEW IF EXISTS T1;
|
|
INSERT INTO p1 DEFAULT VALUES;
|
|
|
|
SELECT COUNT(*) FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM c2;
|
|
ALTER TABLE c1 RENAME COLUMN z TO z_r2716;
|
|
SELECT * FROM c2;
|
|
DROP INDEX IF EXISTS p1;
|
|
RELEASE sp800;
|
|
|
|
SELECT COUNT(*) FROM c1;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
ANALYZE;
|
|
INSERT INTO c1 SELECT * FROM c1;
|
|
INSERT INTO p1 VALUES (NULL, NULL);
|
|
DROP TABLE IF EXISTS T2;
|
|
|
|
SELECT COUNT(*) FROM T1;
|
|
REINDEX;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T2_6563 AFTER UPDATE OF A ON T2 BEGIN INSERT INTO T2(A) VALUES (NULL); END;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T_8673 AS SELECT a FROM T;
|
|
WITH cte AS (SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T1 WHERE A > 0)) SELECT * FROM cte;
|
|
ALTER TABLE T2 RENAME COLUMN X TO X_r1036;
|
|
ALTER TABLE T ADD COLUMN extra_3319 SMALLINT DEFAULT (random());
|
|
SELECT * FROM c1;
|
|
INSERT INTO T2 SELECT * FROM T2;
|
|
CREATE TABLE T1 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
CREATE TABLE T2 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
INSERT INTO T1 VALUES (1,'a'),(2,'b');
|
|
INSERT INTO T2 VALUES (1,'c'),(1,'d');
|
|
SELECT T1.b,
|
|
(
|
|
SELECT GROUP_CONCAT(b,',')
|
|
FROM T2
|
|
WHERE T2.a=T1.a
|
|
) AS c
|
|
FROM T1;
|
|
INSERT INTO c2 DEFAULT VALUES;
|
|
SELECT * FROM T2;
|
|
.timeout 999999
|
|
PRAGMA parser_trace = OFF;
|
|
-- zeroblob({v})
|
|
|
|
.stats off
|
|
SAVEPOINT sp800;
|
|
SAVEPOINT sp1369;
|
|
ATTACH DATABASE ':memory:' AS aux20;
|
|
PRAGMA short_column_names = YES;
|
|
.progress datetime('Inf', '+1 day')
|
|
.shell pwd
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b INTEGER,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (('a' || ''),ceiling(x'c5cb906e1c8cc13d197d23f8b3cc1209'),NULL), ('a',-zeroblob(','),-'%'), ('b',CAST(3.141592653589793 AS INT8),4.5);
|
|
SELECT a,b,c,
|
|
SUM(c) OVER (
|
|
PARTITION BY a
|
|
ORDER BY b
|
|
) AS d
|
|
FROM T;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (like('_1%', 1, '_'), 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
PRAGMA writable_schema = ON;
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
.quit
|
|
BEGIN DEFERRED;
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES ('a',100.0), ('b',-200.5), ('c',9e999);
|
|
SELECT a,b,
|
|
LEAD(b,1) OVER (ORDER BY b DESC) AS c,
|
|
NTILE(2) OVER (ORDER BY b DESC) AS d FROM T;
|
|
PRAGMA cache_spill = NO;
|
|
PRAGMA vdbe_listing = inf;
|
|
PRAGMA empty_result_callbacks = 0;
|
|
.timeout
|
|
CREATE TABLE [p1](a PRIMARY KEY, b UNIQUE);
|
|
CREATE TABLE c1(x, y, z REFERENCES p1(c));
|
|
CREATE TABLE c2(x, y, z, w REFERENCES p1(b));
|
|
ALTER TABLE c1 DROP COLUMN z;
|
|
ALTER TABLE c2 DROP COLUMN z;
|
|
SELECT sql FROM sqlite_schema WHERE name IN ('c1', 'c2');
|
|
COMMIT TRANSACTION;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE +x<31) SELECT x FROM cnt WHERE +x > 15;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_c1_2917 AS SELECT z FROM c1;
|
|
SELECT * FROM c2 AS a FULL OUTER JOIN c1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM p1;
|
|
UPDATE c2 SET z = NULL WHERE rowid = 1;
|
|
CREATE INDEX IF NOT EXISTS idx_c2_3327 ON c2(lower(x)) WHERE x > 0;
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE y IS NOT NULL), SUM(rowid) FILTER (WHERE y > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(y) FILTER (WHERE y > 0 AND y < 100), COUNT(*) FILTER (WHERE typeof(y) = "text") FROM c1;
|
|
REINDEX p1;
|
|
ALTER TABLE c2 RENAME COLUMN w TO w_r831;
|
|
CREATE TABLE T1 (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15) UNIQUE,
|
|
C BIGINT
|
|
);
|
|
CREATE TABLE T2 (
|
|
X VARCHAR(20) PRIMARY KEY,
|
|
A VARCHAR(10),
|
|
FOREIGN KEY (A) REFERENCES T1(A)
|
|
);
|
|
INSERT INTO T1 VALUES ('a', 'p', 9999999999999);
|
|
INSERT INTO T1 VALUES ('b', 'q', -9999999999999);
|
|
SELECT 0x0ACB29BM205381;
|
|
SELECT sqlite_compileoption_used('æçå¤©ä½ æ¯æä¹äº');
|
|
SELECT 1 >> -999999999999999999999999999;
|
|
SELECT sqlite_compileoption_get($1);
|
|
SELECT sqlite_version();
|
|
INSERT INTO T2 VALUES ('m', 'a');
|
|
INSERT INTO T2 VALUES ('n', 'b');
|
|
SELECT T2.X, T1.C FROM T2 INNER JOIN T1 ON T2.A = T1.A WHERE T1.C > -1000000000000;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM c2 t1 LEFT JOIN c2 t2 ON t1.w = (SELECT w FROM c2 WHERE w = t1.w);
|
|
ALTER TABLE p1 DROP COLUMN b;
|
|
ANALYZE;
|
|
SELECT COUNT(*) FROM T2;
|
|
INSERT INTO T1 DEFAULT VALUES;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
SELECT * FROM t1 AS a RIGHT JOIN c2 AS b ON a.rowid = b.rowid;
|
|
DETACH DATABASE aux20;
|
|
RELEASE sp1369;
|
|
SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM T;
|
|
DROP VIEW IF EXISTS T1;
|
|
INSERT INTO p1 DEFAULT VALUES;
|
|
|
|
SELECT COUNT(*) FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM c2;
|
|
ALTER TABLE c1 RENAME COLUMN z TO z_r2716;
|
|
SELECT * FROM c2;
|
|
DROP INDEX IF EXISTS p1;
|
|
RELEASE sp800;
|
|
|
|
SELECT COUNT(*) FROM c1;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
ANALYZE;
|
|
INSERT INTO c1 SELECT * FROM c1;
|
|
INSERT INTO p1 VALUES (NULL, NULL);
|
|
DROP TABLE IF EXISTS T2;
|
|
|
|
SELECT COUNT(*) FROM T1;
|
|
REINDEX;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T2_6563 AFTER UPDATE OF A ON T2 BEGIN INSERT INTO T2(A) VALUES (NULL); END;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T_8673 AS SELECT a FROM T;
|
|
WITH cte AS (SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T1 WHERE A > 0)) SELECT * FROM cte;
|
|
ALTER TABLE T2 RENAME COLUMN X TO X_r1036;
|
|
ALTER TABLE T ADD COLUMN extra_3319 SMALLINT DEFAULT (random());
|
|
SELECT * FROM c1;
|
|
INSERT INTO T2 SELECT * FROM T2;
|
|
CREATE TABLE T1 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
CREATE TABLE T2 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
INSERT INTO T1 VALUES (1,'a'),(2,'b');
|
|
INSERT INTO T2 VALUES (1,'c'),(1,'d');
|
|
SELECT T1.b,
|
|
(
|
|
SELECT GROUP_CONCAT(b,',')
|
|
FROM T2
|
|
WHERE T2.a=T1.a
|
|
) AS c
|
|
FROM T1;
|
|
INSERT INTO c2 DEFAULT VALUES;
|
|
SELECT * FROM T2;
|
|
|
|
ALTER TABLE T RENAME TO T_r4684;
|
|
PRAGMA mmap_size = 50000000;
|
|
CREATE TEMP VIEW IF NOT EXISTS v_c2_7233 AS SELECT y FROM c2;
|
|
.timeout json_set(NULL, '$.new_key', NULL)
|
|
PRAGMA parser_trace = OFF;
|
|
-- zeroblob({v})
|
|
|
|
.stats off
|
|
SAVEPOINT sp800;
|
|
SAVEPOINT sp1369;
|
|
ATTACH DATABASE ':memory:' AS aux20;
|
|
PRAGMA short_column_names = YES;
|
|
.progress CHARINT('Inf', '+1 day')
|
|
.shell pwd
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b INTEGER,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (('a' || ''),ceiling(x'c5cb906e1c8cc13d197d23f8b3cc1209'),NULL), ('a',-zeroblob(','),-'%'), ('b',CAST(abs(NULL) AS INT8),4.5);
|
|
SELECT a,b,c,
|
|
SUM(c) OVER (
|
|
PARTITION BY a
|
|
ORDER BY b
|
|
) AS d
|
|
FROM T;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (like('_1%', 1, '_'), 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
PRAGMA writable_schema = ON;
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
.quit
|
|
BEGIN DEFERRED;
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES ('a',100.0), ('b',-200.5), ('c',9e999);
|
|
SELECT a,b,
|
|
LEAD(b,1) OVER (ORDER BY b DESC) AS c,
|
|
NTILE(2) OVER (ORDER BY b DESC) AS d FROM T;
|
|
PRAGMA cache_spill = NO;
|
|
PRAGMA vdbe_listing = inf;
|
|
PRAGMA empty_result_callbacks = 0;
|
|
.timeout
|
|
CREATE TABLE [p1](a PRIMARY KEY, b UNIQUE);
|
|
CREATE TABLE "c1"(x, y, z REFERENCES p1(c));
|
|
CREATE TABLE c2(x, y, z, w REFERENCES p1(b));
|
|
ALTER TABLE c1 DROP COLUMN z;
|
|
ALTER TABLE c2 DROP COLUMN z;
|
|
SELECT sql FROM sqlite_schema WHERE name IN ('c1', 'c2');
|
|
COMMIT TRANSACTION;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE +x<31) SELECT x FROM cnt WHERE +x > 15;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_c1_2917 AS SELECT z FROM c1;
|
|
SELECT * FROM c2 AS a FULL OUTER JOIN c1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM p1;
|
|
UPDATE c2 SET z = NULL WHERE rowid = 1;
|
|
CREATE INDEX IF NOT EXISTS idx_c2_3327 ON c2(lower(x)) WHERE x > 0;
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE y IS NOT NULL), SUM(rowid) FILTER (WHERE y > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(y) FILTER (WHERE y > 0 AND y < 100), COUNT(*) FILTER (WHERE typeof(y) = "text") FROM c1;
|
|
REINDEX p1;
|
|
ALTER TABLE c2 RENAME COLUMN w TO w_r831;
|
|
CREATE TABLE T1 (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15) UNIQUE,
|
|
C BIGINT
|
|
);
|
|
CREATE TABLE T2 (
|
|
X VARCHAR(20) PRIMARY KEY,
|
|
A VARCHAR(10),
|
|
FOREIGN KEY (A) REFERENCES T1(A)
|
|
);
|
|
INSERT INTO T1 VALUES ('a', 'p', 9999999999999);
|
|
INSERT INTO T1 VALUES ('b', 'q', -9999999999999);
|
|
SELECT 0x0ACB29BM205381;
|
|
SELECT sqlite_compileoption_used('æçå¤©ä½ æ¯æä¹äº');
|
|
SELECT 1 >> -999999999999999999999999999;
|
|
SELECT sqlite_compileoption_get($1);
|
|
SELECT sqlite_version();
|
|
INSERT INTO T2 VALUES ('m', 'a');
|
|
INSERT INTO T2 VALUES ('n', 'b');
|
|
SELECT T2.X, T1.C FROM T2 INNER JOIN T1 ON T2.A = T1.A WHERE T1.C > -1000000000000;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM c2 t1 LEFT JOIN c2 t2 ON t1.w = (SELECT w FROM c2 WHERE w = t1.w);
|
|
ALTER TABLE p1 DROP COLUMN b;
|
|
ANALYZE;
|
|
SELECT COUNT(*) FROM T2;
|
|
INSERT INTO T1 DEFAULT VALUES;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
SELECT * FROM t1 AS a RIGHT JOIN c2 AS b ON a.rowid = b.rowid;
|
|
DETACH DATABASE aux20;
|
|
RELEASE sp1369;
|
|
SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM T;
|
|
DROP VIEW IF EXISTS T1;
|
|
INSERT INTO p1 DEFAULT VALUES;
|
|
|
|
SELECT COUNT(*) FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM c2;
|
|
ALTER TABLE c1 RENAME COLUMN z TO z_r2716;
|
|
SELECT * FROM c2;
|
|
DROP INDEX IF EXISTS p1;
|
|
RELEASE sp800;
|
|
|
|
SELECT COUNT(*) FROM c1;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
ANALYZE;
|
|
INSERT INTO c1 SELECT * FROM c1;
|
|
INSERT INTO p1 VALUES (NULL, NULL);
|
|
DROP TABLE IF EXISTS T2;
|
|
|
|
SELECT COUNT(*) FROM T1;
|
|
REINDEX;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T2_6563 AFTER UPDATE OF A ON T2 BEGIN INSERT INTO T2(A) VALUES (NULL); END;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T_8673 AS SELECT a FROM T;
|
|
WITH cte AS (SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T1 WHERE A > 0)) SELECT * FROM cte;
|
|
ALTER TABLE T2 RENAME COLUMN X TO X_r1036;
|
|
ALTER TABLE T ADD COLUMN extra_3319 SMALLINT DEFAULT (random());
|
|
SELECT * FROM c1;
|
|
INSERT INTO T2 SELECT * FROM T2;
|
|
CREATE TABLE T1 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
CREATE TABLE T2 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
INSERT INTO T1 VALUES (1,'a'),(2,'b');
|
|
INSERT INTO T2 VALUES (1,'c'),(1,'d');
|
|
SELECT T1.b,
|
|
(
|
|
SELECT GROUP_CONCAT(b,',')
|
|
FROM T2
|
|
WHERE T2.a=T1.a
|
|
) AS c
|
|
FROM T1;
|
|
INSERT INTO c2 DEFAULT VALUES;
|
|
SELECT * FROM T2;
|
|
.timeout 999999
|
|
PRAGMA parser_trace = OFF;
|
|
-- zeroblob({v})
|
|
|
|
.stats off
|
|
SAVEPOINT sp800;
|
|
SAVEPOINT sp1369;
|
|
ATTACH DATABASE ':memory:' AS aux20;
|
|
PRAGMA short_column_names = YES;
|
|
.progress datetime('Inf', '+1 day')
|
|
.shell pwd
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b INTEGER,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (('a' || ''),ceiling(x'c5cb906e1c8cc13d197d23f8b3cc1209'),NULL), ('a',-zeroblob(','),-'%'), ('b',CAST(3.141592653589793 AS INT8),4.5);
|
|
SELECT a,b,c,
|
|
SUM(c) OVER (
|
|
PARTITION BY a
|
|
ORDER BY b
|
|
) AS d
|
|
FROM T;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (like('_1%', 1, '_'), 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
PRAGMA writable_schema = ON;
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
.quit
|
|
BEGIN DEFERRED;
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES ('a',100.0), ('b',-200.5), ('c',9e999);
|
|
SELECT a,b,
|
|
LEAD(b,1) OVER (ORDER BY b DESC) AS c,
|
|
NTILE(2) OVER (ORDER BY b DESC) AS d FROM T;
|
|
PRAGMA cache_spill = NO;
|
|
PRAGMA vdbe_listing = inf;
|
|
PRAGMA empty_result_callbacks = 0;
|
|
.timeout
|
|
CREATE TABLE [p1](a PRIMARY KEY, b UNIQUE);
|
|
CREATE TABLE c1(x, y, z REFERENCES p1(c));
|
|
CREATE TABLE c2(x, y, z, w REFERENCES p1(b));
|
|
ALTER TABLE c1 DROP COLUMN z;
|
|
ALTER TABLE c2 DROP COLUMN z;
|
|
SELECT sql FROM sqlite_schema WHERE name IN ('c1', 'c2');
|
|
COMMIT TRANSACTION;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE +x<31) SELECT x FROM cnt WHERE +x > 15;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_c1_2917 AS SELECT z FROM c1;
|
|
SELECT * FROM c2 AS a FULL OUTER JOIN c1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM p1;
|
|
UPDATE c2 SET z = NULL WHERE rowid = 1;
|
|
CREATE INDEX IF NOT EXISTS idx_c2_3327 ON c2(lower(x)) WHERE x > 0;
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE y IS NOT NULL), SUM(rowid) FILTER (WHERE y > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(y) FILTER (WHERE y > 0 AND y < 100), COUNT(*) FILTER (WHERE typeof(y) = "text") FROM c1;
|
|
REINDEX p1;
|
|
ALTER TABLE c2 RENAME COLUMN w TO w_r831;
|
|
CREATE TABLE T1 (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15) UNIQUE,
|
|
C BIGINT
|
|
);
|
|
CREATE TABLE T2 (
|
|
X VARCHAR(20) PRIMARY KEY,
|
|
A VARCHAR(10),
|
|
FOREIGN KEY (A) REFERENCES T1(A)
|
|
);
|
|
INSERT INTO T1 VALUES ('a', 'p', 9999999999999);
|
|
INSERT INTO T1 VALUES ('b', 'q', -9999999999999);
|
|
SELECT 0x0ACB29BM205381;
|
|
SELECT sqlite_compileoption_used('æçå¤©ä½ æ¯æä¹äº');
|
|
SELECT 1 >> -999999999999999999999999999;
|
|
SELECT sqlite_compileoption_get($1);
|
|
SELECT sqlite_version();
|
|
INSERT INTO T2 VALUES ('m', 'a');
|
|
INSERT INTO T2 VALUES ('n', 'b');
|
|
SELECT T2.X, T1.C FROM T2 INNER JOIN T1 ON T2.A = T1.A WHERE T1.C > -1000000000000;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM c2 t1 LEFT JOIN c2 t2 ON t1.w = (SELECT w FROM c2 WHERE w = t1.w);
|
|
ALTER TABLE p1 DROP COLUMN b;
|
|
ANALYZE;
|
|
SELECT COUNT(*) FROM T2;
|
|
INSERT INTO T1 DEFAULT VALUES;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
SELECT * FROM t1 AS a RIGHT JOIN c2 AS b ON a.rowid = b.rowid;
|
|
DETACH DATABASE aux20;
|
|
RELEASE sp1369;
|
|
SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM T;
|
|
DROP VIEW IF EXISTS T1;
|
|
INSERT INTO p1 DEFAULT VALUES;
|
|
|
|
SELECT COUNT(*) FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM c2;
|
|
ALTER TABLE c1 RENAME COLUMN z TO z_r2716;
|
|
SELECT * FROM c2;
|
|
DROP INDEX IF EXISTS p1;
|
|
RELEASE sp800;
|
|
|
|
SELECT COUNT(*) FROM c1;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
ANALYZE;
|
|
INSERT INTO c1 SELECT * FROM c1;
|
|
INSERT INTO p1 VALUES (NULL, NULL);
|
|
DROP TABLE IF EXISTS T2;
|
|
|
|
SELECT COUNT(*) FROM T1;
|
|
REINDEX;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T2_6563 AFTER UPDATE OF A ON T2 BEGIN INSERT INTO T2(A) VALUES (NULL); END;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T_8673 AS SELECT a FROM T;
|
|
WITH cte AS (SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T1 WHERE A > 0)) SELECT * FROM cte;
|
|
ALTER TABLE T2 RENAME COLUMN X TO X_r1036;
|
|
ALTER TABLE T ADD COLUMN extra_3319 SMALLINT DEFAULT (random());
|
|
SELECT * FROM c1;
|
|
INSERT INTO T2 SELECT * FROM T2;
|
|
CREATE TABLE T1 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
CREATE TABLE T2 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
INSERT INTO T1 VALUES (1,'a'),(2,'b');
|
|
INSERT INTO T2 VALUES (1,'c'),(1,'d');
|
|
SELECT T1.b,
|
|
(
|
|
SELECT GROUP_CONCAT(b,',')
|
|
FROM T2
|
|
WHERE T2.a=T1.a
|
|
) AS c
|
|
FROM T1;
|
|
INSERT INTO c2 DEFAULT VALUES;
|
|
SELECT * FROM T2;
|
|
.timeout 999999
|
|
PRAGMA parser_trace = OFF;
|
|
-- zeroblob({v})
|
|
|
|
.stats off
|
|
SAVEPOINT sp800;
|
|
SAVEPOINT sp1369;
|
|
ATTACH DATABASE ':memory:' AS aux20;
|
|
PRAGMA short_column_names = YES;
|
|
.progress datetime('Inf', '+1 day')
|
|
.shell pwd
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b INTEGER,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (('a' || ''),ceiling(x'c5cb906e1c8cc13d197d23f8b3cc1209'),NULL), ('a',-zeroblob(','),-'%'), ('b',CAST(3.141592653589793 AS INT8),4.5);
|
|
SELECT a,b,c,
|
|
SUM(c) OVER (
|
|
PARTITION BY a
|
|
ORDER BY b
|
|
) AS d
|
|
FROM T;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (like('_1%', 1, '_'), 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
PRAGMA writable_schema = ON;
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
.quit
|
|
BEGIN DEFERRED;
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES ('a',100.0), ('b',-200.5), ('c',9e999);
|
|
SELECT a,b,
|
|
LEAD(b,1) OVER (ORDER BY b DESC) AS c,
|
|
NTILE(2) OVER (ORDER BY b DESC) AS d FROM T;
|
|
PRAGMA cache_spill = NO;
|
|
PRAGMA vdbe_listing = inf;
|
|
PRAGMA empty_result_callbacks = 0;
|
|
.timeout
|
|
CREATE TABLE [p1](a PRIMARY KEY, b UNIQUE);
|
|
CREATE TABLE c1(x, y, z REFERENCES p1(c));
|
|
CREATE TABLE c2(x, y, z, w REFERENCES p1(b));
|
|
ALTER TABLE c1 DROP COLUMN z;
|
|
ALTER TABLE c2 DROP COLUMN z;
|
|
SELECT sql FROM sqlite_schema WHERE name IN ('c1', 'c2');
|
|
COMMIT TRANSACTION;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE +x<31) SELECT x FROM cnt WHERE +x > 15;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_c1_2917 AS SELECT z FROM c1;
|
|
SELECT * FROM c2 AS a FULL OUTER JOIN c1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM p1;
|
|
UPDATE c2 SET z = NULL WHERE rowid = 1;
|
|
CREATE INDEX IF NOT EXISTS idx_c2_3327 ON c2(lower(x)) WHERE x > 0;
|
|
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
|
|
DROP TABLE t0;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE y IS NOT NULL), SUM(rowid) FILTER (WHERE y > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(y) FILTER (WHERE y > 0 AND y < 100), COUNT(*) FILTER (WHERE typeof(y) = "text") FROM c1;
|
|
REINDEX p1;
|
|
ALTER TABLE c2 RENAME COLUMN w TO w_r831;
|
|
CREATE TABLE T1 (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15) UNIQUE,
|
|
C BIGINT
|
|
);
|
|
CREATE TABLE T2 (
|
|
X VARCHAR(20) PRIMARY KEY,
|
|
A VARCHAR(10),
|
|
FOREIGN KEY (A) REFERENCES T1(A)
|
|
);
|
|
INSERT INTO T1 VALUES ('a', 'p', 9999999999999);
|
|
INSERT INTO T1 VALUES ('b', 'q', -9999999999999);
|
|
SELECT 0x0ACB29BM205381;
|
|
SELECT sqlite_compileoption_used('æçå¤©ä½ æ¯æä¹äº');
|
|
SELECT 1 >> -999999999999999999999999999;
|
|
SELECT sqlite_compileoption_get($1);
|
|
SELECT sqlite_version();
|
|
INSERT INTO T2 VALUES ('m', 'a');
|
|
INSERT INTO T2 VALUES ('n', 'b');
|
|
SELECT T2.X, T1.C FROM T2 INNER JOIN T1 ON T2.A = T1.A WHERE T1.C > -1000000000000;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM c2 t1 LEFT JOIN c2 t2 ON t1.w = (SELECT w FROM c2 WHERE w = t1.w);
|
|
ALTER TABLE p1 DROP COLUMN b;
|
|
ANALYZE;
|
|
SELECT COUNT(*) FROM T2;
|
|
INSERT INTO T1 DEFAULT VALUES;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
SELECT * FROM t1 AS a RIGHT JOIN c2 AS b ON a.rowid = b.rowid;
|
|
DETACH DATABASE aux20;
|
|
RELEASE sp1369;
|
|
SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM T;
|
|
DROP VIEW IF EXISTS T1;
|
|
INSERT INTO p1 DEFAULT VALUES;
|
|
|
|
SELECT COUNT(*) FROM c2;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM c2;
|
|
ALTER TABLE c1 RENAME COLUMN z TO z_r2716;
|
|
SELECT * FROM c2;
|
|
DROP INDEX IF EXISTS p1;
|
|
RELEASE sp800;
|
|
|
|
SELECT COUNT(*) FROM c1;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
ANALYZE;
|
|
INSERT INTO c1 SELECT * FROM c1;
|
|
INSERT INTO p1 VALUES (NULL, NULL);
|
|
DROP TABLE IF EXISTS T2;
|
|
|
|
SELECT COUNT(*) FROM T1;
|
|
REINDEX;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T2_6563 AFTER UPDATE OF A ON T2 BEGIN INSERT INTO T2(A) VALUES (NULL); END;
|
|
INSERT INTO T2 VALUES (NULL, NULL);
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T_8673 AS SELECT a FROM T;
|
|
WITH cte AS (SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T1 WHERE A > 0)) SELECT * FROM cte;
|
|
ALTER TABLE T2 RENAME COLUMN X TO X_r1036;
|
|
ALTER TABLE T ADD COLUMN extra_3319 SMALLINT DEFAULT (random());
|
|
SELECT * FROM c1;
|
|
INSERT INTO T2 SELECT * FROM T2;
|
|
CREATE TABLE T1 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
CREATE TABLE T2 (
|
|
a INTEGER,
|
|
b TEXT
|
|
);
|
|
INSERT INTO T1 VALUES (1,'a'),(2,'b');
|
|
INSERT INTO T2 VALUES (1,'c'),(1,'d');
|
|
SELECT T1.b,
|
|
(
|
|
SELECT GROUP_CONCAT(b,',')
|
|
FROM T2
|
|
WHERE T2.a=T1.a
|
|
) AS c
|
|
FROM T1;
|
|
INSERT INTO c2 DEFAULT VALUES;
|
|
SELECT * FROM T2;
|
|
|
|
ALTER TABLE T RENAME TO T_r4684;
|
|
PRAGMA mmap_size = 50000000;
|
|
CREATE TEMP VIEW IF NOT EXISTS v_c2_7233 AS SELECT y FROM c2;
|
|
|
|
SELECT TOTAL(a) FROM T2;
|
|
REINDEX T2;
|
|
CREATE VIEW IF NOT EXISTS v_T2_6576 AS SELECT b FROM T2;
|
|
WITH cte AS (SELECT COUNT(*) AS cnt FROM T) SELECT * FROM cte WHERE cnt > 0;
|
|
UPDATE T2 SET b = json_object('k', b) WHERE b BETWEEN 0 AND 100;
|
|
SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM T1;
|
|
WITH cte AS (SELECT * FROM T) SELECT (SELECT COUNT(*) FROM cte) AS total, * FROM cte LIMIT 1;
|
|
UPDATE T1 SET a = json_object('k', a) WHERE 1 RETURNING *;
|
|
DELETE FROM T1 WHERE rowid = 76;
|
|
SELECT a FROM T WHERE a = (SELECT MAX(a) FROM T);
|
|
CREATE TRIGGER IF NOT EXISTS trg_T1_1437 BEFORE INSERT ON T1 FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
DELETE FROM c1 WHERE x IS NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_T1_7644 ON T1(lower(a)) WHERE a IS NOT NULL;
|
|
INSERT INTO T1 DEFAULT VALUES;
|
|
UPDATE T2 SET a = json_object('k', a) WHERE 1;
|
|
WITH cte AS (SELECT a FROM T1 WHERE a IS NULL) SELECT * FROM cte;
|
|
WITH cte AS (SELECT * FROM T1) SELECT * FROM cte JOIN T1 ON cte.b = T1.b;
|
|
DETACH DATABASE aux40;
|
|
ALTER TABLE T1 ADD COLUMN extra_6730 NONE DEFAULT '';
|
|
PRAGMA vdbe_debug = YES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T2_7161 AFTER DELETE ON T2 FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
INSERT OR IGNORE INTO T VALUES (5, 8);
|
|
SELECT STRING_AGG(b) FROM T2;
|
|
CREATE TRIGGER IF NOT EXISTS trg_c1_50 BEFORE UPDATE OF x ON c1 BEGIN SELECT RAISE(IGNORE); END;
|
|
ANALYZE T1;
|
|
```
|
|
|
|
## Actual output
|
|
|
|
```sql
|
|
exclusive
|
|
1000000
|
|
/fuzzer
|
|
a|NU11|NU11|NU11
|
|
a|0|0.0|0.0
|
|
b|NU11|4.5|4.5
|
|
CREATE TABLE "t2"("b","c","_ROWID_",PRIMARY KEY("b","c","_ROWID_"))WITHOUT ROWID;
|
|
WARNING: writing to an imposter table will corrupt the "t1bc" index!
|
|
Braun|30|3
|
|
Wernher|10|1
|
|
von|20|2
|
|
Braun|30
|
|
Wernher|10
|
|
von|20
|
|
```
|
|
|
|
## Expectation
|
|
|
|
```sql
|
|
exclusive
|
|
1000000
|
|
/fuzzer
|
|
a|NU11|NU11|NU11
|
|
a|0|0.0|0.0
|
|
b|NU11|4.5|4.5
|
|
CREATE TABLE "t2"("b","c","_ROWID_",PRIMARY KEY("b","c","_ROWID_"))WITHOUT ROWID;
|
|
Braun|30|3
|
|
Wernher|10|1
|
|
von|20|2
|
|
Braun|30
|
|
Wernher|10
|
|
von|20
|
|
```
|
|
|
|
## Flag
|
|
|
|
```
|
|
-nullvalue "NU11"
|
|
```
|
|
|