[HIVE-15083] tez hsql response different from mr

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 1.2.1
• Component/s: Clients

Environment

centos5.6,hdp2.4.2.0,hadoop2.7.1,hive1.2.1

• Assignee: Unassigned
• Reporter: yinsong
• Created: 27/Oct/16 11:45
• Updated: 28/Oct/16 09:08

Description

newdb.dwd_devices_ds stored as orc

hsql like this:
select count(t.lattice_id)
from
(select a.lattice_id,
case when sum(a.box_count) = 0 then 0 else (sum(a.max_box_orders+a.medium_box_orders+a.min_box_orders+a.ice_box_orders)/sum(a.box_count)) end as c_order_rate
from
(select lattice_id,device_id,box_count,max_box_orders,medium_box_orders,min_box_orders,ice_box_orders from newdb.dwd_devices_ds where device_id NOT IN (1060988,1047539,1043597,1033836,1019976,1019941,1019391,1000019,1000018,4306,1000018,1000019,1001681,1006552,1019391,1019941,1019976,1031750,1033836,1043597,1047539,1047549,1060312,1060988,1061884,1062811)
and (deleted_at is null) and day >= '2016-09-26' and day <= '2016-10-25') a
left join
(select distinct device_id from newdb.dwd_devices_ds where deleted_at is not null and day >= '2016-09-26' and day <= '2016-10-25') as b on a.device_id = b.device_id
where b.device_id is null
group by a.lattice_id) t
where c_order_rate<0.05

hive on tez result:12815
hive on mr result:12530

hive on mr result Should be correct