[HIVE-19115] Merge: Semijoin hints are dropped by the merge

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Component/s: Query Planning, Transactions
• Assignee: Deepak Jaiswal
• Reporter: Gopal Vijayaraghavan
• Created: 05/Apr/18 04:29
• Updated: 13/Aug/18 19:56
• Resolved: 13/Aug/18 19:33

Description

create table target stored as orc as select ss_ticket_number, ss_item_sk, current_timestamp as `ts` from tpcds_bin_partitioned_orc_1000.store_sales;

create table source stored as orc as select sr_ticket_number, sr_item_sk, d_date from tpcds_bin_partitioned_orc_1000.store_returns join tpcds_bin_partitioned_orc_1000.date_dim where d_date_sk = sr_returned_date_sk;


merge /* +semi(T, sr_ticket_number, S, 10000) */ into target T using (select * from source where year(d_date) = 1998) S ON T.ss_ticket_number = S.sr_ticket_number and sr_item_sk = ss_item_sk 
when matched THEN UPDATE SET ts = current_timestamp
when not matched and sr_item_sk is not null and sr_ticket_number is not null THEN INSERT VALUES(S.sr_ticket_number, S.sr_item_sk, current_timestamp);

The semijoin hints are ignored and the code says

     todo: do we care to preserve comments in original SQL?

https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/parse/UpdateDeleteSemanticAnalyzer.java#L624

in this case we do.

Issue Links

• duplicates: HIVE-20354 Semijoin hints dont work with merge statements
• is related to: HIVE-10924 add support for MERGE statement