[jira] [Comment Edited] (CALCITE-2257) Combination of predicates can be proved to be always true

["Vitalii Diravka (JIRA)" <ji...@apache.org>]

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

Vitalii Diravka edited comment on CALCITE-2257 at 4/26/18 1:05 PM:
-------------------------------------------------------------------

[~kgyrtkirk] At first glance, I believed your test cases :)

But emp table doesn't have nullable columns. That's why it can be always true. 
Try to use EMPNULLABLES_20 view (from my test case), then the issue is reproduced:

{code}
@Test public void testOrAlwaysTrue2() {
 HepProgram program = new HepProgramBuilder()
 .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
 .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
 .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE)
 .build();

 final String sql = "select SAL from EMPNULLABLES_20 \n"
 + "where SAL IS NOT NULL OR SAL is null";

 checkPlanning(program, sql);
}
{code}
{code}
    <TestCase name="testOrAlwaysTrue2">
        <Resource name="sql">
            <![CDATA[select SAL from EMPNULLABLES_20 
where SAL IS NOT NULL OR SAL is null]]>
        </Resource>
        <Resource name="planBefore">
            <![CDATA[
LogicalProject(SAL=[$5])
  LogicalFilter(condition=[OR(IS NOT NULL($5), IS NULL($5))])
    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8])
      LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))])
        LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
]]>
        </Resource>
        <Resource name="planAfter">
            <![CDATA[
LogicalProject(SAL=[$5])
    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8])
      LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))])
        LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
]]>
        </Resource>
    </TestCase>
{code}


was (Author: vitalii):
[~kgyrtkirk] At first glance, I have believed your test cases :)

But emp table doesn't have nullable columns. That's why it can be always true. 
Try to use EMPNULLABLES_20 view (from my test case), then the issue is reproduced:

{code}
@Test public void testOrAlwaysTrue2() {
 HepProgram program = new HepProgramBuilder()
 .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
 .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
 .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE)
 .build();

 final String sql = "select SAL from EMPNULLABLES_20 \n"
 + "where SAL IS NOT NULL OR SAL is null";

 checkPlanning(program, sql);
}
{code}
{code}
    <TestCase name="testOrAlwaysTrue2">
        <Resource name="sql">
            <![CDATA[select SAL from EMPNULLABLES_20 
where SAL IS NOT NULL OR SAL is null]]>
        </Resource>
        <Resource name="planBefore">
            <![CDATA[
LogicalProject(SAL=[$5])
  LogicalFilter(condition=[OR(IS NOT NULL($5), IS NULL($5))])
    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8])
      LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))])
        LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
]]>
        </Resource>
        <Resource name="planAfter">
            <![CDATA[
LogicalProject(SAL=[$5])
    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8])
      LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))])
        LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
]]>
        </Resource>
    </TestCase>
{code}

> Combination of predicates can be proved to be always true
> ---------------------------------------------------------
>
>                 Key: CALCITE-2257
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2257
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>    Affects Versions: 1.16.0
>            Reporter: Vitalii Diravka
>            Assignee: Vitalii Diravka
>            Priority: Major
>              Labels: filter
>             Fix For: 1.17.0
>
>
> I have found the case, when Filter operator is not necessary since filter condition is always true, but that is not detected by current version of Calcite.
> {code}
> select SAL from EMPNULLABLES_20 where SAL IS NOT NULL OR SAL is null
> {code}
> {code}
> LogicalProject(SAL=[$5])
>   LogicalFilter(condition=[OR(IS NOT NULL($5), IS NULL($5))])
>     LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8])
>       LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))])
>         LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> {code}
> But filter condition _OR(IS NOT NULL($5), IS NULL($5))_ can be proved to be always true.
> I have tried _ReduceExpressionsRule_, but it doesn't give effect.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)