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)

Reply via email to