[ https://issues.apache.org/jira/browse/CALCITE-6430?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
ASF GitHub Bot updated CALCITE-6430: ------------------------------------ Labels: pull-request-available (was: ) > 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 > Priority: Major > Labels: pull-request-available > 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 * > 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;{code} > will return one row value. -- This message was sent by Atlassian Jira (v8.20.10#820010)