[
https://issues.apache.org/jira/browse/HIVE-1683?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13142295#comment-13142295
]
David Phillips commented on HIVE-1683:
--------------------------------------
This isn't allowed by the ANSI standard and is a bit weird conceptually. MySQL
does allow it, which might cause confusion for users. A problem with their
implementation is when the column is ambiguous (i.e. an output column named the
same as an input column):
{code}
CREATE TABLE foo (a string, b int);
SELECT length(a) AS b FROM foo GROUP BY b;
{code}
In this case, MySQL will choose the output column and emit a warning. I
believe the correct behavior here is to error at compile time.
A common work around for complex expressions is to use an inline view (which
works for both WHERE and GROUP BY):
{code}
SELECT t, COUNT(*)
FROM (
SELECT complex(expression(a)) t
FROM foo
) x
WHERE t > 5
GROUP BY t
;
{code}
> Column aliases cannot be used in a group by clause
> --------------------------------------------------
>
> Key: HIVE-1683
> URL: https://issues.apache.org/jira/browse/HIVE-1683
> Project: Hive
> Issue Type: Bug
> Components: Metastore
> Reporter: Shrikrishna Lawande
> Labels: SQL
>
> Column aliases cannot be used in a group by clause
> Following query would fail :
> select col1 as t, count(col2) from test group by t;
> FAILED: Error in semantic analysis: line 1:49 Invalid Table Alias or Column
> Reference t
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
