Incorrect Query Results Due to Nested JOIN and CASE Logic in SQLite

Understanding the Query Structure and Expected Results

The core issue revolves around a SQLite query that produces inconsistent results when using nested JOINs combined with CASE statements in the WHERE clause. The query involves a single table t0 with one column c1, which contains a single row with the value 0. The query is designed to perform a series of JOIN operations and filter the results using a CASE expression. The inconsistency arises when simplifying the CASE expression, which should theoretically yield the same results but does not.

The table t0 is created and populated as follows:

CREATE TABLE t0 (c1 INT);
INSERT INTO t0 VALUES (0);

The first test case (Test Case 1) uses a complex CASE expression in the WHERE clause:

SELECT 1
FROM (t0 AS ref_0
      RIGHT OUTER JOIN (t0 AS ref_3
                        LEFT OUTER JOIN t0 AS ref_4
                        ON FALSE)
      ON FALSE)
WHERE ((CASE WHEN TRUE THEN ref_0.c1 ELSE ref_0.c1 END) <=
       (CASE WHEN FALSE THEN ref_0.c1 ELSE ref_0.c1 END))
GROUP BY ref_0.c1;

This query returns 0 rows.

The second test case (Test Case 2) simplifies the CASE expression:

SELECT 1
FROM (t0 AS ref_0
      RIGHT OUTER JOIN (t0 AS ref_3
                        LEFT OUTER JOIN t0 AS ref_4
                        ON FALSE)
      ON FALSE)
WHERE (ref_0.c1 <= (CASE WHEN FALSE THEN ref_0.c1 ELSE ref_0.c1 END))
GROUP BY ref_0.c1;

This query returns 1 row.

The inconsistency between the two test cases is problematic because the simplification of the CASE expression should not alter the logical outcome of the query. This discrepancy suggests an underlying issue with how SQLite processes nested JOINs and CASE expressions in the WHERE clause.

Analyzing the Role of JOINs and CASE Expressions in Query Execution

The issue stems from the interaction between the nested JOIN operations and the CASE expressions in the WHERE clause. To understand the problem, we need to break down the query execution step by step.

Nested JOINs and Their Impact

The query uses a combination of RIGHT OUTER JOIN and LEFT OUTER JOIN operations. The RIGHT OUTER JOIN ensures that all rows from the right table (ref_3 and ref_4) are included in the result set, even if there are no matching rows in the left table (ref_0). The LEFT OUTER JOIN ensures that all rows from the left table (ref_4) are included, even if there are no matching rows in the right table (ref_3). The ON FALSE condition ensures that no rows from the joined tables match, resulting in a Cartesian product of the tables.

In this specific case, since t0 contains only one row, the nested JOINs produce a result set with a single row where all columns are NULL except for ref_0.c1, which retains its original value of 0.

CASE Expressions in the WHERE Clause

The CASE expressions in the WHERE clause are used to conditionally evaluate the value of ref_0.c1. In Test Case 1, the CASE expression (CASE WHEN TRUE THEN ref_0.c1 ELSE ref_0.c1 END) simplifies to ref_0.c1 because the condition TRUE is always satisfied. Similarly, (CASE WHEN FALSE THEN ref_0.c1 ELSE ref_0.c1 END) simplifies to ref_0.c1 because the condition FALSE is never satisfied, and the ELSE branch is always executed.

In Test Case 2, the CASE expression is simplified manually to ref_0.c1, which should logically produce the same result as Test Case 1. However, the query returns different results, indicating that SQLite is not handling the CASE expressions as expected.

Grouping and Aggregation

The GROUP BY ref_0.c1 clause groups the results by the value of ref_0.c1. Since ref_0.c1 is 0 for the single row in the table, the grouping operation should not alter the result set. However, the inconsistency in the WHERE clause evaluation affects the final output.

Resolving the Inconsistency: Debugging and Fixing the Query

To resolve the inconsistency, we need to examine the query execution plan and identify where the logic breaks down. Here are the steps to troubleshoot and fix the issue:

Step 1: Verify the Query Execution Plan

