[ https://issues.apache.org/jira/browse/IMPALA-7845?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Paul Rogers reassigned IMPALA-7845: ----------------------------------- Assignee: (was: Paul Rogers) > Clarify Impala's policy for use of column aliases > ------------------------------------------------- > > Key: IMPALA-7845 > URL: https://issues.apache.org/jira/browse/IMPALA-7845 > Project: IMPALA > Issue Type: Bug > Components: Frontend > Affects Versions: Impala 3.0 > Reporter: Paul Rogers > Priority: Minor > > Column aliases can allow users to provide a name to an expression within the > {{SELECT}} clause: > {code:sql} > SELECT (id + int_col) / 3 AS c > FROM ... > {code} > There is considerable variation about how SQL implementations allow aliases > to be used. Impala's rules appear to be: > * {{GROUP BY}} and {{ORDER BY}} can reference aliases, but not within > expressions. > * {{HAVING}} attempts to allow aliases, but fails to do so due to bugs. > The general rule is that the analyzer will recognize aliases as long as the > alias is the only node in an expression. For example: > {code:sql} > ORDER BY c -- c can be an alias or column > ORDER BY c + 2 -- c must be a table column, not an alias > {code} > As a result of the above rule, alias are not supported in the {{HAVING}} > clause, since {{HAVING}} is an expression (like {{WHERE}}) not a list (like > {{GROUP BY}} and {{ORDER BY}}.) > However, Impala [tries to > support|https://github.com/apache/impala/blob/master/fe/src/main/java/org/apache/impala/analysis/SelectStmt.java#L549] > aliases in {{HAVING}}: > {code:sql} > havingPred_ = substituteOrdinalOrAlias(havingClause_, "HAVING", > analyzer_); > {code} > Again, this _does not_ work because the top-level node must be a Boolean > operator (such as {{=}}), it will never be an alias. > This ticket asks to clearly define Impala's intent for the use of aliases, > then adjust the analyzer code to implement this decision. > References: > * [MySQL supports aliases in > HAVING|https://www.w3schools.com/sql/sql_having.asp]. > * [MySQL supports aliases in > WHERE|https://www.physicsforums.com/threads/can-i-order-by-an-alias-column-mssql.856311/] > * [SQLServer supports alias in GROUP > BY|https://docs.microsoft.com/en-us/sql/sql-server/install/column-aliases-in-order-by-clause-cannot-be-prefixed-by-table-alias?view=sql-server-2014] > * [SQLServer does not support alias in > WHERE|http://www.sqlservercentral.com/blogs/samvangassql/2011/11/08/column-alias-in-order-by-and-where/] > * [PostgreSql supports aliases in ORDER > BY|http://www.postgresqltutorial.com/postgresql-alias/], but not in WHERE, > HAVING or GROUP BY. -- This message was sent by Atlassian JIRA (v7.6.3#76005) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-all-unsubscr...@impala.apache.org For additional commands, e-mail: issues-all-h...@impala.apache.org