Unexpected COUNT(*) Result Due to Indexed Query with Aggregate Subquery Grouping

Incorrect Query Output with Indexed WHERE Clause and Aggregate Subquery

The core issue involves a SQLite query returning an unexpected value (1) instead of the logically anticipated result (0) when combining an indexed outer query with a specific type of aggregate subquery. This occurs under precise conditions related to query planner optimizations introduced in SQLite 3.41.0. The outer query uses an index (i0) to filter rows based on a subquery that aggregates data from a view (v0). The subquery’s logic (via v0.c0 BETWEEN 0 AND 0) should eliminate all rows, but due to an internal parser misconfiguration during query planning, the outer COUNT(*) incorrectly counts one row. The problem is rooted in how SQLite’s query planner interacts with indexed expressions and aggregate subqueries containing GROUP BY clauses that reference columns not present in the result set.

Conditions Triggering the Query Planner Misoptimization

Five preconditions must align for this issue to manifest:

1. Presence of an Aggregate Subquery: The query must contain a subquery with an aggregate function (e.g., AVG(), COUNT()) that processes data from a table or view.
2. GROUP BY Clause in Subquery: The aggregate subquery must include a GROUP BY clause.
3. GROUP BY References Non-Result Columns: The GROUP BY clause must reference columns or expressions not included in the subquery’s output.
4. Sort-Based GROUP BY Implementation: SQLite’s query planner must choose a sorting method (as opposed to hashing) to implement the GROUP BY operation.
5. Indexed Expressions in Outer Query: The outer query must utilize an index (via INDEXED BY or automatic index selection) that references expressions used in the WHERE clause.

When these conditions converge, a variable within SQLite’s abstract syntax tree (AST) that tracks the relationship between GROUP BY terms and result columns is incorrectly set. This leads to byte-code generation errors during query execution, causing the outer query to miscalculate the number of qualifying rows.

Diagnosing and Resolving the Indexed Aggregate Subquery Misbehavior

Step 1: Confirm the SQLite Version and Optimization Eligibility
First, verify whether the SQLite version is 3.41.0 or newer using SELECT sqlite_version();. Versions prior to 3.41.0 are unaffected. If the version is vulnerable, proceed to analyze the query structure.

Step 2: Identify Aggregate Subqueries with GROUP BY Mismatches
Examine all subqueries in the problematic query for aggregate functions and GROUP BY clauses. Specifically, check if the GROUP BY references columns or expressions not present in the subquery’s result set. In the example, v0 groups by 1>t0.c0 but outputs AVG(t0.c0), omitting the grouping key.

Step 3: Inspect Index Usage in the Outer Query
Use EXPLAIN QUERY PLAN to determine whether the outer query employs an index. In the test case, INDEXED BY i0 forces the use of an index on t0(c0 > 0). Automatic index selection could also trigger the issue.

Step 4: Check for Sort-Based GROUP BY Implementation
SQLite uses either sorting or hashing for GROUP BY. Sorting is chosen when the GROUP BY terms are not compatible with hash tables (e.g., expressions). Run EXPLAIN on the subquery; if the output includes OpenEphemeral with ORDER BY, sorting is active.

Step 5: Apply Immediate Workarounds
If upgrading to the patched SQLite trunk is impractical, implement these fixes:

• Disable the Faulty Optimization: Execute PRAGMA optimizer_flags='0x00000001'; to disable the "stat4" accelerator. This reverts to pre-3.41.0 behavior.
• Rewrite the Subquery: Include the GROUP BY expression in the subquery’s result set. Modify v0 to SELECT 1>t0.c0 AS grp, AVG(t0.c0) ... GROUP BY grp.
• Materialize the Subquery: Force the subquery to compute results before the outer query executes. Use a CTE:

  WITH v0_cte AS (SELECT AVG(t0.c0) AS c0 FROM t0 GROUP BY 1>t0.c0)
  SELECT COUNT(*) FROM t0 INDEXED BY i0 WHERE (SELECT COUNT(*) FROM v0_cte WHERE c0 BETWEEN 0 AND 0);
  

• Avoid Indexed Expressions: Remove INDEXED BY i0 or restructure the query to prevent the planner from choosing the problematic index.

Step 6: Upgrade to a Fixed SQLite Version
The issue is resolved in the latest trunk. Obtain the source from SQLite’s repository and compile it, or wait for an official release containing the fix.

Step 7: Validate Query Correctness Post-Fix
After applying a workaround or upgrading, re-run the test case. The outer COUNT(*) should now return 0, as the subquery (SELECT COUNT(*) FROM v0 ...) correctly evaluates to 0.

Step 8: Review Indexing Strategies for Aggregate Subqueries
To prevent recurrence, avoid creating indexes on expressions that interact with aggregate subqueries unless necessary. Use EXPLAIN and EXPLAIN QUERY PLAN to audit how indexes influence query planner decisions.

Step 9: Implement Regression Tests
Embed the test case or similar logic into application-level tests to detect regressions early. This is critical when using query planner optimizations in newer SQLite versions.

Step 10: Monitor Query Planner Changes in Release Notes
SQLite’s release notes and change logs detail query planner adjustments. Scrutinize these when upgrading to identify risks related to aggregate subqueries and indexed expressions.

By systematically addressing the interaction between indexed expressions, aggregate subqueries, and the query planner’s optimization logic, developers can resolve this issue and prevent similar anomalies in SQLite-based applications.