Use the EXPLAIN QUERY PLAN statement to analyze how SQLite processes the query. This will provide insights into the order of operations and how the JOINs and WHERE clause are evaluated.

For Test Case 1:

EXPLAIN QUERY PLAN
SELECT 1
FROM (t0 AS ref_0
      RIGHT OUTER JOIN (t0 AS ref_3
                        LEFT OUTER JOIN t0 AS ref_4
                        ON FALSE)
      ON FALSE)
WHERE ((CASE WHEN TRUE THEN ref_0.c1 ELSE ref_0.c1 END) <=
       (CASE WHEN FALSE THEN ref_0.c1 ELSE ref_0.c1 END))
GROUP BY ref_0.c1;

For Test Case 2:

EXPLAIN QUERY PLAN
SELECT 1
FROM (t0 AS ref_0
      RIGHT OUTER JOIN (t0 AS ref_3
                        LEFT OUTER JOIN t0 AS ref_4
                        ON FALSE)
      ON FALSE)
WHERE (ref_0.c1 <= (CASE WHEN FALSE THEN ref_0.c1 ELSE ref_0.c1 END))
GROUP BY ref_0.c1;

Compare the execution plans to identify any differences in how the WHERE clause is processed.

Step 2: Simplify the Query Further

To isolate the issue, simplify the query by removing unnecessary components. For example, remove the GROUP BY clause and evaluate the WHERE clause independently.

Simplified Test Case 1:

SELECT 1
FROM (t0 AS ref_0
      RIGHT OUTER JOIN (t0 AS ref_3
                        LEFT OUTER JOIN t0 AS ref_4
                        ON FALSE)
      ON FALSE)
WHERE ((CASE WHEN TRUE THEN ref_0.c1 ELSE ref_0.c1 END) <=
       (CASE WHEN FALSE THEN ref_0.c1 ELSE ref_0.c1 END));

Simplified Test Case 2:

SELECT 1
FROM (t0 AS ref_0
      RIGHT OUTER JOIN (t0 AS ref_3
                        LEFT OUTER JOIN t0 AS ref_4
                        ON FALSE)
      ON FALSE)
WHERE (ref_0.c1 <= (CASE WHEN FALSE THEN ref_0.c1 ELSE ref_0.c1 END));

Evaluate whether the inconsistency persists in the simplified queries.

Step 3: Check for SQLite Version-Specific Bugs

The issue might be caused by a bug in the specific version of SQLite being used (3.45.0). Check the SQLite changelog and bug tracker for any reported issues related to JOINs, CASE expressions, or WHERE clause evaluation.

Step 4: Apply the Fix

Based on the analysis, apply the fix provided by Dan Kennedy in the SQLite repository:
https://sqlite.org/src/info/530d10e9

Update to the latest version of SQLite that includes the fix and verify that the issue is resolved.

Step 5: Validate the Results

After applying the fix, re-run the test cases to ensure that the results are consistent:

-- Test Case 1
SELECT 1
FROM (t0 AS ref_0
      RIGHT OUTER JOIN (t0 AS ref_3
                        LEFT OUTER JOIN t0 AS ref_4
                        ON FALSE)
      ON FALSE)
WHERE ((CASE WHEN TRUE THEN ref_0.c1 ELSE ref_0.c1 END) <=
       (CASE WHEN FALSE THEN ref_0.c1 ELSE ref_0.c1 END))
GROUP BY ref_0.c1;

-- Test Case 2
SELECT 1
FROM (t0 AS ref_0
      RIGHT OUTER JOIN (t0 AS ref_3
                        LEFT OUTER JOIN t0 AS ref_4
                        ON FALSE)
      ON FALSE)
WHERE (ref_0.c1 <= (CASE WHEN FALSE THEN ref_0.c1 ELSE ref_0.c1 END))
GROUP BY ref_0.c1;

Both queries should now return the same result, confirming that the issue has been resolved.

By following these steps, you can systematically diagnose and resolve the inconsistency caused by nested JOINs and CASE expressions in SQLite queries. This approach ensures that your queries produce accurate and reliable results, even when dealing with complex logic and joins.