Regression in SQLite 3.35.4: Mixing AND with OR in WHERE Clause Returns No Rows

Issue Overview: Mixing AND with OR in WHERE Clause Yields No Rows in SQLite 3.35.4

The core issue revolves around a regression in SQLite 3.35.4 where a query combining AND and OR logical operators in the WHERE clause unexpectedly returns no rows, despite the same query working correctly in earlier versions (3.33.0 and 3.34.1). The query in question filters rows from a table named folder_id_email_list where the column value is not null and matches a specific pattern using the LIKE operator. The problem arises when the same condition is repeated with an OR operator, which should logically return the same result set as the original query. However, in SQLite 3.35.4, this combination results in an empty result set.

The query structure is as follows:

SELECT * FROM folder_id_email_list AS el 
WHERE (el.value IS NOT NULL AND el.value LIKE '%jackson.com') 
   OR (el.value IS NOT NULL AND el.value LIKE '%jackson.com');

This query should return the same rows as the simpler query:

SELECT * FROM folder_id_email_list AS el 
WHERE el.value IS NOT NULL AND el.value LIKE '%jackson.com';

However, in SQLite 3.35.4, the first query returns no rows, while the second query returns the expected three rows. This behavior is inconsistent with earlier versions of SQLite, where both queries return the same result set.

The issue was reported with a reproducible database file, and the regression was confirmed by the SQLite development team. The problem was traced to a specific bug introduced in SQLite 3.35.4, which has since been fixed in the latest development branch (branch-3.35). The fix has been cherry-picked for inclusion in future releases, such as SQLite 3.35.5.

Possible Causes: Logical Operator Precedence and Query Optimization Bugs

The root cause of this issue lies in how SQLite 3.35.4 handles logical operator precedence and query optimization. In SQLite, logical operators such as AND and OR have specific precedence rules that determine the order in which conditions are evaluated. The AND operator has higher precedence than OR, meaning that conditions connected by AND are evaluated before those connected by OR. However, when parentheses are used, they override the default precedence, ensuring that the enclosed conditions are evaluated first.

In the problematic query, the conditions are grouped using parentheses, which should ensure that the AND conditions are evaluated first, followed by the OR condition. However, due to a bug in SQLite 3.35.4, the query optimizer incorrectly handles this precedence, leading to an incorrect evaluation of the WHERE clause. This results in the query returning no rows, even though the conditions should logically match the same rows as the simpler query.

The bug was introduced in SQLite 3.35.4 as part of changes to the query optimizer, which aimed to improve performance and handle complex queries more efficiently. However, these changes inadvertently introduced a regression that affects queries combining AND and OR operators in specific ways. The bug was identified and fixed in the latest development branch, but it remains present in SQLite 3.35.4.

Another potential contributing factor is the handling of NULL values in the WHERE clause. The condition el.value IS NOT NULL is used to filter out rows where the value column is NULL. However, if the query optimizer incorrectly evaluates this condition in conjunction with the OR operator, it may lead to unexpected results. This is particularly relevant in cases where the value column contains NULL values, as the optimizer may incorrectly exclude rows that should be included in the result set.

Troubleshooting Steps, Solutions & Fixes: Identifying, Resolving, and Preventing the Issue

To troubleshoot this issue, the first step is to confirm that the problem is indeed caused by the regression in SQLite 3.35.4. This can be done by running the problematic query on different versions of SQLite and comparing the results. If the query returns the expected result set in earlier versions (e.g., 3.33.0 and 3.34.1) but fails in SQLite 3.35.4, this confirms the presence of the regression.

Once the regression is confirmed, the next step is to apply the fix provided by the SQLite development team. The fix has been cherry-picked into the branch-3.35 branch, which means that users can either wait for the next official release (e.g., SQLite 3.35.5) or manually build SQLite from the latest source code on the branch-3.35 branch. Building SQLite from source requires downloading the latest tarball from the SQLite website and following the build instructions provided in the documentation.

For users who cannot upgrade to a fixed version of SQLite immediately, a temporary workaround is to rewrite the query to avoid the problematic combination of AND and OR operators. One approach is to use a UNION ALL operator to combine the results of two separate queries, each containing one of the conditions. For example:

SELECT * FROM folder_id_email_list AS el 
WHERE el.value IS NOT NULL AND el.value LIKE '%jackson.com'
UNION ALL
SELECT * FROM folder_id_email_list AS el 
WHERE el.value IS NOT NULL AND el.value LIKE '%jackson.com';

This approach ensures that each condition is evaluated independently, avoiding the issue with logical operator precedence in SQLite 3.35.4. However, this workaround may not be suitable for all use cases, particularly those involving more complex queries or performance constraints.

To prevent similar issues in the future, it is important to thoroughly test queries after upgrading to a new version of SQLite. This includes running regression tests to ensure that existing queries continue to return the expected results. Additionally, users should monitor the SQLite release notes and bug reports for any known issues or regressions that may affect their applications.

In summary, the regression in SQLite 3.35.4 that causes queries mixing AND and OR operators to return no rows is a result of a bug in the query optimizer. The issue has been fixed in the latest development branch, and users can either upgrade to a fixed version or apply a temporary workaround. To prevent similar issues, thorough testing and monitoring of SQLite releases are recommended.