[
https://issues.apache.org/jira/browse/CALCITE-2799?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16750547#comment-16750547
]
Volodymyr Vysotskyi commented on CALCITE-2799:
----------------------------------------------
I agree with [~vladimirsitnikov] that the problem here is in the priorities of
alias and column for both having and group by clauses, and it is observed for a
more simple case:
{noformat}
drop table t;
\\
create table t (i int, j int);
\\
insert into t values (1, 1), (2, 3);
\\
select j * 0 as j
from t
group by j;
j
===
0
0
{noformat}
But regarding using aliases inside aggregate functions in having clauses, MySQL
really allows it:
{noformat}
drop table t;
\\
create table t (i int, j int);
\\
insert into t values (1, 1), (2, 3);
\\
select i, j + 10 as j1
from t
group by i
having max(j1) < 13;
i j1
=== ===
1 11
{noformat}
One more thing about the problem with aliases. MySQLÂ uses either column or
alias if such column wasn't found. In Calcite for regular tables I suppose it
can be also implemented, but for dynamic tables, we don't know whether a table
has such a column.
> 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)
