Hi It seem that you are right and it a bug with the CTE when there’s an “IS NULL” predicate involved. I’ve opened a bug for this. https://issues.apache.org/jira/browse/HIVE-13733
Dudu 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 From: mahender bigdata [mailto:[email protected]] Sent: Wednesday, May 11, 2016 4:03 AM To: [email protected] Subject: Hive cte Alias problem Hi, I see peculiar difference while querying using CTE where I'm aliasing one of column in table to another column name in same table. Instead of getting values of source column, hive returns NULLS i.e column 8 values with cte_temp as ( select a.COLUMN1, a.Column2,a.Column2 as Column8,ID from <table1> a where Coalesce(ltrim(rtrim(a.COLUMN1)) ,'') <> '' AND Coalesce(ltrim(rtrim(a.COLUMN2)), '') <> '' AND Coalesce(ltrim(rtrim(a.COLUMN2)), '') <> '' AND a.COLUMN8 IS NULL and a.ID=100 ) select * from cte_temp ; Results cte_temp.column1,cte_temp.column2,cte_temp.column8,ID Row1,UK,,49 Row5,UP,,49 From the above query, Col2 has not null values and I'm filtering on Col8 =null, I'm aliasing Col2 has Col8. Whenever I perform Select * from CTE, I see instead of showing Col2 values, it is showing Col8 values. Is it bug with Hive. When i run query using SQL SELECT only, it is working fine. select a.COLUMN1, a.Column2,a.Column2 as Column8,ID from <table1> a where Coalesce(ltrim(rtrim(a.COLUMN1)) ,'') <> '' AND Coalesce(ltrim(rtrim(a.COLUMN2)), '') <> '' AND Coalesce(ltrim(rtrim(a.COLUMN2)), '') <> '' AND a.COLUMN8 IS NULL and a.ID=100 Results cte_temp.column1,cte_temp.column2,cte_temp.column8,ID Row1,UK,test,49 Row5,UP,test,49 Please let me know whether it is problem with CTE. /Mahender
