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