Il mer 25 set 2019, 23:38 Stamatis Zampetakis <zabe...@gmail.com> 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 <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 > > > > > > > > >
