hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "cyril liao (JIRA)" <j...@apache.org>
Subject [jira] [Created] (HIVE-3827) LATERAL VIEW doesn't work with union all statement
Date Fri, 21 Dec 2012 06:57:12 GMT
cyril liao created HIVE-3827:
--------------------------------

             Summary: LATERAL VIEW doesn't work with union all statement
                 Key: HIVE-3827
                 URL: https://issues.apache.org/jira/browse/HIVE-3827
             Project: Hive
          Issue Type: Bug
          Components: Query Processor
    Affects Versions: 0.9.0
         Environment: hive0.9.0 hadoop 0.20.205
            Reporter: cyril liao


LATER VIEW lose data working with union all.


query NO.1:
SELECT
1 as from_pid,
1 as to_pid,
cid as from_path,
(CASE WHEN pid=0 THEN cid ELSE pid END) as to_path,
0 as status
FROM
(SELECT union_map(c_map) AS c_map
FROM
(SELECT collect_map(id,parent_id)AS c_map
FROM
wl_channels
GROUP BY id,parent_id
)tmp
)tmp2
LATERAL VIEW recursion_concat(c_map) a AS cid, pid
this query returns about 10000 rows ,and their status is 0.

query NO.2:
select
a.from_pid as from_pid,
a.to_pid as to_pid, 
a.from_path as from_path,
a.to_path as to_path,
a.status as status
from wl_dc_channels a
where a.status <> 0
this query returns about 100 rows ,and their status is 1 or 2.

query NO.3:
select
from_pid,
to_pid,
from_path,
to_path,
status
from
(
SELECT
1 as from_pid,
1 as to_pid,
cid as from_path,
(CASE WHEN pid=0 THEN cid ELSE pid END) as to_path,
0 as status
FROM
(SELECT union_map(c_map) AS c_map
FROM
(SELECT collect_map(id,parent_id)AS c_map
FROM
wl_channels
GROUP BY id,parent_id
)tmp
)tmp2
LATERAL VIEW recursion_concat(c_map) a AS cid, pid
union all
select
a.from_pid as from_pid,
a.to_pid as to_pid, 
a.from_path as from_path,
a.to_path as to_path,
a.status as status
from wl_dc_channels a
where a.status <> 0
) unin_tbl
this query has the same result as query NO.2

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message