[
https://issues.apache.org/jira/browse/HIVE-3827?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Carl Steinbach updated HIVE-3827:
---------------------------------
Labels: Correctness CorrectnessBug (was: CorrectnessBug)
> LATERAL VIEW with UNION ALL produces incorrect results
> ------------------------------------------------------
>
> 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, 1.2.1
> Environment: hive0.9.0 hadoop 0.20.205
> Reporter: cyril liao
> Labels: Correctness, CorrectnessBug
>
> 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 was sent by Atlassian JIRA
(v6.3.4#6332)
