[HIVE-11735] Different results when multiple if() functions are used

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.14.0, 1.0.0, 1.1.1, 1.2.1
• Fix Version/s: 2.0.0
• Component/s: Query Planning
• Assignee: Ashutosh Chauhan
• Reporter: Chetna Chaudhari
• Created: 04/Sep/15 09:31
• Updated: 16/Feb/16 23:50
• Resolved: 18/Jan/16 19:03

Description

Hive if() udf is returns different results when string equality is used as condition, with case change.
Observation:
1) if( name = 'chetna' , 3, 4) and if( name = 'Chetna', 3, 4) both are treated as equal.
2) The rightmost udf result is pushed to predicates on left side. Leading to same result for both the udfs.

How to reproduce the issue:
1) CREATE TABLE `sample`(
`name` string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
TBLPROPERTIES (
'transient_lastDdlTime'='1425075745');

2) insert into table sample values ('chetna');
3) select min(if(name = 'chetna', 4, 3)) , min(if(name='Chetna', 4, 3)) from sample;
This will give result :
3 3
Expected result:
4 3
4) select min(if(name = 'Chetna', 4, 3)) , min(if(name='chetna', 4, 3)) from sample;
This will give result
4 4
Expected result:
3 4

Issue Links

• is superceded by: HIVE-12590 Repeated UDAFs with literals can produce incorrect result