[jira] [Created] (HIVE-14666) LEFT OUTER JOIN - ON CLAUSE

["Dharmendra Shavkani (JIRA)" <ji...@apache.org>]

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)