[
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)
