hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "daveKim (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-21054) union all query result empty
Date Tue, 18 Dec 2018 07:48:00 GMT

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

daveKim updated HIVE-21054:
---------------------------
    Description: 
 

 
{code:java}
// 
set hive.vectorized.execution.enabled=FALSE;
set hive.optimize.union.remove=FALSE;


SELECT distinct t1.ctype, t1.id_all , t2.list19
FROM (
SELECT ctype22 AS ctype, id20 AS id_all, list19 AS item_list 
FROM tab WHERE stn1='20130101010100'
AND ctype22 BETWEEN 2 AND 5   --result 45 row

UNION ALL 

SELECT ctype22 AS ctype, id20 AS id_all, list19 AS item_list 
FROM space 
WHERE stn1='20130102010100'
AND ctype22 BETWEEN 2 AND 5   --result empty

) t1 JOIN colon t2 ON (t1.id_all = t2.id20 AND t2.list19 ='ITEM30') -- join matched 3 rows
{code}
expected result 
||ctype||id_all||list19||
|2|104|ITEM30|
|2|683|ITEM30|
|1|970|ITEM30|

but, actual result empty..

 

instead "union all" below query result is not empty case are work.
{code:java}
// 코드 자리 표시자
set hive.vectorized.execution.enabled=FALSE;
set hive.optimize.union.remove=FALSE;


SELECT distinct t1.ctype, t1.id_all , t2.list19
FROM (
SELECT ctype22 AS ctype, id20 AS id_all, list19 AS item_list 
FROM tab WHERE stn1='20130101010100'
AND ctype22 BETWEEN 2 AND 5   --result 45 row

UNION ALL 

SELECT 1 AS ctype, '111' AS id_all, 'ITEM30' AS item_list --dummy row

) t1 JOIN colon t2 ON (t1.id_all = t2.id20 AND t2.list19 ='ITEM30') -- join matched 3 rows
{code}
 

am i wrong properties or query?

 

 

reference
 # HIVE-12788
 # HIVE-20319

 

 

 

  was:
 

 
{code:java}
// 
set hive.vectorized.execution.enabled=FALSE;
set hive.optimize.union.remove=FALSE;


SELECT distinct t1.ctype, t1.id_all , t2.list19
FROM (
SELECT ctype22 AS ctype, id20 AS id_all, list19 AS item_list 
FROM tab WHERE stn1='20130101010100'
AND ctype22 BETWEEN 2 AND 5   --result 45 row

UNION ALL 

SELECT ctype22 AS ctype, id20 AS id_all, list19 AS item_list 
FROM space 
WHERE stn1='20130102010100'
AND ctype22 BETWEEN 2 AND 5   --result empty

) t1 JOIN colon t2 ON (t1.id_all = t2.id20 AND t2.list19 ='ITEM30') -- join matched 3 rows
{code}
expected result 

 
||ctype||id_all||list19||
|2|104|ITEM30|
|2|683|ITEM30|
|1|970|ITEM30|

 

but, actual result empty..

am i wrong properties or query?

 

 

reference
 # HIVE-12788
 # HIVE-20319

 

 

 


> union all query result empty
> ----------------------------
>
>                 Key: HIVE-21054
>                 URL: https://issues.apache.org/jira/browse/HIVE-21054
>             Project: Hive
>          Issue Type: Bug
>          Components: Query Planning
>    Affects Versions: 3.1.1
>            Reporter: daveKim
>            Priority: Major
>
>  
>  
> {code:java}
> // 
> set hive.vectorized.execution.enabled=FALSE;
> set hive.optimize.union.remove=FALSE;
> SELECT distinct t1.ctype, t1.id_all , t2.list19
> FROM (
> SELECT ctype22 AS ctype, id20 AS id_all, list19 AS item_list 
> FROM tab WHERE stn1='20130101010100'
> AND ctype22 BETWEEN 2 AND 5   --result 45 row
> UNION ALL 
> SELECT ctype22 AS ctype, id20 AS id_all, list19 AS item_list 
> FROM space 
> WHERE stn1='20130102010100'
> AND ctype22 BETWEEN 2 AND 5   --result empty
> ) t1 JOIN colon t2 ON (t1.id_all = t2.id20 AND t2.list19 ='ITEM30') -- join matched 3
rows
> {code}
> expected result 
> ||ctype||id_all||list19||
> |2|104|ITEM30|
> |2|683|ITEM30|
> |1|970|ITEM30|
> but, actual result empty..
>  
> instead "union all" below query result is not empty case are work.
> {code:java}
> // 코드 자리 표시자
> set hive.vectorized.execution.enabled=FALSE;
> set hive.optimize.union.remove=FALSE;
> SELECT distinct t1.ctype, t1.id_all , t2.list19
> FROM (
> SELECT ctype22 AS ctype, id20 AS id_all, list19 AS item_list 
> FROM tab WHERE stn1='20130101010100'
> AND ctype22 BETWEEN 2 AND 5   --result 45 row
> UNION ALL 
> SELECT 1 AS ctype, '111' AS id_all, 'ITEM30' AS item_list --dummy row
> ) t1 JOIN colon t2 ON (t1.id_all = t2.id20 AND t2.list19 ='ITEM30') -- join matched 3
rows
> {code}
>  
> am i wrong properties or query?
>  
>  
> reference
>  # HIVE-12788
>  # HIVE-20319
>  
>  
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message