[jira] [Created] (HIVE-20206) Hive Union all query with two views on the same hbase external table producing incorrect results

["Venkata (JIRA)" <ji...@apache.org>]

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)