[
https://issues.apache.org/jira/browse/CALCITE-6430?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6430:
---------------------------------
Summary: SINGLE_VALUE rewrite to wrong SQL when the sub-query returns one
not-NULL value and NULL value in PostgreSQL, MySQL, HSQL dialect (was:
SINGLE_VALUE rewrite to wrong sql when the sub-query return one NOT-NULL value
and NULL value in PostgreSQL、MySQL、HSQL dialect)
> SINGLE_VALUE rewrite to wrong SQL when the sub-query returns 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)
