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
