[jira] [Comment Edited] (PHOENIX-6413) Having cannot resolve alias

["Istvan Toth (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-6413?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17303188#comment-17303188 ] 

Istvan Toth edited comment on PHOENIX-6413 at 3/17/21, 8:25 AM:
----------------------------------------------------------------

Allowing aliases in HAVING is not standard SQL, but a Mysql extension.
As such, this is not a bug, but rather a possible new convenience feature.

See https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html


was (Author: stoty):
Allowing aliases in HAVING is not standard SQL, but a Mysql extension.
As such, this is not a bug, but rather a possible new convenience feature.


> Having cannot resolve alias
> ---------------------------
>
>                 Key: PHOENIX-6413
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-6413
>             Project: Phoenix
>          Issue Type: New Feature
>    Affects Versions: 4.14.3
>            Reporter: Baiqiang Zhao
>            Assignee: Baiqiang Zhao
>            Priority: Major
>
> For example:
> {code:java}
> CREATE TABLE test_table (a_string varchar not null, col1 integer CONSTRAINT pk PRIMARY KEY (a_string));
> select a_string, sum(col1) as sumCol1 from test_table group by a_string having sumCol1>20 order by sumCol1;
> {code}
> Error log:
> {code:java}
> Caused by: org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703): Undefined column. columnName=TEST_TABLE.SUMCOL1Caused by: org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703): Undefined column. columnName=TEST_TABLE.SUMCOL1 at org.apache.phoenix.schema.PTableImpl.getColumnForColumnName(PTableImpl.java:828) at org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.resolveColumn(FromCompiler.java:477) at org.apache.phoenix.compile.HavingCompiler$HavingClauseVisitor.visit(HavingCompiler.java:173) at org.apache.phoenix.compile.HavingCompiler$HavingClauseVisitor.visit(HavingCompiler.java:104) at org.apache.phoenix.parse.ColumnParseNode.accept(ColumnParseNode.java:56) at org.apache.phoenix.parse.CompoundParseNode.acceptChildren(CompoundParseNode.java:64) at org.apache.phoenix.parse.ComparisonParseNode.accept(ComparisonParseNode.java:45) at org.apache.phoenix.compile.HavingCompiler.rewrite(HavingCompiler.java:75) at org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:548) at org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:507) at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:193) at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:153) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:493) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:459) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:302) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:291) at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:290) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:283) at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1834) at org.apache.calcite.avatica.jdbc.JdbcMeta.prepareAndExecute(JdbcMeta.java:747) ... 24 more
> {code}
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)