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