xiong duan created CALCITE-6430:
-----------------------------------
Summary: SINGLE_VALUE rewrite to wrong sql when the sub-query
return one not-null value and NULL value in PostgreSQL、MySQL、HSQL dialect
Key: CALCITE-6430
URL: https://issues.apache.org/jira/browse/CALCITE-6430
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.37.0
Reporter: xiong duan
Assignee: xiong duan
Fix For: 1.38.0
Calcite rewrite the SINGLE_VALUE to different sql :
For example in HSQL:
{code:java}
CASE COUNT(*)
WHEN 0 THEN NULL
WHEN 1 THEN MIN(<result>)
ELSE (VALUES 1 UNION ALL VALUES 1)
END{code}
This is right. But Calcite will generate：
{code:java}
CASE COUNT(result)
WHEN 0 THEN NULL
WHEN 1 THEN MIN(<result>)
ELSE (VALUES 1 UNION ALL VALUES 1)
END{code}
This sql will return wrong result.
For Example:
tableA:
||c1||c2||
|4|1|
|NULL|NULL|
|NULL|NULL|
|NULL|NULL|
TheSQL:
{code:java}
select *
from tableA
where c1 > (select c2 from tableA);{code}
will throw : [21000][1242] Subquery returns more than 1 row
But SQL:
{code:java}
select c1 as column1C1
from column3
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 column3 ) as t1 on true
where column3.c1 > t1.alias;{code}
will return one row value.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
