You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Julian Feinauer <j....@pragmaticminds.de> on 2022/01/10 20:39:17 UTC
Sort getting removed during optimization
Hi all,
I just observed a Plan transformation that I don’t quite understand.
The Logical Plan is:
LogicalSort(sort0=[$0], dir0=[DESC])
LogicalProject(time=[COALESCE($0, $2)], s1=[$1], s0=[$3])
LogicalJoin(condition=[=($0, $2)], joinType=[full])
LogicalTableScan(table=[[root, root.test.d0.s1]])
LogicalTableScan(table=[[root, root.vehicle.d0.s0]])
And the result of the Volcano Planner is
BindableProject(time=[COALESCE($0, $2)], s1=[$1], s0=[$3])
BindableJoin(condition=[=($0, $2)], joinType=[full])
BindableTableScan(table=[[root, root.test.d0.s1]])
BindableTableScan(table=[[root, root.vehicle.d0.s0]])
I know wonder, why the LogicalSort is removed from the planner as the Output of the Join is NOT sorted (for a full join, for a left join it would be fine as the input table is sorted).
Is there anything I am missing or is this probably a Bug?
Thanks already!
Julian
Re: Sort getting removed during optimization
Posted by Vladimir Ozerov <pp...@gmail.com>.
Hi Julian,
When invoking the optimizer, you may provide the desired trait set of the
top-level node. It might happen, that the specific collation is not
requested from the optimizer, and hence the plan with a top-level Sort
operator is not chosen. Could you please show how you invoke the planner?
Regards,
Vladimir.
вт, 11 янв. 2022 г. в 12:44, Julian Feinauer <j....@pragmaticminds.de>:
> Hey Stamatis,
>
> yes, thats why I looked it up at first… the results are wrong : )
> So both tables for themselves are sorted but the Full Join is finally two
> blocks. The Left Join (sorted like the left rel) and then the remaining
> entries from the right side (also ordered). But overall not ordered.
>
> Best
> Julian
>
> From: Stamatis Zampetakis <za...@gmail.com>
> Date: Tuesday, 11. January 2022 at 09:43
> To: dev@calcite.apache.org <de...@calcite.apache.org>
> Subject: Re: Sort getting removed during optimization
> Hi Julian F,
>
> Quite a naive question but did you get wrong results from the given plan? A
> missing sort is not necessarily problematic.
>
> I hope I am not saying something stupid but I think there are cases where a
> full join algorithm can retain the order of some of its inputs.
>
> Best,
> Stamatis
>
> On Tue, Jan 11, 2022 at 8:30 AM Julian Feinauer <
> j.feinauer@pragmaticminds.de> wrote:
>
> > Hi Julian, Xiong,
> >
> > thanks for your fast replies!
> >
> > So first, the default Rules were registered:
> >
> > planner = new VolcanoPlanner();
> > RelOptUtil.registerDefaultRules(planner, false, true);
> >
> > And as traits I used:
> >
> > planner.addRelTraitDef(ConventionTraitDef.INSTANCE);
> > planner.addRelTraitDef(RelCollationTraitDef.INSTANCE);
> >
> > I digged a bit deeper and what was triggered was the `SortRemoveRule`.
> > If I disabled the Collation Trait this did no longer happen and all
> worked.
> >
> > I will later try to get a MWE done to reproduce this, if this is a bug.
> > Because the bug would then either be the Full Join producing a wrong
> > Collation or the SortRemoveRule investigating the input Collation wrong,
> or?
> >
> > But nonetheless, thank you very much!
> > Julian
> >
> > From: Julian Hyde <jh...@gmail.com>
> > Date: Tuesday, 11. January 2022 at 00:38
> > To: dev@calcite.apache.org <de...@calcite.apache.org>
> > Subject: Re: Sort getting removed during optimization
> > Is it possible that the Sort is being removed because some component
> knows
> > that the input is already sorted?
> >
> > In particular, if a relation has at most one row, it is always sorted.
> > Maybe the planner is deducing this via a some row-count metadata or
> > uniqueness constraint.
> >
> >
> > > On Jan 10, 2022, at 3:35 PM, xiong duan <no...@gmail.com> wrote:
> > >
> > > If I understand correctly, If we remove the BINDABLE_SORT_RULE, the
> > > result will throw an exception about the Plan transformation. So it
> > looks
> > > like a wrong rule's result. If you don't customize the rule, It is a
> bug,
> > > and please test this using Calcite's new version.
> >
>
Re: Sort getting removed during optimization
Posted by Vladimir Ozerov <pp...@gmail.com>.
Hi Julian,
Could you please check if your RelRoot.collation has the expected value? I
would expect it to be [$0 DESC]. If the value is correct, you may add it to
the "desired" trait set: "desired.replace(root.collation)". Hopefully, this
should help.
Regards,
Vladimir.
вт, 11 янв. 2022 г. в 16:47, Julian Feinauer <j....@pragmaticminds.de>:
> Hey Vladimir,
>
> when this issue appeared it was
>
> RelTraitSet desired = cluster.traitSet()
> .replace(BindableConvention.INSTANCE);
>
> RelNode expectedRoot = planner.changeTraits(root, desired);
> planner.setRoot(expectedRoot);
>
> And then
>
>
> RelNode exp = planner.findBestExp();
>
> So the root node had no sorting „requirement”.
> But from my understanding of the SortRemoveRule it does remove the Sort
> and at the same time adds the respective CollationTrait to the input node.
> In my case this was a LogicalProject.
> I have no idea how the Project itself then assures that the CollationTrait
> is fulfilled.
>
> By the way, is there a way to shot the Traits from a RelNode Tree? This
> could help to analyze this kind of situations?
>
> Thanks!
> Julian
>
>
>
> From: Vladimir Ozerov <pp...@gmail.com>
> Date: Tuesday, 11. January 2022 at 14:09
> To: dev@calcite.apache.org (dev@calcite.apache.org) <
> dev@calcite.apache.org>
> Subject: Re: Sort getting removed during optimization
> Hi Julian,
>
> When invoking the optimizer, you may provide the desired trait set of the
> top-level node. It might happen, that the specific collation is not
> requested from the optimizer, and hence the plan with a top-level Sort
> operator is not chosen. Could you please show how you invoke the planner?
>
> Regards,
> Vladimir.
>
> вт, 11 янв. 2022 г. в 12:44, Julian Feinauer <j.feinauer@pragmaticminds.de
> >:
>
> > Hey Stamatis,
> >
> > yes, thats why I looked it up at first… the results are wrong : )
> > So both tables for themselves are sorted but the Full Join is finally two
> > blocks. The Left Join (sorted like the left rel) and then the remaining
> > entries from the right side (also ordered). But overall not ordered.
> >
> > Best
> > Julian
> >
> > From: Stamatis Zampetakis <za...@gmail.com>
> > Date: Tuesday, 11. January 2022 at 09:43
> > To: dev@calcite.apache.org <de...@calcite.apache.org>
> > Subject: Re: Sort getting removed during optimization
> > Hi Julian F,
> >
> > Quite a naive question but did you get wrong results from the given
> plan? A
> > missing sort is not necessarily problematic.
> >
> > I hope I am not saying something stupid but I think there are cases
> where a
> > full join algorithm can retain the order of some of its inputs.
> >
> > Best,
> > Stamatis
> >
> > On Tue, Jan 11, 2022 at 8:30 AM Julian Feinauer <
> > j.feinauer@pragmaticminds.de> wrote:
> >
> > > Hi Julian, Xiong,
> > >
> > > thanks for your fast replies!
> > >
> > > So first, the default Rules were registered:
> > >
> > > planner = new VolcanoPlanner();
> > > RelOptUtil.registerDefaultRules(planner, false, true);
> > >
> > > And as traits I used:
> > >
> > > planner.addRelTraitDef(ConventionTraitDef.INSTANCE);
> > > planner.addRelTraitDef(RelCollationTraitDef.INSTANCE);
> > >
> > > I digged a bit deeper and what was triggered was the `SortRemoveRule`.
> > > If I disabled the Collation Trait this did no longer happen and all
> > worked.
> > >
> > > I will later try to get a MWE done to reproduce this, if this is a bug.
> > > Because the bug would then either be the Full Join producing a wrong
> > > Collation or the SortRemoveRule investigating the input Collation
> wrong,
> > or?
> > >
> > > But nonetheless, thank you very much!
> > > Julian
> > >
> > > From: Julian Hyde <jh...@gmail.com>
> > > Date: Tuesday, 11. January 2022 at 00:38
> > > To: dev@calcite.apache.org <de...@calcite.apache.org>
> > > Subject: Re: Sort getting removed during optimization
> > > Is it possible that the Sort is being removed because some component
> > knows
> > > that the input is already sorted?
> > >
> > > In particular, if a relation has at most one row, it is always sorted.
> > > Maybe the planner is deducing this via a some row-count metadata or
> > > uniqueness constraint.
> > >
> > >
> > > > On Jan 10, 2022, at 3:35 PM, xiong duan <no...@gmail.com> wrote:
> > > >
> > > > If I understand correctly, If we remove the BINDABLE_SORT_RULE, the
> > > > result will throw an exception about the Plan transformation. So it
> > > looks
> > > > like a wrong rule's result. If you don't customize the rule, It is a
> > bug,
> > > > and please test this using Calcite's new version.
> > >
> >
>
Re: Sort getting removed during optimization
Posted by Julian Feinauer <j....@pragmaticminds.de>.
Hey Vladimir,
when this issue appeared it was
RelTraitSet desired = cluster.traitSet()
.replace(BindableConvention.INSTANCE);
RelNode expectedRoot = planner.changeTraits(root, desired);
planner.setRoot(expectedRoot);
And then
RelNode exp = planner.findBestExp();
So the root node had no sorting „requirement”.
But from my understanding of the SortRemoveRule it does remove the Sort and at the same time adds the respective CollationTrait to the input node.
In my case this was a LogicalProject.
I have no idea how the Project itself then assures that the CollationTrait is fulfilled.
By the way, is there a way to shot the Traits from a RelNode Tree? This could help to analyze this kind of situations?
Thanks!
Julian
From: Vladimir Ozerov <pp...@gmail.com>
Date: Tuesday, 11. January 2022 at 14:09
To: dev@calcite.apache.org (dev@calcite.apache.org) <de...@calcite.apache.org>
Subject: Re: Sort getting removed during optimization
Hi Julian,
When invoking the optimizer, you may provide the desired trait set of the
top-level node. It might happen, that the specific collation is not
requested from the optimizer, and hence the plan with a top-level Sort
operator is not chosen. Could you please show how you invoke the planner?
Regards,
Vladimir.
вт, 11 янв. 2022 г. в 12:44, Julian Feinauer <j....@pragmaticminds.de>:
> Hey Stamatis,
>
> yes, thats why I looked it up at first… the results are wrong : )
> So both tables for themselves are sorted but the Full Join is finally two
> blocks. The Left Join (sorted like the left rel) and then the remaining
> entries from the right side (also ordered). But overall not ordered.
>
> Best
> Julian
>
> From: Stamatis Zampetakis <za...@gmail.com>
> Date: Tuesday, 11. January 2022 at 09:43
> To: dev@calcite.apache.org <de...@calcite.apache.org>
> Subject: Re: Sort getting removed during optimization
> Hi Julian F,
>
> Quite a naive question but did you get wrong results from the given plan? A
> missing sort is not necessarily problematic.
>
> I hope I am not saying something stupid but I think there are cases where a
> full join algorithm can retain the order of some of its inputs.
>
> Best,
> Stamatis
>
> On Tue, Jan 11, 2022 at 8:30 AM Julian Feinauer <
> j.feinauer@pragmaticminds.de> wrote:
>
> > Hi Julian, Xiong,
> >
> > thanks for your fast replies!
> >
> > So first, the default Rules were registered:
> >
> > planner = new VolcanoPlanner();
> > RelOptUtil.registerDefaultRules(planner, false, true);
> >
> > And as traits I used:
> >
> > planner.addRelTraitDef(ConventionTraitDef.INSTANCE);
> > planner.addRelTraitDef(RelCollationTraitDef.INSTANCE);
> >
> > I digged a bit deeper and what was triggered was the `SortRemoveRule`.
> > If I disabled the Collation Trait this did no longer happen and all
> worked.
> >
> > I will later try to get a MWE done to reproduce this, if this is a bug.
> > Because the bug would then either be the Full Join producing a wrong
> > Collation or the SortRemoveRule investigating the input Collation wrong,
> or?
> >
> > But nonetheless, thank you very much!
> > Julian
> >
> > From: Julian Hyde <jh...@gmail.com>
> > Date: Tuesday, 11. January 2022 at 00:38
> > To: dev@calcite.apache.org <de...@calcite.apache.org>
> > Subject: Re: Sort getting removed during optimization
> > Is it possible that the Sort is being removed because some component
> knows
> > that the input is already sorted?
> >
> > In particular, if a relation has at most one row, it is always sorted.
> > Maybe the planner is deducing this via a some row-count metadata or
> > uniqueness constraint.
> >
> >
> > > On Jan 10, 2022, at 3:35 PM, xiong duan <no...@gmail.com> wrote:
> > >
> > > If I understand correctly, If we remove the BINDABLE_SORT_RULE, the
> > > result will throw an exception about the Plan transformation. So it
> > looks
> > > like a wrong rule's result. If you don't customize the rule, It is a
> bug,
> > > and please test this using Calcite's new version.
> >
>
Re: Sort getting removed during optimization
Posted by Julian Feinauer <j....@pragmaticminds.de>.
Hey Stamatis,
yes, thats why I looked it up at first… the results are wrong : )
So both tables for themselves are sorted but the Full Join is finally two blocks. The Left Join (sorted like the left rel) and then the remaining entries from the right side (also ordered). But overall not ordered.
Best
Julian
From: Stamatis Zampetakis <za...@gmail.com>
Date: Tuesday, 11. January 2022 at 09:43
To: dev@calcite.apache.org <de...@calcite.apache.org>
Subject: Re: Sort getting removed during optimization
Hi Julian F,
Quite a naive question but did you get wrong results from the given plan? A
missing sort is not necessarily problematic.
I hope I am not saying something stupid but I think there are cases where a
full join algorithm can retain the order of some of its inputs.
Best,
Stamatis
On Tue, Jan 11, 2022 at 8:30 AM Julian Feinauer <
j.feinauer@pragmaticminds.de> wrote:
> Hi Julian, Xiong,
>
> thanks for your fast replies!
>
> So first, the default Rules were registered:
>
> planner = new VolcanoPlanner();
> RelOptUtil.registerDefaultRules(planner, false, true);
>
> And as traits I used:
>
> planner.addRelTraitDef(ConventionTraitDef.INSTANCE);
> planner.addRelTraitDef(RelCollationTraitDef.INSTANCE);
>
> I digged a bit deeper and what was triggered was the `SortRemoveRule`.
> If I disabled the Collation Trait this did no longer happen and all worked.
>
> I will later try to get a MWE done to reproduce this, if this is a bug.
> Because the bug would then either be the Full Join producing a wrong
> Collation or the SortRemoveRule investigating the input Collation wrong, or?
>
> But nonetheless, thank you very much!
> Julian
>
> From: Julian Hyde <jh...@gmail.com>
> Date: Tuesday, 11. January 2022 at 00:38
> To: dev@calcite.apache.org <de...@calcite.apache.org>
> Subject: Re: Sort getting removed during optimization
> Is it possible that the Sort is being removed because some component knows
> that the input is already sorted?
>
> In particular, if a relation has at most one row, it is always sorted.
> Maybe the planner is deducing this via a some row-count metadata or
> uniqueness constraint.
>
>
> > On Jan 10, 2022, at 3:35 PM, xiong duan <no...@gmail.com> wrote:
> >
> > If I understand correctly, If we remove the BINDABLE_SORT_RULE, the
> > result will throw an exception about the Plan transformation. So it
> looks
> > like a wrong rule's result. If you don't customize the rule, It is a bug,
> > and please test this using Calcite's new version.
>
Re: Sort getting removed during optimization
Posted by Stamatis Zampetakis <za...@gmail.com>.
Hi Julian F,
Quite a naive question but did you get wrong results from the given plan? A
missing sort is not necessarily problematic.
I hope I am not saying something stupid but I think there are cases where a
full join algorithm can retain the order of some of its inputs.
Best,
Stamatis
On Tue, Jan 11, 2022 at 8:30 AM Julian Feinauer <
j.feinauer@pragmaticminds.de> wrote:
> Hi Julian, Xiong,
>
> thanks for your fast replies!
>
> So first, the default Rules were registered:
>
> planner = new VolcanoPlanner();
> RelOptUtil.registerDefaultRules(planner, false, true);
>
> And as traits I used:
>
> planner.addRelTraitDef(ConventionTraitDef.INSTANCE);
> planner.addRelTraitDef(RelCollationTraitDef.INSTANCE);
>
> I digged a bit deeper and what was triggered was the `SortRemoveRule`.
> If I disabled the Collation Trait this did no longer happen and all worked.
>
> I will later try to get a MWE done to reproduce this, if this is a bug.
> Because the bug would then either be the Full Join producing a wrong
> Collation or the SortRemoveRule investigating the input Collation wrong, or?
>
> But nonetheless, thank you very much!
> Julian
>
> From: Julian Hyde <jh...@gmail.com>
> Date: Tuesday, 11. January 2022 at 00:38
> To: dev@calcite.apache.org <de...@calcite.apache.org>
> Subject: Re: Sort getting removed during optimization
> Is it possible that the Sort is being removed because some component knows
> that the input is already sorted?
>
> In particular, if a relation has at most one row, it is always sorted.
> Maybe the planner is deducing this via a some row-count metadata or
> uniqueness constraint.
>
>
> > On Jan 10, 2022, at 3:35 PM, xiong duan <no...@gmail.com> wrote:
> >
> > If I understand correctly, If we remove the BINDABLE_SORT_RULE, the
> > result will throw an exception about the Plan transformation. So it
> looks
> > like a wrong rule's result. If you don't customize the rule, It is a bug,
> > and please test this using Calcite's new version.
>
Re: Sort getting removed during optimization
Posted by Julian Feinauer <j....@pragmaticminds.de>.
Hi Julian, Xiong,
thanks for your fast replies!
So first, the default Rules were registered:
planner = new VolcanoPlanner();
RelOptUtil.registerDefaultRules(planner, false, true);
And as traits I used:
planner.addRelTraitDef(ConventionTraitDef.INSTANCE);
planner.addRelTraitDef(RelCollationTraitDef.INSTANCE);
I digged a bit deeper and what was triggered was the `SortRemoveRule`.
If I disabled the Collation Trait this did no longer happen and all worked.
I will later try to get a MWE done to reproduce this, if this is a bug.
Because the bug would then either be the Full Join producing a wrong Collation or the SortRemoveRule investigating the input Collation wrong, or?
But nonetheless, thank you very much!
Julian
From: Julian Hyde <jh...@gmail.com>
Date: Tuesday, 11. January 2022 at 00:38
To: dev@calcite.apache.org <de...@calcite.apache.org>
Subject: Re: Sort getting removed during optimization
Is it possible that the Sort is being removed because some component knows that the input is already sorted?
In particular, if a relation has at most one row, it is always sorted. Maybe the planner is deducing this via a some row-count metadata or uniqueness constraint.
> On Jan 10, 2022, at 3:35 PM, xiong duan <no...@gmail.com> wrote:
>
> If I understand correctly, If we remove the BINDABLE_SORT_RULE, the
> result will throw an exception about the Plan transformation. So it looks
> like a wrong rule's result. If you don't customize the rule, It is a bug,
> and please test this using Calcite's new version.
Re: Sort getting removed during optimization
Posted by Julian Hyde <jh...@gmail.com>.
Is it possible that the Sort is being removed because some component knows that the input is already sorted?
In particular, if a relation has at most one row, it is always sorted. Maybe the planner is deducing this via a some row-count metadata or uniqueness constraint.
> On Jan 10, 2022, at 3:35 PM, xiong duan <no...@gmail.com> wrote:
>
> If I understand correctly, If we remove the BINDABLE_SORT_RULE, the
> result will throw an exception about the Plan transformation. So it looks
> like a wrong rule's result. If you don't customize the rule, It is a bug,
> and please test this using Calcite's new version.
Re: Sort getting removed during optimization
Posted by xiong duan <no...@gmail.com>.
If I understand correctly, If we remove the BINDABLE_SORT_RULE, the
result will throw an exception about the Plan transformation. So it looks
like a wrong rule's result. If you don't customize the rule, It is a bug,
and please test this using Calcite's new version.
Re: Sort getting removed during optimization
Posted by Justin Swett <js...@google.com.INVALID>.
What rules have been added to the planner? Does it include BINDABLE_SORT_RULE?
On Mon, Jan 10, 2022 at 1:44 PM Julian Feinauer
<j....@pragmaticminds.de> wrote:
>
> Hi all,
>
> I just observed a Plan transformation that I don’t quite understand.
> The Logical Plan is:
>
> LogicalSort(sort0=[$0], dir0=[DESC])
> LogicalProject(time=[COALESCE($0, $2)], s1=[$1], s0=[$3])
> LogicalJoin(condition=[=($0, $2)], joinType=[full])
> LogicalTableScan(table=[[root, root.test.d0.s1]])
> LogicalTableScan(table=[[root, root.vehicle.d0.s0]])
>
> And the result of the Volcano Planner is
>
> BindableProject(time=[COALESCE($0, $2)], s1=[$1], s0=[$3])
> BindableJoin(condition=[=($0, $2)], joinType=[full])
> BindableTableScan(table=[[root, root.test.d0.s1]])
> BindableTableScan(table=[[root, root.vehicle.d0.s0]])
>
> I know wonder, why the LogicalSort is removed from the planner as the Output of the Join is NOT sorted (for a full join, for a left join it would be fine as the input table is sorted).
>
> Is there anything I am missing or is this probably a Bug?
>
> Thanks already!
> Julian