[
https://issues.apache.org/jira/browse/CALCITE-4063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17134398#comment-17134398
]
Ruben Q L edited comment on CALCITE-4063 at 6/12/20, 5:21 PM:
--------------------------------------------------------------
You are right [~julianhyde].
The root cause seems to be a mismatch regarding what an UNNEST of a "simple"
ROW (just 1 item) is expected to return as element type (integer), and what its
enumerable actually returns (List), see attachment.
If we run the UNNEST just by itself:
{code:sql}
select * from UNNEST(array[ROW(3), ROW(4)]) as T2(y)
-- Ok
{code}
there is no problem. But if we try to chain it with other operations:
{code:sql}
select * from (values (1), (2)) T1(x), UNNEST(array[ROW(3), ROW(4)]) as T2(y);
-- Error!
{code}
we run into the exception due to this mismatch.
Note that the problem does not occur if we UNNEST a "complex" ROW (more than 1
item), because in that case the element type and the enumerable return type are
aligned (List):
{code:sql}
select * from (values (1), (2)) T1(x), UNNEST(array[ROW(3, 5), ROW(4, 6)]) as
T2(y, z)
-- Ok
-- 'with ordinality' adds a second item to the ROW, so the problem is avoided:
select * from (values (1), (2)) T1(x), UNNEST(array[ROW(3), ROW(4)]) WITH
ORDINALITY as T2(y, o)
-- Ok
{code}
was (Author: rubenql):
You are right [~julianhyde].
The root cause seems to be a mismatch regarding what an UNNEST of a "simple"
ROW (just 1 item) is expected to return as element type (integer), and what its
enumerable actually returns (List), see attachment.
If we run the UNNEST just by itself:
{code:sql}
select * from UNNEST(array[ROW(3), ROW(4)]) as T2(y)
{code}
there is no problem. But if we try to chain it with other operations:
{code:sql}
select * from (values (1), (2)) T1(x), UNNEST(array[ROW(3), ROW(4)]) as T2(y);
-- Error!
{code}
we run into the exception due to this mismatch.
Note that the problem does not occur if we UNNEST a "complex" ROW (more than 1
item), because in that case the element type and the enumerable return type are
aligned (List):
{code:sql}
select * from (values (1), (2)) T1(x), UNNEST(array[ROW(3, 5), ROW(4, 6)]) as
T2(y, z)
-- 'with ordinality' adds a second item to the ROW, so the problem is avoided:
select * from (values (1), (2)) T1(x), UNNEST(array[ROW(3), ROW(4)]) WITH
ORDINALITY as T2(y, o)
{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
> Attachments: DynamicCode.java
>
>
> 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)
