[ https://issues.apache.org/jira/browse/HIVE-3827?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13539450#comment-13539450 ]
cyril liao commented on HIVE-3827: ---------------------------------- if i create a table named "tmp_tbl" as 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 and use the table "tmp_tbl" to select ,the result goes right. at the same time ,i do the same works under hive 0.7.1 , the result goes right too. > 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