[
https://issues.apache.org/jira/browse/HIVE-27923?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Riju Trivedi updated HIVE-27923:
--------------------------------
Description:
Query fails with 'Invalid column reference' error if the column in Order by
clause is not referenced similar to Group By clause. Like below 2 queries will
compile correctly but the third one will fail.
{code:java}
CREATE VIEW view_order_by_test AS SELECT to_date(`order_by_test`.`result_date`)
AS `process_date`,COUNT AS `count`
FROM `order_by_test`
GROUP BY to_date(`order_by_test`.`result_date`)
ORDER BY to_date(`order_by_test`.`result_date`) DESC;
---Successful
CREATE VIEW view_order_by_test AS SELECT to_date(`result_date`) AS
`process_date`,COUNT AS `count`
FROM `order_by_test`
GROUP BY to_date(`result_date`)
ORDER BY to_date(`result_date`) DESC;
--Successful
CREATE VIEW view_order_by_test AS SELECT to_date(`order_by_test`.`result_date`)
AS `process_date`,COUNT AS `count`
FROM `order_by_test`
GROUP BY to_date(`order_by_test`.`result_date`)
ORDER BY to_date(`result_date`) DESC;
--Failure
{code}
Both column name and alias use should be supported for "ORDER BY" clause.
was:
Query fails with 'Invalid column reference' error if the column in Order by
clause is not referenced similar to Group By clause. Like below 2 queries will
compile correctly but the third one will fail.
CREATE VIEW view_order_by_test AS SELECT to_date(`order_by_test`.`result_date`)
AS `process_date`,COUNT(*) AS `count`
FROM `order_by_test`
GROUP BY to_date(`order_by_test`.`result_date`)
ORDER BY to_date(`order_by_test`.`result_date`) DESC;
---Successful
CREATE VIEW view_order_by_test AS SELECT to_date(`result_date`) AS
`process_date`,COUNT(*) AS `count`
FROM `order_by_test`
GROUP BY to_date(`result_date`)
ORDER BY to_date(`result_date`) DESC;
--Successful
CREATE VIEW view_order_by_test AS SELECT to_date(`order_by_test`.`result_date`)
AS `process_date`,COUNT(*) AS `count`
FROM `order_by_test`
GROUP BY to_date(`order_by_test`.`result_date`)
ORDER BY to_date(`result_date`) DESC;
--Failure
> Query fails with SemanticException if column referenced with name in ORDER by
>
> -------------------------------------------------------------------------------
>
> Key: HIVE-27923
> URL: https://issues.apache.org/jira/browse/HIVE-27923
> Project: Hive
> Issue Type: Bug
> Components: Logical Optimizer
> Reporter: Riju Trivedi
> Priority: Major
>
> Query fails with 'Invalid column reference' error if the column in Order by
> clause is not referenced similar to Group By clause. Like below 2 queries
> will compile correctly but the third one will fail.
> {code:java}
> CREATE VIEW view_order_by_test AS SELECT
> to_date(`order_by_test`.`result_date`) AS `process_date`,COUNT AS `count`
> FROM `order_by_test`
> GROUP BY to_date(`order_by_test`.`result_date`)
> ORDER BY to_date(`order_by_test`.`result_date`) DESC;
> ---Successful
> CREATE VIEW view_order_by_test AS SELECT to_date(`result_date`) AS
> `process_date`,COUNT AS `count`
> FROM `order_by_test`
> GROUP BY to_date(`result_date`)
> ORDER BY to_date(`result_date`) DESC;
> --Successful
> CREATE VIEW view_order_by_test AS SELECT
> to_date(`order_by_test`.`result_date`) AS `process_date`,COUNT AS `count`
> FROM `order_by_test`
> GROUP BY to_date(`order_by_test`.`result_date`)
> ORDER BY to_date(`result_date`) DESC;
> --Failure
> {code}
> Both column name and alias use should be supported for "ORDER BY" clause.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
