[
https://issues.apache.org/jira/browse/CALCITE-3789?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17076500#comment-17076500
]
Julian Hyde commented on CALCITE-3789:
--------------------------------------
[~my7ym], That's interesting. Could we treat this as syntactic sugaring? In
other words, is there a standard SQL statement where it would be valid to write
"k.empno"?
It would be good if we could do it using sugaring. The rules the validator uses
to resolve multi-part identifiers are very complicated (because standard SQL is
complicated) and it would be great if we could keep the same rules. Syntactic
sugaring would change the input to the validator rather than changing the
validator.
> 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: 11h 40m
> 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)
