[jira] [Commented] (CALCITE-3789) Support validation of UNNEST multiple array columns like Presto

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3789?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17044928#comment-17044928 ] 

Julian Hyde commented on CALCITE-3789:
--------------------------------------

Review comments:
* Can you split into a separate commit that adds Presto conformance? (It's OK if both commits have the same JIRA case.)
* The enum constant PRESTO needs a comment.
* Can you please review all of the methods in SqlConformance and modify the javadoc and the implementation for any where Presto differs from the default. {{isPercentRemainderAllowed()}} is one.

> 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
>          Time Spent: 2h 20m
>  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)