hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mahender bigdata <Mahender.BigD...@outlook.com>
Subject Hive cte Alias problem
Date Wed, 11 May 2016 01:02:53 GMT
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

withcte_temp as

(

select  a.COLUMN1, a.Column2,a.Column2 asColumn8,ID

from<table1> a whereCoalesce(ltrim(rtrim(a.COLUMN1)) ,'') <> ''

ANDCoalesce(ltrim(rtrim(a.COLUMN2)), '') <> ''

ANDCoalesce(ltrim(rtrim(a.COLUMN2)), '') <> ''

ANDa.COLUMN8 ISNULL

anda.ID=100

)

select* fromcte_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 asColumn8,ID

from<table1> a whereCoalesce(ltrim(rtrim(a.COLUMN1)) ,'') <> ''

ANDCoalesce(ltrim(rtrim(a.COLUMN2)), '') <> ''

ANDCoalesce(ltrim(rtrim(a.COLUMN2)), '') <> ''

ANDa.COLUMN8 ISNULL

anda.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