[
https://issues.apache.org/jira/browse/CALCITE-7920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18139139#comment-18139139
]
Alessandro Solimando commented on CALCITE-7920:
-----------------------------------------------
I filed the ticket while reviewing your PR for CALCITE-7608 not to forget, and
I got sidetracked before I could link it, thanks for the reminder.
I hope I will manage to finalize the review for your PR by the end of this week.
> 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
> Priority: Major
>
> 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)
