[
https://issues.apache.org/jira/browse/CALCITE-3789?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-3789:
------------------------------------
Labels: pull-request-available (was: )
> Support validation of UNNEST multiple array columns like Presto
> ---------------------------------------------------------------
>
> Key: CALCITE-3789
> URL: https://issues.apache.org/jira/browse/CALCITE-3789
> Project: Calcite
> Issue Type: New Feature
> Components: core
> Affects Versions: 1.21.0
> Reporter: Will Yu
> Assignee: Will Yu
> Priority: Minor
> Labels: pull-request-available
>
> In Presto, users are able to UNNEST multiple array columns and CROSS JOIN
> with the original table . As shown in the [Presto
> doc|https://prestodb.io/docs/current/sql/select.html]:
> {code:sql}
> SELECT numbers, animals, n, a
> FROM (
> VALUES
> (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']),
> (ARRAY[7, 8, 9], ARRAY['cow', 'pig'])
> ) AS x (numbers, animals)
> CROSS JOIN UNNEST(numbers, animals) AS t (n, a)
> {code}
> yields:
> numbers | animals | n | a
> -----------+------------------+------+------
> [2, 5] | [dog, cat, bird] | 2 | dog
> [2, 5] | [dog, cat, bird] | 5 | cat
> [2, 5] | [dog, cat, bird] | NULL | bird
> [7, 8, 9] | [cow, pig] | 7 | cow
> [7, 8, 9] | [cow, pig] | 8 | pig
> [7, 8, 9] | [cow, pig] | 9 | NULL
> It seems Calcite does not have such a feature to support this semantics. In
> Calcite and for above SQL, _n_ and _a_ will be identified as alias of
> subfields of numbers.
> The plan will be to introduce a new Presto conformance and enable validation
> of such SQLs
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
