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 <eolive...@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 <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 > > > > >
