[jira] [Commented] (HIVE-3827) LATERAL VIEW doesn't work with union all statement

["cyril liao (JIRA)" <ji...@apache.org>]

    [ 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