Il giorno mar 24 set 2019 alle ore 13:45 XING JIN <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
> > >
> >
>
