SQLite

Dbsqlfuzz discovered that the final query in the SQL below gets different answers depending on whether or not query flattening is enabled:

CREATE TABLE t0(c0 INT);
INSERT INTO t0 VALUES(0);
CREATE VIEW v0(c0) AS SELECT CAST(t0.c0 AS INTEGER) FROM t0;
CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT);
INSERT INTO t1_a VALUES(1,'one'),(4,'four');
CREATE TABLE t1_b(c INTEGER PRIMARY KEY, d TEXT);
INSERT INTO t1_b VALUES(2,'two'),(5,'five');
CREATE VIEW t1 AS 
  SELECT a, b FROM t1_a   UNION ALL
  SELECT c, c FROM t1_b;
SELECT * FROM (
  SELECT t1.a AS a, t1.b AS b, t0.c0 AS c, v0.c0 AS d
    FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1
) AS t2 WHERE b='2';
.testctrl optimization 1
SELECT * FROM (
  SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d
    FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1
) AS t2 WHERE b='2';

PostgreSQL does not accept the definition of the view T1 due to a type mismatch. To make T1 acceptable to PG, it has to be changed by inserting a CAST as follows:

CREATE VIEW t1 AS 
  SELECT a, b FROM t1_a   UNION ALL
  SELECT c, CAST(c AS text) FROM t1_b;

With that change to t1, PG and SQLite get the same answer, and SQLite always works, with or without the query flattener. So we presume that the answer with query flattening enabled is correct.

SQLite got the correct answer prior to df12f097224ebc94 (2020-10-03) which was a fix for ticket [95302bdb08fb6495]. After that change, an incorrect answer was obtained (no rows) regardless of whether or not the query flattener was used.

Beginning with df1d6482f9e92daf on 2020-12-19, when the query flattener is enabled, the compile fails with an error: "ON clause references tables to its right".

Beginning with d554f710a5abbe64 on 2020-12-21, attempts to run the query result in an assertion fault. The assertion fault problem was fixed by e4f8a79fd8b3be9b on 2021-02-13. Ever since then, the query has returned the correct answer when query flattening is enabled, and an incorrect answer when query flattening is disabled.

Reopened

Check-in 88a05141c28e5ff1 that was suppose to fix this problem introduced a new problem seen here:

.echo on
.mode qbox
.eqp on
CREATE VIEW dual(dummay) AS SELECT 'X';
SELECT x, typeof(x) FROM (SELECT CAST(4.0 AS NUMERIC) AS x) JOIN dual;
SELECT x, typeof(x) FROM (SELECT CAST(4.0 AS NUMERIC) AS x) CROSS JOIN dual;

The two queries get a different result. The difference is that the second query uses a co-routine and the first query materializes the subquery.

It seems like the same result should be obtained regardless of whether or not the query planner chooses to implement a subquery by materializing or as a co-routine.