Dharmendra Shavkani created HIVE-14666:
------------------------------------------
Summary: LEFT OUTER JOIN - ON CLAUSE
Key: HIVE-14666
URL: https://issues.apache.org/jira/browse/HIVE-14666
Project: Hive
Issue Type: Bug
Components: Beeline, CLI
Affects Versions: 1.1.0
Reporter: Dharmendra Shavkani
When we execute below SQL it is failing in HIVE.
SELECT T3.facility_name AS Facility_Name,
Count(DISTINCT ORDERS_SRL.tc_order_id) AS Count_of_Orders,
SUM(order_line_item.order_qty) AS Order_Line_Quantity
FROM order_line_item ORDER_LINE_ITEM join orders ORDERS_SRL ON
(order_line_item.order_id = ORDERS_SRL.order_id )
left outer join (facility T2 join facility_alias T3 ON T2.facility_id =
T3.facility_id) ON (ORDERS_SRL.o_facility_id = T2.facility_id)
GROUP BY T3.facility_name;
Error --> Error: Error while compiling statement: FAILED: ParseException line
5:97 cannot recognize input near 'ON' 'ORDERS_SRL' '.' in expression
specification (state=42000,code=40000)
Same above SQL will work if we re-write as below.
Working SQL
--------------------
SELECT TAB2.Facility_Name,TAB1.Count_of_Orders,TAB1.Order_Line_Quantity FROM
(SELECT ORDERS_SRL.o_facility_id AS o_facility_id,
Count(DISTINCT ORDERS_SRL.tc_order_id) AS Count_of_Orders,
SUM(order_line_item.order_qty) AS Order_Line_Quantity FROM
order_line_item ORDER_LINE_ITEM join orders ORDERS_SRL ON
order_line_item.order_id = ORDERS_SRL.order_id GROUP BY
ORDERS_SRL.o_facility_id) TAB1
left outer join
(SELECT T3.facility_name, T2.facility_id FROM facility T2 join
facility_alias T3 ON T2.facility_id = T3.facility_id ) TAB2
ON TAB1.o_facility_id = TAB2.facility_id;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
