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)