ast-project/part1/results/2/bug_05e21089-84b3-49cb-b078-44d3ab264c1e_logic.md

Summary

**No review yet**

Minimized query

BEGIN IMMEDIATE TRANSACTION;
.tables non_existent_table
CREATE TABLE T (
  a INTEGER,
  b TEXT,
  c INTEGER
);
INSERT INTO T VALUES ((0 + 1),'a',NULL), (2,'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 JOIN d ON T.c=d.a
)
SELECT * FROM d;
PRAGMA cache_spill = 0;
CREATE TABLE main.artists (id integer NOT NULL PRIMARY KEY	AUTOINCREMENT, name varchar(255));
CREATE TABLE albums ( id integer NOT NULL PRIMARY KEY AUTOINCREMENT, name varchar(255), artist_id integer REFERENCES artists);
INSERT INTO artists (name) VALUES ('Ar');
INSERT INTO albums (name, artist_id) VALUES ('Al', 1);
SELECT artists.* FROM artists INNER JOIN artists AS b ON (b.id = artists.id) WHERE (artists.id IN ( SELECT albums.artist_id FROM albums WHERE ((name = 'Al') AND (albums.artist_id IS NOT NULL) AND (albums.id IN ( SELECT id FROM ( SELECT albums.id, row_number() OVER (PARTITION BY /**/ albums.artist_id ORDER BY name) AS x FROM albums WHERE (name = 'Al') ) AS t1 WHERE (x = 1) )) AND (albums.id IN (1, 2))) ));

CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (1, '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
ANALYZE;
REINDEX albums;
REINDEX artists;
SELECT AVG(id) FROM artists;
ALTER TABLE t1 DROP COLUMN c;
DELETE FROM t1 WHERE 0 RETURNING *;
SELECT AVG(b) FROM t1;
CREATE TRIGGER IF NOT EXISTS trg_T_5677 BEFORE UPDATE ON T FOR EACH ROW BEGIN SELECT RAISE(IGNORE); END;
WITH cte AS (SELECT a, COUNT(*) AS cnt FROM t1 GROUP BY a) SELECT * FROM cte WHERE cnt > 1;
INSERT INTO t1 SELECT * FROM t1;
UPDATE albums SET artist_id = artist_id + 1 WHERE artist_id IS NOT NULL RETURNING *;
END;
SELECT DENSE_RANK() OVER (PARTITION BY a ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS) FROM t1;

Actual output

1|a||1
2|b|1|2
3|c|2|3
1|Ar
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

1|a||1
2|b|1|2
3|c|2|3
1|Ar
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