SQLite

View Ticket
Login
Plaintext
Ticket Hash: cad1ab4cb7b0fc344b2e8c726b72e94e24c4f383
Title: Segfault due to LEFT JOIN flattening optimization
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2017-05-23 19:16:33
Version Found In: 3.19.0
User Comments:
drh added on 2017-05-23 01:17:09:

The following SQL causes SQLite 3.19.0 to segfault: 

SELECT *
FROM (SELECT 1 a) s
LEFT JOIN (
    SELECT 1 b, * FROM (
        SELECT * FROM (
            SELECT 1 c
        ) x
    ) x
) x;

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.

drh added on 2017-05-23 12:35:04:

Another test case: 

SELECT *
FROM (SELECT 1 a) s
LEFT JOIN (
    SELECT c+1 b, * FROM (
        SELECT * FROM (
            SELECT 1 c
        ) x
    ) x
) x;

drh added on 2017-05-23 15:18:38:

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

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;

And 

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