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

["Paul Rogers (JIRA)" <ji...@apache.org>]

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