Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
1.1.0
-
None
-
None
Description
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 results marked in red are wrong results.
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 |
311 | john3 | hr3 | c | | 311 | john3 | hr3 | c | | 312 | rambo3 | eng3 | c | | 312 | rambo3 | eng3 | c | | 313 | alex3 | dev3 | c | | 313 | alex3 | dev3 | c | --------- |