Mihai Budiu created CALCITE-7943:
------------------------------------

             Summary: SubQueryRemoveRule gives wrong results for "<> SOME" and 
"= ALL" when another sub-query comes first
                 Key: CALCITE-7943
                 URL: https://issues.apache.org/jira/browse/CALCITE-7943
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.42.0
            Reporter: Mihai Budiu


The queries below use the scott schema. 
{code:sql}
select empno, sal > all (select sal from emp where deptno = 10) as x,
  comm <> some (select comm from emp where empno = 7499) as y
from emp where deptno = 30;
{code}
For EMPNO 7499, Calcite gives Y = TRUE. The correct value is FALSE: the 
sub-query returns only 300, and COMM of 7499 is 300. When the two sub-queries 
are in the other order, the result is correct.
{code:sql}
select empno, comm in (select comm from emp where deptno = 30) as s,
  comm = all (select comm from emp where empno = 7499) as a
from emp where deptno = 30;
{code}
For EMPNO 7499, Calcite gives A = NULL. The correct value is TRUE.

The correlated rewrite has the same problem:
{code:sql}
select dname, 1000 > all (select comm from emp where emp.deptno = dept.deptno) 
as x,
  300 <> some (select comm from emp
               where emp.deptno = dept.deptno and emp.comm = 300) as y
from dept;
{code}
For SALES, Calcite gives Y = NULL. The correct value is FALSE.

 



--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

Reply via email to