admin/ast-project
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
ast-project/part1/results/2/bug_bc447b9b-c916-4573-af87-b205066e0fec_unexpected_error.md
T
admin fd930e15cb ---
2026-06-24 13:47:14 +02:00

24 KiB
Raw Permalink Blame History

Summary

**No review yet**

Minimized query

BEGIN DEFERRED;
CREATE TABLE T (
  a TEXT,
  b TEXT,
  c REAL
);
INSERT INTO T VALUES ('a','b',round(max(NULL, concat_ws('|', NULL, NULL, NULL)), 2)), ('a','c',5.0), ('b','d',-8.25);
SELECT a,b,c,
RANK() OVER (PARTITION BY a ORDER BY c DESC) AS d FROM T;
SAVEPOINT sp2718;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (-instr(NULL, NULL), 'Wernher', CAST(random() % -(-(-9223372036854775807)) AS BOOLEAN), 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
SAVEPOINT sp4982;
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;
.auth OFF
BEGIN EXCLUSIVE;
PRAGMA temp_store = 'DEFAULT';
SAVEPOINT sp8774;
ATTACH ':memory:' AS aux1;
CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(trim('xxxhelloxxx', 'x'),'x1');
CREATE TABLE t2(a,b); /**/ INSERT INTO t2 VALUES(222,'x2');
CREATE TEMP TABLE t3(a,b); INSERT INTO t3 VALUES(333,'x3');
CREATE TABLE main.t4(a,b); INSERT INTO main.t4 VALUES(444,'x4');
CREATE TABLE aux1.t4(a,b); INSERT INTO aux1.t4 VALUES(555,'x5');
UPDATE t2 SET a=111;
UPDATE t3 SET a=111;
UPDATE t4 SET a=111;
SELECT * FROM (t1), (t2), (t3), (t4) WHERE t4.a=t3.a+111 OR t3.a=t2.a+111 AND t2.a=t1.a+111;
ANALYZE;
WITH cte AS (SELECT a, LAG(a) OVER (ORDER BY a) AS prev FROM t2) SELECT * FROM cte;
DELETE FROM t4 WHERE 0;
SELECT COUNT(*) FROM t4;
ALTER TABLE t4 RENAME TO t4_r261;
RELEASE sp8774;
ALTER TABLE t1 ADD COLUMN extra_1569 DOUB DEFAULT '';
INSERT INTO t2 VALUES (NULL, NULL);
INSERT INTO t2 DEFAULT VALUES;
SELECT NTILE(1) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t1;
CREATE TABLE T1 (
  A VARCHAR(10) PRIMARY KEY,
  B VARCHAR(15) UNIQUE,
  C INTEGER
);
CREATE TABLE T2 (
  X VARCHAR(20) PRIMARY KEY,
  A VARCHAR(10) NOT NULL UNIQUE,
  FOREIGN KEY (A) REFERENCES T1(A)
);
INSERT INTO T1 VALUES ('a', 'p', -2147483648);
INSERT INTO T1 VALUES ('b', 'q', 2147483647);
INSERT INTO T2 VALUES ('m', 'a');
INSERT INTO T2 VALUES ('n', 'b');
SELECT T2.X, T1.B, T1.C FROM T2, T1 WHERE T2.A = T1.A AND T1.C >= 0;
SELECT * FROM t2 WHERE a IN (SELECT a FROM t2 WHERE a IN (SELECT a FROM t2));
PRAGMA reverse_unordered_selects = TRUE;
INSERT INTO t2 VALUES (NULL, NULL);
ROLLBACK;
PRAGMA full_column_names = NO;
SELECT COUNT(*) FROM t3;
CREATE  VIEW IF NOT EXISTS v_T2_9810 AS SELECT C FROM T2;
ALTER TABLE t3 DROP COLUMN b;
WITH a AS (SELECT a FROM t1), b AS (SELECT a FROM a WHERE a IS NOT NULL), c AS (SELECT COUNT(*) AS cnt FROM b) SELECT cnt FROM c;
WITH cte AS (SELECT A FROM T2 EXCEPT SELECT A FROM T2) SELECT * FROM cte;
ROLLBACK TO sp4982;
RELEASE sp4982;
SELECT * FROM t4;
INSERT INTO T1 VALUES (NULL, NULL);
REINDEX;
DROP TRIGGER IF EXISTS T2;
ANALYZE;
RELEASE sp2718;
SELECT * FROM t4 WHERE b NOT IN (SELECT b FROM t4);
INSERT INTO t3 VALUES (1, NULL) ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b;
INSERT INTO t1 VALUES (NULL, 'x') ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b;
ALTER TABLE t3 DROP COLUMN a;
SELECT * FROM t3;
CREATE TABLE T1 (
  A VARCHAR(10) PRIMARY KEY,
  B VARCHAR(15) UNIQUE,
  C INTEGER
);
CREATE TABLE T2 (
  X VARCHAR(20) PRIMARY KEY,
  A VARCHAR(10) NOT NULL UNIQUE,
  FOREIGN KEY (A) REFERENCES T1(A)
);
INSERT INTO T1 VALUES ('a', 'p', -2147483648);
INSERT INTO T1 VALUES ('b', 'q', 2147483647);
INSERT INTO T2 VALUES ('m', 'a');
INSERT INTO T2 VALUES ('n', 'b');
SELECT T2.X, T1.B, T1.C FROM T2, T1 WHERE T2.A = T1.A AND T1.C >= 0;
CREATE TABLE T (
  a TEXT,
  b TEXT,
  c REAL
);
INSERT INTO T VALUES ('a','b',round(max(NULL, 5.0), 2)), ('a','c',5.0), ('b','d',-8.25);
SELECT a,b,c,
RANK() OVER (PARTITION BY a ORDER BY c DESC) AS d FROM T;
SAVEPOINT sp2718;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (-instr(NULL, NULL), 'Wernher', CAST(random() % -(-(-9223372036854775807)) AS BOOLEAN), 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
SAVEPOINT sp4982;
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;
.auth OFF
BEGIN EXCLUSIVE;
PRAGMA temp_store = 'DEFAULT';
SAVEPOINT sp8774;
ATTACH ':memory:' AS aux1;
CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(trim('xxxhelloxxx', 'x'),'x1');
CREATE TABLE t2(a,b); /**/ INSERT INTO t2 VALUES(222,'x2');
CREATE TEMP TABLE t3(a,b); INSERT INTO t3 VALUES(333,'x3');
CREATE TABLE main.t4(a,b); INSERT INTO main.t4 VALUES(444,'x4');
CREATE TABLE aux1.t4(a,b); INSERT INTO aux1.t4 VALUES(555,'x5');
UPDATE t2 SET a=111;
UPDATE t3 SET a=111;
UPDATE t4 SET a=111;
SELECT * FROM (t1), (t2), (t3), (t4) WHERE t4.a=t3.a+111 OR t3.a=t2.a+111 AND t2.a=t1.a+111;
ANALYZE;
WITH cte AS (SELECT a, LAG(a) OVER (ORDER BY a) AS prev FROM t2) SELECT * FROM cte;
DELETE FROM t4 WHERE 0;
SELECT COUNT(*) FROM t4;
ALTER TABLE t4 RENAME TO t4_r261;
RELEASE sp8774;
ALTER TABLE t1 ADD COLUMN extra_1569 DOUB DEFAULT '';
INSERT INTO t2 VALUES (NULL, NULL);
INSERT INTO t2 DEFAULT VALUES;
SELECT NTILE(1) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t1;
CREATE TABLE T1 (
  A VARCHAR(10) PRIMARY KEY,
  B VARCHAR(15) UNIQUE,
  C INTEGER
);
CREATE TABLE T2 (
  X VARCHAR(20) PRIMARY KEY,
  A VARCHAR(10) NOT NULL UNIQUE,
  FOREIGN KEY (A) REFERENCES T1(A)
);
INSERT INTO T1 VALUES ('a', 'p', -2147483648);
INSERT INTO T1 VALUES ('b', 'q', 2147483647);
INSERT INTO T2 VALUES ('m', 'a');
INSERT INTO T2 VALUES ('n', 'b');
SELECT T2.X, T1.B, T1.C FROM T2, T1 WHERE T2.A = T1.A AND T1.C >= 0;
SELECT * FROM t2 WHERE a IN (SELECT a FROM t2 WHERE a IN (SELECT a FROM t2));
PRAGMA reverse_unordered_selects = TRUE;
INSERT INTO t2 VALUES (NULL, NULL);
ROLLBACK;
PRAGMA full_column_names = NO;
SELECT COUNT(*) FROM t3;
CREATE  VIEW IF NOT EXISTS v_T2_9810 AS SELECT C FROM T2;
ALTER TABLE t3 DROP COLUMN b;
WITH a AS (SELECT a FROM t1), b AS (SELECT a FROM a WHERE a IS NOT NULL), c AS (SELECT COUNT(*) AS cnt FROM b) SELECT cnt FROM c;
WITH cte AS (SELECT A FROM T2 EXCEPT SELECT A FROM T2) SELECT * FROM cte;
ROLLBACK TO sp4982;
RELEASE sp4982;
SELECT * FROM t4;
INSERT INTO T1 VALUES (NULL, NULL);
REINDEX;
DROP TRIGGER IF EXISTS T2;
ANALYZE;
RELEASE sp2718;
SELECT * FROM t4 WHERE b NOT IN (SELECT b FROM t4);
INSERT INTO t3 VALUES (1, NULL) ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b;
INSERT INTO t1 VALUES (NULL, 'x') ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b;
ALTER TABLE t3 DROP COLUMN a;
SELECT * FROM t3;
CREATE TABLE T1 (
  A VARCHAR(10) PRIMARY KEY,
  B VARCHAR(15) UNIQUE,
  C INTEGER
);
CREATE TABLE T2 (
  X VARCHAR(20) PRIMARY KEY,
  A VARCHAR(10) NOT NULL UNIQUE,
  FOREIGN KEY (A) REFERENCES T1(A)
);
INSERT INTO T1 VALUES ('a', 'p', -2147483648);
INSERT INTO T1 VALUES ('b', 'q', 2147483647);
INSERT INTO T2 VALUES ('m', 'a');
INSERT INTO T2 VALUES ('n', 'b');
SELECT T2.X, T1.B, T1.C FROM T2, T1 WHERE T2.A = T1.A AND T1.C >= 0;
CREATE TABLE T (
  a TEXT,
  b TEXT,
  c REAL
);
INSERT INTO T VALUES ('a','b',round(max(NULL, 5.0), 2)), ('a','c',5.0), ('b','d',-8.25);
SELECT a,b,c,
RANK() OVER (PARTITION BY a ORDER BY c DESC) AS d FROM T;
SAVEPOINT sp2718;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (-instr(NULL, NULL), 'Wernher', CAST(random() % -(-(-9223372036854775807)) AS BOOLEAN), 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
SAVEPOINT sp4982;
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;
.auth OFF
BEGIN EXCLUSIVE;
PRAGMA temp_store = 'DEFAULT';
SAVEPOINT sp8774;
ATTACH ':memory:' AS aux1;
CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(trim('xxxhelloxxx', 'x'),'x1');
CREATE TABLE t2(a,b); /**/ INSERT INTO t2 VALUES(222,'x2');
CREATE TEMP TABLE t3(a,b); INSERT INTO t3 VALUES(333,'x3');
CREATE TABLE main.t4(a,b); INSERT INTO main.t4 VALUES(444,'x4');
CREATE TABLE aux1.t4(a,b); INSERT INTO aux1.t4 VALUES(555,'x5');
UPDATE t2 SET a=111;
UPDATE t3 SET a=111;
UPDATE t4 SET a=111;
SELECT * FROM (t1), (t2), (t3), (t4) WHERE t4.a=t3.a+111 OR t3.a=t2.a+111 AND t2.a=t1.a+111;
ANALYZE;
WITH cte AS (SELECT a, LAG(a) OVER (ORDER BY a) AS prev FROM t2) SELECT * FROM cte;
DELETE FROM t4 WHERE 0;
SELECT COUNT(*) FROM t4;
ALTER TABLE t4 RENAME TO t4_r261;
RELEASE sp8774;
ALTER TABLE t1 ADD COLUMN extra_1569 DOUB DEFAULT '';
INSERT INTO t2 VALUES (NULL, NULL);
INSERT INTO t2 DEFAULT VALUES;
SELECT NTILE(1) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t1;
CREATE TABLE T1 (
  A VARCHAR(10) PRIMARY KEY,
  B VARCHAR(15) UNIQUE,
  C INTEGER
);
CREATE TABLE T2 (
  X VARCHAR(20) PRIMARY KEY,
  A VARCHAR(10) NOT NULL UNIQUE,
  FOREIGN KEY (A) REFERENCES T1(A)
);
INSERT INTO T1 VALUES ('a', 'p', -2147483648);
INSERT INTO T1 VALUES ('b', 'q', 2147483647);
INSERT INTO T2 VALUES ('m', 'a');
INSERT INTO T2 VALUES ('n', 'b');
SELECT T2.X, T1.B, T1.C FROM T2, T1 WHERE T2.A = T1.A AND T1.C >= 0;
SELECT * FROM t2 WHERE a IN (SELECT a FROM t2 WHERE a IN (SELECT a FROM t2));
PRAGMA reverse_unordered_selects = TRUE;
INSERT INTO t2 VALUES (NULL, NULL);
ROLLBACK;
PRAGMA full_column_names = NO;
SELECT COUNT(*) FROM t3;
CREATE  VIEW IF NOT EXISTS v_T2_9810 AS SELECT C FROM T2;
ALTER TABLE t3 DROP COLUMN b;
WITH a AS (SELECT a FROM t1), b AS (SELECT a FROM a WHERE a IS NOT NULL), c AS (SELECT COUNT(*) AS cnt FROM b) SELECT cnt FROM c;
WITH cte AS (SELECT A FROM T2 EXCEPT SELECT A FROM T2) SELECT * FROM cte;
ROLLBACK TO sp4982;
RELEASE sp4982;
SELECT * FROM t4;
INSERT INTO T1 VALUES (NULL, NULL);
REINDEX;
DROP TRIGGER IF EXISTS T2;
ANALYZE;
RELEASE sp2718;
SELECT * FROM t4 WHERE b NOT IN (SELECT b FROM t4);
INSERT INTO t3 VALUES (1, NULL) ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b;
INSERT INTO t1 VALUES (NULL, 'x') ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b;
ALTER TABLE t3 DROP COLUMN a;
SELECT * FROM t3;
CREATE TABLE T1 (
  A VARCHAR(10) PRIMARY KEY,
  B VARCHAR(15) UNIQUE,
  C INTEGER
);
CREATE TABLE T2 (
  X VARCHAR(20) PRIMARY KEY,
  A VARCHAR(10) NOT NULL UNIQUE,
  FOREIGN KEY (A) REFERENCES T1(A)
);
INSERT INTO T1 VALUES ('a', 'p', -2147483648);
INSERT INTO T1 VALUES ('b', 'q', 2147483647);
INSERT INTO T2 VALUES ('m', 'a');
INSERT INTO T2 VALUES ('n', 'b');
SELECT T2.X, T1.B, T1.C FROM T2, T1 WHERE T2.A = T1.A AND T1.C >= 0;
CREATE TABLE T (
  a TEXT,
  b TEXT,
  c REAL
);
INSERT INTO T VALUES ('a','b',round(max(NULL, 5.0), 2)), ('a','c',5.0), ('b','d',-8.25);
SELECT a,b,c,
RANK() OVER (PARTITION BY a ORDER BY c DESC) AS d FROM T;
SAVEPOINT sp2718;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (-instr(NULL, NULL), 'Wernher', CAST(random() % -(-(-9223372036854775807)) AS BOOLEAN), 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
SAVEPOINT sp4982;
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;
.auth OFF
BEGIN EXCLUSIVE;
PRAGMA temp_store = 'DEFAULT';
SAVEPOINT sp8774;
ATTACH ':memory:' AS aux1;
CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(trim('xxxhelloxxx', 'x'),'x1');
CREATE TABLE t2(a,b); /**/ INSERT INTO t2 VALUES(222,'x2');
CREATE TEMP TABLE t3(a,b); INSERT INTO t3 VALUES(333,'x3');
CREATE TABLE main.t4(a,b); INSERT INTO	main.t4 VALUES(444,'x4');
CREATE TABLE aux1.t4(a,b); INSERT INTO aux1.t4 VALUES(555,'x5');
UPDATE t2 SET a=111;
UPDATE t3 SET a=111;
UPDATE t4 SET a=111;
SELECT * FROM (t1), (t2), (t3), (t4) WHERE t4.a=t3.a+111 OR t3.a=t2.a+111 AND t2.a=t1.a+111;
ANALYZE;
WITH cte AS (SELECT a, LAG(a) OVER (ORDER BY a) AS prev FROM t2) SELECT * FROM cte;
DELETE FROM t4 WHERE 0;
SELECT COUNT(*) FROM t4;
ALTER TABLE t4 RENAME TO t4_r261;
RELEASE sp8774;
ALTER TABLE t1 ADD COLUMN extra_1569 DOUB DEFAULT '';
INSERT INTO t2 VALUES (NULL, NULL);
INSERT INTO t2 DEFAULT VALUES;
SELECT NTILE(1) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t1;
CREATE TABLE T1 (
  A VARCHAR(10) PRIMARY KEY,
  B VARCHAR(15) UNIQUE,
  C INTEGER
);
CREATE TABLE T2 (
  X VARCHAR(20) PRIMARY KEY,
  A VARCHAR(10) NOT NULL UNIQUE,
  FOREIGN KEY (A) REFERENCES T1(A)
);
INSERT INTO T1 VALUES ('a', 'p', -2147483648);
INSERT INTO T1 VALUES ('b', 'q', 2147483647);
INSERT INTO T2 VALUES ('m', 'a');
INSERT INTO T2 VALUES ('n', 'b');
SELECT T2.X, T1.B, T1.C FROM T2, T1 WHERE T2.A = T1.A AND T1.C >= 0;
SELECT * FROM t2 WHERE a IN (SELECT a FROM t2 WHERE a IN (SELECT a FROM t2));
PRAGMA reverse_unordered_selects = TRUE;
INSERT INTO t2 VALUES (NULL, NULL);
ROLLBACK;
PRAGMA full_column_names = NO;
SELECT COUNT(*) FROM t3;
CREATE  VIEW IF NOT EXISTS v_T2_9810 AS SELECT C FROM T2;
ALTER TABLE t3 DROP COLUMN b;
WITH a AS (SELECT a FROM t1), b AS (SELECT a FROM a WHERE a IS NOT NULL), c AS (SELECT COUNT(*) AS cnt FROM b) SELECT cnt FROM c;
WITH cte AS (SELECT A FROM T2 EXCEPT SELECT A FROM T2) SELECT * FROM cte;
ROLLBACK TO sp4982;
RELEASE sp4982;
SELECT * FROM t4;
INSERT INTO T1 VALUES (NULL, NULL);
REINDEX;
DROP TRIGGER IF EXISTS T2;
ANALYZE;
RELEASE sp2718;
SELECT * FROM t4 WHERE b NOT IN (SELECT b FROM t4);
INSERT INTO t3 VALUES (1, NULL) ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b;
INSERT INTO t1 VALUES (NULL, 'x') ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b;
ALTER TABLE t3 DROP COLUMN a;
SELECT * FROM t3;
CREATE TABLE T1 (
  A VARCHAR(10) PRIMARY KEY,
  B VARCHAR(15) UNIQUE,
  C INTEGER
);
CREATE TABLE T2 (
  X VARCHAR(20) PRIMARY KEY,
  A VARCHAR(10) NOT NULL UNIQUE,
  FOREIGN KEY (A) REFERENCES T1(A)
);
INSERT INTO T1 VALUES ('a', 'p', -2147483648);
INSERT INTO T1 VALUES ('b', 'q', 2147483647);
INSERT INTO T2 VALUES ('m', 'a');
INSERT INTO T2 VALUES ('n', 'b');
SELECT T2.X, T1.B, T1.C FROM T2, T1 WHERE T2.A = T1.A AND T1.C >= 0;
CREATE TABLE T (
  a TEXT,
  b TEXT,
  c REAL
);
INSERT INTO T VALUES ('a','b',round(max(NULL, 5.0), 2)), ('a','c',5.0), ('b','d',-8.25);
SELECT a,b,c,
RANK() OVER (PARTITION BY a ORDER BY c DESC) AS d FROM T;
SAVEPOINT sp2718;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (-instr(NULL, NULL), 'Wernher', CAST(random() % -(-(-9223372036854775807)) AS BOOLEAN), 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
SAVEPOINT sp4982;
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;
.auth OFF
BEGIN EXCLUSIVE;
PRAGMA temp_store = 'DEFAULT';
SAVEPOINT sp8774;
ATTACH ':memory:' AS aux1;
CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(trim('xxxhelloxxx', 'x'),'x1');
CREATE TABLE t2(a,b); /**/ INSERT INTO t2 VALUES(222,'x2');
CREATE TEMP TABLE t3(a,b); INSERT INTO t3 VALUES(333,'x3');
CREATE TABLE main.t4(a,b); INSERT INTO main.t4 VALUES(444,'x4');
CREATE TABLE aux1.t4(a,b); INSERT INTO aux1.t4 VALUES(555,'x5');
UPDATE t2 SET a=111;
UPDATE t3 SET a=111;
UPDATE t4 SET a=111;
SELECT * FROM (t1), (t2), (t3), (t4) WHERE t4.a=t3.a+111 OR t3.a=t2.a+111 AND t2.a=t1.a+111;
ANALYZE;
WITH cte AS (SELECT a, LAG(a) OVER (ORDER BY a) AS prev FROM t2) SELECT * FROM cte;
DELETE FROM t4 WHERE 0;
SELECT COUNT(*) FROM t4;
ALTER TABLE t4 RENAME TO t4_r261;
RELEASE sp8774;
ALTER TABLE t1 ADD COLUMN extra_1569 DOUB DEFAULT '';
INSERT INTO t2 VALUES (NULL, NULL);
INSERT INTO t2 DEFAULT VALUES;
SELECT NTILE(1) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t1;
CREATE TABLE T1 (
  A VARCHAR(10) PRIMARY KEY,
  B VARCHAR(15) UNIQUE,
  C INTEGER
);
CREATE TABLE T2 (
  X VARCHAR(20) PRIMARY KEY,
  A VARCHAR(10) NOT NULL UNIQUE,
  FOREIGN KEY (A) REFERENCES T1(A)
);
INSERT INTO T1 VALUES ('a', 'p', -2147483648);
INSERT INTO T1 VALUES ('b', 'q', 2147483647);
INSERT INTO T2 VALUES ('m', 'a');
INSERT INTO T2 VALUES ('n', 'b');
SELECT T2.X, T1.B, T1.C FROM T2, T1 WHERE T2.A = T1.A AND T1.C >= 0;
SELECT * FROM t2 WHERE a IN (SELECT a FROM t2 WHERE a IN (SELECT a FROM t2));
PRAGMA reverse_unordered_selects = TRUE;
INSERT INTO t2 VALUES (NULL, NULL);
ROLLBACK;
PRAGMA full_column_names = NO;
SELECT COUNT(*) FROM t3;
CREATE  VIEW IF NOT EXISTS v_T2_9810 AS SELECT C FROM T2;
ALTER TABLE t3 DROP COLUMN b;
WITH a AS (SELECT a FROM t1), b AS (SELECT a FROM a WHERE a IS NOT NULL), c AS (SELECT COUNT(*) AS cnt FROM b) SELECT cnt FROM c;
WITH cte AS (SELECT A FROM T2 EXCEPT SELECT A FROM T2) SELECT * FROM cte;
ROLLBACK TO sp4982;
RELEASE sp4982;
SELECT * FROM t4;
INSERT INTO T1 VALUES (NULL, NULL);
REINDEX;
DROP TRIGGER IF EXISTS T2;
ANALYZE;
RELEASE sp2718;
SELECT * FROM t4 WHERE b NOT IN (SELECT b FROM t4);
INSERT INTO t3 VALUES (1, NULL) ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b;
INSERT INTO t1 VALUES (NULL, 'x') ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b;
ALTER TABLE t3 DROP COLUMN a;
SELECT * FROM t3;
CREATE TABLE T1 (
  A VARCHAR(10) PRIMARY KEY,
  B VARCHAR(15) UNIQUE,
  C INTEGER
);
CREATE TABLE T2 (
  X VARCHAR(20) PRIMARY KEY,
  A VARCHAR(10) NOT NULL UNIQUE,
  FOREIGN KEY (A) REFERENCES T1(A)
);
INSERT INTO T1 VALUES ('a', 'p', -2147483648);
INSERT INTO T1 VALUES ('b', 'q', 2147483647);
INSERT INTO T2 VALUES ('m', 'a');
INSERT INTO T2 VALUES ('n', 'b');
SELECT T2.X, T1.B, T1.C FROM T2, T1 WHERE T2.A = T1.A AND T1.C >= 0;
CREATE TABLE T (
  a TEXT,
  b TEXT,
  c REAL
);
INSERT INTO T VALUES ('a','b',round(max(NULL, 5.0), 2)), ('a','c',5.0), ('b','d',-8.25);
SELECT a,b,c,
RANK() OVER (PARTITION BY a ORDER BY c DESC) AS d FROM T;
SAVEPOINT sp2718;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (-instr(NULL, NULL), 'Wernher', CAST(random() % -(-(-9223372036854775807)) AS BOOLEAN), 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
SAVEPOINT sp4982;
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;
.auth OFF
BEGIN EXCLUSIVE;
PRAGMA temp_store = 'DEFAULT';
SAVEPOINT sp8774;
ATTACH ':memory:' AS aux1;
CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(trim('xxxhelloxxx', 'x'),'x1');
CREATE TABLE t2(a,b); /**/ INSERT INTO t2 VALUES(222,'x2');
CREATE TEMP TABLE t3(a,b); INSERT INTO t3 VALUES(333,'x3');
CREATE TABLE main.t4(a,b); INSERT INTO main.t4 VALUES(444,'x4');
CREATE TABLE aux1.t4(a,b); INSERT INTO aux1.t4 VALUES(555,'x5');
UPDATE t2 SET a=111;
UPDATE t3 SET a=111;
UPDATE t4 SET a=111;
SELECT * FROM (t1), (t2), (t3), (t4) WHERE t4.a=t3.a+111 OR t3.a=t2.a+111 AND t2.a=t1.a+111;
ANALYZE;
WITH cte AS (SELECT a, LAG(a) OVER (ORDER BY a) AS prev FROM t2) SELECT * FROM cte;
DELETE FROM t4 WHERE 0;
SELECT COUNT(*) FROM t4;
ALTER TABLE t4 RENAME TO t4_r261;
RELEASE sp8774;
ALTER TABLE t1 ADD COLUMN extra_1569 DOUB DEFAULT '';
INSERT INTO t2 VALUES (NULL, NULL);
INSERT INTO t2 DEFAULT VALUES;
SELECT NTILE(1) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t1;
CREATE TABLE T1 (
  A VARCHAR(10) PRIMARY KEY,
  B VARCHAR(15) UNIQUE,
  C INTEGER
);
CREATE TABLE T2 (
  X VARCHAR(20) PRIMARY KEY,
  A VARCHAR(10) NOT NULL UNIQUE,
  FOREIGN KEY (A) REFERENCES T1(A)
);
INSERT INTO T1 VALUES ('a', 'p', -2147483648);
INSERT INTO T1 VALUES ('b', 'q', 2147483647);
INSERT INTO T2 VALUES ('m', 'a');
INSERT INTO T2 VALUES ('n', 'b');
SELECT T2.X, T1.B, T1.C FROM T2, T1 WHERE T2.A = T1.A AND T1.C >= 0;
SELECT * FROM t2 WHERE a IN (SELECT a FROM t2 WHERE a IN (SELECT a FROM t2));
PRAGMA reverse_unordered_selects = TRUE;
INSERT INTO t2 VALUES (NULL, NULL);
ROLLBACK;
PRAGMA full_column_names = NO;
SELECT COUNT(*) FROM t3;
CREATE  VIEW IF NOT EXISTS v_T2_9810 AS SELECT C FROM T2;
ALTER TABLE t3 DROP COLUMN b;
WITH a AS (SELECT a FROM t1), b AS (SELECT a FROM a WHERE a IS NOT NULL), c AS (SELECT COUNT(*) AS cnt FROM b) SELECT cnt FROM c;
WITH cte AS (SELECT A FROM T2 EXCEPT SELECT A FROM T2) SELECT * FROM cte;
ROLLBACK TO sp4982;
RELEASE sp4982;
SELECT * FROM t4;
INSERT INTO T1 VALUES (NULL, NULL);
REINDEX;
DROP TRIGGER IF EXISTS T2;
ANALYZE;
RELEASE sp2718;
SELECT * FROM t4 WHERE b NOT IN (SELECT b FROM t4);
INSERT INTO t3 VALUES (1, NULL) ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b;
INSERT INTO t1 VALUES (NULL, 'x') ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b;
ALTER TABLE t3 DROP COLUMN a;
SELECT * FROM t3;
CREATE TABLE T1 (
  A VARCHAR(10) PRIMARY KEY,
  B VARCHAR(15) UNIQUE,
  C INTEGER
);
CREATE TABLE T2 (
  X VARCHAR(20) PRIMARY KEY,
  A VARCHAR(10) NOT NULL UNIQUE,
  FOREIGN KEY (A) REFERENCES T1(A)
);
INSERT INTO T1 VALUES ('a', 'p', -2147483648);
INSERT INTO T1 VALUES ('b', 'q', 2147483647);
INSERT INTO T2 VALUES ('m', 'a');
INSERT INTO T2 VALUES ('n', 'b');
SELECT T2.X, T1.B, T1.C FROM T2, T1 WHERE T2.A = T1.A AND T1.C >= 0;

CREATE TEMPORARY  VIEW IF NOT EXISTS v_T2_6598 AS SELECT X FROM T2;
SELECT * FROM T;
ALTER TABLE T2 RENAME COLUMN A TO A_r4018;
ALTER TABLE T DROP COLUMN b;
END;

Actual output

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|-2257356642007453381|1
von|20|2
Braun|30
Wernher|-2257356642007453381
von|20

Expectation

Flag
Reference in New Issue View Git Blame Copy Permalink