You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Dmitry Sysolyatin <dm...@gmail.com> on 2022/05/02 15:09:19 UTC
Incorrect plan for complex query
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?
Re: Incorrect plan for complex query
Posted by Julian Hyde <jh...@apache.org>.
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
<dm...@gmail.com> 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 <dm...@gmail.com>
> 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 <jh...@gmail.com> 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 <jh...@gmail.com> 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 <dm...@gmail.com>
> >> 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?
> >>
> >
Re: Incorrect plan for complex query
Posted by Dmitry Sysolyatin <dm...@gmail.com>.
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 <dm...@gmail.com>
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 <jh...@gmail.com> 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 <jh...@gmail.com> 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 <dm...@gmail.com>
>> 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?
>>
>
Re: Incorrect plan for complex query
Posted by Dmitry Sysolyatin <dm...@gmail.com>.
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 <jh...@gmail.com> 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 <jh...@gmail.com> 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 <dm...@gmail.com>
> 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?
>
Re: Incorrect plan for complex query
Posted by Julian Hyde <jh...@gmail.com>.
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 <jh...@gmail.com> 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 <dm...@gmail.com> 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?
Re: Incorrect plan for complex query
Posted by Julian Hyde <jh...@gmail.com>.
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 <dm...@gmail.com> 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?