CLUSTER silently corrupts cluster ids when a user column/alias is named __giql_is_new_cluster

[conradbzura]

Description

When a CLUSTER query projects a user column or alias whose name is literally the synthesized flag name __giql_is_new_cluster, the cluster ids are silently computed wrong. Examples:

-- table has a real column __giql_is_new_cluster
SELECT *, __giql_is_new_cluster AS leaked, CLUSTER(interval) AS cid FROM peaks
-- cids come out 7/14/23 instead of 1/1/2

SELECT *, score AS __giql_is_new_cluster, CLUSTER(interval) AS cid FROM peaks
-- cids come out 5/10/19 instead of 1/1/2

The user's column and the synthesized flag share the exact name; DuckDB renames the synthesized one to __giql_is_new_cluster_1, the outer * EXCLUDE ("__giql_is_new_cluster") strips the user's column, and the outer SUM(__giql_is_new_cluster) binds to the user's values instead of the 0/1 boundary flag — a silent cumulative-sum corruption of the cluster ids.

Root cause

giql.expanders.cluster._transform_for_cluster synthesizes __giql_is_new_cluster (giql.constants.CLUSTER_FLAG_COL) and both EXCLUDEs it from the outer star and sums it, but nothing rejects a user projection that references or aliases to a __giql_-prefixed name. The reserved __giql_ prefix is documented (#161, #178) to avoid collisions, but there is no validation pass enforcing it — so the collision fails silently rather than loudly.

Expected behavior

A user projection referencing or aliasing to a __giql_-prefixed reserved name should raise a clear error (converting a silent wrong-answer into a loud one), consistent with the reserved-namespace contract in #161/#178. Relatedly, the existing #161 test (test_transpile_should_disambiguate_cluster_when_source_has_is_new_cluster_column) only covers the unprefixed is_new_cluster column, giving false confidence that the prefixed collision is handled — that coverage gap should be closed. Surfaced during the #190 (PR #193) review; pre-existing and out of #190's scope.

https://claude.ai/code/session_01KAYsMtN7vYuECZHeeFFzCM

[conradbzura]

Scope update — one sub-case incidentally fixed by PR #193 (#190/#194)

PR #193's Round-2 remediation materializes a star's aliased sibling items in the inner __giql_lag_calc subquery under reserved __giql_sibling_N names (rather than under the user's alias). That change touches one corner of this issue, so the scope here has narrowed slightly — recording it so the eventual fix targets only what remains.

Now fixed (verified on DuckDB at PR #193 head 5ac3aaa): aliasing a projection item to the reserved name when the table has no real column of that name —

-- table has NO __giql_is_new_cluster column
SELECT *, score AS __giql_is_new_cluster, CLUSTER(interval) AS cid FROM peaks
-- before #193: ParserException (duplicate EXCLUDE entry) — a crash, surfaced in the #190 review
-- after  #193: cids come out 1/1/2 correctly; the aliased column surfaces its own value

The aliased sibling no longer shares a name with the synthesized flag, so it neither duplicates the EXCLUDE list nor mis-binds the SUM.

Still broken (unchanged — this remains the substance of #195): whenever a real table column named __giql_is_new_cluster is surfaced by a star, the outer SUM(__giql_is_new_cluster) binds to the user's values and the cluster ids are silently corrupted. Both original examples still reproduce at 5ac3aaa:

-- table HAS a real __giql_is_new_cluster column
SELECT *, __giql_is_new_cluster AS leaked, CLUSTER(interval) AS cid FROM peaks  -- cids 7/14/21, want 1/1/2
SELECT *, CLUSTER(interval) AS cid FROM peaks                                    -- cids 7/14/21, want 1/1/2

Net: the alias-to-reserved-name variant is now handled (loud crash → correct output); the real-reserved-named-column variant is untouched. The recommended fix is unchanged — a validation pass that rejects a user projection referencing or aliasing to a __giql_-prefixed reserved name (converting the silent wrong-answer into a loud one), plus closing the #161 test gap that only covers the unprefixed is_new_cluster name. Reason for keeping this open: the reserved-sibling change addresses aliases by side effect but does not enforce the reserved-namespace contract, so a real __giql_-named source column still corrupts silently.

Verified with dialect="duckdb" transpile + execute; no code change filed against #195.