You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by mahender bigdata <Ma...@outlook.com> on 2016/05/11 01:02:53 UTC
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
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
RE: Hive cte Alias problem
Posted by "Markovitz, Dudu" <dm...@paypal.com>.
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