Alessandro Solimando created CALCITE-7920:
---------------------------------------------
Summary: Enhance RelFieldTrimmer to trim Uncollect nodes
Key: CALCITE-7920
URL: https://issues.apache.org/jira/browse/CALCITE-7920
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.42.0
Reporter: Alessandro Solimando
h3. Preliminaries
_Uncollect_ expands input collections into output rows. When expanding multiple
collections, elements are paired by position and shorter collections are padded
with NULL. The longest collection determines the number of output rows.
For example, this query selects only a:
{code:sql}
SELECT a
FROM UNNEST(ARRAY[10, 20], ARRAY[1, 2, 3]) AS t(a, b);
{code}
It returns three rows: 10, 20 and NULL. Although b is not selected, its
collection produces a third row, where a is NULL.
Removing that collection changes the query to:
{code:sql}
SELECT a
FROM UNNEST(ARRAY[10, 20]) AS t(a);
{code}
This returns only two rows: 10 and 20. Field trimming may remove column b from
the output, but must retain ARRAY[1, 2, 3] as an input to UNNEST, because it
causes the third output row.
h3. Missing support
_RelFieldTrimmer_ has no specialized handling for {_}Uncollect{_}. Its generic
implementation treats every input field as used and returns an identity output
mapping
([here|https://github.com/apache/calcite/blob/148bb0975f6be47057137283a98c1914bab87037/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java#L414-L436]).
Add explicit handling so that field trimming can distinguish required output
fields from collection inputs that must remain to preserve row counts.
h3. Verification
The examples were executed locally on PostgreSQL 18.6 and Trino 484. Both
returned 10, 20 and NULL for the two-collection query, and 10 and 20 for the
one-collection query.
Equivalent queries were executed on DuckDB 2.0.0 using its SELECT-list syntax:
{code:sql}
SELECT a
FROM (
SELECT unnest([10, 20]) AS a,
unnest([1, 2, 3]) AS b
) AS t;
SELECT a
FROM (
SELECT unnest([10, 20]) AS a
) AS t;
{code}
DuckDB returned the same values. Additional count queries confirmed these
results on all three engines:
||Expansion||COUNT( * )||COUNT(a)||
|Two collections|3|2|
|One collection|2|2|
These executions confirm that removing an unused collection input can change
the query result.
h3. Proposed fix
Add {_}Uncollect{_}-specific handling to {_}RelFieldTrimmer{_}. Return the
required output fields with the appropriate mapping, introducing a projection
where necessary, and propagate the required input fields to the child.
Preserve collection inputs whose cardinality affects the result, even when
their output fields are unused.
Add coverage in _RelFieldTrimmerTest_ for partially used outputs, multiple
collections, ordinality, and preservation of row counts for NULL and empty
collections. This issue stays within the current field-trimming framework. A
future extension could use information from ancestor operators to remove
collection inputs when doing so provably preserves the final result.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
