[HIVE-4173] Hive Ignoring where clause for multitable insert

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Duplicate
• Affects Version/s: 0.8.1, 0.9.0
• Component/s: Query Processor

Environment

Red Hat Enterprise Linux Server release 6.3 (Santiago),

• Tags: Hive, HQL, Multitable insert
• Assignee: Unassigned
• Reporter: hussain
• Created: 14/Mar/13 08:16
• Updated: 17/Sep/13 17:13
• Resolved: 17/Sep/13 07:37

Description

Hive is ignoring Filter conditions given at Multi Insert select statement when Filtering given on Source Query..

To highlight this issue, please see below example with where clause (status!='C') from employee12 table causing issue and due to which insert filters (batch_id='12 and batch_id!='12' )not working and dumping all the data coming from source to both the tables.

I have checked the hive execution plan, and didn't find Filter predicates under for filtering record per insert statements

from
(from employee12
select *
where status!='C') t
insert into table employee1
select
status,
field1,
'T' as field2,
'P' as field3,
'C' as field4
where batch_id='12'
insert into table employee2
select
status,
field1,
'D' as field2,
'P' as field3,
'C' as field4
where batch_id!='12';

It is working fine with single insert. Hive generating plan properly..
I am able to reproduce this issue with 8.1 and 9.0 version of Hive.

Issue Links

• duplicates: HIVE-3699 Multiple insert overwrite into multiple tables query stores same results in all tables