SQLite: View Ticket

2022-06-23
15:15		Add back the ability to flatten a LEFT JOIN subquery - previously removed due to ticket [cad1ab4cb7b0fc344]. (check-in: f8fe936ad4 user: drh tags: flatten-left-join)
2017-06-20
16:15		• New ticket [892fc34f] Incorrect query result when a LEFT JOIN subquery is flattened. (artifact: d28d654485 user: drh)
2017-05-23
19:16		• Fixed ticket [cad1ab4c]: Segfault due to LEFT JOIN flattening optimization plus 3 other changes (artifact: 83102567a5 user: drh)
15:33		Disable the LEFT JOIN flattening optimization for aggregate queries, as it does not currently work. Further fix for ticket [cad1ab4cb7b0fc344]. (check-in: 05ada74155 user: drh tags: branch-3.19)
15:21		Disable the LEFT JOIN flattening optimization for aggregate queries, as it does not currently work. Further fix for ticket [cad1ab4cb7b0fc344]. (check-in: 44b21e35c9 user: drh tags: trunk)
15:18		• Open ticket [cad1ab4c]: Segfault due to LEFT JOIN flattening optimization plus 3 other changes (artifact: f952f39768 user: drh)
15:18		• Ticket [cad1ab4c]: 3 changes (artifact: 49dcb66aea user: drh)
12:44		Ensure that the expression rewriter inside the query flattener decends into the substructure of the TK_IF_NULL_ROW operator. This is a continuation of the fix for ticket [cad1ab4cb7b0fc344]. (check-in: 28d2902d8f user: drh tags: branch-3.19)
12:36		Ensure that the expression rewriter inside the query flattener decends into the substructure of the TK_IF_NULL_ROW operator. This is a continuation of the fix for ticket [cad1ab4cb7b0fc344]. (check-in: 941d8142b7 user: drh tags: trunk)
12:35		• Ticket [cad1ab4c] Segfault due to LEFT JOIN flattening optimization status still Fixed with 3 other changes (artifact: 88096b0cb3 user: drh)
01:29		• Fixed ticket [cad1ab4c]. (artifact: b4ade83baa user: drh)
01:29		When flattening a query, make sure iTable attribute of TK_IF_NULL_ROW operators (that result from a prior flattening of a LEFT JOIN) are updated correctly. Fix for ticket [cad1ab4cb7b0fc344]. (check-in: 919d36e6ea user: drh tags: branch-3.19)
01:21		When flattening a query, make sure iTable attribute of TK_IF_NULL_ROW operators (that result from a prior flattening of a LEFT JOIN) are updated correctly. Fix for ticket [cad1ab4cb7b0fc344]. (check-in: 92c178507d user: drh tags: trunk)
01:17		• New ticket [cad1ab4c] Segfault due to LEFT JOIN flattening optimization. (artifact: 6e17327e62 user: drh)

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;