SQLite

The following SQL causes SQLite 3.19.0 to segfault:

<blockquote><pre>
SELECT *
FROM (SELECT 1 a) s
LEFT JOIN (
    SELECT 1 b, * FROM (
        SELECT * FROM (
            SELECT 1 c
        ) x
    ) x
) x;
</pre></blockquote>

The problem is the new LEFT JOIN flattening optimization in the just released
SQLite 3.19.0.  No prior versions of SQLite are affected.
The problem was reported by Mark Brand.

Another test case:

<blockquote><verbatim>
SELECT *
FROM (SELECT 1 a) s
LEFT JOIN (
    SELECT c+1 b, * FROM (
        SELECT * FROM (
            SELECT 1 c
        ) x
    ) x
) x;
</verbatim></blockquote>

Further test cases for faults in the LEFT JOIN query flattener:

<blockquote><verbatim>
CREATE TABLE t1(c PRIMARY KEY,a TEXT(10000),b TEXT (10000));
SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1) GROUP BY 1;
</verbatim></blockquote>

And

<blockquote><verbatim>
CREATE TABLE t1(c PRIMARY KEY) WITHOUT ROWID;
SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1) GROUP BY 1;
</verbatim></blockquote>