Will Yu created CALCITE-3789:
--------------------------------
Summary: 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
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)
