[CALCITE-6430] SINGLE_VALUE rewrite to wrong SQL when the sub-query returns one not-NULL value and one NULL value in PostgreSQL, MySQL, HSQL dialect

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.37.0
• Fix Version/s: 1.38.0
• Component/s: core
• Labels: pull-request-available
• Assignee: xiong duan
• Reporter: xiong duan
• Created: 07/Jun/24 12:37
• Updated: 17/Oct/24 22:41
• Resolved: 17/Oct/24 22:41

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.

Issue Links

• links to: GitHub Pull Request #3815