[jira] [Commented] (CALCITE-3198) Enhance RexSimplify to handle (x<>a or x<>b)

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3198?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16899145#comment-16899145 ] 

Julian Hyde commented on CALCITE-3198:
--------------------------------------

As I remarked in the PR, let's not use {{RexBuilder.constantNull()}}. It produces an un-typed NULL. It's better to create a NULL of type BOOLEAN; call {{RexBuilder.makeNullLiteral(RelDataType)}}.

I am reviewing CALCITE-2921 currently and there are plan differences depending on which of the above methods are used. We should probably rationalize those methods (and maybe deprecate some) but we don't fully understand the problem yet.

> Enhance RexSimplify to handle (x<>a or x<>b)
> --------------------------------------------
>
>                 Key: CALCITE-3198
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3198
>             Project: Calcite
>          Issue Type: Improvement
>    Affects Versions: 1.20.0
>            Reporter: Ruben Quesada Lopez
>            Assignee: Ruben Quesada Lopez
>            Priority: Minor
>              Labels: pull-request-available
>             Fix For: 1.21.0
>
>          Time Spent: 7h
>  Remaining Estimate: 0h
>
> Currently, ReduceExpressionsRule.FILTER_INSTANCE can successfully reduce a query like this one (see RelOptRulesTest#testReduceConstantsDup):
> {code}
> // query:
> select d.deptno from dept d where d.deptno=7 and d.deptno=8
> // plan before:
> LogicalProject(DEPTNO=[$0])
>   LogicalFilter(condition=[AND(=($0, 7), =($0, 8))])
>     LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> // plan after:
> LogicalProject(DEPTNO=[$0])
>   LogicalValues(tuples=[[]])
> {code}
> As we can see, since the filter is 'always false', the LogicalTableScan+LogicalFilter are correctly replaced by an empty LogicalValues.
> However, the same filter with a NOT expression, is not correctly simplified:
> {code}
> // query:
> select d.deptno from dept d where not(d.deptno=7 and d.deptno=8)
> // plan before:
> LogicalProject(DEPTNO=[$0])
>   LogicalFilter(condition=[NOT(AND(=($0, 7), =($0, 8)))])
>     LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> // plan after (actual, NOT distributivity for AND):
> LogicalProject(DEPTNO=[$0])
>   LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))])
>     LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> // plan after (expected, when $0's type is NOT nullable, filter removed):
> LogicalProject(DEPTNO=[$0])
>   LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> // plan after (expected, when $0's type IS nullable, filter simplified as IS NOT NULL):
> LogicalProject(DEPTNO=[$0])
>   LogicalFilter(condition=[IS NOT NULL($0)])
>     LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)