SQLite

View Ticket
Login
Plaintext
2022-06-23
15:15
Add back the ability to flatten a LEFT JOIN subquery - previously removed due to ticket [cad1ab4cb7b0fc344]. (check-in: f8fe936a 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: d28d6544 user: drh)
2017-05-23
19:16 • Fixed ticket [cad1ab4c]: Segfault due to LEFT JOIN flattening optimization plus 3 other changes (artifact: 83102567 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: 05ada741 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: 44b21e35 user: drh tags: trunk)
15:18 • Open ticket [cad1ab4c]: Segfault due to LEFT JOIN flattening optimization plus 3 other changes (artifact: f952f397 user: drh)
15:18 • Ticket [cad1ab4c]: 3 changes (artifact: 49dcb66a 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: 28d2902d 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: 941d8142 user: drh tags: trunk)
12:35 • Ticket [cad1ab4c] Segfault due to LEFT JOIN flattening optimization status still Fixed with 3 other changes (artifact: 88096b0c user: drh)
01:29 • Fixed ticket [cad1ab4c]. (artifact: b4ade83b 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: 919d36e6 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: 92c17850 user: drh tags: trunk)
01:17 • New ticket [cad1ab4c] Segfault due to LEFT JOIN flattening optimization. (artifact: 6e17327e 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;