Styxis
Company
Knowledge

ClickHouse MATERIALIZED CTEs: verify the final consumer still receives data

A successful first read does not establish that later consumers receive the same shared intermediate result.

Different consumers have different cardinalities

A shared intermediate result must preserve coverage for every consumer. A UNION ALL consumer that reads it repeatedly and a self-join consumer can require different row counts. A Truthound check should therefore inspect results by reader role rather than one number for the whole query. [1, 2, 3]

The reported empty later reader

The public ClickHouse example first reads a MATERIALIZED CTE through a UNION ALL scalar subquery, then through another scalar reader, before a final self-join becomes empty. Controls disable materialized CTEs or change reader order. This is a reported execution pattern, not a universal rule about every CTE's lifetime. [1]

Include the last reader in acceptance

Record expected and actual cardinalities for every consumer of the CTE. Change the reading order in a control to check whether the last consumer becomes empty. Preserve each reader's filters and join keys. An order comparison requires unchanged inputs and consumer semantics. [1, 3]

Derive the self-join expectation independently

An illustrative CTE containing 10 distinct-key rows produces 10 rows when self-joined on that key. A final self-join consumer receiving 10 rows retains the distinct-key input count; receiving 0 rows means it did not obtain the expected CTE scope. Duplicate keys invalidate this expectation. Do not impose the same count on another consumer that deliberately reads the CTE repeatedly through UNION ALL. [1, 3]

Group by reader role

Truthound Core's QueryGroupAggregateValidator can aggregate a prepared reader-audit table with SQL and compare each approved role with its expected range. Missing expected roles must also be checked. The feature does not manage CTE readers or shared-buffer lifetimes. If disabling materialization is a viable control, compare values and cardinalities and consider its cost before selecting an interim execution path. [1, 2, 3]

Application conditions

Prepare input_contract and actual_rows for each reader_role on the same CTE input, preserving filters, join keys and read order.

Alternatives

Inspect each consumer through a control with MATERIALIZED CTE settings disabled.

Limitations

  • Truthound does not manage CTE-reader lifetimes or shared buffers. Do not impose one expected count on different reader roles.

Evidence

Event checked
Subject checked
Modified