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

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

     [ https://issues.apache.org/jira/browse/HIVE-20206?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Alice Fan reassigned HIVE-20206:
--------------------------------

    Assignee: Alice Fan

> 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
>            Assignee: Alice Fan
>            Priority: Major
>         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 results marked in {color:#FF0000}red{color} 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|
> |{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)