hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Carl Steinbach (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-3827) LATERAL VIEW with UNION ALL produces incorrect results
Date Tue, 18 Oct 2016 22:38:58 GMT

     [ https://issues.apache.org/jira/browse/HIVE-3827?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Carl Steinbach updated HIVE-3827:
---------------------------------
    Summary: LATERAL VIEW with UNION ALL produces incorrect results  (was: LATERAL VIEW doesn't
work with union all statement)

> 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
>         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)

Mime
View raw message