[
https://issues.apache.org/jira/browse/CALCITE-2799?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16750325#comment-16750325
]
Julian Hyde commented on CALCITE-2799:
--------------------------------------
If an aggregate function has an alias, say 'count\(*) as c', then there's no
question that you can use 'c' in the HAVING clause.
However, using an alias as the argument to the aggregate function? I don't
think MySQL does that, nor should we. In [rexTester for
MySQL|https://rextester.com/l/mysql_online_compiler] try this:
{code}drop table t;
\\
create table t (i int, j int);
\\
insert into t values (1, 1), (2, 3);
\\
select i, j + 10 as j
from t
group by i
having max(j) < 10;
\\
i j
=== ===
1 11
2 13
{code}
Note that for the "j" that is argument to "max", MySQL is using the original
"j", with values 1 and 3, not the j alias with values 11 and 13.
> 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)
