Details
-
Improvement
-
Status: Resolved
-
Major
-
Resolution: Fixed
-
1.37.0
Description
Calcite rewrite the SINGLE_VALUE to different sql :
For example in HSQL:
CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN MIN(<result>) ELSE (VALUES 1 UNION ALL VALUES 1) END
This is right. But Calcite will generateļ¼
CASE COUNT(result) WHEN 0 THEN NULL WHEN 1 THEN MIN(<result>) ELSE (VALUES 1 UNION ALL VALUES 1) END
This sql will return wrong result.
For Example:
tableA:
c1 | c2 |
---|---|
4 | 1 |
NULL | NULL |
NULL | NULL |
NULL | NULL |
TheSQL:
select * from tableA where c1 > (select c2 from tableA);
will throw : [21000][1242] Subquery returns more than 1 row
But SQL:
select * from tableA left join (select case count(c2) when 0 then null when 1 then min(c2) else (select cast(null as integer) union all select cast(null as integer)) end as alias from tableA) as t1 on true where tableA.c1 > t1.alias;
will return one row value.
Attachments
Issue Links
- links to