[
https://issues.apache.org/jira/browse/CALCITE-2799?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16751476#comment-16751476
]
Julian Hyde commented on CALCITE-2799:
--------------------------------------
I don't think we should allow aliases as arguments to aggregate functions.
First, MySQL is inconsistent: it allows
{code}select x, y, x + y as z from t group by x, y having sum(z) < 4{code}
but does not allow
{code}select x, y, x + y as z, sum(z) from t group by x, y{code}
Second, MySQL (before the
[only_full_group_by|https://mysqlserverteam.com/mysql-5-7-only_full_group_by-improved-recognizing-functional-dependencies-enabled-by-default/]
option) is very lax as to what it will allow in the SELECT clause of a GROUP
BY query; for instance, it allows
{code}SELECT deptno, salary + commission AS remuneration FROM emp GROUP BY
deptno{code}
As a result, you can put expressions in to the SELECT clause merely because
they are useful in building expressions elsewhere in the query. Other
databases are not so lax, and only allow expressions in the SELECT clause that
have the same value for every row in the group. Not very useful as arguments to
aggregate functions!
Third, setting up the name-resolution scopes so that you can see the SELECT
clause both before and after aggregation seems muddled; it will lead to bugs
and inconsistency.
> Allow alias in having clause for aggregate functions
> ----------------------------------------------------
>
> Key: CALCITE-2799
> URL: https://issues.apache.org/jira/browse/CALCITE-2799
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.18.0
> Reporter: Arina Ielchiieva
> Assignee: Julian Hyde
> Priority: Major
> Fix For: 1.19.0
>
>
> Currently alias is not allowed in having for aggregate functions.
> MySql supports such cases and taking into account that alias in having clause
> is allowed only for the following conformance levels: MYSQL_5, LENIENT,
> BABEL, it makes sense to allow alias in having for aggregate functions.
> {noformat}
> /**
> * Whether to allow aliases from the {@code SELECT} clause to be used as
> * column names in the {@code HAVING} clause.
> *
> * <p>Among the built-in conformance levels, true in
> * {@link SqlConformanceEnum#BABEL},
> * {@link SqlConformanceEnum#LENIENT},
> * {@link SqlConformanceEnum#MYSQL_5};
> * false otherwise.
> */
> boolean isHavingAlias();
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
