Distinguish a NULL aggregate row from no row in DuckDB
Check a NULL aggregate value separately from the absence of its result row on empty input. Retain controls for correlation and DISTINCT so missing-value handling cannot hide a cardinality error.
A NULL value can have a row
An aggregate value can be NULL while its result row still exists. That difference matters when an outer COUNT observes a scalar subquery. Truthound Core can check result cardinality once the empty-input semantics have been established independently. [1, 2, 3]
A correlated empty-aggregate report
DuckDB issue #28050 reports DISTINCT MIN over empty input changing from 1 row to 0 under an always-true correlation. The DISTINCT-free control differs. A follow-up links a correction and planned 2.0 backport; it does not establish which installed release contains that change. [1]
Keep value and cardinality separate
Record the aggregate value and row count separately across correlation and DISTINCT variants. Keep the empty source fixed. Replacing NULL with 0 would obscure the difference between an existing row with no value and a missing result row. [1, 3]
The single aggregate row
For empty e without GROUP BY, MIN(a) creates 1 row holding NULL. DISTINCT does not remove that sole NULL row, so 1 output row passes. An output of 0 rows fails because it changes the outer COUNT. This expected count comes from the declared aggregate shape. [1, 3]
Check completed results only
RowCountValidator in Truthound Core Python SDK 3.3.2 can apply exact_count=1 to the materialized result. The operator must obtain a completed query result; a failed query is not an empty valid table. The SDK does not repair correlation removal. [1, 2, 3]
Verify materialization and version
Compare direct materialization and the DISTINCT-free control, then verify the actual deployed version if adopting the linked correction. Other aggregates have different empty-input values, so define their contracts separately. [1, 3]
A null check cannot inspect absence
The acceptance record needs both result cardinality and value state. A NULL check alone can never distinguish the absent row, because there is no row on which that check could run. [1, 3]
Application conditions
Prepare completed results for the fixed empty source and correlation/DISTINCT variants.
Alternatives
Check direct materialization and a DISTINCT-free control together.
Limitations
Do not turn a failed query into a valid empty table or NULL value.
Evidence
- Event checked
- Subject checked
- Modified
