291 lines
10 KiB
Markdown
291 lines
10 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
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (format('%d', '&'), 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
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
|
|
.vfsname
|
|
BEGIN DEFERRED TRANSACTION;
|
|
ATTACH DATABASE ':memory:' AS aux66;
|
|
PRAGMA checkpoint_fullsync = sign(NULL);
|
|
.connection close
|
|
PRAGMA temp_store_directory;
|
|
-- -mmap 5
|
|
|
|
PRAGMA count_changes = YES;
|
|
-- PRAGMA short_column_names = NO;
|
|
.save /dev/null
|
|
PRAGMA fullsync = NO;
|
|
.log OFF
|
|
CREATE TABLE T (
|
|
A VARCHAR(CAST(coalesce(jsonb_object(), 'fallback') AS MEDIUMINT)) PRIMARY KEY,
|
|
B VARCHAR('`') NOT NULL,
|
|
C BIGINT UNIQUE
|
|
);
|
|
INSERT INTO T VALUES ('a', 'x', -unhex('48656C6C6F'));
|
|
INSERT INTO T VALUES ('b', 'y', -x'447943ac280a');
|
|
INSERT INTO T VALUES ('c', 'y', (concat_ws('|', NULL, 'a', 'b') + 0));
|
|
INSERT INTO T VALUES ('d', 'y', 42);
|
|
INSERT INTO T VALUES ('e', 'x', 9223372036854775807);
|
|
INSERT INTO T VALUES ('f', 'z', 2147483648);
|
|
WITH _m AS MATERIALIZED (WITH _m AS MATERIALIZED (SELECT * FROM T) SELEC_m * FROM T) SELEC_m * FROM T WHERE NOT NOT (NOT NOT (NOT NOT (C > 0))) ORDER BY C DESC;
|
|
PRAGMA full_column_names;
|
|
SAVEPOINT sp8342;
|
|
.eqp on
|
|
ATTACH DATABASE ':memory:' AS aux74;
|
|
PRAGMA vdbe_addoptrace = replace((1 + 0), 'a', NULL);
|
|
PRAGMA page_size;
|
|
.dbinfo main
|
|
CREATE TABLE T1 (
|
|
A VARCHAR(15) PRIMARY KEY,
|
|
B VARCHAR(30) NOT NULL UNIQUE
|
|
);
|
|
CREATE TABLE T2 (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
X VARCHAR(15) NOT NULL,
|
|
C BIGINT,
|
|
FOREIGN KEY (X) REFERENCES T1(A)
|
|
);
|
|
INSERT INTO T1 VALUES ('p', 'x');
|
|
INSERT INTO T1 VALUES ('q', 'y');
|
|
INSERT INTO T2 VALUES ('a', 'p', 9223372036854775807);
|
|
INSERT INTO T2 VALUES ('b', 'q', -9223372036854775808);
|
|
SELECT X, SUM(C) AS D, COUNT(*) AS E FROM T2 GROUP BY X;
|
|
BEGIN IMMEDIATE TRANSACTION;
|
|
CREATE TABLE T (
|
|
A VARCHAR(-zeroblob(upper(NULL))) PRIMARY KEY,
|
|
B VARCHAR(CAST(30 AS SMALLINT)) NOT NULL,
|
|
C BIGINT UNIQUE
|
|
);
|
|
INSERT INTO T VALUES ('a', 'x', -9223372036854775808);
|
|
INSERT INTO T VALUES ('b', 'y', -1000000);
|
|
INSERT INTO T VALUES ('c', 'y', 0);
|
|
INSERT INTO T VALUES ('d', 'y', 42);
|
|
INSERT INTO T VALUES ('e', 'x', 9223372036854775807);
|
|
INSERT INTO T VALUES ('f', 'z', 2147483648);
|
|
SELECT * FROM T WHERE C > 0 ORDER BY C DESC;
|
|
SAVEPOINT sp6597;
|
|
SAVEPOINT sp2363;
|
|
BEGIN DEFERRED;
|
|
ATTACH DATABASE ':memory:' AS aux1;
|
|
ATTACH DATABASE (':memory:' || '') AS aux67;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c INTEGER
|
|
);
|
|
INSERT INTO T VALUES (NULL,'a',NULL), (date(json_object('k', json(mod(-datetime(like('_', -6873762334067965370, '#')) -> '$.a' -> '$.b', 9223372036854775806))), 'start of month'),'b',1), (3,'c',2);
|
|
WITH RECURSIVE d(a,b,c,e) AS (
|
|
SELECT *,1 FROM T WHERE c IS NULL
|
|
UNION ALL
|
|
SELECT T.a,T.b,T.c,d.e+1
|
|
FROM T LEFT OUTER JOIN d ON T.c<=d.a
|
|
)
|
|
SELECT * FROM d;
|
|
DETACH DATABASE aux67;
|
|
SELECT COUNT(c) FILTER (WHERE NULL) OVER (PARTITION BY c ORDER BY c ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP) FROM T;
|
|
INSERT OR FAIL INTO T VALUES (NULL, 'x', 'x');
|
|
ALTER TABLE T DROP COLUMN b;
|
|
PRAGMA count_changes = FALSE;
|
|
CREATE VIEW IF NOT EXISTS v_T_1517 AS SELECT a FROM T;
|
|
SELECT COUNT(*) FROM T;
|
|
SELECT MIN(a) FILTER (WHERE a IS NOT NULL) OVER (ORDER BY a) FROM T;
|
|
SELECT COUNT(*) FROM T;
|
|
ALTER TABLE T DROP COLUMN b;
|
|
DELETE FROM T WHERE rowid = 42 RETURNING *;
|
|
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;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (1,1.5), (2,-2.5), (3,0.0);
|
|
CREATE INDEX c
|
|
ON T(b);
|
|
SELECT * FROM T WHERE b >= -2.5 AND b < 2.0;
|
|
INSERT INTO T VALUES (NULL, NULL);
|
|
INSERT OR REPLACE INTO T VALUES (0, 0);
|
|
DETACH DATABASE aux1;
|
|
SELECT * FROM T WHERE b IN (SELECT b FROM T WHERE b GLOB "*");
|
|
REINDEX;
|
|
PRAGMA automatic_index = FALSE;
|
|
SELECT * FROM T WHERE a >= ALL (SELECT a FROM T);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_T_8746 ON T(b COLLATE NOCASE) WHERE b IS NOT NULL;
|
|
INSERT INTO T VALUES (NULL, 'x') ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (1,1.5), (2,-2.5), (3,0.0);
|
|
CREATE INDEX c
|
|
ON T(b);
|
|
SELECT * FROM T WHERE b >= -2.5 AND b < 2.0;
|
|
INSERT INTO T VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FILTER (WHERE b IS NOT NULL), SUM(rowid) FILTER (WHERE b > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(b) FILTER (WHERE b > 0 AND b < 100), COUNT(*) FILTER (WHERE typeof(b) = "text") FROM T;
|
|
ANALYZE;
|
|
WITH cte AS (SELECT 1 AS x) SELECT x + 0.5 FROM cte;
|
|
COMMIT TRANSACTION;
|
|
DROP TABLE IF EXISTS T;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_T_6152 AS SELECT b FROM T;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,2.0,10.5), (1,-3.5,20.25), (2,9e999,-1.0);
|
|
WITH d AS (SELECT a,SUM(b*c) AS e FROM T GROUP BY a) SELECT * FROM d;
|
|
RELEASE sp2363;
|
|
INSERT INTO T VALUES (NULL, NULL);
|
|
ALTER TABLE T RENAME COLUMN a TO a_r9141;
|
|
INSERT INTO T VALUES (1, 'x') ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_T_2246 AS SELECT b FROM T;
|
|
SELECT * FROM T;
|
|
REINDEX T;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(10),
|
|
C INTEGER,
|
|
PRIMARY KEY (A, B)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', -2147483648);
|
|
INSERT INTO T VALUES ('b', 'q', 100);
|
|
INSERT INTO T VALUES ('b', 'r', 200);
|
|
INSERT INTO T VALUES ('c', 's', 300);
|
|
INSERT INTO T VALUES ('d', 't', 2147483647);
|
|
INSERT INTO T VALUES ('b', 't', 50);
|
|
SELECT A, COUNT(*) AS D, SUM(C) AS E FROM T GROUP BY A HAVING COUNT(*) > 1;
|
|
ROLLBACK TRANSACTION TO SAVEPOINT sp6597;
|
|
RELEASE SAVEPOINT sp6597;
|
|
SELECT LAG(b, 1) OVER (PARTITION BY b ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM T;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_9622 AFTER UPDATE OF a ON T BEGIN SELECT RAISE(ABORT, 'abort'); END;
|
|
DROP INDEX IF EXISTS T;
|
|
SELECT COUNT(*) FILTER (WHERE C IS NOT NULL), SUM(rowid) FILTER (WHERE C > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(C) FILTER (WHERE C > 0 AND C < 100), COUNT(*) FILTER (WHERE typeof(C) = "text") FROM T;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_T_5161 AS SELECT A FROM T;
|
|
PRAGMA cache_spill = 1234;
|
|
COMMIT;
|
|
SELECT * FROM T2 AS a RIGHT OUTER JOIN T1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM T1;
|
|
DETACH DATABASE aux74;
|
|
DROP VIEW IF EXISTS T;
|
|
SELECT COUNT(*) FROM T2;
|
|
DROP TABLE IF EXISTS T2;
|
|
ROLLBACK TO sp8342;
|
|
RELEASE sp8342;
|
|
INSERT INTO T1 VALUES (NULL, NULL);
|
|
SELECT * FROM T2 WHERE X > (SELECT COUNT(*) FROM T2);
|
|
INSERT INTO T1 VALUES (NULL, 1) ON CONFLICT(A) DO UPDATE SET A = excluded.A, B = excluded.B;
|
|
CREATE TABLE T (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15) NOT NULL,
|
|
C INTEGER,
|
|
UNIQUE (B, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', -2147483648);
|
|
INSERT INTO T VALUES ('b', 'q', 700);
|
|
INSERT INTO T VALUES ('c', 'r', 0);
|
|
INSERT INTO T VALUES ('d', 's', 2147483647);
|
|
INSERT INTO T VALUES ('e', 't', -1);
|
|
INSERT INTO T VALUES ('f', 'u', 1);
|
|
SELECT * FROM T WHERE C BETWEEN -1000 AND 1000 ORDER BY C ASC;
|
|
SELECT * FROM T2;
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T2_9099 AS SELECT A FROM T2;
|
|
DROP INDEX IF EXISTS T1;
|
|
PRAGMA wal_checkpoint('TRUNCATE');
|
|
CREATE TRIGGER IF NOT EXISTS trg_T2_252 BEFORE UPDATE OF X ON T2 BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
SELECT FIRST_VALUE(A) OVER (ORDER BY A ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP) FROM T1;
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T_5251 AS SELECT C FROM T;
|
|
ANALYZE T2;
|
|
REINDEX;
|
|
SELECT * FROM T WHERE B = ANY (SELECT B FROM T);
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_4566 AFTER UPDATE OF A ON T FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
INSERT OR ROLLBACK INTO T2 VALUES (NULL, NULL, 0);
|
|
ANALYZE;
|
|
DROP VIEW IF EXISTS T;
|
|
UPDATE T1 SET A = CURRENT_TIMESTAMP WHERE rowid = 1 RETURNING *;
|
|
DELETE FROM T2 WHERE A IS NULL;
|
|
INSERT INTO T1 VALUES (NULL, NULL);
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T1_792 AS SELECT A FROM T1;
|
|
DROP INDEX IF EXISTS T2;
|
|
SELECT * FROM T1 WHERE A = ALL (SELECT A FROM T1);
|
|
SELECT COUNT(*) FILTER (WHERE C IS NOT NULL), SUM(rowid) FILTER (WHERE C > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(C) FILTER (WHERE C > 0 AND C < 100), COUNT(*) FILTER (WHERE typeof(C) = "text") FROM T;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T1_9486 BEFORE UPDATE ON T1 BEGIN SELECT RAISE(ABORT, 'abort'); END;
|
|
INSERT INTO T SELECT * FROM T;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_T_4296 AS SELECT A FROM T;
|
|
SELECT COUNT(*) FILTER (WHERE B IS NOT NULL), SUM(rowid) FILTER (WHERE B > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(B) FILTER (WHERE B > 0 AND B < 100), COUNT(*) FILTER (WHERE typeof(B) = "text") FROM T;
|
|
ALTER TABLE T RENAME TO T_r5102;
|
|
INSERT INTO T1 VALUES (1, 1) ON CONFLICT(A) DO UPDATE SET A = excluded.A, B = excluded.B;
|
|
REINDEX;
|
|
INSERT INTO T1 VALUES ('x', 1) ON CONFLICT(A) DO UPDATE SET A = excluded.A, B = excluded.B;
|
|
INSERT INTO T VALUES (NULL, NULL, NULL);
|
|
REINDEX;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_T1_1950 ON T1(lower(B));
|
|
SELECT * FROM T;
|
|
DETACH DATABASE aux66;
|
|
INSERT OR ABORT INTO T VALUES ('x', NULL, '');
|
|
INSERT INTO T VALUES ('x', 1, 'x') ON CONFLICT(A) DO UPDATE SET A = excluded.A, B = excluded.B, C = excluded.C;
|
|
INSERT INTO T1 SELECT * FROM T1;
|
|
ALTER TABLE T RENAME TO T_r3658;
|
|
DROP TABLE IF EXISTS T;
|
|
INSERT INTO T VALUES ('x', NULL, NULL) ON CONFLICT(A) DO UPDATE SET A = excluded.A, B = excluded.B, C = excluded.C;
|
|
INSERT INTO T2 DEFAULT VALUES;
|
|
ALTER TABLE T2 RENAME COLUMN C TO C_r6159;
|
|
END TRANSACTION;
|
|
SELECT * FROM T2;
|
|
DELETE FROM t1 WHERE 0;
|
|
SELECT MAX(a) FROM t1;
|
|
```
|
|
|
|
## Actual output
|
|
|
|
```sql
|
|
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|0
|
|
von|20|2
|
|
Braun|30
|
|
Wernher|10
|
|
von|20
|
|
```
|
|
|
|
## Expectation
|
|
|
|
```sql
|
|
CREATE TABLE "t2"("b","c","_ROWID_",PRIMARY KEY("b","c","_ROWID_"))WITHOUT ROWID;
|
|
Braun|30|3
|
|
Wernher|10|0
|
|
von|20|2
|
|
Braun|30
|
|
Wernher|10
|
|
von|20
|
|
```
|
|
|
|
## Flag
|
|
|
|
```
|
|
-safe
|
|
```
|
|
|