[jira] [Resolved] (CALCITE-3789) Supports Presto style unnest with items alias

["Danny Chen (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-3789?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Danny Chen resolved CALCITE-3789.
---------------------------------
    Resolution: Fixed

Fixed in [e44beba|https://github.com/apache/calcite/commit/e44beba286ea9049c5fd00c3a3b0e4a4f1c03356], thanks for your PR, [~my7ym] !

> 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)