Incorrect NULL Handling in SQLite Window Functions and Aggregations
Issue Overview: Incorrect NULL Results in Window Functions and Aggregations
The core issue revolves around SQLite’s handling of NULL values in the context of window functions and aggregations, particularly when combined with the NOT NULL constraint optimization. The problem manifests when a query involving a window function or aggregation returns unexpected results due to the NOT NULL strength reduction optimization. This optimization, designed to improve query efficiency by assuming that columns marked as NOT NULL will never contain NULL values, can lead to incorrect results in certain edge cases.
The specific example provided involves a table v0 with a column c1 marked as INTEGER PRIMARY KEY and a column c2 of type TEXT. A view v5 is created using a window function (SUM(c2) OVER (PARTITION BY c1)). When querying this view, the first query (sql1) returns a NULL value for c1, which is expected since the table is empty. However, the second query (sql2), which filters for rows where c1 IS NULL, returns no results instead of the expected NULL value. This discrepancy is due to the NOT NULL strength reduction optimization incorrectly assuming that c1 cannot be NULL, even though the table is empty and the column is part of a window function.
The issue is further illustrated by another example involving a table t1 with a column X marked as NOT NULL. When querying this table with an aggregation (SELECT X IS NULL, count(*) FROM t1;), the query incorrectly returns "0 0" instead of the expected "1 0" because the optimization incorrectly assumes that X cannot be NULL, even though the table is empty.
Possible Causes: NOT NULL Strength Reduction Optimization and Edge Cases
The root cause of this issue lies in the NOT NULL strength reduction optimization, which was introduced to improve query efficiency by assuming that columns marked as NOT NULL will never contain NULL values. This optimization is generally beneficial and has been in use for over three years without significant issues. However, it fails in specific edge cases, particularly when dealing with empty tables and window functions or aggregations.
In the case of the table v0 and view v5, the optimization incorrectly assumes that c1 cannot be NULL, even though the table is empty and the column is part of a window function. This leads to the incorrect result in sql2, where the query filtering for c1 IS NULL returns no results instead of the expected NULL value.
Similarly, in the case of the table t1, the optimization incorrectly assumes that X cannot be NULL, even though the table is empty. This leads to the incorrect result in the aggregation query, where the query returns "0 0" instead of the expected "1 0".
The issue is exacerbated by the fact that the optimization is applied globally, affecting all parts of the query, including the WHERE clause. This global application of the optimization leads to incorrect results in edge cases where the assumption that a NOT NULL column cannot contain NULL values is violated, such as when dealing with empty tables or window functions.
Troubleshooting Steps, Solutions & Fixes: Addressing the NOT NULL Strength Reduction Optimization
To address this issue, it is necessary to modify the NOT NULL strength reduction optimization to ensure that it is only applied in contexts where the assumption that a NOT NULL column cannot contain NULL values is valid. Specifically, the optimization should only be applied in the WHERE clause, where the assumption is generally valid, and not in other parts of the query, such as window functions or aggregations, where the assumption may not hold.
The fix involves modifying the SQLite codebase to restrict the application of the NOT NULL strength reduction optimization to the WHERE clause. This change ensures that the optimization is only applied in contexts where it is safe to assume that a NOT NULL column cannot contain NULL values, while avoiding incorrect results in edge cases involving empty tables or window functions.
The fix has been implemented in the SQLite trunk, branch-3.45, and the SQLite Fiddle, and will be included in the next release. This fix ensures that queries involving window functions or aggregations on empty tables will return the correct results, even when the NOT NULL constraint is present.
In addition to the fix, it is important to be aware of the potential for similar issues in other contexts where the NOT NULL strength reduction optimization may be applied. Developers should carefully consider the implications of the optimization when designing queries, particularly when dealing with empty tables or complex queries involving window functions or aggregations.
To summarize, the issue of incorrect NULL handling in SQLite window functions and aggregations is caused by the NOT NULL strength reduction optimization, which incorrectly assumes that NOT NULL columns cannot contain NULL values in all contexts. The fix involves restricting the application of the optimization to the WHERE clause, ensuring that it is only applied in contexts where the assumption is valid. This fix has been implemented in the SQLite codebase and will be included in the next release, ensuring that queries involving window functions or aggregations on empty tables return the correct results. Developers should be aware of the potential for similar issues in other contexts and carefully consider the implications of the optimization when designing queries.
