Thanks for the report and for simplifying the test case. Here is an even
simpler test case:
> ~~~
WITH
t1(x) AS (SELECT 111),
t2(y) AS (SELECT 222),
t3(z) AS (SELECT * FROM t2 WHERE false UNION ALL SELECT * FROM t2)
SELECT * FROM t1, t3;
~~~
I think I understand the malfunction. I should have a fix later today.
If you are just curious about what is going wrong, you can keep reading.
Processing begins by expanding the various subqueries defined by
the WITH clause. We have:
> ~~~
SELECT *
FROM (SELECT 111) AS t1,
(SELECT * FROM (SELECT 222) WHERE false UNION ALL SELECT * FROM (SELECT 222)) AS t2;
~~~
The query optimizer then brings the UNION ALL operator to the top-level
as follows:
> ~~~
SELECT *
FROM (SELECT 111) AS t1, (SELECT * FROM (SELECT 222)) AS t2 WHERE false
UNION ALL
SELECT *
FROM (SELECT 111) AS t1, (SELECT * FROM (SELECT 222)) AS t2;
~~~
Next the [push down optimization][1] is applied to the
left-hand side of the UNION ALL operator to give us:
[1]: https://sqlite.org/optoverview.html#the_push_down_optimization
> ~~~
SELECT *
FROM (SELECT 111 WHERE false) AS t1, (SELECT * FROM (SELECT 222) WHERE false) AS t2 WHERE false
UNION ALL
SELECT *
FROM (SELECT 111) AS t1, (SELECT * FROM (SELECT 222)) AS t2;
~~~
So far, everything is copacetic. The optimized query is still equivalent
to the original. But here comes the problem: While SQLite is
generated the byte-code for the left-hand side of the UNION ALL, it sees the
"T1" subquery and it notices that T1 will be reused when evaluating the
right-hand side of UNION ALL so it saves the result of the
first T1 and tries to reuse it for the second T1. SQLite fails to notice
that the first T1 was modified by the push-down optimization and is hence
no longer equivalent to the second T1. Thus the query that ends up being
evaluated is:
>
<pre>
SELECT *
FROM (SELECT 111 WHERE false) AS t1, (SELECT * FROM (SELECT 222) WHERE false) AS t2 WHERE false
UNION ALL
SELECT *
FROM (SELECT 111 <font color="red">WHERE false</font>) AS t1, (SELECT * FROM (SELECT 222)) AS t2;
</pre>
The extra "WHERE false" (shown in red)
that gets inserted into the second T1 is the
cause of the incorrect result.