[HIVE-13733] CTE + "IS NULL" predicate + column aliasing as existing column leads to wrong results

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 1.1.0
• Assignee: Pengcheng Xiong
• Reporter: Dudu Markovitz
• Created: 11/May/16 07:49
• Updated: 27/Feb/24 22:23
• Resolved: 13/May/16 17:15

Description

hive> create table t (i int,a string,b string);
hive> insert into t values (1,'hello','world'),(2,'bye',null);
hive> select * from t where t.b is null;
2 bye NULL

This is wrong, all 3 columns should return the same value - t.a.

hive> with cte as (select t.a as a,t.a as b,t.a as c from t where t.b is null) select * from cte;
bye NULL bye

However, these are right:

hive> select t.a as a,t.a as b,t.a as c from t where t.b is null;
bye bye bye

hive> with cte as (select t.a as a,t.a as b,t.a as c from t where t.b is not null) select * from cte;OK
hello hello hello

Issue Links

• is related to: HIVE-13602 TPCH q16 return wrong result when CBO is on
• relates to: HIVE-15888 constant propagation optimizer failed when query has the same alias with subquery