DuckDB WITH RECURSIVE and NULL branches: make the Boolean schema explicit
A branch containing only NULL still needs a clear type contract for downstream consumers.
A missing value is not an unspecified type
A leading empty-valued branch should declare its intended type; NULL itself is not a sufficient data-type contract. Truthound can verify that contract after a result arrives. First distinguish a failed query from a successfully delivered set of legitimate NULL values. [1, 2, 3]
A non-recursive CTE can still fail
The public DuckDB issue combines NULL and TRUE with UNION ALL in a non-recursive CTE. WITH succeeds, while WITH RECURSIVE attempts to cast BOOLEAN to the internal NULL type and fails. A control explicitly typing NULL as BOOLEAN is also provided. Changing the recursive keyword should not change the logical type required by the consumer. [1]
Inspect the schema that actually arrives
Fix the expected Boolean schema and compare completed query results across keyword and branch-order variants. Include an all-NULL case. Skipping type checks because no concrete value is present can hide a contract error until the next non-null input arrives. [1, 3]
The type is the illustrative comparison value
For an illustrative consumer contract defined as Boolean, an actual Boolean output type passes and an internal Null type fails. Empty or entirely missing-valued data does not change that basis. If execution produced no result, however, do not record that a type comparison was performed. [1, 3]
Explicit casts and Truthound
Compare a path with an explicit BOOLEAN cast on the NULL branch, then use Truthound Core's ColumnTypeValidator to inspect the received table against the approved type. Preserve transport metadata before conversion if Arrow or another format carries additional type details. This does not repair the CTE binder or guarantee support across execution modes. [1, 2, 3]
Application conditions
Preserve actual values and column types from a completed CTE execution and compare them with the source consumer contract for the same execution scope.
Alternatives
Compare a query that explicitly casts the NULL branch to BOOLEAN.
Limitations
A query that did not execute cannot pass a data-quality check. The check does not repair CTE-binder type inference.
Evidence
- Event checked
- Subject checked
- Modified
