Those queries, and the ones in the 5127 test case, look valid to me. If "books" is an array then your first query is requesting an array of arrays. If that's not what you want, you should probably use UNNEST.
On Tue, May 3, 2022 at 5:40 AM Dmitry Sysolyatin <[email protected]> wrote: > > I found more failing queries but they are not related to unnest. But looks > like the cause of both https://issues.apache.org/jira/browse/CALCITE-5127 and > those queries is wrong correlate variable's row type. But I am not sure > 100%, need an expert :) > > !use bookstore > SELECT array(SELECT lau."books") > FROM "bookstore"."authors" au > LEFT JOIN "bookstore"."authors" lau ON (lau."name" = au."name"); > > Throw java.lang.AssertionError: Conversion to relational algebra failed to > preserve datatypes > > !use bookstore > SELECT array(SELECT lau."name" || 'test') > FROM "bookstore"."authors" au > LEFT JOIN "bookstore"."authors" lau ON (lau."name" = au."name"); > > Throw field ordinal [5] out of range; input fields are: [name0, name00] > > > On Tue, May 3, 2022 at 11:33 AM Dmitry Sysolyatin <[email protected]> > wrote: > > > I have created jira task - > > https://issues.apache.org/jira/browse/CALCITE-5127 > > And also created a commit with test cases - > > https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8 > > > > On Mon, May 2, 2022 at 6:47 PM Julian Hyde <[email protected]> wrote: > > > >> Oops, I misspoke. Calcite hasn’t used the array-to-Values transformation > >> here. I’m not sure where the ARRAY literal has gone. Possibly it’s still > >> present, just not being printed in the plan. > >> > >> Julian > >> > >> > On May 2, 2022, at 8:41 AM, Julian Hyde <[email protected]> wrote: > >> > > >> > The plan isn’t “completely wrong”. Calcite intentionally converts > >> UNNEST of a constant ARRAY into Values because the latter is a primitive > >> relational expression. > >> > > >> > As for the casts, maybe some confusion about scalar types versus record > >> types with a single field. Can you please log a jira case. > >> > > >> > Julian > >> > > >> >> On May 2, 2022, at 8:09 AM, Dmitry Sysolyatin <[email protected]> > >> wrote: > >> >> > >> >> Hi! > >> >> I am working on https://issues.apache.org/jira/browse/CALCITE-5126. > >> When I > >> >> was writing tests for this task I faced with issue that reproducible in > >> >> main branch as well. > >> >> > >> >> I tried to execute the following query using unnest.iq file > >> >> > >> >> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) > >> >> FROM (SELECT ARRAY[1,2,3] as x) s; > >> >> > >> >> Calcite generated the following plan: > >> >> > >> >> EnumerableProject(EXPR$0=[$1]): rowcount = 1.0, cumulative cost = {14.0 > >> >> rows, 12.0 cpu, 0.0 io}, id = 98 > >> >> > >> >> EnumerableCorrelate(correlation=[$cor0], joinType=[inner], > >> >> requiredColumns=[{0}]): rowcount = 1.0, cumulative cost = {13.0 rows, > >> 11.0 > >> >> cpu, 0.0 io}, id = 97 > >> >> > >> >> EnumerableValues(tuples=[[{ 0 }]]): rowcount = 1.0, cumulative cost = > >> >> {1.0 rows, 1.0 cpu, 0.0 io}, id = 79 > >> >> > >> >> EnumerableCollect(field=[x]): rowcount = 1.0, cumulative cost = {5.0 > >> >> rows, 5.0 cpu, 0.0 io}, id = 96 > >> >> > >> >> EnumerableProject(EXPR$0=[+($0, 1)]): rowcount = 1.0, cumulative > >> cost > >> >> = {4.0 rows, 4.0 cpu, 0.0 io}, id = 95 > >> >> > >> >> EnumerableUncollect: rowcount = 1.0, cumulative cost = {3.0 rows, > >> >> 3.0 cpu, 0.0 io}, id = 94 > >> >> > >> >> EnumerableProject(X=[$cor0.X]): rowcount = 1.0, cumulative > >> cost = > >> >> {2.0 rows, 2.0 cpu, 0.0 io}, id = 93 > >> >> > >> >> EnumerableValues(tuples=[[{ 0 }]]): rowcount = 1.0, > >> cumulative > >> >> cost = {1.0 rows, 1.0 cpu, 0.0 io}, id = 79 > >> >> > >> >> It is a completely wrong plan. It does not use ARRAY[1,2,3] at all and > >> code > >> >> fails with error: > >> >> Caused by: java.lang.ClassCastException: class java.lang.Integer > >> cannot be > >> >> cast to class java.util.List (java.lang.Integer and java.util.List are > >> in > >> >> module java.base of loader 'bootstrap') > >> >> > >> >> > >> >> Maybe someone knows how to fastly fix this? > >> > >
