[ https://issues.apache.org/jira/browse/CALCITE-6430?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17853440#comment-17853440 ]
xiong duan commented on CALCITE-6430: ------------------------------------- Fixed in [fb15511|https://github.com/apache/calcite/commit/fb15511e76c660cbd440578421645ebe63941bf7].Thanks for the review [~mbudiu]. > 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)