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

Exists subquery returns incorrect result when or condition involves null column

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.40.0
    • None

    Description

      Test in sub-query.iq

      select *
      from "scott".emp as e
      where exists (
        select empno
        from "scott".emp as ee
        where e.empno = ee.empno or e.comm >= ee.sal
      )
      

      Expected result:

      +-------+--------+-----------+------+------------+---------+---------+--------+
      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
      +-------+--------+-----------+------+------------+---------+---------+--------+
      |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |         |     20 |
      |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
      |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
      |  7566 | JONES  | MANAGER   | 7839 | 1981-02-04 | 2975.00 |         |     20 |
      |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
      |  7698 | BLAKE  | MANAGER   | 7839 | 1981-01-05 | 2850.00 |         |     30 |
      |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |         |     10 |
      |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |         |     20 |
      |  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |         |     10 |
      |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
      |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |         |     20 |
      |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |         |     30 |
      |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |         |     20 |
      |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |         |     10 |
      +-------+--------+-----------+------+------------+---------+---------+--------+
      (14 rows)
      

      Actual result:

      +-------+--------+----------+------+------------+---------+---------+--------+
      | EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
      +-------+--------+----------+------+------------+---------+---------+--------+
      |  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
      |  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
      |  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
      |  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
      +-------+--------+----------+------+------------+---------+---------+--------+
      

      Plan:

      EnumerableSort(sort0=[$0], sort1=[$5], dir0=[ASC], dir1=[ASC])
        EnumerableHashJoin(condition=[AND(=($0, $10), =($6, $11))], joinType=[semi])
          EnumerableTableScan(table=[[scott, EMP]])
          EnumerableNestedLoopJoin(condition=[OR(=($2, $0), >=($3, $1))], joinType=[inner])
            EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
              EnumerableTableScan(table=[[scott, EMP]])
            EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], COMM=[$t6])
              EnumerableTableScan(table=[[scott, EMP]])
      

      Rows where COMM is null were excluded from the result set due to the join condition. This is likely caused by a semantic mismatch between the original EXISTS clause and its translated semi-join form:

      EnumerableHashJoin(condition=[AND(=($0, $10), =($6, $11))], joinType=[semi])

      If comm is null, 

      e.empno = ee.empno or e.comm >= ee.sal

       evaluates to true, but

      AND(=($0, $10), =($6, $11))

      evaluates to false.

      Attachments

        Issue Links

          Activity

            People

              suibianwanwan33 suibianwanwan
              suibianwanwan33 suibianwanwan
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: