[jira] [Comment Edited] (CALCITE-4063) Unnest an array of single-item structs causes ClassCastException

["Ruben Q L (Jira)" <ji...@apache.org>]

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

Ruben Q L edited comment on CALCITE-4063 at 6/18/20, 8:30 AM:
--------------------------------------------------------------

[~danny0405], thanks for you comment.
First of all, please bear in mind that this patch does not change the behavior of UNNEST in the scenarios like the one you mention (array of arrays), it just fixes an issue of UNNEST an array with single-item ROWs inside. So the example that you mention works the same before and after the patch.
I was also a bit surprise, because I have run some tests with PostgreSQL too. But, during the analysis of this problem, I digged into the original implementation of UNNEST features (CALCITE-855, CALCITE-854, PHOENIX-953), and found some relevant comments in the last one, specially this one [1] by [~julianhyde] :
{quote}Postgres implementation of UNNEST departs from the standard in a big way. I think it is weird, and these folks on stackoverflow seem to agree: [http://stackoverflow.com/questions/23003601/sql-multiple-unnest-in-single-select-list].
Calcite supports the standard version of UNNEST, not Postgres's variant of it.
{quote}

I think that explains the mismatch between Calcite and PosgreSQL results.

[1] https://issues.apache.org/jira/browse/PHOENIX-953?focusedCommentId=14682172&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14682172


was (Author: rubenql):
[~danny0405], thanks for you comment.
First of all, please bear in mind that this patch does not change the behavior of UNNEST in the scenarios like the one you mention (array of arrays), it just fixes an issue of UNNEST an array with single-item ROWs inside. So the example that you propose works the same before and after the patch.
I was also a bit surprise, because I have run some tests with PostgreSQL too. But, during the analysis of this problem, I digged into the original implementation of UNNEST features (CALCITE-855, CALCITE-854, PHOENIX-953), and found some relevant comments in the last one, specially this one [1] by [~julianhyde] :
{quote}Postgres implementation of UNNEST departs from the standard in a big way. I think it is weird, and these folks on stackoverflow seem to agree: [http://stackoverflow.com/questions/23003601/sql-multiple-unnest-in-single-select-list].
Calcite supports the standard version of UNNEST, not Postgres's variant of it.
{quote}

I think that explains the mismatch between Calcite and PosgreSQL results.

[1] https://issues.apache.org/jira/browse/PHOENIX-953?focusedCommentId=14682172&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14682172

> Unnest an array of single-item structs causes ClassCastException
> ----------------------------------------------------------------
>
>                 Key: CALCITE-4063
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4063
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.23.0
>            Reporter: Ruben Q L
>            Assignee: Ruben Q L
>            Priority: Major
>              Labels: pull-request-available
>         Attachments: DynamicCode.java
>
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> If we run the following queries with UNNEST operator, we get the expected results:
> {code:sql}
> select * from UNNEST(array[3, 4]) as T2(y);
> -- y=3
> -- y=4
> select * from UNNEST(array[array[3], array[4]]) as T2(y)
> -- y=[3]
> -- y=[4]
> select * from UNNEST(array[ROW(3), ROW(4)]) as T2(y)
> -- y=[3]
> -- y=[4]
> -- Is this result ok? (see first comment of the current ticket)
> {code}
> However, if we try to combine them with a correlation with some other values, as we could do in more realistic examples: 
> {{select * from dept_nested as d, UNNEST(d.employees) e2}}
> The first two return the expected results, but the last one throws an exception:
> {code:sql}
> select * from (values (1), (2)) T1(x), UNNEST(array[3, 4]) as T2(y);
> -- x=1; y=3
> -- x=1; y=4
> -- x=2; y=3
> -- x=2; y=4
> select * from (values (1), (2)) T1(x), UNNEST(array[array[3], array[4]]) as T2(y);
> -- x=1; y=[3]
> -- x=1; y=[4]
> -- x=2; y=[3]
> -- x=2; y=[4]
> select * from (values (1), (2)) T1(x), UNNEST(array[ROW(3), ROW(4)]) as T2(y);
> -- ERROR!!!
> -- java.lang.ClassCastException: org.apache.calcite.runtime.FlatLists$Flat1List cannot be cast to java.lang.Integer
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)