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.

Incorrect answer when flattening a UNION ALL compound