[jira] [Updated] (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

[Julian Hyde (Jira)]

     [ 
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 one NULL value in PostgreSQL, MySQL, HSQL dialect  (was: 
SINGLE_VALUE rewrite to wrong SQL when the sub-query returns 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 one 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)