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