You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Hajime Osako (JIRA)" <ji...@apache.org> on 2016/08/26 00:44:20 UTC
[jira] [Commented] (HIVE-3827) LATERAL VIEW doesn't work with union
all statement
[ https://issues.apache.org/jira/browse/HIVE-3827?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15438211#comment-15438211 ]
Hajime Osako commented on HIVE-3827:
------------------------------------
This seems reproducible with Hive 1.2.1.
Is anyone looking into this Jira?
Or supposed to be fixed?
> 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 was sent by Atlassian JIRA
(v6.3.4#6332)