Unexpected Repeated Values in SQLite JSON Aggregate Functions with Window Clauses

Issue Overview: Inconsistent Behavior of JSON Aggregate Functions in Windowed Queries

The core issue revolves around unexpected results when using SQLite’s JSON aggregate functions within windowed queries. Users observed that functions like json_group_array() and json_group_object() produced repeated values instead of aggregating all rows within the window frame when combined with other aggregate functions like group_concat(). This behavior contradicted expectations based on standard SQL aggregation logic and differed from PostgreSQL’s correct handling of analogous operations.

Key Observations from the Test Case

A table d with columns n (text), i (integer), and v (integer) was populated with nine rows across three groups (a, b, c). A windowed query partitioned by n and ordered by i was executed to demonstrate the problem:

select distinct
  n,
  json_group_array(json_array(i, v)) over w as "json_group_array() nested",
  group_concat('(' || i || ',' || v || ')', ', ') over w as "group_concat()",
  group_concat(i) over w as "group_concat(i)",
  group_concat(v) over w as "group_concat(v)",
  json_group_array(v) over w as "json_group_array() flat",
  json_group_object(i, v) over w as "json_group_object()"
from d
window w as (partition by n order by i range between unbounded preceding and unbounded following);

Expected Results: All aggregate functions should return values corresponding to the full partition. For group a (rows with n='a'), this would mean:

• json_group_array() nested: [[1,11],[2,21],[3,31]]
• group_concat(): (1,11), (2,21), (3,31)
• json_group_array() flat: [11,21,31]

Actual Results:

• json_group_array() nested: [[1,11],[1,11],[1,11]]
• json_group_array() flat: [11,11,11]
• group_concat() and json_group_object() worked as expected.

The JSON functions only produced valid results when positioned as the last aggregate function in the query. Changing the order of columns altered which JSON function failed. This positional dependency indicated a deeper issue in SQLite’s query execution logic.

Root Cause Identification

The problem stemmed from SQLite’s internal handling of JSON aggregate functions in windowed queries. Unlike traditional aggregates (e.g., sum(), group_concat()), JSON aggregates maintained internal state that was not properly reset when multiple such functions appeared in the same query. This led to cross-contamination of aggregation contexts, where earlier JSON functions reused cached values from prior rows instead of reprocessing the entire partition.

Possible Causes: Internal State Management and Window Function Execution Order

1. Incorrect State Reset Between JSON Aggregate Functions

SQLite’s window function implementation processes rows incrementally. For most aggregates, the engine resets their state at partition boundaries. However, JSON aggregates (json_group_array(), json_group_object()) exhibited a bug where their internal state was not fully reset when multiple instances coexisted in the same query. This caused earlier JSON functions to retain values from previous rows, resulting in repeated outputs.

2. Order-Dependent Execution of Aggregates

The positional dependency of results suggested that SQLite processed aggregate functions in the order they appeared in the SELECT clause. When a JSON aggregate was followed by another aggregate, the latter might have inadvertently modified or reused the former’s state. Traditional aggregates like group_concat() did not interfere with each other, but JSON aggregates shared an improperly managed context.

3. Misalignment with SQL Standard Window Function Semantics

SQL standards dictate that window functions operate over the entire partition independently. The observed behavior violated this principle by allowing JSON aggregates to influence one another. PostgreSQL’s correct handling of analogous queries highlighted that SQLite’s implementation had a deviation in state management for JSON-specific aggregates.

Troubleshooting Steps, Solutions & Fixes: Resolving JSON Aggregate Anomalies

Step 1: Verify SQLite Version and Apply Patches

The bug was fixed in SQLite commit 9430ead7ba433cbf. Users should:

1. Check their SQLite version:

   sqlite3 --version
   

2. Upgrade to version 3.38.2 or newer, where the fix is included. For Linux systems:

   wget https://sqlite.org/2022/sqlite-autoconf-3380200.tar.gz
   tar xvfz sqlite-autoconf-3380200.tar.gz
   cd sqlite-autoconf-3380200
   ./configure && make
   sudo make install
   

Step 2: Modify Query Structure for Compatibility

If upgrading isn’t feasible, restructure queries to isolate JSON aggregates:

1. Position JSON Aggregates Last: Ensure JSON functions are the final aggregates in the SELECT clause.

   select distinct
     group_concat(i) over w as "group_concat(i)",  -- Traditional first
     json_group_array(v) over w as "json_group_array() flat"  -- JSON last
   from d
   window w as (...);
   

2. Use Subqueries or CTEs: Separate JSON aggregation into layers.

   with base as (
     select n, i, v,
       group_concat(i) over w as gc_i
     from d
     window w as (...)
   )
   select n, gc_i, json_group_array(v) over w as json_v
   from base
   window w as (...);
   

Step 3: Validate Results Against PostgreSQL (Optional)

For critical systems, cross-check results with PostgreSQL to ensure correctness:

1. Use equivalent JSON functions (jsonb_agg(), jsonb_object_agg()):

   select distinct
     n,
     jsonb_agg(i) over w as "jsonb_agg(i)",
     jsonb_agg(v) over w as "jsonb_agg(v)"
   from d
   window w as (partition by n order by i);
   

2. Compare outputs to identify discrepancies caused by SQLite’s pre-patch behavior.

Step 4: Report and Monitor Edge Cases

If anomalies persist after upgrades:

1. Minimize Test Cases: Reproduce the issue with the smallest possible dataset.
2. Submit Detailed Reports: Include SQLite version, query, schema, and output in bug reports to the SQLite Forum.

Long-Term Best Practices

1. Avoid Mixing JSON and Traditional Aggregates: Where possible, separate concerns into multiple queries.
2. Leverage Window Function Framing: Explicitly define RANGE or ROWS clauses to control partition bounds.
3. Benchmark After Upgrades: Confirm that performance remains optimal, as fixes might alter execution plans.

By methodically applying these steps, users can resolve inconsistencies in JSON aggregate functions and align SQLite’s behavior with expected SQL standards.