[jira] [Commented] (CALCITE-6901) SINGLE_VALUE rewrite to wrong sql in MySQL dialect

["xiong duan (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-6901?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17936970#comment-17936970 ] 

xiong duan commented on CALCITE-6901:
-------------------------------------

Fixed in [bbcde68|https://github.com/apache/calcite/commit/bbcde68048b432b08b6370a3a94677134e9a4fde]. Thanks for the review [~mbudiu] .

> SINGLE_VALUE rewrite to wrong sql in MySQL dialect
> --------------------------------------------------
>
>                 Key: CALCITE-6901
>                 URL: https://issues.apache.org/jira/browse/CALCITE-6901
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: xiong duan
>            Assignee: xiong duan
>            Priority: Major
>              Labels: pull-request-available
>
> Now, the MySQL dialect rewrites the SINGLE_VALUE to:
> {code:java}
> CASE COUNT(*)
> WHEN 0 THEN NULL
> WHEN 1 THEN <result>
> ELSE (SELECT NULL UNION ALL SELECT NULL)
> END{code}
> But it should be:
> {code:java}
> CASE COUNT(*)
> WHEN 0 THEN NULL 
> WHEN 1 THEN min(<result>) 
> ELSE (SELECT NULL UNION ALL SELECT NULL) END {code}
> The exception:
> {code:java}
> SQL 错误 [1140] [42000]: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column '*****'; this is incompatible with sql_mode=only_full_group_by {code}
> Throw this exception only if multiple values are returned.
> After the fix, the exception will be "SQL error [1242] [21000]: Subquery returns more than 1 row".



--
This message was sent by Atlassian Jira
(v8.20.10#820010)