[CALCITE-3789] Supports Presto style unnest with items alias

• Type: New Feature
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.21.0
• Fix Version/s: 1.23.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Will Yu
• Reporter: Will Yu
• Created: 12/Feb/20 23:44
• Updated: 24/May/20 12:00
• Resolved: 27/Apr/20 07:27

Description

In Presto, users are able to UNNEST multiple array columns and CROSS JOIN with the original table . As shown in the Presto doc:

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)

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

Issue Links

• links to: GitHub Pull Request #1811