hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Markovitz, Dudu" <dmarkov...@paypal.com>
Subject RE: Hive cte Alias problem
Date Wed, 11 May 2016 07:53:42 GMT
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:Mahender.BigData@outlook.com]
Sent: Wednesday, May 11, 2016 4:03 AM
To: user@hive.apache.org
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
Mime
View raw message