Venkata created HIVE-20206:
------------------------------
Summary: Hive Union all query with two views on the same hbase
external table producing incorrect results
Key: HIVE-20206
URL: https://issues.apache.org/jira/browse/HIVE-20206
Project: Hive
Issue Type: Bug
Components: Beeline
Affects Versions: 1.1.0
Reporter: Venkata
Attachments: hive_view_union_all.txt
We are running this in Cloudera cdh5.13.3 version.
Hive version - 1.1.0-cdh5.13.3
Hbase version - 1.2.0-cdh5.13.3
When running hive union all query with two views created on the same hive
*external* hbase table, the query is returning incorrect results.
The query is returning correct results if *set hive.optimize.ppd=false;*
We don't want to turn off the ppd as it will greatly impact performance.
Note: The union all is working fine for the views created on the same Hive
*Managed* table.
The below are the queries ( i have attached the same queries as an attachment):
*HBASE:*
create '*test*','default'
put 'test','111','default:name','john1'
put 'test','111','default:dept','hr1'
put 'test','111','default:type','a'
put 'test','112','default:name','rambo1'
put 'test','112','default:dept','eng1'
put 'test','112','default:type','a'
put 'test','113','default:name','alex1'
put 'test','113','default:dept','dev1'
put 'test','113','default:type','a'
put 'test','211','default:name','john2'
put 'test','211','default:dept','hr2'
put 'test','211','default:type','b'
put 'test','212','default:name','rambo2'
put 'test','212','default:dept','eng2'
put 'test','212','default:type','b'
put 'test','213','default:name','alex2'
put 'test','213','default:dept','dev2'
put 'test','213','default:type','b'
put 'test','311','default:name','john3'
put 'test','311','default:dept','hr3'
put 'test','311','default:type','c'
put 'test','312','default:name','rambo3'
put 'test','312','default:dept','eng3'
put 'test','312','default:type','c'
put 'test','313','default:name','alex3'
put 'test','313','default:dept','dev3'
put 'test','313','default:type','c'
################################
*BEELINE*
CREATE external TABLE *hbase_ext_table*(id string, name string,dept string,type
string) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH
SERDEPROPERTIES ("hbase.columns.mapping" =
":key,default:name,default:dept,default:type") TBLPROPERTIES
("hbase.table.name" = "*test*");
create view *view1* as select id ,name,dept,type from *hbase_ext_table* where
id>='1' and id<'2' and type is not null;
create view *view2* as select id,name,dept,type from *hbase_ext_table* where
id>='2' and id<'3' and type is not null;
> select * from view1;
+-----------------+-------------------+-------------------+-------------------+--+
| view1.id | view1.name | view1.dept | view1.type |
+-----------------+-------------------+-------------------+-------------------+--+
| 111 | john1 | hr1 | a |
| 112 | rambo1 | eng1 | a |
| 113 | alex1 | dev1 | a |
+-----------------+-------------------+-------------------+-------------------+--+
> select * from view2;
+-----------------+-------------------+-------------------+-------------------+--+
| view2.id | view2.name | view2.dept | view2.type |
+-----------------+-------------------+-------------------+-------------------+--+
| 211 | john2 | hr2 | b |
| 212 | rambo2 | eng2 | b |
| 213 | alex2 | dev2 | b |
+-----------------+-------------------+-------------------+-------------------+--+
> select id,name,dept,type from *view1* union all select id,name,dept,type from
> *view2*;
+---------+-----------+-----------+-----------+--+
| _u1.id | _u1.name | _u1.dept | _u1.type |
+---------+-----------+-----------+-----------+--+
| 111 | john1 | hr1 | a |
| 111 | john1 | hr1 | a |
| 112 | rambo1 | eng1 | a |
| 112 | rambo1 | eng1 | a |
| 113 | alex1 | dev1 | a |
| 113 | alex1 | dev1 | a |
| 211 | john2 | hr2 | b |
| 211 | john2 | hr2 | b |
| 212 | rambo2 | eng2 | b |
| 212 | rambo2 | eng2 | b |
| 213 | alex2 | dev2 | b |
| 213 | alex2 | dev2 | b |
| {color:#FF0000}*311 | john3 | hr3 | c |*{color}
{color:#FF0000}*| 311 | john3 | hr3 | c |*{color}
{color:#FF0000}*| 312 | rambo3 | eng3 | c |*{color}
{color:#FF0000}*| 312 | rambo3 | eng3 | c |*{color}
{color:#FF0000}*| 313 | alex3 | dev3 | c |*{color}
{color:#FF0000}*| 313 | alex3 | dev3 | c |*{color}
+---------+-----------+-----------+-----------+--+
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
