[jira] [Commented] (IMPALA-10920) UNNEST function for arrays in the select list

["Gabor Kaszab (Jira)" <ji...@apache.org>]

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

Gabor Kaszab commented on IMPALA-10920:
---------------------------------------

The outcome of the discussion was to have 2 separate syntaxes:

1: The ISO:SQL:2016 emntions UNNEST operator in the FROM clause. The first approach is to follow what the standard says:
{code:java}
SELECT a1.item, a2.item FROM complextypes_arrays t, UNNEST(t.arr1, t.arr2);
{code}

2: The other syntax is to guarantee compatibility with Postgres:

{code:java}
SELECT UNNEST(arr1), UNNEST(arr2) FROM complextypes_arrays;
{code}


> UNNEST function for arrays in the select list
> ---------------------------------------------
>
>                 Key: IMPALA-10920
>                 URL: https://issues.apache.org/jira/browse/IMPALA-10920
>             Project: IMPALA
>          Issue Type: Sub-task
>          Components: Backend, Frontend
>            Reporter: Gabor Kaszab
>            Assignee: Gabor Kaszab
>            Priority: Major
>              Labels: complextype
>
> There is a need for implementing an UNNEST(array) function that could be given in the select list so that we can replicate Postgres functionality that has the very same.
> Considering the following table:
> {code:java}
> CREATE TABLE tbl (
>   id int,
>   arr1 array<int>,
>   arr2 array<int>)
> STORED AS PARQUET;
> {code}
> with this data:
> {code:java}
> 1, {1,2}, {11,22,33}
> {code}
> A sample query would look like this:
> {code:java}
> SELECT id, UNNEST(arr1), UNNEST(arr2) FROM tbl;
> {code}
> ||id||unnest(arr1)||unnest(arr2)||
> |1|1|11|
> |1|2|22|
> |1|null|33|
> Not that the expected is to 'zip' the arrays instead of joining their values to have the same behaviour as Postgres 10+.



--
This message was sent by Atlassian Jira
(v8.20.1#820001)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org