Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.27.0
Description
As discussed in the comments: The SQL
select * from "scott".emp where sal > some (4000, 2000)
is illegal SQL. So we should throw an exception instead of return a wrong result.
The SQL include:
SOME and ALL:
1: select deptno from emp where deptno > some (23, 56); 2: select deptno from emp where deptno > some (cast(23 as integer), cast(56 as integer)); 3: select deptno from emp where deptno > all (cast(23 as integer), cast(56 as integer)); 4: select deptno from emp where deptno > all (23, 56)
NOT IN:
select deptno from emp where deptno not in (60, 56);
DEPTNO
10
10
20
30
30
50
50
null
!ok
This case can't handle NULL value.
But when we use the VALUES expression, SQL always can return the correct result. The SQL like:
select deptno from emp where deptno > some (VALUES (23), (56)); DEPTNO 30 30 50 50 60 !ok
Attachments
Issue Links
- Blocked
-
CALCITE-4756 When subquery include NULL value, Calcite should return the right result
-
- Closed
-
- is related to
-
CALCITE-4843 optimize ALL,SOME sub-query list when op is the <, <=,> or >=
-
- Open
-
- links to