[
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)
