[
https://issues.apache.org/jira/browse/CALCITE-7189?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18020829#comment-18020829
]
Julian Hyde commented on CALCITE-7189:
--------------------------------------
What do you propose to call this mode?
You should probably implement by wrap b in the {{ANY_VALUE}} aggregate function
(see CALCITE-2366). That way, the behavior is limited to the
validator/sql-to-rel.
If b is functionally dependent on a then the SQL standard allows the query as
is. For example,
{code:java}
SELECT empno, deptno
FROM emp
GROUP BY empno {code}
should be valid even if "loose mode" is not enabled.
> Support MySQL-style non-standard GROUP BY
> -----------------------------------------
>
> Key: CALCITE-7189
> URL: https://issues.apache.org/jira/browse/CALCITE-7189
> Project: Calcite
> Issue Type: New Feature
> Reporter: Zhen Chen
> Priority: Minor
>
> MySQL allows queries like SELECT a, b FROM t GROUP BY a; when
> ONLY_FULL_GROUP_BY is disabled, where columns in the SELECT list are not
> included in the GROUP BY clause or an aggregate function. In this loose mode,
> MySQL returns any value of b for each group.
> We can add an option to Calcite to support MySQL’s non-standard GROUP BY mode
> (like disabling ONLY_FULL_GROUP_BY), allowing non-aggregated columns in
> SELECT that are not present in GROUP BY.
> Example:
> {code:sql}
> SELECT a, b FROM t GROUP BY a;
> -- Should return one arbitrary 'b' value per group, as MySQL does in loose
> mode.
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
