[
https://issues.apache.org/jira/browse/CALCITE-6901?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-6901:
--------------------------------
Description:
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".
was:
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 fix, the
> 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
> Priority: Major
>
> 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)
