Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-443

getPredicates from a union is not correct

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.9.2-incubating
    • Component/s: None
    • Labels:
      None

      Description

      getPredicates from a union is not correct when one input of the union returns true.

      1. CALCITE-443.1.patch
        4 kB
        Pengcheng Xiong

        Activity

        Hide
        pxiong Pengcheng Xiong added a comment -

        FYI, the wrong output without patch

        <TestCase name="testTransitiveInferenceUnionAlwaysTrue">
        <Resource name="sql">
        <![CDATA[select d.deptno, e.deptno from
        (select deptno from sales.emp where deptno < 4) d
        inner join
        (select deptno from sales.emp where deptno > 7
        union all
        select deptno from sales.emp) e
        on d.deptno = e.deptno ]]>
        </Resource>
        <Resource name="planBefore">
        <![CDATA[
        ProjectRel(DEPTNO=[$0], DEPTNO0=[$1])
        JoinRel(condition=[=($0, $1)], joinType=[inner])
        ProjectRel(DEPTNO=[$7])
        FilterRel(condition=[<($7, 4)])
        TableAccessRel(table=[[CATALOG, SALES, EMP]])
        UnionRel(all=[true])
        ProjectRel(DEPTNO=[$7])
        FilterRel(condition=[>($7, 7)])
        TableAccessRel(table=[[CATALOG, SALES, EMP]])
        ProjectRel(DEPTNO=[$7])
        TableAccessRel(table=[[CATALOG, SALES, EMP]])
        ]]>
        </Resource>
        <Resource name="planAfter">
        <![CDATA[
        ProjectRel(DEPTNO=[$0], DEPTNO0=[$1])
        JoinRel(condition=[=($0, $1)], joinType=[inner])
        FilterRel(condition=[>($0, 7)])
        ProjectRel(DEPTNO=[$7])
        FilterRel(condition=[<($7, 4)])
        TableAccessRel(table=[[CATALOG, SALES, EMP]])
        FilterRel(condition=[<($0, 4)])
        UnionRel(all=[true])
        ProjectRel(DEPTNO=[$7])
        FilterRel(condition=[>($7, 7)])
        TableAccessRel(table=[[CATALOG, SALES, EMP]])
        ProjectRel(DEPTNO=[$7])
        TableAccessRel(table=[[CATALOG, SALES, EMP]])
        ]]>
        </Resource>

        Show
        pxiong Pengcheng Xiong added a comment - FYI, the wrong output without patch <TestCase name="testTransitiveInferenceUnionAlwaysTrue"> <Resource name="sql"> <![CDATA[select d.deptno, e.deptno from (select deptno from sales.emp where deptno < 4) d inner join (select deptno from sales.emp where deptno > 7 union all select deptno from sales.emp) e on d.deptno = e.deptno ]]> </Resource> <Resource name="planBefore"> <![CDATA[ ProjectRel(DEPTNO= [$0] , DEPTNO0= [$1] ) JoinRel(condition= [=($0, $1)] , joinType= [inner] ) ProjectRel(DEPTNO= [$7] ) FilterRel(condition= [<($7, 4)] ) TableAccessRel(table=[ [CATALOG, SALES, EMP] ]) UnionRel(all= [true] ) ProjectRel(DEPTNO= [$7] ) FilterRel(condition= [>($7, 7)] ) TableAccessRel(table=[ [CATALOG, SALES, EMP] ]) ProjectRel(DEPTNO= [$7] ) TableAccessRel(table=[ [CATALOG, SALES, EMP] ]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ ProjectRel(DEPTNO= [$0] , DEPTNO0= [$1] ) JoinRel(condition= [=($0, $1)] , joinType= [inner] ) FilterRel(condition= [>($0, 7)] ) ProjectRel(DEPTNO= [$7] ) FilterRel(condition= [<($7, 4)] ) TableAccessRel(table=[ [CATALOG, SALES, EMP] ]) FilterRel(condition= [<($0, 4)] ) UnionRel(all= [true] ) ProjectRel(DEPTNO= [$7] ) FilterRel(condition= [>($7, 7)] ) TableAccessRel(table=[ [CATALOG, SALES, EMP] ]) ProjectRel(DEPTNO= [$7] ) TableAccessRel(table=[ [CATALOG, SALES, EMP] ]) ]]> </Resource>
        Hide
        julianhyde Julian Hyde added a comment -

        Given a query such as

        SELECT * FROM emp WHERE deptno = 10
        UNION ALL
        SELECT * FROM emp
        

        (note the second child is basically 'WHERE TRUE') Calcite was incorrectly inferring that the constraint on the rows coming out of the UNION was 'deptno = 10' when in fact no constraint can be inferred.

        Show
        julianhyde Julian Hyde added a comment - Given a query such as SELECT * FROM emp WHERE deptno = 10 UNION ALL SELECT * FROM emp (note the second child is basically 'WHERE TRUE') Calcite was incorrectly inferring that the constraint on the rows coming out of the UNION was 'deptno = 10' when in fact no constraint can be inferred.
        Show
        julianhyde Julian Hyde added a comment - Fixed in https://git-wip-us.apache.org/repos/asf?p=incubator-calcite.git;a=commit;h=df35cd236580e2653e55cc939c0b7c5e396ed318 . I added more tests in https://git-wip-us.apache.org/repos/asf?p=incubator-calcite.git;a=commit;h=28f160268c53915a5440066defef971eec3e3d36 . And I have pushed a new 0.9.2-incubating-SNAPSHOT to nexus with these changes.
        Hide
        julianhyde Julian Hyde added a comment -

        Bulk update closing "fixed" issues that are more than a year old.

        Show
        julianhyde Julian Hyde added a comment - Bulk update closing "fixed" issues that are more than a year old.

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            pxiong Pengcheng Xiong
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development