[jira] [Commented] (IMPALA-7845) Clarify Impala's policy for use of column aliases

["Tim Armstrong (JIRA)" <ji...@apache.org>]

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

Tim Armstrong commented on IMPALA-7845:
---------------------------------------

[~boroknagyz] did some work to standardise the behaviour in 6.0 (before that we allowed referencing column aliases in expressions).

> 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
>            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-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org