[jira] [Comment Edited] (IMPALA-11148) Unnesting from views work only when alias is provided

["Csaba Ringhofer (Jira)" <ji...@apache.org>]

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

Csaba Ringhofer edited comment on IMPALA-11148 at 11/4/22 3:31 PM:
-------------------------------------------------------------------

found a similar issue related to star expansion:
select * from array_tbl t left join t.int_1d;

The query above works by default, but if set expand_complex_types=1; then it returns an error:
ERROR: AnalysisException: Duplicate table alias: 't.int_1d'

The query works in both cases if we add an alias for the array:
select * from array_tbl t left join t.int_1d a;
but it is questionable whether we get what we want: the select list will include int_1d both in an unnested form (item) and for each line as the original array in json

We get the same exception from
select * from array_tbl t, unnest(t.int_1d);

Checked what Hive does with lateral views and star expansion, and it also includes both the expanded and the non expanded array in select star:
select * from array_tbl t lateral view explode(t.int_1d) item as i;




was (Author: csringhofer):
found a similar issue related to star expansion:
select * from array_tbl t left join t.int_1d;

The query above works by default, but if set expand_complex_types=1; then it returns an error:
ERROR: AnalysisException: Duplicate table alias: 't.int_1d'

The query works in both cases if we add an alias for the array:
select * from array_tbl t left join t.int_1d a;
but it is questionable whether we get what we want: the select list will include int_1d both in an unnested form (item) and for each line as the original array in json



> Unnesting from views work only when alias is provided
> -----------------------------------------------------
>
>                 Key: IMPALA-11148
>                 URL: https://issues.apache.org/jira/browse/IMPALA-11148
>             Project: IMPALA
>          Issue Type: Bug
>          Components: Frontend
>    Affects Versions: Impala 4.1.0
>            Reporter: Gabor Kaszab
>            Priority: Major
>              Labels: complextype
>
> With [IMPALA-9498|https://issues.apache.org/jira/browse/IMPALA-9498] it is now possible to provide arrays in the select list. However, with that change some existing functionality seems broken. When doing a regular (joining) unnest from views we get an error:
> {code:java}
> > select id, arr1.item from view_arrays va, va.arr1;
> ERROR: AnalysisException: Illegal column/field reference 'arr1.item' with intermediate collection 'arr1' of type 'ARRAY<INT>'{code}
> Meanwhile when doing the same with regular tables it works:
> {code:java}
> > select id, arr1.item from functional_orc_def.complextypes_arrays va, va.arr1;
> +----+------+
> | id | item |
> +----+------+
> | 1  | 1    |
> | 1  | 2    |
> | 1  | 3    |
> | 1  | 4    |
> | 1  | 5    |
> | 2  | 1    |
> | 2  | NULL |
> | 2  | 3    |
> | 2  | 4    |
> | 2  | 5    |
> | 3  | 10   |
> | 3  | 9    |
> | 3  | 8    |
> | 4  | 10   |
> | 5  | 10   |
> | 5  | NULL |
> | 5  | 12   |
> | 7  | 1    |
> | 7  | 2    |
> | 10 | 1    |
> | 10 | 2    |
> | 10 | 3    |
> +----+------+
> Fetched 22 row(s) in 3.70s
> {code}
> Also, with the "query from view approach" it works if we provide an alias explicitly for the array:
> {code:java}
> select id, arr1.item from view_arrays va, va.arr1 arr1; {code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

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