Paul Rogers created IMPALA-7845:
-----------------------------------
Summary: 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
Assignee: Paul Rogers
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: [email protected]
For additional commands, e-mail: [email protected]
