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 <[email protected]> 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 <[email protected]>
> wrote:
> >
> > Il giorno mar 24 set 2019 alle ore 13:45 XING JIN <
> [email protected] <mailto:[email protected]>>
> > 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 <[email protected]> 于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 <[email protected]> 于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
>
>
