Re: Trivial query simplification

[Enrico Olivelli]

Il mer 25 set 2019, 23:38 Stamatis Zampetakis  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 
> 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 
> 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 
> > > wrote:
> > > >
> > > > Il giorno mar 24 set 2019 alle ore 13:45 XING JIN <
> > > jinxing.co...@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

Re: Trivial query simplification

[Stamatis Zampetakis]

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  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  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 
> > wrote:
> > >
> > > Il giorno mar 24 set 2019 alle ore 13:45 XING JIN <
> > jinxing.co...@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
> > >
> > >

Re: Trivial query simplification

[Enrico Olivelli]

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  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 
> wrote:
> >
> > Il giorno mar 24 set 2019 alle ore 13:45 XING JIN <
> jinxing.co...@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:
> >
> > 

Re: Trivial query simplification

[Julian Hyde]

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  wrote:
> 
> Il giorno mar 24 set 2019 alle ore 13:45 XING JIN  >
> 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 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,
>  

Re: Trivial query simplification

[Enrico Olivelli]

Il giorno mar 24 set 2019 alle ore 13:45 XING JIN 
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 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  于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  于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

[XING JIN]

"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  于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  于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

[Enrico Olivelli]

Il mar 24 set 2019, 11:50 Feng Zhu  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  于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

[Feng Zhu]

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  于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
>

Trivial query simplification

[Enrico Olivelli]

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