Incorrect COUNT Result on View with Window Function and Constant Expression

Analyzing Unexpected Query Results Involving Window Functions and Constant WHERE Clauses

Structure of the Query and Observed Behavior

The core issue arises from a combination of a view definition using a window function (TOTAL() OVER) and a WHERE clause that evaluates a constant expression. The query returns 0 for COUNT(*) when the logical expectation is 1. The components involved are:

1. Table and View Definition:

   • A table t0 with a single row containing the value 0.
   • A view v0 defined as:

     SELECT TOTAL(0) OVER (PARTITION BY t0.c0) FROM t0;
     

     This computes a windowed sum of the constant 0 over the partition defined by t0.c0 (which contains only one row). The result of the view is a single row with the value 0.0 (since TOTAL() returns a floating-point value).

2. Query Execution:
   The problematic query:

   SELECT COUNT(*) FROM v0 WHERE ('1' IS NOT('1' NOTNULL)) - (0);
   

   • The WHERE clause evaluates a constant expression. The user expects this expression to resolve to 1 (true), leading to a count of all rows in v0 (which is 1). Instead, the result is 0.

Key Factors Leading to Incorrect Results

The discrepancy stems from how SQLite optimizes and evaluates constant expressions in the presence of window functions and views. Three primary factors contribute to this behavior:

1. Window Function Execution Context:
   Window functions are evaluated after the FROM and WHERE clauses in standard SQL processing. However, when a view encapsulates a window function, the query planner might inadvertently alter the order of operations. In this case, the view’s computed column (v0.c0) is derived from a window function, which could interfere with the optimization of the WHERE clause if the planner incorrectly assumes the WHERE clause depends on the view’s output.

2. Constant Expression Folding:
   SQLite’s query planner attempts to evaluate constant expressions at compile time (a process called "constant folding"). The expression ('1' IS NOT('1' NOTNULL)) - (0) should be reduced to 1 (true) during this phase. However, a bug in older SQLite versions caused this folding to fail when combined with queries involving views that use window functions. The planner incorrectly treated the expression as non-constant or misapplied type conversion rules.

3. Type Handling in Comparison Operations:
   The expression '1' IS NOT('1' NOTNULL) involves implicit type conversions:

   • '1' NOTNULL evaluates to 1 (boolean true, stored as an integer).
   • '1' IS NOT 1 compares a string ('1') to an integer (1). In SQLite, these are not equal because their storage classes differ (TEXT vs. INTEGER). This comparison should return 1 (true).
   • Subtracting 0 from the result leaves 1, making the WHERE clause always true. The bug caused SQLite to miscalculate this result, leading to incorrect filtering.

Resolution and Verification Steps

To diagnose and resolve this issue:

1. Isolate the WHERE Clause:
   Execute the WHERE clause as a standalone query to verify its correctness:

   SELECT ('1' IS NOT('1' NOTNULL)) - (0);
   

   • Expected Result: 1.
   • Observed Result in Buggy Versions: 0 or NULL.

   If this returns 0, the problem lies in SQLite’s handling of the IS NOT operator or type conversions.

2. Simplify the View Definition:
   Remove the window function to test if it affects the WHERE clause evaluation:

   CREATE VIEW v0_simple(c0) AS SELECT 0 FROM t0;
   SELECT COUNT(*) FROM v0_simple WHERE ('1' IS NOT('1' NOTNULL)) - (0);
   

   • Expected Result: 1.
   • Observed Result in Buggy Versions: If this returns 1, the window function in the original view is the culprit.

3. Upgrade to SQLite Trunk (3.41.0+):
   The bug was addressed in commit 8fe13f7a. Verify the SQLite version:

   SELECT sqlite_version();
   

   Upgrade if the version predates March 2023.

4. Explicit Type Casting:
   Modify the WHERE clause to force consistent typing:

   SELECT COUNT(*) FROM v0 
   WHERE (CAST('1' AS INTEGER) IS NOT('1' NOTNULL)) - (0);
   

   This eliminates ambiguity in type comparisons.

Underlying Fix in SQLite

The root cause was an incorrect optimization in the query planner that mishandled constant expressions in subqueries or views containing window functions. The fix ensures that:

• Constant folding occurs after resolving dependencies on window functions.
• Type conversions in IS NOT comparisons are strictly evaluated by storage class (TEXT vs. INTEGER).

Best Practices to Avoid Similar Issues

1. Avoid Mixing Types in Comparisons:
   Explicitly cast operands to matching types when using IS NOT, =, or other comparison operators.

2. Test Views Separately:
   Validate views with window functions using simple SELECT statements before incorporating them into complex queries.

3. Monitor Query Planner Behavior:
   Use EXPLAIN or EXPLAIN QUERY PLAN to inspect how SQLite processes the query, especially for constant expressions and window functions.