SQLite 3.35.x INNER JOIN Bug with Compound Queries and UNION ALL

Issue Overview: INNER JOIN with Compound Queries and UNION ALL Returns Incorrect Results in SQLite 3.35.x

The core issue revolves around a specific SQLite query involving INNER JOIN, Common Table Expressions (CTEs), and a UNION ALL operation. The query, which should return a single row, fails to do so in SQLite versions 3.35.x and 3.36.x. Instead, it returns no rows, indicating a bug in the query optimizer’s handling of compound queries and INNER JOIN operations.

The query structure involves three CTEs: T1, T2, and T3. T1 and T2 are simple CTEs returning constant values, while T3 is a compound query that combines a SELECT statement with a WHERE false condition and a UNION ALL operation. The INNER JOIN operation is performed across these CTEs, and the expected result is a single row combining the values from T1, T2, and T3. However, in SQLite 3.35.x and 3.36.x, the query returns no rows, which is incorrect.

The issue is particularly sensitive to the structure of the query. For example, replacing INNER JOIN with LEFT JOIN produces the correct result. Similarly, removing T1 or the UNION ALL operation also resolves the issue. This sensitivity suggests that the bug is related to how SQLite’s query optimizer handles compound queries and INNER JOIN operations in specific scenarios.

Possible Causes: Query Optimizer Misapplies Push-Down Optimization in Compound Queries

The root cause of the issue lies in SQLite’s query optimizer, specifically in how it applies the push-down optimization to compound queries involving UNION ALL and INNER JOIN. The push-down optimization is a technique used by SQLite to improve query performance by pushing WHERE clauses and other conditions down into subqueries. However, in this case, the optimizer misapplies the optimization, leading to incorrect results.

When the query is executed, SQLite first expands the CTEs and then applies the push-down optimization to the UNION ALL operation. The optimizer attempts to reuse the result of the T1 subquery for both sides of the UNION ALL operation. However, it fails to account for the fact that the push-down optimization modifies the T1 subquery on the left-hand side of the UNION ALL. As a result, the modified T1 subquery is incorrectly reused on the right-hand side, leading to an extra WHERE false condition being applied where it shouldn’t be.

This misapplication of the push-down optimization causes the query to return no rows, even though the correct result should be a single row. The issue is exacerbated by the presence of INNER JOIN, which is more sensitive to the presence of WHERE conditions than LEFT JOIN. This explains why replacing INNER JOIN with LEFT JOIN resolves the issue.

Troubleshooting Steps, Solutions & Fixes: Workarounds and Upgrading to a Fixed Version

To address this issue, there are several workarounds and solutions available, depending on the specific requirements and constraints of your use case.

Workaround 1: Replace INNER JOIN with LEFT JOIN
One immediate workaround is to replace the INNER JOIN with a LEFT JOIN. This change avoids the issue because LEFT JOIN is less sensitive to the presence of WHERE conditions in the subqueries. However, this workaround may not be suitable for all use cases, especially if the query logic relies on the behavior of INNER JOIN.

Workaround 2: Simplify the Query Structure
Another workaround is to simplify the query structure by removing unnecessary elements. For example, if the UNION ALL operation is not essential, removing it can resolve the issue. Similarly, removing T1 or restructuring the query to avoid compound queries can also help. However, this approach may not be feasible if the query logic requires the current structure.

Workaround 3: Use Explicit Subqueries
A more robust workaround is to rewrite the query using explicit subqueries instead of relying on the query optimizer to handle the UNION ALL operation. By manually structuring the query, you can avoid the optimizer’s misapplication of the push-down optimization. For example:

WITH
T1 (x) AS (SELECT 10),
T2 (y) AS (SELECT 1),
T3 (z) AS (SELECT * FROM T2 WHERE false UNION ALL SELECT * FROM T2)
SELECT *
FROM (SELECT * FROM T1) AS t1,
     (SELECT * FROM T2) AS t2,
     (SELECT * FROM T3) AS t3;

This approach ensures that the subqueries are evaluated independently, avoiding the optimizer’s incorrect reuse of modified subqueries.

Solution: Upgrade to a Fixed Version of SQLite
The most effective solution is to upgrade to a version of SQLite where this bug has been fixed. The issue was identified and addressed in later versions of SQLite, so upgrading to the latest stable release should resolve the problem. Before upgrading, ensure that the new version is compatible with your application and database schema.

Detailed Explanation of the Fix
The fix for this issue involves modifying the query optimizer to correctly handle the push-down optimization in compound queries with UNION ALL and INNER JOIN. Specifically, the optimizer now ensures that subqueries modified by the push-down optimization are not incorrectly reused in other parts of the query. This change prevents the extra WHERE false condition from being applied where it shouldn’t be, ensuring that the query returns the correct result.

Verification Steps
To verify that the issue has been resolved, you can execute the original query in the upgraded version of SQLite and confirm that it returns the expected single row. Additionally, you can review the query plan to ensure that the push-down optimization is being applied correctly. The query plan should show that the subqueries are being evaluated independently, without any incorrect reuse of modified subqueries.

Conclusion
The SQLite 3.35.x INNER JOIN bug with compound queries and UNION ALL is a complex issue caused by the query optimizer’s misapplication of the push-down optimization. While there are several workarounds available, the most effective solution is to upgrade to a fixed version of SQLite. By understanding the root cause of the issue and applying the appropriate troubleshooting steps, you can ensure that your queries return the correct results and maintain the integrity of your database operations.