[HIVE-27923] Query fails with SemanticException if column referenced with name in ORDER by

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: Logical Optimizer
• Assignee: Unassigned
• Reporter: Riju Trivedi
• Created: 30/Nov/23 19:00
• Updated: 01/Dec/23 17:54

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. Attached repro.q

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

Both column name and alias use should be supported for the "ORDER BY" clause.

 

 

 

Issue Links

• is related to: HIVE-26054 Distinct + Groupby with column alias is failing