[
https://issues.apache.org/jira/browse/HIVE-3104?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13536934#comment-13536934
]
cyril liao commented on HIVE-3104:
----------------------------------
LATER VIEW doesn't work with UNION ALL too.
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 there 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 there 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
> Predicate pushdown doesn't work with multi-insert statements using LATERAL
> VIEW
> -------------------------------------------------------------------------------
>
> Key: HIVE-3104
> URL: https://issues.apache.org/jira/browse/HIVE-3104
> Project: Hive
> Issue Type: Bug
> Components: CLI
> Affects Versions: 0.9.0
> Environment: Apache Hive 0.9.0, Apache Hadoop 0.20.205.0
> Reporter: Mark Grover
>
> Predicate pushdown seems to work for single-insert queries using LATERAL
> VIEW. It also seems to work for multi-insert queries *not* using LATERAL
> VIEW. However, it doesn't work for multi-insert queries using LATERAL VIEW.
> Here are some examples. In the below examples, I make use of the fact that a
> query with no partition filtering when run under "hive.mapred.mode=strict"
> fails.
> --Table creation and population
> DROP TABLE IF EXISTS test;
> CREATE TABLE test (col1 array<int>, col2 int) PARTITIONED BY (part_col int);
> INSERT OVERWRITE TABLE test PARTITION (part_col=1) SELECT array(1,2),
> count(*) FROM test;
> INSERT OVERWRITE TABLE test PARTITION (part_col=2) SELECT array(2,4,6),
> count(*) FROM test;
> -- Query 1
> -- This succeeds (using LATERAL VIEW with single insert)
> set hive.mapred.mode=strict;
> FROM partition_test
> LATERAL VIEW explode(col1) tmp AS exp_col1
> INSERT OVERWRITE DIRECTORY '/test/1'
> SELECT exp_col1
> WHERE (part_col=2);
> -- Query 2
> -- This succeeds (NOT using LATERAL VIEW with multi-insert)
> set hive.mapred.mode=strict;
> FROM partition_test
> INSERT OVERWRITE DIRECTORY '/test/1'
> SELECT col1
> WHERE (part_col=2)
> INSERT OVERWRITE DIRECTORY '/test/2'
> SELECT col1
> WHERE (part_col=2);
> -- Query 3
> -- This fails (using LATERAL VIEW with multi-insert)
> set hive.mapred.mode=strict;
> FROM partition_test
> LATERAL VIEW explode(col1) tmp AS exp_col1
> INSERT OVERWRITE DIRECTORY '/test/1'
> SELECT exp_col1
> WHERE (part_col=2)
> INSERT OVERWRITE DIRECTORY '/test/2'
> SELECT exp_col1
> WHERE (part_col=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
