[ 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. Attached repro.q 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. {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. > 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. > Attached repro.q > -- This message was sent by Atlassian Jira (v8.20.10#820010)