[ 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