ast-project/part1/results/2/bug_d94e17f6-d3f5-499f-9bac-ff105430a9d6_logic.md

Summary

**No review yet**

Minimized query

PRAGMA wal_autocheckpoint = 1000;
.eqp on
ATTACH DATABASE ':memory:' AS aux95;
CREATE TABLE T1 (
  A VARCHAR(20) PRIMARY KEY,
  X VARCHAR(10) UNIQUE
);
CREATE TABLE T2 (
  A VARCHAR(20) PRIMARY KEY,
  Y VARCHAR(10) UNIQUE
);
INSERT INTO T1 VALUES ('a', 'm');
INSERT INTO T1 VALUES ('b', 'n');
INSERT INTO T1 VALUES ('c', 'o');
INSERT INTO T2 VALUES ('b', 'k');
INSERT INTO T2 VALUES ('c', 'l');
selECt A FROM T1 INTERSECT SELECT A FROM T2;
INSERT OR IGNORE INTO T2 VALUES (0, 3);
CREATE  INDEX IF NOT EXISTS idx_T1_3456 ON T1(A COLLATE NOCASE);
DETACH DATABASE aux95;
creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z");
DROP TABLE t0;

SELECT COUNT(*) FROM T1;
DROP INDEX IF EXISTS T2;
PRAGMA cache_spill = ON;
DELETE FROM T2 WHERE Y > (SELECT AVG(Y) FROM T2);

SELECT COUNT(*) FROM T1;

SELECT COUNT(*) FROM T1;
ALTER TABLE T2 RENAME TO T2_r6528;
PRAGMA legacy_alter_table;
WITH cte AS (SELECT 1 AS x) SELECT x + 0.5 FROM cte;

Actual output

1000
QUERY PLAN
`--COMPOUND QUERY
   |--LEFT-MOST SUBQUERY
   |  `--SCAN T1 USING COVERING INDEX sqlite_autoindex_T1_1
   `--INTERSECT USING TEMP B-TREE
      `--SCAN T2 USING COVERING INDEX sqlite_autoindex_T2_1
b
c
QUERY PLAN
`--SCAN T1 USING COVERING INDEX sqlite_autoindex_T1_2
3
QUERY PLAN
|--SEARCH T2 USING INDEX sqlite_autoindex_T2_2 (Y>?)
`--SCALAR SUBQUERY 1
   `--SCAN T2 USING COVERING INDEX sqlite_autoindex_T2_2
QUERY PLAN
`--SCAN T1 USING COVERING INDEX sqlite_autoindex_T1_2
3
QUERY PLAN
`--SCAN T1 USING COVERING INDEX sqlite_autoindex_T1_2
3
0
QUERY PLAN
|--CO-ROUTINE cte
|  `--SCAN CONSTANT ROW
`--SCAN cte
1.5

Expectation

1000
QUERY PLAN
`--COMPOUND QUERY
   |--LEFT-MOST SUBQUERY
   |  `--SCAN T1 USING COVERING INDEX sqlite_autoindex_T1_1
   `--INTERSECT USING TEMP B-TREE
      `--SCAN T2 USING COVERING INDEX sqlite_autoindex_T2_1
b
c
QUERY PLAN
`--SCAN T1 USING COVERING INDEX sqlite_autoindex_T1_2
3
QUERY PLAN
|--SEARCH T2 USING COVERING INDEX sqlite_autoindex_T2_2 (Y>?)
`--SCALAR SUBQUERY 1
   `--SCAN T2 USING COVERING INDEX sqlite_autoindex_T2_2
QUERY PLAN
`--SCAN T1 USING COVERING INDEX sqlite_autoindex_T1_2
3
QUERY PLAN
`--SCAN T1 USING COVERING INDEX sqlite_autoindex_T1_2
3
0
QUERY PLAN
|--CO-ROUTINE cte
|  `--SCAN CONSTANT ROW
`--SCAN cte
1.5

Flag