If we run the following queries with UNNEST operator, we get the expected results:
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)
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:
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
 relates to

CALCITE2000 UNNEST a collection that has a field with nested data generates an Exception
 Closed

CALCITE4064 Unnest on array with nested structs causes ArrayStoreException
 Closed
 links to