You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Will Yu (Jira)" <ji...@apache.org> on 2020/04/28 03:32:00 UTC
[jira] [Commented] (CALCITE-3789) Supports Presto style unnest with
items alias
[ https://issues.apache.org/jira/browse/CALCITE-3789?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17094119#comment-17094119 ]
Will Yu commented on CALCITE-3789:
----------------------------------
Thanks [~danny0405] for the patient & detailed review. Appreciate it!
> Supports Presto style unnest with items alias
> ---------------------------------------------
>
> 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
> Fix For: 1.23.0
>
> Time Spent: 15h 10m
> Remaining Estimate: 0h
>
> 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)