[ https://issues.apache.org/jira/browse/CALCITE-4063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17134302#comment-17134302 ]
Ruben Q L commented on CALCITE-4063: ------------------------------------ The issue might be on the UNNESTing of a simple ROW, which returns an array instead of a value. {code:sql} select * from UNNEST(array[ROW(3), ROW(4)]) as T2(y) -- y=[3] => should it be y=3 ??? -- y=[4] => should it be y=4 ??? {code} If we compare it with a similar query with a "complex" ROW, the problem does not occur: {code:sql} select * from UNNEST(array[ROW(3, 3), ROW(4, 4)]) as T2(y, z) -- y=3; z=3 -- y=4; z=4 select * from (values (1), (2)) T1(x), UNNEST(array[ROW(3, 3), ROW(4, 4)]) as T2(y, z) -- x=1; y=3; z=3 -- x=1; y=4; z=4 -- x=2; y=3; z=3 -- x=2; y=4; z=4 {code} > Unnest an array of 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 > Priority: Major > > 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] > {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)