You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Enrico Olivelli <eo...@gmail.com> on 2019/09/24 09:40:54 UTC
Trivial query simplification
Hi,
I have a query like
SELECT * FROM MYTABLE WHERE v = 1 and v is null
I am expecting Calcite to simplify it to
SELECT * FROM MYTABLE WHERE v = 1
but this does not happen.
Is any rule I should enable in order to make it happen ?
This is the configuration of my Volcano planner:
final FrameworkConfig config = Frameworks.newConfigBuilder()
.parserConfig(....)
.defaultSchema(...)
.traitDefs(....)
.programs(Programs.ofRules(Programs.RULE_SET))
.build();
Best regards
Enrico
Re: Trivial query simplification
Posted by Enrico Olivelli <eo...@gmail.com>.
Il mar 24 set 2019, 11:50 Feng Zhu <we...@gmail.com> ha scritto:
> Hi, Enrico,
> I'm a little confused about your expectations. Could you clarify it?
> Moreover, is it right for the below simplification (do you mean v is not
> null)?
> (v=1 and v is null) -> v=1
> (do you mean v is not null?)
>
Yes, sorry
Enrico
>
> Best regards
>
> Enrico Olivelli <eo...@gmail.com> 于2019年9月24日周二 下午5:41写道：
>
> > Hi,
> > I have a query like
> > SELECT * FROM MYTABLE WHERE v = 1 and v is null
> >
> > I am expecting Calcite to simplify it to
> > SELECT * FROM MYTABLE WHERE v = 1
> >
> > but this does not happen.
> >
> > Is any rule I should enable in order to make it happen ?
> >
> > This is the configuration of my Volcano planner:
> >
> > final FrameworkConfig config = Frameworks.newConfigBuilder()
> > .parserConfig(....)
> > .defaultSchema(...)
> > .traitDefs(....)
> > .programs(Programs.ofRules(Programs.RULE_SET))
> > .build();
> >
> > Best regards
> > Enrico
> >
>
Re: Trivial query simplification
Posted by Enrico Olivelli <eo...@gmail.com>.
Il mer 25 set 2019, 23:38 Stamatis Zampetakis <za...@gmail.com> ha
scritto:
> Hi Enrico,
>
> The ReduceExpressionsRule.FILTER_INSTANCE is using the simplifier so if it
> works
> correctly I don't think there is anything more to be done.
>
Fine.
Cheers
Enrico
>
> Best,
> Stamatis
>
> On Wed, Sep 25, 2019 at 5:31 PM Enrico Olivelli <eo...@gmail.com>
> wrote:
>
> > Thank you for your feedback.
> >
> > Actually ReduceExpressionsRule.FILTER_INSTANCE fixes the problem.
> >
> > RelOptPlanner optPlanner = cluster.getPlanner();
> > optPlanner.addRule(ReduceExpressionsRule.FILTER_INSTANCE);
> >
> > This code I had was wrong:
> > final FrameworkConfig config = Frameworks.newConfigBuilder()
> > .parserConfig(SQL_PARSER_CONFIG)
> > .defaultSchema(subSchema)
> > .traitDefs(TRAITS)
> > .programs(Programs.ofRules(Programs.RULE_SET))
> > .build();
> >
> > as the 'programs' property of FrameworkConfig is applied only if you are
> > using Planner#transform .
> >
> > @Julian do I have to create a JIRA case for RexSimplify ? Honestly I
> don't
> > have enough knowledge to explain the problem and create a meaning full
> > issue.
> >
> > Is it work to add ReduceExpressionsRule.FILTER_INSTANCE to the default
> > rules ?
> >
> > As far as I can see the "default rules" are in CalcitePrepareImpl, is
> that
> > correct?
> >
> >
> > Cheers
> > Enrico
> >
> >
> > Il giorno mar 24 set 2019 alle ore 20:01 Julian Hyde <jh...@apache.org>
> ha
> > scritto:
> >
> > > A few thoughts on this.
> > >
> > >
> > > 0. I am surprised that this simplification does not happen, and I think
> > we
> > > should do it. Specifically, "filter(x = 0 and x is not null)" should
> > > simplify to “filter(x = 0)”.
> > >
> > > 1. Enrico, please log a JIRA case now, and transcribe the key points of
> > > this discussion into the JIRA case when the discussion has finished.
> > >
> > > 2. The most obvious way to achieve this is via simplification, i.e.
> > > RexSimplify, which occurs when building expressions via RelBuilder. It
> > does
> > > not require planner rules.
> > >
> > > 3. An algorithm to achieve this would be to gather the implied
> predicates
> > > as we go through a conjunction. After generating the condition “x = 0”
> we
> > > arrive at “x is not null”. We know that “x = 0” holds, therefore we
> could
> > > also deduce that “x is not null” holds (we could also deduce other
> > > conditions, such as “x < 100” holds).
> > >
> > > 4. Another way to achieve this is via
> > > ReduceExpressionsRule.FILTER_INSTANCE. The algorithm would be as
> follows.
> > > First note the condition “x = 0” and therefore constant-reduce x to 0
> in
> > > the expression “0 is not null”. This algorithm has similar problems to
> > the
> > > algorithm in 2 - it passes along the conjunctive predicates in strict
> > order
> > > and therefore only works if they are in a particular order. Also, as a
> > > planner rule, this algorithm is more expensive, so would not be applied
> > as
> > > early/often as the RexSimplify implementation.
> > >
> > > 5. We could also simplify “filter(x is not null and x = 0)” to
> “filter(x
> > =
> > > 0)”, and people would reasonably expect that we would. But that is a
> more
> > > complex algorithm because it would require, for instance, re-ordering
> > > predicates. In the past, we have discussed re-ordering predicates as
> part
> > > of simplification; I am cautious about doing it because it would
> affect a
> > > lot of existing plans (and tests). There is no perfect order for
> > > predicates, so we might come back in 6 months and want to change the
> > order
> > > again. Better to sort them during simplification but then spit them out
> > in
> > > the original order.
> > >
> > > Julian
> > >
> > >
> > >
> > > > On Sep 24, 2019, at 8:34 AM, Enrico Olivelli <eo...@gmail.com>
> > > wrote:
> > > >
> > > > Il giorno mar 24 set 2019 alle ore 13:45 XING JIN <
> > > jinxing.corey@gmail.com <ma...@gmail.com>>
> > > > ha scritto:
> > > >
> > > >> "v = 1 and v is null"
> > > >> cannot be simplified to "v = 1" not matter v is nullable or not
> > nullable
> > > >>
> > > >> If you really mean that "v is not null", I made below test case in
> > > >> RelOptRulesTest.java for illustration:
> > > >>
> > > >>
> > > >> // mgr is nullable
> > > >> @Test public void testDEV() throws Exception {
> > > >> HepProgram program = new HepProgramBuilder()
> > > >> .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
> > > >> .build();
> > > >>
> > > >> final String sql = "select deptno"
> > > >> + " from emp"
> > > >> + " where mgr = 10 and mgr is not null";
> > > >> checkPlanning(new HepPlanner(program), sql);
> > > >> }
> > > >>
> > > >> The plan is
> > > >> LogicalProject(DEPTNO=[$7])
> > > >> LogicalFilter(condition=[=($3, 10)])
> > > >> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> > > >>
> > > >> Enrico ~ you may try ReduceExpressionsRule.FILTER_INSTANCE
> > > >>
> > > >
> > > >
> > > > @XING JIN
> > > > thank you.
> > > > I am with Calcite 1.19 and VolcanoPlanner
> > > >
> > > > Original query:
> > > >
> > > > select * from pippo where n1 is null AND n1 is not null
> > > >
> > > > Logical plan:
> > > >
> > > > LogicalFilter(condition=[AND(IS NULL($1), IS NOT NULL($1))]):
> rowcount
> > =
> > > > 1.35, cumulative cost = {7.35 rows, 13.0 cpu, 0.0 io}, id = 48
> > > >
> > > > EnumerableTableScan(table=[[herd, pippo]]): rowcount = 6.0,
> > cumulative
> > > > cost = {6.0 rows, 7.0 cpu, 0.0 io}, id = 47
> > > >
> > > > Final Plan:
> > > >
> > > > BindableTableScan(table=[[herd, pippo]], filters=[[AND(IS NULL($1),
> IS
> > > NOT
> > > > NULL($1))]]): rowcount = 6.0, cumulative cost = {0.03 rows, 0.035
> cpu,
> > > 0.0
> > > > io}, id = 59
> > > >
> > > >
> > > > It seems that ReduceExpressionsRule.FILTER_INSTANCE does not have any
> > > > effect.
> > > > May it be a problem of 1.19 or VolcanoPlanner ?
> > > >
> > > > This is my "program" now:
> > > >
> > > > public static final ImmutableSet<RelOptRule> RULE_SET =
> > > > ImmutableSet.of(
> > > > EnumerableRules.ENUMERABLE_JOIN_RULE,
> > > > EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE,
> > > > EnumerableRules.ENUMERABLE_SEMI_JOIN_RULE,
> > > > EnumerableRules.ENUMERABLE_CORRELATE_RULE,
> > > > EnumerableRules.ENUMERABLE_PROJECT_RULE,
> > > > EnumerableRules.ENUMERABLE_FILTER_RULE,
> > > > EnumerableRules.ENUMERABLE_AGGREGATE_RULE,
> > > > EnumerableRules.ENUMERABLE_SORT_RULE,
> > > > EnumerableRules.ENUMERABLE_LIMIT_RULE,
> > > > EnumerableRules.ENUMERABLE_UNION_RULE,
> > > > EnumerableRules.ENUMERABLE_INTERSECT_RULE,
> > > > EnumerableRules.ENUMERABLE_MINUS_RULE,
> > > > EnumerableRules.ENUMERABLE_TABLE_MODIFICATION_RULE,
> > > > EnumerableRules.ENUMERABLE_VALUES_RULE,
> > > > EnumerableRules.ENUMERABLE_WINDOW_RULE,
> > > > SemiJoinRule.PROJECT,
> > > > SemiJoinRule.JOIN,
> > > > TableScanRule.INSTANCE,
> > > > CalciteSystemProperty.COMMUTE.value()
> > > > ? JoinAssociateRule.INSTANCE
> > > > : ProjectMergeRule.INSTANCE,
> > > > AggregateStarTableRule.INSTANCE,
> > > > AggregateStarTableRule.INSTANCE2,
> > > > FilterTableScanRule.INSTANCE,
> > > > FilterProjectTransposeRule.INSTANCE,
> > > > FilterJoinRule.FILTER_ON_JOIN,
> > > > AggregateExpandDistinctAggregatesRule.INSTANCE,
> > > > AggregateReduceFunctionsRule.INSTANCE,
> > > > FilterAggregateTransposeRule.INSTANCE,
> > > > JoinCommuteRule.INSTANCE,
> > > > JoinPushThroughJoinRule.RIGHT,
> > > > JoinPushThroughJoinRule.LEFT,
> > > > SortProjectTransposeRule.INSTANCE,
> > > > ReduceExpressionsRule.FILTER_INSTANCE); <------ HERE
> > > >
> > > > Enrico
> > > >
> > > >
> > > >
> > > >>
> > > >> Feng Zhu <we...@gmail.com> 于2019年9月24日周二 下午5:50写道：
> > > >>
> > > >>> Hi, Enrico,
> > > >>> I'm a little confused about your expectations. Could you clarify
> it?
> > > >>> Moreover, is it right for the below simplification (do you mean v
> is
> > > not
> > > >>> null)?
> > > >>> (v=1 and v is null) -> v=1
> > > >>> (do you mean v is not null?)
> > > >>>
> > > >>> Best regards
> > > >>>
> > > >>> Enrico Olivelli <eo...@gmail.com> 于2019年9月24日周二 下午5:41写道：
> > > >>>
> > > >>>> Hi,
> > > >>>> I have a query like
> > > >>>> SELECT * FROM MYTABLE WHERE v = 1 and v is null
> > > >>>>
> > > >>>> I am expecting Calcite to simplify it to
> > > >>>> SELECT * FROM MYTABLE WHERE v = 1
> > > >>>>
> > > >>>> but this does not happen.
> > > >>>>
> > > >>>> Is any rule I should enable in order to make it happen ?
> > > >>>>
> > > >>>> This is the configuration of my Volcano planner:
> > > >>>>
> > > >>>> final FrameworkConfig config = Frameworks.newConfigBuilder()
> > > >>>> .parserConfig(....)
> > > >>>> .defaultSchema(...)
> > > >>>> .traitDefs(....)
> > > >>>> .programs(Programs.ofRules(Programs.RULE_SET))
> > > >>>> .build();
> > > >>>>
> > > >>>> Best regards
> > > >>>> Enrico
> > >
> > >
> >
>
Re: Trivial query simplification
Posted by Stamatis Zampetakis <za...@gmail.com>.
Hi Enrico,
The ReduceExpressionsRule.FILTER_INSTANCE is using the simplifier so if it
works
correctly I don't think there is anything more to be done.
Best,
Stamatis
On Wed, Sep 25, 2019 at 5:31 PM Enrico Olivelli <eo...@gmail.com> wrote:
> Thank you for your feedback.
>
> Actually ReduceExpressionsRule.FILTER_INSTANCE fixes the problem.
>
> RelOptPlanner optPlanner = cluster.getPlanner();
> optPlanner.addRule(ReduceExpressionsRule.FILTER_INSTANCE);
>
> This code I had was wrong:
> final FrameworkConfig config = Frameworks.newConfigBuilder()
> .parserConfig(SQL_PARSER_CONFIG)
> .defaultSchema(subSchema)
> .traitDefs(TRAITS)
> .programs(Programs.ofRules(Programs.RULE_SET))
> .build();
>
> as the 'programs' property of FrameworkConfig is applied only if you are
> using Planner#transform .
>
> @Julian do I have to create a JIRA case for RexSimplify ? Honestly I don't
> have enough knowledge to explain the problem and create a meaning full
> issue.
>
> Is it work to add ReduceExpressionsRule.FILTER_INSTANCE to the default
> rules ?
>
> As far as I can see the "default rules" are in CalcitePrepareImpl, is that
> correct?
>
>
> Cheers
> Enrico
>
>
> Il giorno mar 24 set 2019 alle ore 20:01 Julian Hyde <jh...@apache.org> ha
> scritto:
>
> > A few thoughts on this.
> >
> >
> > 0. I am surprised that this simplification does not happen, and I think
> we
> > should do it. Specifically, "filter(x = 0 and x is not null)" should
> > simplify to “filter(x = 0)”.
> >
> > 1. Enrico, please log a JIRA case now, and transcribe the key points of
> > this discussion into the JIRA case when the discussion has finished.
> >
> > 2. The most obvious way to achieve this is via simplification, i.e.
> > RexSimplify, which occurs when building expressions via RelBuilder. It
> does
> > not require planner rules.
> >
> > 3. An algorithm to achieve this would be to gather the implied predicates
> > as we go through a conjunction. After generating the condition “x = 0” we
> > arrive at “x is not null”. We know that “x = 0” holds, therefore we could
> > also deduce that “x is not null” holds (we could also deduce other
> > conditions, such as “x < 100” holds).
> >
> > 4. Another way to achieve this is via
> > ReduceExpressionsRule.FILTER_INSTANCE. The algorithm would be as follows.
> > First note the condition “x = 0” and therefore constant-reduce x to 0 in
> > the expression “0 is not null”. This algorithm has similar problems to
> the
> > algorithm in 2 - it passes along the conjunctive predicates in strict
> order
> > and therefore only works if they are in a particular order. Also, as a
> > planner rule, this algorithm is more expensive, so would not be applied
> as
> > early/often as the RexSimplify implementation.
> >
> > 5. We could also simplify “filter(x is not null and x = 0)” to “filter(x
> =
> > 0)”, and people would reasonably expect that we would. But that is a more
> > complex algorithm because it would require, for instance, re-ordering
> > predicates. In the past, we have discussed re-ordering predicates as part
> > of simplification; I am cautious about doing it because it would affect a
> > lot of existing plans (and tests). There is no perfect order for
> > predicates, so we might come back in 6 months and want to change the
> order
> > again. Better to sort them during simplification but then spit them out
> in
> > the original order.
> >
> > Julian
> >
> >
> >
> > > On Sep 24, 2019, at 8:34 AM, Enrico Olivelli <eo...@gmail.com>
> > wrote:
> > >
> > > Il giorno mar 24 set 2019 alle ore 13:45 XING JIN <
> > jinxing.corey@gmail.com <ma...@gmail.com>>
> > > ha scritto:
> > >
> > >> "v = 1 and v is null"
> > >> cannot be simplified to "v = 1" not matter v is nullable or not
> nullable
> > >>
> > >> If you really mean that "v is not null", I made below test case in
> > >> RelOptRulesTest.java for illustration:
> > >>
> > >>
> > >> // mgr is nullable
> > >> @Test public void testDEV() throws Exception {
> > >> HepProgram program = new HepProgramBuilder()
> > >> .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
> > >> .build();
> > >>
> > >> final String sql = "select deptno"
> > >> + " from emp"
> > >> + " where mgr = 10 and mgr is not null";
> > >> checkPlanning(new HepPlanner(program), sql);
> > >> }
> > >>
> > >> The plan is
> > >> LogicalProject(DEPTNO=[$7])
> > >> LogicalFilter(condition=[=($3, 10)])
> > >> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> > >>
> > >> Enrico ~ you may try ReduceExpressionsRule.FILTER_INSTANCE
> > >>
> > >
> > >
> > > @XING JIN
> > > thank you.
> > > I am with Calcite 1.19 and VolcanoPlanner
> > >
> > > Original query:
> > >
> > > select * from pippo where n1 is null AND n1 is not null
> > >
> > > Logical plan:
> > >
> > > LogicalFilter(condition=[AND(IS NULL($1), IS NOT NULL($1))]): rowcount
> =
> > > 1.35, cumulative cost = {7.35 rows, 13.0 cpu, 0.0 io}, id = 48
> > >
> > > EnumerableTableScan(table=[[herd, pippo]]): rowcount = 6.0,
> cumulative
> > > cost = {6.0 rows, 7.0 cpu, 0.0 io}, id = 47
> > >
> > > Final Plan:
> > >
> > > BindableTableScan(table=[[herd, pippo]], filters=[[AND(IS NULL($1), IS
> > NOT
> > > NULL($1))]]): rowcount = 6.0, cumulative cost = {0.03 rows, 0.035 cpu,
> > 0.0
> > > io}, id = 59
> > >
> > >
> > > It seems that ReduceExpressionsRule.FILTER_INSTANCE does not have any
> > > effect.
> > > May it be a problem of 1.19 or VolcanoPlanner ?
> > >
> > > This is my "program" now:
> > >
> > > public static final ImmutableSet<RelOptRule> RULE_SET =
> > > ImmutableSet.of(
> > > EnumerableRules.ENUMERABLE_JOIN_RULE,
> > > EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE,
> > > EnumerableRules.ENUMERABLE_SEMI_JOIN_RULE,
> > > EnumerableRules.ENUMERABLE_CORRELATE_RULE,
> > > EnumerableRules.ENUMERABLE_PROJECT_RULE,
> > > EnumerableRules.ENUMERABLE_FILTER_RULE,
> > > EnumerableRules.ENUMERABLE_AGGREGATE_RULE,
> > > EnumerableRules.ENUMERABLE_SORT_RULE,
> > > EnumerableRules.ENUMERABLE_LIMIT_RULE,
> > > EnumerableRules.ENUMERABLE_UNION_RULE,
> > > EnumerableRules.ENUMERABLE_INTERSECT_RULE,
> > > EnumerableRules.ENUMERABLE_MINUS_RULE,
> > > EnumerableRules.ENUMERABLE_TABLE_MODIFICATION_RULE,
> > > EnumerableRules.ENUMERABLE_VALUES_RULE,
> > > EnumerableRules.ENUMERABLE_WINDOW_RULE,
> > > SemiJoinRule.PROJECT,
> > > SemiJoinRule.JOIN,
> > > TableScanRule.INSTANCE,
> > > CalciteSystemProperty.COMMUTE.value()
> > > ? JoinAssociateRule.INSTANCE
> > > : ProjectMergeRule.INSTANCE,
> > > AggregateStarTableRule.INSTANCE,
> > > AggregateStarTableRule.INSTANCE2,
> > > FilterTableScanRule.INSTANCE,
> > > FilterProjectTransposeRule.INSTANCE,
> > > FilterJoinRule.FILTER_ON_JOIN,
> > > AggregateExpandDistinctAggregatesRule.INSTANCE,
> > > AggregateReduceFunctionsRule.INSTANCE,
> > > FilterAggregateTransposeRule.INSTANCE,
> > > JoinCommuteRule.INSTANCE,
> > > JoinPushThroughJoinRule.RIGHT,
> > > JoinPushThroughJoinRule.LEFT,
> > > SortProjectTransposeRule.INSTANCE,
> > > ReduceExpressionsRule.FILTER_INSTANCE); <------ HERE
> > >
> > > Enrico
> > >
> > >
> > >
> > >>
> > >> Feng Zhu <we...@gmail.com> 于2019年9月24日周二 下午5:50写道：
> > >>
> > >>> Hi, Enrico,
> > >>> I'm a little confused about your expectations. Could you clarify it?
> > >>> Moreover, is it right for the below simplification (do you mean v is
> > not
> > >>> null)?
> > >>> (v=1 and v is null) -> v=1
> > >>> (do you mean v is not null?)
> > >>>
> > >>> Best regards
> > >>>
> > >>> Enrico Olivelli <eo...@gmail.com> 于2019年9月24日周二 下午5:41写道：
> > >>>
> > >>>> Hi,
> > >>>> I have a query like
> > >>>> SELECT * FROM MYTABLE WHERE v = 1 and v is null
> > >>>>
> > >>>> I am expecting Calcite to simplify it to
> > >>>> SELECT * FROM MYTABLE WHERE v = 1
> > >>>>
> > >>>> but this does not happen.
> > >>>>
> > >>>> Is any rule I should enable in order to make it happen ?
> > >>>>
> > >>>> This is the configuration of my Volcano planner:
> > >>>>
> > >>>> final FrameworkConfig config = Frameworks.newConfigBuilder()
> > >>>> .parserConfig(....)
> > >>>> .defaultSchema(...)
> > >>>> .traitDefs(....)
> > >>>> .programs(Programs.ofRules(Programs.RULE_SET))
> > >>>> .build();
> > >>>>
> > >>>> Best regards
> > >>>> Enrico
> >
> >
>
Re: Trivial query simplification
Posted by Enrico Olivelli <eo...@gmail.com>.
Thank you for your feedback.
Actually ReduceExpressionsRule.FILTER_INSTANCE fixes the problem.
RelOptPlanner optPlanner = cluster.getPlanner();
optPlanner.addRule(ReduceExpressionsRule.FILTER_INSTANCE);
This code I had was wrong:
final FrameworkConfig config = Frameworks.newConfigBuilder()
.parserConfig(SQL_PARSER_CONFIG)
.defaultSchema(subSchema)
.traitDefs(TRAITS)
.programs(Programs.ofRules(Programs.RULE_SET))
.build();
as the 'programs' property of FrameworkConfig is applied only if you are
using Planner#transform .
@Julian do I have to create a JIRA case for RexSimplify ? Honestly I don't
have enough knowledge to explain the problem and create a meaning full
issue.
Is it work to add ReduceExpressionsRule.FILTER_INSTANCE to the default
rules ?
As far as I can see the "default rules" are in CalcitePrepareImpl, is that
correct?
Cheers
Enrico
Il giorno mar 24 set 2019 alle ore 20:01 Julian Hyde <jh...@apache.org> ha
scritto:
> A few thoughts on this.
>
>
> 0. I am surprised that this simplification does not happen, and I think we
> should do it. Specifically, "filter(x = 0 and x is not null)" should
> simplify to “filter(x = 0)”.
>
> 1. Enrico, please log a JIRA case now, and transcribe the key points of
> this discussion into the JIRA case when the discussion has finished.
>
> 2. The most obvious way to achieve this is via simplification, i.e.
> RexSimplify, which occurs when building expressions via RelBuilder. It does
> not require planner rules.
>
> 3. An algorithm to achieve this would be to gather the implied predicates
> as we go through a conjunction. After generating the condition “x = 0” we
> arrive at “x is not null”. We know that “x = 0” holds, therefore we could
> also deduce that “x is not null” holds (we could also deduce other
> conditions, such as “x < 100” holds).
>
> 4. Another way to achieve this is via
> ReduceExpressionsRule.FILTER_INSTANCE. The algorithm would be as follows.
> First note the condition “x = 0” and therefore constant-reduce x to 0 in
> the expression “0 is not null”. This algorithm has similar problems to the
> algorithm in 2 - it passes along the conjunctive predicates in strict order
> and therefore only works if they are in a particular order. Also, as a
> planner rule, this algorithm is more expensive, so would not be applied as
> early/often as the RexSimplify implementation.
>
> 5. We could also simplify “filter(x is not null and x = 0)” to “filter(x =
> 0)”, and people would reasonably expect that we would. But that is a more
> complex algorithm because it would require, for instance, re-ordering
> predicates. In the past, we have discussed re-ordering predicates as part
> of simplification; I am cautious about doing it because it would affect a
> lot of existing plans (and tests). There is no perfect order for
> predicates, so we might come back in 6 months and want to change the order
> again. Better to sort them during simplification but then spit them out in
> the original order.
>
> Julian
>
>
>
> > On Sep 24, 2019, at 8:34 AM, Enrico Olivelli <eo...@gmail.com>
> wrote:
> >
> > Il giorno mar 24 set 2019 alle ore 13:45 XING JIN <
> jinxing.corey@gmail.com <ma...@gmail.com>>
> > ha scritto:
> >
> >> "v = 1 and v is null"
> >> cannot be simplified to "v = 1" not matter v is nullable or not nullable
> >>
> >> If you really mean that "v is not null", I made below test case in
> >> RelOptRulesTest.java for illustration:
> >>
> >>
> >> // mgr is nullable
> >> @Test public void testDEV() throws Exception {
> >> HepProgram program = new HepProgramBuilder()
> >> .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
> >> .build();
> >>
> >> final String sql = "select deptno"
> >> + " from emp"
> >> + " where mgr = 10 and mgr is not null";
> >> checkPlanning(new HepPlanner(program), sql);
> >> }
> >>
> >> The plan is
> >> LogicalProject(DEPTNO=[$7])
> >> LogicalFilter(condition=[=($3, 10)])
> >> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> >>
> >> Enrico ~ you may try ReduceExpressionsRule.FILTER_INSTANCE
> >>
> >
> >
> > @XING JIN
> > thank you.
> > I am with Calcite 1.19 and VolcanoPlanner
> >
> > Original query:
> >
> > select * from pippo where n1 is null AND n1 is not null
> >
> > Logical plan:
> >
> > LogicalFilter(condition=[AND(IS NULL($1), IS NOT NULL($1))]): rowcount =
> > 1.35, cumulative cost = {7.35 rows, 13.0 cpu, 0.0 io}, id = 48
> >
> > EnumerableTableScan(table=[[herd, pippo]]): rowcount = 6.0, cumulative
> > cost = {6.0 rows, 7.0 cpu, 0.0 io}, id = 47
> >
> > Final Plan:
> >
> > BindableTableScan(table=[[herd, pippo]], filters=[[AND(IS NULL($1), IS
> NOT
> > NULL($1))]]): rowcount = 6.0, cumulative cost = {0.03 rows, 0.035 cpu,
> 0.0
> > io}, id = 59
> >
> >
> > It seems that ReduceExpressionsRule.FILTER_INSTANCE does not have any
> > effect.
> > May it be a problem of 1.19 or VolcanoPlanner ?
> >
> > This is my "program" now:
> >
> > public static final ImmutableSet<RelOptRule> RULE_SET =
> > ImmutableSet.of(
> > EnumerableRules.ENUMERABLE_JOIN_RULE,
> > EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE,
> > EnumerableRules.ENUMERABLE_SEMI_JOIN_RULE,
> > EnumerableRules.ENUMERABLE_CORRELATE_RULE,
> > EnumerableRules.ENUMERABLE_PROJECT_RULE,
> > EnumerableRules.ENUMERABLE_FILTER_RULE,
> > EnumerableRules.ENUMERABLE_AGGREGATE_RULE,
> > EnumerableRules.ENUMERABLE_SORT_RULE,
> > EnumerableRules.ENUMERABLE_LIMIT_RULE,
> > EnumerableRules.ENUMERABLE_UNION_RULE,
> > EnumerableRules.ENUMERABLE_INTERSECT_RULE,
> > EnumerableRules.ENUMERABLE_MINUS_RULE,
> > EnumerableRules.ENUMERABLE_TABLE_MODIFICATION_RULE,
> > EnumerableRules.ENUMERABLE_VALUES_RULE,
> > EnumerableRules.ENUMERABLE_WINDOW_RULE,
> > SemiJoinRule.PROJECT,
> > SemiJoinRule.JOIN,
> > TableScanRule.INSTANCE,
> > CalciteSystemProperty.COMMUTE.value()
> > ? JoinAssociateRule.INSTANCE
> > : ProjectMergeRule.INSTANCE,
> > AggregateStarTableRule.INSTANCE,
> > AggregateStarTableRule.INSTANCE2,
> > FilterTableScanRule.INSTANCE,
> > FilterProjectTransposeRule.INSTANCE,
> > FilterJoinRule.FILTER_ON_JOIN,
> > AggregateExpandDistinctAggregatesRule.INSTANCE,
> > AggregateReduceFunctionsRule.INSTANCE,
> > FilterAggregateTransposeRule.INSTANCE,
> > JoinCommuteRule.INSTANCE,
> > JoinPushThroughJoinRule.RIGHT,
> > JoinPushThroughJoinRule.LEFT,
> > SortProjectTransposeRule.INSTANCE,
> > ReduceExpressionsRule.FILTER_INSTANCE); <------ HERE
> >
> > Enrico
> >
> >
> >
> >>
> >> Feng Zhu <we...@gmail.com> 于2019年9月24日周二 下午5:50写道：
> >>
> >>> Hi, Enrico,
> >>> I'm a little confused about your expectations. Could you clarify it?
> >>> Moreover, is it right for the below simplification (do you mean v is
> not
> >>> null)?
> >>> (v=1 and v is null) -> v=1
> >>> (do you mean v is not null?)
> >>>
> >>> Best regards
> >>>
> >>> Enrico Olivelli <eo...@gmail.com> 于2019年9月24日周二 下午5:41写道：
> >>>
> >>>> Hi,
> >>>> I have a query like
> >>>> SELECT * FROM MYTABLE WHERE v = 1 and v is null
> >>>>
> >>>> I am expecting Calcite to simplify it to
> >>>> SELECT * FROM MYTABLE WHERE v = 1
> >>>>
> >>>> but this does not happen.
> >>>>
> >>>> Is any rule I should enable in order to make it happen ?
> >>>>
> >>>> This is the configuration of my Volcano planner:
> >>>>
> >>>> final FrameworkConfig config = Frameworks.newConfigBuilder()
> >>>> .parserConfig(....)
> >>>> .defaultSchema(...)
> >>>> .traitDefs(....)
> >>>> .programs(Programs.ofRules(Programs.RULE_SET))
> >>>> .build();
> >>>>
> >>>> Best regards
> >>>> Enrico
>
>
Re: Trivial query simplification
Posted by Julian Hyde <jh...@apache.org>.
A few thoughts on this.
0. I am surprised that this simplification does not happen, and I think we should do it. Specifically, "filter(x = 0 and x is not null)" should simplify to “filter(x = 0)”.
1. Enrico, please log a JIRA case now, and transcribe the key points of this discussion into the JIRA case when the discussion has finished.
2. The most obvious way to achieve this is via simplification, i.e. RexSimplify, which occurs when building expressions via RelBuilder. It does not require planner rules.
3. An algorithm to achieve this would be to gather the implied predicates as we go through a conjunction. After generating the condition “x = 0” we arrive at “x is not null”. We know that “x = 0” holds, therefore we could also deduce that “x is not null” holds (we could also deduce other conditions, such as “x < 100” holds).
4. Another way to achieve this is via ReduceExpressionsRule.FILTER_INSTANCE. The algorithm would be as follows. First note the condition “x = 0” and therefore constant-reduce x to 0 in the expression “0 is not null”. This algorithm has similar problems to the algorithm in 2 - it passes along the conjunctive predicates in strict order and therefore only works if they are in a particular order. Also, as a planner rule, this algorithm is more expensive, so would not be applied as early/often as the RexSimplify implementation.
5. We could also simplify “filter(x is not null and x = 0)” to “filter(x = 0)”, and people would reasonably expect that we would. But that is a more complex algorithm because it would require, for instance, re-ordering predicates. In the past, we have discussed re-ordering predicates as part of simplification; I am cautious about doing it because it would affect a lot of existing plans (and tests). There is no perfect order for predicates, so we might come back in 6 months and want to change the order again. Better to sort them during simplification but then spit them out in the original order.
Julian
> On Sep 24, 2019, at 8:34 AM, Enrico Olivelli <eo...@gmail.com> wrote:
>
> Il giorno mar 24 set 2019 alle ore 13:45 XING JIN <jinxing.corey@gmail.com <ma...@gmail.com>>
> ha scritto:
>
>> "v = 1 and v is null"
>> cannot be simplified to "v = 1" not matter v is nullable or not nullable
>>
>> If you really mean that "v is not null", I made below test case in
>> RelOptRulesTest.java for illustration:
>>
>>
>> // mgr is nullable
>> @Test public void testDEV() throws Exception {
>> HepProgram program = new HepProgramBuilder()
>> .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
>> .build();
>>
>> final String sql = "select deptno"
>> + " from emp"
>> + " where mgr = 10 and mgr is not null";
>> checkPlanning(new HepPlanner(program), sql);
>> }
>>
>> The plan is
>> LogicalProject(DEPTNO=[$7])
>> LogicalFilter(condition=[=($3, 10)])
>> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>>
>> Enrico ~ you may try ReduceExpressionsRule.FILTER_INSTANCE
>>
>
>
> @XING JIN
> thank you.
> I am with Calcite 1.19 and VolcanoPlanner
>
> Original query:
>
> select * from pippo where n1 is null AND n1 is not null
>
> Logical plan:
>
> LogicalFilter(condition=[AND(IS NULL($1), IS NOT NULL($1))]): rowcount =
> 1.35, cumulative cost = {7.35 rows, 13.0 cpu, 0.0 io}, id = 48
>
> EnumerableTableScan(table=[[herd, pippo]]): rowcount = 6.0, cumulative
> cost = {6.0 rows, 7.0 cpu, 0.0 io}, id = 47
>
> Final Plan:
>
> BindableTableScan(table=[[herd, pippo]], filters=[[AND(IS NULL($1), IS NOT
> NULL($1))]]): rowcount = 6.0, cumulative cost = {0.03 rows, 0.035 cpu, 0.0
> io}, id = 59
>
>
> It seems that ReduceExpressionsRule.FILTER_INSTANCE does not have any
> effect.
> May it be a problem of 1.19 or VolcanoPlanner ?
>
> This is my "program" now:
>
> public static final ImmutableSet<RelOptRule> RULE_SET =
> ImmutableSet.of(
> EnumerableRules.ENUMERABLE_JOIN_RULE,
> EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE,
> EnumerableRules.ENUMERABLE_SEMI_JOIN_RULE,
> EnumerableRules.ENUMERABLE_CORRELATE_RULE,
> EnumerableRules.ENUMERABLE_PROJECT_RULE,
> EnumerableRules.ENUMERABLE_FILTER_RULE,
> EnumerableRules.ENUMERABLE_AGGREGATE_RULE,
> EnumerableRules.ENUMERABLE_SORT_RULE,
> EnumerableRules.ENUMERABLE_LIMIT_RULE,
> EnumerableRules.ENUMERABLE_UNION_RULE,
> EnumerableRules.ENUMERABLE_INTERSECT_RULE,
> EnumerableRules.ENUMERABLE_MINUS_RULE,
> EnumerableRules.ENUMERABLE_TABLE_MODIFICATION_RULE,
> EnumerableRules.ENUMERABLE_VALUES_RULE,
> EnumerableRules.ENUMERABLE_WINDOW_RULE,
> SemiJoinRule.PROJECT,
> SemiJoinRule.JOIN,
> TableScanRule.INSTANCE,
> CalciteSystemProperty.COMMUTE.value()
> ? JoinAssociateRule.INSTANCE
> : ProjectMergeRule.INSTANCE,
> AggregateStarTableRule.INSTANCE,
> AggregateStarTableRule.INSTANCE2,
> FilterTableScanRule.INSTANCE,
> FilterProjectTransposeRule.INSTANCE,
> FilterJoinRule.FILTER_ON_JOIN,
> AggregateExpandDistinctAggregatesRule.INSTANCE,
> AggregateReduceFunctionsRule.INSTANCE,
> FilterAggregateTransposeRule.INSTANCE,
> JoinCommuteRule.INSTANCE,
> JoinPushThroughJoinRule.RIGHT,
> JoinPushThroughJoinRule.LEFT,
> SortProjectTransposeRule.INSTANCE,
> ReduceExpressionsRule.FILTER_INSTANCE); <------ HERE
>
> Enrico
>
>
>
>>
>> Feng Zhu <we...@gmail.com> 于2019年9月24日周二 下午5:50写道：
>>
>>> Hi, Enrico,
>>> I'm a little confused about your expectations. Could you clarify it?
>>> Moreover, is it right for the below simplification (do you mean v is not
>>> null)?
>>> (v=1 and v is null) -> v=1
>>> (do you mean v is not null?)
>>>
>>> Best regards
>>>
>>> Enrico Olivelli <eo...@gmail.com> 于2019年9月24日周二 下午5:41写道：
>>>
>>>> Hi,
>>>> I have a query like
>>>> SELECT * FROM MYTABLE WHERE v = 1 and v is null
>>>>
>>>> I am expecting Calcite to simplify it to
>>>> SELECT * FROM MYTABLE WHERE v = 1
>>>>
>>>> but this does not happen.
>>>>
>>>> Is any rule I should enable in order to make it happen ?
>>>>
>>>> This is the configuration of my Volcano planner:
>>>>
>>>> final FrameworkConfig config = Frameworks.newConfigBuilder()
>>>> .parserConfig(....)
>>>> .defaultSchema(...)
>>>> .traitDefs(....)
>>>> .programs(Programs.ofRules(Programs.RULE_SET))
>>>> .build();
>>>>
>>>> Best regards
>>>> Enrico
Re: Trivial query simplification
Posted by Enrico Olivelli <eo...@gmail.com>.
Il giorno mar 24 set 2019 alle ore 13:45 XING JIN <ji...@gmail.com>
ha scritto:
> "v = 1 and v is null"
> cannot be simplified to "v = 1" not matter v is nullable or not nullable
>
> If you really mean that "v is not null", I made below test case in
> RelOptRulesTest.java for illustration:
>
>
> // mgr is nullable
> @Test public void testDEV() throws Exception {
> HepProgram program = new HepProgramBuilder()
> .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
> .build();
>
> final String sql = "select deptno"
> + " from emp"
> + " where mgr = 10 and mgr is not null";
> checkPlanning(new HepPlanner(program), sql);
> }
>
> The plan is
> LogicalProject(DEPTNO=[$7])
> LogicalFilter(condition=[=($3, 10)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>
> Enrico ~ you may try ReduceExpressionsRule.FILTER_INSTANCE
>
@XING JIN
thank you.
I am with Calcite 1.19 and VolcanoPlanner
Original query:
select * from pippo where n1 is null AND n1 is not null
Logical plan:
LogicalFilter(condition=[AND(IS NULL($1), IS NOT NULL($1))]): rowcount =
1.35, cumulative cost = {7.35 rows, 13.0 cpu, 0.0 io}, id = 48
EnumerableTableScan(table=[[herd, pippo]]): rowcount = 6.0, cumulative
cost = {6.0 rows, 7.0 cpu, 0.0 io}, id = 47
Final Plan:
BindableTableScan(table=[[herd, pippo]], filters=[[AND(IS NULL($1), IS NOT
NULL($1))]]): rowcount = 6.0, cumulative cost = {0.03 rows, 0.035 cpu, 0.0
io}, id = 59
It seems that ReduceExpressionsRule.FILTER_INSTANCE does not have any
effect.
May it be a problem of 1.19 or VolcanoPlanner ?
This is my "program" now:
public static final ImmutableSet<RelOptRule> RULE_SET =
ImmutableSet.of(
EnumerableRules.ENUMERABLE_JOIN_RULE,
EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE,
EnumerableRules.ENUMERABLE_SEMI_JOIN_RULE,
EnumerableRules.ENUMERABLE_CORRELATE_RULE,
EnumerableRules.ENUMERABLE_PROJECT_RULE,
EnumerableRules.ENUMERABLE_FILTER_RULE,
EnumerableRules.ENUMERABLE_AGGREGATE_RULE,
EnumerableRules.ENUMERABLE_SORT_RULE,
EnumerableRules.ENUMERABLE_LIMIT_RULE,
EnumerableRules.ENUMERABLE_UNION_RULE,
EnumerableRules.ENUMERABLE_INTERSECT_RULE,
EnumerableRules.ENUMERABLE_MINUS_RULE,
EnumerableRules.ENUMERABLE_TABLE_MODIFICATION_RULE,
EnumerableRules.ENUMERABLE_VALUES_RULE,
EnumerableRules.ENUMERABLE_WINDOW_RULE,
SemiJoinRule.PROJECT,
SemiJoinRule.JOIN,
TableScanRule.INSTANCE,
CalciteSystemProperty.COMMUTE.value()
? JoinAssociateRule.INSTANCE
: ProjectMergeRule.INSTANCE,
AggregateStarTableRule.INSTANCE,
AggregateStarTableRule.INSTANCE2,
FilterTableScanRule.INSTANCE,
FilterProjectTransposeRule.INSTANCE,
FilterJoinRule.FILTER_ON_JOIN,
AggregateExpandDistinctAggregatesRule.INSTANCE,
AggregateReduceFunctionsRule.INSTANCE,
FilterAggregateTransposeRule.INSTANCE,
JoinCommuteRule.INSTANCE,
JoinPushThroughJoinRule.RIGHT,
JoinPushThroughJoinRule.LEFT,
SortProjectTransposeRule.INSTANCE,
ReduceExpressionsRule.FILTER_INSTANCE); <------ HERE
Enrico
>
> Feng Zhu <we...@gmail.com> 于2019年9月24日周二 下午5:50写道：
>
> > Hi, Enrico,
> > I'm a little confused about your expectations. Could you clarify it?
> > Moreover, is it right for the below simplification (do you mean v is not
> > null)?
> > (v=1 and v is null) -> v=1
> > (do you mean v is not null?)
> >
> > Best regards
> >
> > Enrico Olivelli <eo...@gmail.com> 于2019年9月24日周二 下午5:41写道：
> >
> > > Hi,
> > > I have a query like
> > > SELECT * FROM MYTABLE WHERE v = 1 and v is null
> > >
> > > I am expecting Calcite to simplify it to
> > > SELECT * FROM MYTABLE WHERE v = 1
> > >
> > > but this does not happen.
> > >
> > > Is any rule I should enable in order to make it happen ?
> > >
> > > This is the configuration of my Volcano planner:
> > >
> > > final FrameworkConfig config = Frameworks.newConfigBuilder()
> > > .parserConfig(....)
> > > .defaultSchema(...)
> > > .traitDefs(....)
> > > .programs(Programs.ofRules(Programs.RULE_SET))
> > > .build();
> > >
> > > Best regards
> > > Enrico
> > >
> >
>
Re: Trivial query simplification
Posted by XING JIN <ji...@gmail.com>.
"v = 1 and v is null"
cannot be simplified to "v = 1" not matter v is nullable or not nullable
If you really mean that "v is not null", I made below test case in
RelOptRulesTest.java for illustration:
// mgr is nullable
@Test public void testDEV() throws Exception {
HepProgram program = new HepProgramBuilder()
.addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
.build();
final String sql = "select deptno"
+ " from emp"
+ " where mgr = 10 and mgr is not null";
checkPlanning(new HepPlanner(program), sql);
}
The plan is
LogicalProject(DEPTNO=[$7])
LogicalFilter(condition=[=($3, 10)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
Enrico ~ you may try ReduceExpressionsRule.FILTER_INSTANCE
Feng Zhu <we...@gmail.com> 于2019年9月24日周二 下午5:50写道：
> Hi, Enrico,
> I'm a little confused about your expectations. Could you clarify it?
> Moreover, is it right for the below simplification (do you mean v is not
> null)?
> (v=1 and v is null) -> v=1
> (do you mean v is not null?)
>
> Best regards
>
> Enrico Olivelli <eo...@gmail.com> 于2019年9月24日周二 下午5:41写道：
>
> > Hi,
> > I have a query like
> > SELECT * FROM MYTABLE WHERE v = 1 and v is null
> >
> > I am expecting Calcite to simplify it to
> > SELECT * FROM MYTABLE WHERE v = 1
> >
> > but this does not happen.
> >
> > Is any rule I should enable in order to make it happen ?
> >
> > This is the configuration of my Volcano planner:
> >
> > final FrameworkConfig config = Frameworks.newConfigBuilder()
> > .parserConfig(....)
> > .defaultSchema(...)
> > .traitDefs(....)
> > .programs(Programs.ofRules(Programs.RULE_SET))
> > .build();
> >
> > Best regards
> > Enrico
> >
>
Re: Trivial query simplification
Posted by Feng Zhu <we...@gmail.com>.
Hi, Enrico,
I'm a little confused about your expectations. Could you clarify it?
Moreover, is it right for the below simplification (do you mean v is not
null)?
(v=1 and v is null) -> v=1
(do you mean v is not null?)
Best regards
Enrico Olivelli <eo...@gmail.com> 于2019年9月24日周二 下午5:41写道：
> Hi,
> I have a query like
> SELECT * FROM MYTABLE WHERE v = 1 and v is null
>
> I am expecting Calcite to simplify it to
> SELECT * FROM MYTABLE WHERE v = 1
>
> but this does not happen.
>
> Is any rule I should enable in order to make it happen ?
>
> This is the configuration of my Volcano planner:
>
> final FrameworkConfig config = Frameworks.newConfigBuilder()
> .parserConfig(....)
> .defaultSchema(...)
> .traitDefs(....)
> .programs(Programs.ofRules(Programs.RULE_SET))
> .build();
>
> Best regards
> Enrico
>