[
https://issues.apache.org/jira/browse/CALCITE-5934?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
libopeng updated CALCITE-5934:
------------------------------
Description:
{code:java}
> +-------+--------+-----------+------+------------+---------+---------+--------+
> | 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
> |
> +-------+--------+-----------+------+------------+---------+---------+--------+
> {code}
{code:java}
select e1.*
from "scott".emp e1
inner join "scott".emp e2 on e1.COMM=e2.COMM and ((e1.COMM is null) = (e2.COMM
is null))
WHERE e1.MGR is null;
+-------+-------+-----------+-----+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+-----+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
+-------+-------+-----------+-----+------------+---------+------+--------+{code}
The above sql executes the above result is wrong, 'MGR is null' has only one
line of data, and the 'comm' of this line is also null, the correct result is
to return empty data.
But the following sql execution results are correct.
{code:java}
select e1.*
from "scott".emp e1
inner join "scott".emp e2 on e1.COMM=e2.COMM <-- Here reduce the condition
WHERE e1.MGR is null;
+-------+-------+-----+-----+----------+-----+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----+-----+----------+-----+------+--------+
+-------+-------+-----+-----+----------+-----+------+--------+{code}
was:
{code:java}
> +-------+--------+-----------+------+------------+---------+---------+--------+
> | 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
> |
> +-------+--------+-----------+------+------------+---------+---------+--------+
> {code}
{code:java}
select e1.*
from "scott".emp e1
inner join "scott".emp e2 on e1.COMM=e2.COMM and ((e1.COMM is null) = (e2.COMM
is null))
WHERE e1.MGR is null;
+-------+-------+-----------+-----+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+-----+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
+-------+-------+-----------+-----+------------+---------+------+--------+{code}
The above sql executes the above result is wrong, 'MGR is null' has only one
line of data, and the 'comm' of this line is also null, the correct result is
to return empty data.
But the following sql execution results are correct.
{code:java}
select e1.*
from "scott".emp e1
inner join "scott".emp e2 on e1.COMM=e2.COMM <-- Here reduce the condition
WHERE e1.MGR is null;
+-------+-------+-----+-----+----------+-----+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----+-----+----------+-----+------+--------+
+-------+-------+-----+-----+----------+-----+------+--------+{code}
> calcite avatica calculation error
> ---------------------------------
>
> Key: CALCITE-5934
> URL: https://issues.apache.org/jira/browse/CALCITE-5934
> Project: Calcite
> Issue Type: Bug
> Reporter: libopeng
> Priority: Major
>
> {code:java}
> > +-------+--------+-----------+------+------------+---------+---------+--------+
> > | 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 |
> > +-------+--------+-----------+------+------------+---------+---------+--------+
> > {code}
> {code:java}
> select e1.*
> from "scott".emp e1
> inner join "scott".emp e2 on e1.COMM=e2.COMM and ((e1.COMM is null) =
> (e2.COMM is null))
> WHERE e1.MGR is null;
> +-------+-------+-----------+-----+------------+---------+------+--------+
> | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
> +-------+-------+-----------+-----+------------+---------+------+--------+
> | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
> | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
> | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
> | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
> | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
> | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
> | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
> | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
> | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
> | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
> +-------+-------+-----------+-----+------------+---------+------+--------+{code}
> The above sql executes the above result is wrong, 'MGR is null' has only one
> line of data, and the 'comm' of this line is also null, the correct result is
> to return empty data.
> But the following sql execution results are correct.
> {code:java}
> select e1.*
> from "scott".emp e1
> inner join "scott".emp e2 on e1.COMM=e2.COMM <-- Here reduce the condition
> WHERE e1.MGR is null;
> +-------+-------+-----+-----+----------+-----+------+--------+
> | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
> +-------+-------+-----+-----+----------+-----+------+--------+
> +-------+-------+-----+-----+----------+-----+------+--------+{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
