http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/join42.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/join42.q b/ql/src/test/queries/clientpositive/join42.q index a48c127..dc0f927 100644 --- a/ql/src/test/queries/clientpositive/join42.q +++ b/ql/src/test/queries/clientpositive/join42.q @@ -1,8 +1,8 @@ set hive.mapred.mode=nonstrict; create table L as select 4436 id; -create table LA as select 4436 loan_id, 4748 aid, 4415 pi_id; +create table LA_n11 as select 4436 loan_id, 4748 aid, 4415 pi_id; create table FR as select 4436 loan_id; -create table A as select 4748 id; +create table A_n11 as select 4748 id; create table PI as select 4415 id; create table acct as select 4748 aid, 10 acc_n, 122 brn; @@ -14,11 +14,11 @@ explain select acct.ACC_N, acct.brn FROM L -JOIN LA ON L.id = LA.loan_id +JOIN LA_n11 ON L.id = LA_n11.loan_id JOIN FR ON L.id = FR.loan_id -JOIN A ON LA.aid = A.id -JOIN PI ON PI.id = LA.pi_id -JOIN acct ON A.id = acct.aid +JOIN A_n11 ON LA_n11.aid = A_n11.id +JOIN PI ON PI.id = LA_n11.pi_id +JOIN acct ON A_n11.id = acct.aid WHERE L.id = 4436 and acct.brn is not null; @@ -27,11 +27,11 @@ select acct.ACC_N, acct.brn FROM L -JOIN LA ON L.id = LA.loan_id +JOIN LA_n11 ON L.id = LA_n11.loan_id JOIN FR ON L.id = FR.loan_id -JOIN A ON LA.aid = A.id -JOIN PI ON PI.id = LA.pi_id -JOIN acct ON A.id = acct.aid +JOIN A_n11 ON LA_n11.aid = A_n11.id +JOIN PI ON PI.id = LA_n11.pi_id +JOIN acct ON A_n11.id = acct.aid WHERE L.id = 4436 and acct.brn is not null;
http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/join44.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/join44.q b/ql/src/test/queries/clientpositive/join44.q index 69aa088..dc16df5 100644 --- a/ql/src/test/queries/clientpositive/join44.q +++ b/ql/src/test/queries/clientpositive/join44.q @@ -3,11 +3,11 @@ set hive.cbo.enable=false; -- SORT_QUERY_RESULTS -CREATE TABLE mytable(val1 INT, val2 INT, val3 INT); +CREATE TABLE mytable_n1(val1 INT, val2 INT, val3 INT); EXPLAIN SELECT * -FROM mytable src1, mytable src2 +FROM mytable_n1 src1, mytable_n1 src2 WHERE src1.val1=src2.val1 AND src1.val2 between 2450816 and 2451500 AND src2.val2 between 2450816 and 2451500; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/join46.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/join46.q b/ql/src/test/queries/clientpositive/join46.q index a661c0f..f40acd4 100644 --- a/ql/src/test/queries/clientpositive/join46.q +++ b/ql/src/test/queries/clientpositive/join46.q @@ -1,275 +1,275 @@ set hive.strict.checks.cartesian.product=false; set hive.join.emit.interval=2; -CREATE TABLE test1 (key INT, value INT, col_1 STRING); -INSERT INTO test1 VALUES (NULL, NULL, 'None'), (98, NULL, 'None'), +CREATE TABLE test1_n2 (key INT, value INT, col_1 STRING); +INSERT INTO test1_n2 VALUES (NULL, NULL, 'None'), (98, NULL, 'None'), (99, 0, 'Alice'), (99, 2, 'Mat'), (100, 1, 'Bob'), (101, 2, 'Car'); -CREATE TABLE test2 (key INT, value INT, col_2 STRING); -INSERT INTO test2 VALUES (102, 2, 'Del'), (103, 2, 'Ema'), +CREATE TABLE test2_n0 (key INT, value INT, col_2 STRING); +INSERT INTO test2_n0 VALUES (102, 2, 'Del'), (103, 2, 'Ema'), (104, 3, 'Fli'), (105, NULL, 'None'); -- Basic outer join EXPLAIN SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value); SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value); -- Conjunction with pred on multiple inputs and single inputs (left outer join) EXPLAIN SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value - AND test1.key between 100 and 102 - AND test2.key between 100 and 102); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + AND test1_n2.key between 100 and 102 + AND test2_n0.key between 100 and 102); SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value - AND test1.key between 100 and 102 - AND test2.key between 100 and 102); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + AND test1_n2.key between 100 and 102 + AND test2_n0.key between 100 and 102); -- Conjunction with pred on single inputs (left outer join) EXPLAIN SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.key between 100 and 102 - AND test2.key between 100 and 102); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.key between 100 and 102 + AND test2_n0.key between 100 and 102); SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.key between 100 and 102 - AND test2.key between 100 and 102); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.key between 100 and 102 + AND test2_n0.key between 100 and 102); -- Conjunction with pred on multiple inputs and none (left outer join) EXPLAIN SELECT * -FROM test1 RIGHT OUTER JOIN test2 -ON (test1.value=test2.value AND true); +FROM test1_n2 RIGHT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value AND true); SELECT * -FROM test1 RIGHT OUTER JOIN test2 -ON (test1.value=test2.value AND true); +FROM test1_n2 RIGHT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value AND true); -- Condition on one input (left outer join) EXPLAIN SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.key between 100 and 102); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.key between 100 and 102); SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.key between 100 and 102); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.key between 100 and 102); -- Disjunction with pred on multiple inputs and single inputs (left outer join) EXPLAIN SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value - OR test1.key between 100 and 102 - OR test2.key between 100 and 102); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102); SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value - OR test1.key between 100 and 102 - OR test2.key between 100 and 102); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102); -- Disjunction with pred on multiple inputs and left input (left outer join) EXPLAIN SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value - OR test1.key between 100 and 102); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test1_n2.key between 100 and 102); SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value - OR test1.key between 100 and 102); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test1_n2.key between 100 and 102); -- Disjunction with pred on multiple inputs and right input (left outer join) EXPLAIN SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value - OR test2.key between 100 and 102); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test2_n0.key between 100 and 102); SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value - OR test2.key between 100 and 102); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test2_n0.key between 100 and 102); -- Keys plus residual (left outer join) EXPLAIN SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value - AND (test1.key between 100 and 102 - OR test2.key between 100 and 102)); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + AND (test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102)); SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value - AND (test1.key between 100 and 102 - OR test2.key between 100 and 102)); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + AND (test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102)); -- Complex condition, projecting columns EXPLAIN SELECT col_1, col_2 -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value - OR test1.key=test2.key); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test1_n2.key=test2_n0.key); SELECT col_1, col_2 -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value - OR test1.key=test2.key); +FROM test1_n2 LEFT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test1_n2.key=test2_n0.key); -- Disjunction with pred on multiple inputs and single inputs (right outer join) EXPLAIN SELECT * -FROM test1 RIGHT OUTER JOIN test2 -ON (test1.value=test2.value - OR test1.key between 100 and 102 - OR test2.key between 100 and 102); +FROM test1_n2 RIGHT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102); SELECT * -FROM test1 RIGHT OUTER JOIN test2 -ON (test1.value=test2.value - OR test1.key between 100 and 102 - OR test2.key between 100 and 102); +FROM test1_n2 RIGHT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102); -- Disjunction with pred on multiple inputs and left input (right outer join) EXPLAIN SELECT * -FROM test1 RIGHT OUTER JOIN test2 -ON (test1.value=test2.value - OR test1.key between 100 and 102); +FROM test1_n2 RIGHT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test1_n2.key between 100 and 102); SELECT * -FROM test1 RIGHT OUTER JOIN test2 -ON (test1.value=test2.value - OR test1.key between 100 and 102); +FROM test1_n2 RIGHT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test1_n2.key between 100 and 102); -- Disjunction with pred on multiple inputs and right input (right outer join) EXPLAIN SELECT * -FROM test1 RIGHT OUTER JOIN test2 -ON (test1.value=test2.value - OR test2.key between 100 and 102); +FROM test1_n2 RIGHT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test2_n0.key between 100 and 102); SELECT * -FROM test1 RIGHT OUTER JOIN test2 -ON (test1.value=test2.value - OR test2.key between 100 and 102); +FROM test1_n2 RIGHT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test2_n0.key between 100 and 102); -- Keys plus residual (right outer join) EXPLAIN SELECT * -FROM test1 RIGHT OUTER JOIN test2 -ON (test1.value=test2.value - AND (test1.key between 100 and 102 - OR test2.key between 100 and 102)); +FROM test1_n2 RIGHT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + AND (test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102)); SELECT * -FROM test1 RIGHT OUTER JOIN test2 -ON (test1.value=test2.value - AND (test1.key between 100 and 102 - OR test2.key between 100 and 102)); +FROM test1_n2 RIGHT OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + AND (test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102)); -- Disjunction with pred on multiple inputs and single inputs (full outer join) EXPLAIN SELECT * -FROM test1 FULL OUTER JOIN test2 -ON (test1.value=test2.value - OR test1.key between 100 and 102 - OR test2.key between 100 and 102); +FROM test1_n2 FULL OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102); SELECT * -FROM test1 FULL OUTER JOIN test2 -ON (test1.value=test2.value - OR test1.key between 100 and 102 - OR test2.key between 100 and 102); +FROM test1_n2 FULL OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102); -- Disjunction with pred on multiple inputs and left input (full outer join) EXPLAIN SELECT * -FROM test1 FULL OUTER JOIN test2 -ON (test1.value=test2.value - OR test1.key between 100 and 102); +FROM test1_n2 FULL OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test1_n2.key between 100 and 102); SELECT * -FROM test1 FULL OUTER JOIN test2 -ON (test1.value=test2.value - OR test1.key between 100 and 102); +FROM test1_n2 FULL OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test1_n2.key between 100 and 102); -- Disjunction with pred on multiple inputs and right input (full outer join) EXPLAIN SELECT * -FROM test1 FULL OUTER JOIN test2 -ON (test1.value=test2.value - OR test2.key between 100 and 102); +FROM test1_n2 FULL OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test2_n0.key between 100 and 102); SELECT * -FROM test1 FULL OUTER JOIN test2 -ON (test1.value=test2.value - OR test2.key between 100 and 102); +FROM test1_n2 FULL OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + OR test2_n0.key between 100 and 102); -- Keys plus residual (full outer join) EXPLAIN SELECT * -FROM test1 FULL OUTER JOIN test2 -ON (test1.value=test2.value - AND (test1.key between 100 and 102 - OR test2.key between 100 and 102)); +FROM test1_n2 FULL OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + AND (test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102)); SELECT * -FROM test1 FULL OUTER JOIN test2 -ON (test1.value=test2.value - AND (test1.key between 100 and 102 - OR test2.key between 100 and 102)); +FROM test1_n2 FULL OUTER JOIN test2_n0 +ON (test1_n2.value=test2_n0.value + AND (test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102)); -- Mixed ( FOJ (ROJ, LOJ) ) EXPLAIN SELECT * FROM ( - SELECT test1.key AS key1, test1.value AS value1, test1.col_1 AS col_1, - test2.key AS key2, test2.value AS value2, test2.col_2 AS col_2 - FROM test1 RIGHT OUTER JOIN test2 - ON (test1.value=test2.value - AND (test1.key between 100 and 102 - OR test2.key between 100 and 102)) + SELECT test1_n2.key AS key1, test1_n2.value AS value1, test1_n2.col_1 AS col_1, + test2_n0.key AS key2, test2_n0.value AS value2, test2_n0.col_2 AS col_2 + FROM test1_n2 RIGHT OUTER JOIN test2_n0 + ON (test1_n2.value=test2_n0.value + AND (test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102)) ) sq1 FULL OUTER JOIN ( - SELECT test1.key AS key3, test1.value AS value3, test1.col_1 AS col_3, - test2.key AS key4, test2.value AS value4, test2.col_2 AS col_4 - FROM test1 LEFT OUTER JOIN test2 - ON (test1.value=test2.value - AND (test1.key between 100 and 102 - OR test2.key between 100 and 102)) + SELECT test1_n2.key AS key3, test1_n2.value AS value3, test1_n2.col_1 AS col_3, + test2_n0.key AS key4, test2_n0.value AS value4, test2_n0.col_2 AS col_4 + FROM test1_n2 LEFT OUTER JOIN test2_n0 + ON (test1_n2.value=test2_n0.value + AND (test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102)) ) sq2 ON (sq1.value1 is null or sq2.value4 is null and sq2.value3 != sq1.value2); SELECT * FROM ( - SELECT test1.key AS key1, test1.value AS value1, test1.col_1 AS col_1, - test2.key AS key2, test2.value AS value2, test2.col_2 AS col_2 - FROM test1 RIGHT OUTER JOIN test2 - ON (test1.value=test2.value - AND (test1.key between 100 and 102 - OR test2.key between 100 and 102)) + SELECT test1_n2.key AS key1, test1_n2.value AS value1, test1_n2.col_1 AS col_1, + test2_n0.key AS key2, test2_n0.value AS value2, test2_n0.col_2 AS col_2 + FROM test1_n2 RIGHT OUTER JOIN test2_n0 + ON (test1_n2.value=test2_n0.value + AND (test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102)) ) sq1 FULL OUTER JOIN ( - SELECT test1.key AS key3, test1.value AS value3, test1.col_1 AS col_3, - test2.key AS key4, test2.value AS value4, test2.col_2 AS col_4 - FROM test1 LEFT OUTER JOIN test2 - ON (test1.value=test2.value - AND (test1.key between 100 and 102 - OR test2.key between 100 and 102)) + SELECT test1_n2.key AS key3, test1_n2.value AS value3, test1_n2.col_1 AS col_3, + test2_n0.key AS key4, test2_n0.value AS value4, test2_n0.col_2 AS col_4 + FROM test1_n2 LEFT OUTER JOIN test2_n0 + ON (test1_n2.value=test2_n0.value + AND (test1_n2.key between 100 and 102 + OR test2_n0.key between 100 and 102)) ) sq2 ON (sq1.value1 is null or sq2.value4 is null and sq2.value3 != sq1.value2); http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/join5.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/join5.q b/ql/src/test/queries/clientpositive/join5.q index 0cb12d5..f7cf954 100644 --- a/ql/src/test/queries/clientpositive/join5.q +++ b/ql/src/test/queries/clientpositive/join5.q @@ -3,7 +3,7 @@ set hive.mapred.mode=nonstrict; -- SORT_QUERY_RESULTS -CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n126(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE; EXPLAIN FROM ( @@ -18,7 +18,7 @@ FROM ( ON (a.c1 = b.c3) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4; +INSERT OVERWRITE TABLE dest1_n126 SELECT c.c1, c.c2, c.c3, c.c4; FROM ( FROM @@ -32,6 +32,6 @@ FROM ( ON (a.c1 = b.c3) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4; +INSERT OVERWRITE TABLE dest1_n126 SELECT c.c1, c.c2, c.c3, c.c4; -SELECT dest1.* FROM dest1; +SELECT dest1_n126.* FROM dest1_n126; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/join6.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/join6.q b/ql/src/test/queries/clientpositive/join6.q index 4fe1885..6b3095f 100644 --- a/ql/src/test/queries/clientpositive/join6.q +++ b/ql/src/test/queries/clientpositive/join6.q @@ -1,7 +1,7 @@ --! qt:dataset:src1 --! qt:dataset:src set hive.mapred.mode=nonstrict; -CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n156(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE; -- SORT_QUERY_RESULTS @@ -18,7 +18,7 @@ FROM ( ON (a.c1 = b.c3) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4; +INSERT OVERWRITE TABLE dest1_n156 SELECT c.c1, c.c2, c.c3, c.c4; FROM ( FROM @@ -32,7 +32,7 @@ FROM ( ON (a.c1 = b.c3) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4; +INSERT OVERWRITE TABLE dest1_n156 SELECT c.c1, c.c2, c.c3, c.c4; -SELECT dest1.* FROM dest1; +SELECT dest1_n156.* FROM dest1_n156; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/join7.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/join7.q b/ql/src/test/queries/clientpositive/join7.q index 1047707..d3a22a6 100644 --- a/ql/src/test/queries/clientpositive/join7.q +++ b/ql/src/test/queries/clientpositive/join7.q @@ -1,7 +1,7 @@ --! qt:dataset:src1 --! qt:dataset:src set hive.mapred.mode=nonstrict; -CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING, c5 INT, c6 STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n17(c1 INT, c2 STRING, c3 INT, c4 STRING, c5 INT, c6 STRING) STORED AS TEXTFILE; -- SORT_QUERY_RESULTS @@ -23,7 +23,7 @@ FROM ( ON (a.c1 = c.c5) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4, c.c5 AS c5, c.c6 AS c6 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6; +INSERT OVERWRITE TABLE dest1_n17 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6; FROM ( FROM @@ -42,6 +42,6 @@ FROM ( ON (a.c1 = c.c5) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4, c.c5 AS c5, c.c6 AS c6 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6; +INSERT OVERWRITE TABLE dest1_n17 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6; -SELECT dest1.* FROM dest1; +SELECT dest1_n17.* FROM dest1_n17; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/join8.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/join8.q b/ql/src/test/queries/clientpositive/join8.q index 292c07c..3561bb6 100644 --- a/ql/src/test/queries/clientpositive/join8.q +++ b/ql/src/test/queries/clientpositive/join8.q @@ -3,7 +3,7 @@ set hive.mapred.mode=nonstrict; -- SORT_QUERY_RESULTS -CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n173(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE; EXPLAIN FROM ( @@ -18,7 +18,7 @@ FROM ( ON (a.c1 = b.c3) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4 where c.c3 IS NULL AND c.c1 IS NOT NULL; +INSERT OVERWRITE TABLE dest1_n173 SELECT c.c1, c.c2, c.c3, c.c4 where c.c3 IS NULL AND c.c1 IS NOT NULL; FROM ( FROM @@ -32,6 +32,6 @@ FROM ( ON (a.c1 = b.c3) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4 where c.c3 IS NULL AND c.c1 IS NOT NULL; +INSERT OVERWRITE TABLE dest1_n173 SELECT c.c1, c.c2, c.c3, c.c4 where c.c3 IS NULL AND c.c1 IS NOT NULL; -SELECT dest1.* FROM dest1; +SELECT dest1_n173.* FROM dest1_n173; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/join9.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/join9.q b/ql/src/test/queries/clientpositive/join9.q index 0c17b04..670f2fd 100644 --- a/ql/src/test/queries/clientpositive/join9.q +++ b/ql/src/test/queries/clientpositive/join9.q @@ -4,13 +4,13 @@ set hive.mapred.mode=nonstrict; -- SORT_QUERY_RESULTS -CREATE TABLE dest1(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n39(key INT, value STRING) STORED AS TEXTFILE; EXPLAIN EXTENDED FROM srcpart src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.key, src2.value where src1.ds = '2008-04-08' and src1.hr = '12'; +INSERT OVERWRITE TABLE dest1_n39 SELECT src1.key, src2.value where src1.ds = '2008-04-08' and src1.hr = '12'; FROM srcpart src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.key, src2.value where src1.ds = '2008-04-08' and src1.hr = '12'; +INSERT OVERWRITE TABLE dest1_n39 SELECT src1.key, src2.value where src1.ds = '2008-04-08' and src1.hr = '12'; -SELECT dest1.* FROM dest1; +SELECT dest1_n39.* FROM dest1_n39; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual1.q b/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual1.q index 2be8dcc..174dc36 100644 --- a/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual1.q +++ b/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual1.q @@ -1,6 +1,6 @@ --! qt:dataset:part set hive.mapred.mode=nonstrict; -create table part2( +create table part2_n0( p2_partkey INT, p2_name STRING, p2_mfgr STRING, @@ -12,7 +12,7 @@ create table part2( p2_comment STRING ); -create table part3( +create table part3_n0( p3_partkey INT, p3_name STRING, p3_mfgr STRING, @@ -25,13 +25,13 @@ create table part3( ); explain select * -from part p1 join part2 p2 join part3 p3 on p1.p_name = p2_name and p2_name = p3_name; +from part p1 join part2_n0 p2 join part3_n0 p3 on p1.p_name = p2_name and p2_name = p3_name; explain select * -from part p1 join part2 p2 join part3 p3 on p2_name = p1.p_name and p3_name = p2_name; +from part p1 join part2_n0 p2 join part3_n0 p3 on p2_name = p1.p_name and p3_name = p2_name; explain select * -from part p1 join part2 p2 join part3 p3 on p2_partkey + p_partkey = p1.p_partkey and p3_name = p2_name; +from part p1 join part2_n0 p2 join part3_n0 p3 on p2_partkey + p_partkey = p1.p_partkey and p3_name = p2_name; explain select * -from part p1 join part2 p2 join part3 p3 on p2_partkey = 1 and p3_name = p2_name; +from part p1 join part2_n0 p2 join part3_n0 p3 on p2_partkey = 1 and p3_name = p2_name; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual3.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual3.q b/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual3.q index 5f0c985..b18745e 100644 --- a/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual3.q +++ b/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual3.q @@ -1,6 +1,6 @@ --! qt:dataset:part set hive.mapred.mode=nonstrict; -create table part2( +create table part2_n5( p2_partkey INT, p2_name STRING, p2_mfgr STRING, @@ -12,7 +12,7 @@ create table part2( p2_comment STRING ); -create table part3( +create table part3_n2( p3_partkey INT, p3_name STRING, p3_mfgr STRING, @@ -25,17 +25,17 @@ create table part3( ); explain select * -from part p1 join part2 p2 join part3 p3 +from part p1 join part2_n5 p2 join part3_n2 p3 where p1.p_name = p2_name and p2_name = p3_name; explain select * -from part p1 join part2 p2 join part3 p3 +from part p1 join part2_n5 p2 join part3_n2 p3 where p2_name = p1.p_name and p3_name = p2_name; explain select * -from part p1 join part2 p2 join part3 p3 +from part p1 join part2_n5 p2 join part3_n2 p3 where p2_partkey + p1.p_partkey = p1.p_partkey and p3_name = p2_name; explain select * -from part p1 join part2 p2 join part3 p3 +from part p1 join part2_n5 p2 join part3_n2 p3 where p2_partkey = 1 and p3_name = p2_name; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual4.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual4.q b/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual4.q index 9bbecca..320ebfb 100644 --- a/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual4.q +++ b/ql/src/test/queries/clientpositive/join_cond_pushdown_unqual4.q @@ -1,6 +1,6 @@ --! qt:dataset:part set hive.mapred.mode=nonstrict; -create table part2( +create table part2_n4( p2_partkey INT, p2_name STRING, p2_mfgr STRING, @@ -12,7 +12,7 @@ create table part2( p2_comment STRING ); -create table part3( +create table part3_n1( p3_partkey INT, p3_name STRING, p3_mfgr STRING, @@ -25,10 +25,10 @@ create table part3( ); explain select * -from part p1 join part2 p2 join part3 p3 on p1.p_name = p2_name join part p4 +from part p1 join part2_n4 p2 join part3_n1 p3 on p1.p_name = p2_name join part p4 where p2_name = p3_name and p1.p_name = p4.p_name; explain select * -from part p1 join part2 p2 join part3 p3 on p2_name = p1.p_name join part p4 +from part p1 join part2_n4 p2 join part3_n1 p3 on p2_name = p1.p_name join part p4 where p2_name = p3_name and p1.p_partkey = p4.p_partkey and p1.p_partkey = p2_partkey; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/join_emit_interval.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/join_emit_interval.q b/ql/src/test/queries/clientpositive/join_emit_interval.q index c59d97d..1ebbff1 100644 --- a/ql/src/test/queries/clientpositive/join_emit_interval.q +++ b/ql/src/test/queries/clientpositive/join_emit_interval.q @@ -1,31 +1,31 @@ set hive.strict.checks.cartesian.product=false; set hive.join.emit.interval=1; -CREATE TABLE test1 (key INT, value INT, col_1 STRING); -INSERT INTO test1 VALUES (NULL, NULL, 'None'), (98, NULL, 'None'), +CREATE TABLE test1_n7 (key INT, value INT, col_1 STRING); +INSERT INTO test1_n7 VALUES (NULL, NULL, 'None'), (98, NULL, 'None'), (99, 0, 'Alice'), (99, 2, 'Mat'), (100, 1, 'Bob'), (101, 2, 'Car'); -CREATE TABLE test2 (key INT, value INT, col_2 STRING); -INSERT INTO test2 VALUES (102, 2, 'Del'), (103, 2, 'Ema'), +CREATE TABLE test2_n4 (key INT, value INT, col_2 STRING); +INSERT INTO test2_n4 VALUES (102, 2, 'Del'), (103, 2, 'Ema'), (104, 3, 'Fli'), (105, NULL, 'None'); -- Equi-condition and condition on one input (left outer join) EXPLAIN SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value AND test1.key between 100 and 102); +FROM test1_n7 LEFT OUTER JOIN test2_n4 +ON (test1_n7.value=test2_n4.value AND test1_n7.key between 100 and 102); SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value AND test1.key between 100 and 102); +FROM test1_n7 LEFT OUTER JOIN test2_n4 +ON (test1_n7.value=test2_n4.value AND test1_n7.key between 100 and 102); -- Condition on one input (left outer join) EXPLAIN SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.key between 100 and 102); +FROM test1_n7 LEFT OUTER JOIN test2_n4 +ON (test1_n7.key between 100 and 102); SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.key between 100 and 102); +FROM test1_n7 LEFT OUTER JOIN test2_n4 +ON (test1_n7.key between 100 and 102); http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/join_filters.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/join_filters.q b/ql/src/test/queries/clientpositive/join_filters.q index 0113c40..3469dfc 100644 --- a/ql/src/test/queries/clientpositive/join_filters.q +++ b/ql/src/test/queries/clientpositive/join_filters.q @@ -1,156 +1,156 @@ set hive.mapred.mode=nonstrict; -- SORT_AND_HASH_QUERY_RESULTS -CREATE TABLE myinput1(key int, value int); -LOAD DATA LOCAL INPATH '../../data/files/in3.txt' INTO TABLE myinput1; - -SELECT * FROM myinput1 a JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a LEFT OUTER JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a RIGHT OUTER JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a FULL OUTER JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT * FROM myinput1 a JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a JOIN myinput1 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT * FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.key and a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT * FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key=b.key and a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT * FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT * from myinput1 a LEFT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT * from myinput1 a RIGHT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT * FROM myinput1 a LEFT OUTER JOIN myinput1 b RIGHT OUTER JOIN myinput1 c ON a.value = b.value and b.value = c.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; -SELECT * from myinput1 a LEFT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT * from myinput1 a RIGHT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT * FROM myinput1 a LEFT OUTER JOIN myinput1 b RIGHT OUTER JOIN myinput1 c ON a.value = b.value and b.key = c.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; - -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a JOIN myinput1 b ON a.value = b.value and a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a JOIN myinput1 b ON a.value = b.value and a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -CREATE TABLE smb_input1(key int, value int) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS; -CREATE TABLE smb_input2(key int, value int) CLUSTERED BY (value) SORTED BY (value) INTO 2 BUCKETS; -LOAD DATA LOCAL INPATH '../../data/files/in/000000_0' into table smb_input1; -LOAD DATA LOCAL INPATH '../../data/files/in/000001_0' into table smb_input1; -LOAD DATA LOCAL INPATH '../../data/files/in/000000_0' into table smb_input2; -LOAD DATA LOCAL INPATH '../../data/files/in/000001_0' into table smb_input2; +CREATE TABLE myinput1_n8(key int, value int); +LOAD DATA LOCAL INPATH '../../data/files/in3.txt' INTO TABLE myinput1_n8; + +SELECT * FROM myinput1_n8 a JOIN myinput1_n8 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a FULL OUTER JOIN myinput1_n8 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON a.key = b.key and a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON a.key=b.key and a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT * FROM myinput1_n8 a FULL OUTER JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a FULL OUTER JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a FULL OUTER JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a FULL OUTER JOIN myinput1_n8 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT * from myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1_n8 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT * from myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1_n8 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b RIGHT OUTER JOIN myinput1_n8 c ON a.value = b.value and b.value = c.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; +SELECT * from myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1_n8 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT * from myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1_n8 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b RIGHT OUTER JOIN myinput1_n8 c ON a.value = b.value and b.key = c.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; + +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a JOIN myinput1_n8 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.value = b.value and a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a JOIN myinput1_n8 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.value = b.value and a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +CREATE TABLE smb_input1_n3(key int, value int) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS; +CREATE TABLE smb_input2_n3(key int, value int) CLUSTERED BY (value) SORTED BY (value) INTO 2 BUCKETS; +LOAD DATA LOCAL INPATH '../../data/files/in/000000_0' into table smb_input1_n3; +LOAD DATA LOCAL INPATH '../../data/files/in/000001_0' into table smb_input1_n3; +LOAD DATA LOCAL INPATH '../../data/files/in/000000_0' into table smb_input2_n3; +LOAD DATA LOCAL INPATH '../../data/files/in/000001_0' into table smb_input2_n3; SET hive.optimize.bucketmapjoin = true; SET hive.optimize.bucketmapjoin.sortedmerge = true; SET hive.input.format = org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; -SELECT /*+ MAPJOIN(a) */ * FROM smb_input1 a JOIN smb_input1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM smb_input1 a JOIN smb_input2 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM smb_input2 a JOIN smb_input2 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM smb_input1 a JOIN smb_input1 b ON a.key = b.key AND a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT /*+ MAPJOIN(b) */ * FROM smb_input1 a JOIN smb_input1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM smb_input1 a JOIN smb_input2 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM smb_input2 a JOIN smb_input2 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM smb_input2 a JOIN smb_input2 b ON a.key = b.key AND a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT /*+ MAPJOIN(b) */ * FROM smb_input1 a LEFT OUTER JOIN smb_input1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM smb_input1 a LEFT OUTER JOIN smb_input2 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM smb_input2 a LEFT OUTER JOIN smb_input2 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT /*+ MAPJOIN(a) */ * FROM smb_input1 a RIGHT OUTER JOIN smb_input1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM smb_input1 a RIGHT OUTER JOIN smb_input2 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM smb_input2 a RIGHT OUTER JOIN smb_input2 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT * FROM myinput1 a JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a LEFT OUTER JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a RIGHT OUTER JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a FULL OUTER JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT * FROM myinput1 a JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a JOIN myinput1 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT * FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.key and a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT * FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key=b.key and a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT * FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT * FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT * from myinput1 a LEFT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT * from myinput1 a RIGHT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT * FROM myinput1 a LEFT OUTER JOIN myinput1 b RIGHT OUTER JOIN myinput1 c ON a.value = b.value and b.value = c.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; -SELECT * from myinput1 a LEFT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT * from myinput1 a RIGHT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT * FROM myinput1 a LEFT OUTER JOIN myinput1 b RIGHT OUTER JOIN myinput1 c ON a.value = b.value and b.key = c.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; - -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a JOIN myinput1 b ON a.value = b.value and a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a JOIN myinput1 b ON a.value = b.value and a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT /*+ MAPJOIN(a) */ * FROM smb_input1 a JOIN smb_input1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM smb_input1 a JOIN smb_input2 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM smb_input2 a JOIN smb_input2 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM smb_input1 a JOIN smb_input1 b ON a.key = b.key AND a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT /*+ MAPJOIN(b) */ * FROM smb_input1 a JOIN smb_input1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM smb_input1 a JOIN smb_input2 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM smb_input2 a JOIN smb_input2 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM smb_input2 a JOIN smb_input2 b ON a.key = b.key AND a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT /*+ MAPJOIN(b) */ * FROM smb_input1 a LEFT OUTER JOIN smb_input1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM smb_input1 a LEFT OUTER JOIN smb_input2 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(b) */ * FROM smb_input2 a LEFT OUTER JOIN smb_input2 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT /*+ MAPJOIN(a) */ * FROM smb_input1 a RIGHT OUTER JOIN smb_input1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM smb_input1 a RIGHT OUTER JOIN smb_input2 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT /*+ MAPJOIN(a) */ * FROM smb_input2 a RIGHT OUTER JOIN smb_input2 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM smb_input1_n3 a JOIN smb_input1_n3 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM smb_input1_n3 a JOIN smb_input2_n3 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM smb_input2_n3 a JOIN smb_input2_n3 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM smb_input1_n3 a JOIN smb_input1_n3 b ON a.key = b.key AND a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT /*+ MAPJOIN(b) */ * FROM smb_input1_n3 a JOIN smb_input1_n3 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM smb_input1_n3 a JOIN smb_input2_n3 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM smb_input2_n3 a JOIN smb_input2_n3 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM smb_input2_n3 a JOIN smb_input2_n3 b ON a.key = b.key AND a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT /*+ MAPJOIN(b) */ * FROM smb_input1_n3 a LEFT OUTER JOIN smb_input1_n3 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM smb_input1_n3 a LEFT OUTER JOIN smb_input2_n3 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM smb_input2_n3 a LEFT OUTER JOIN smb_input2_n3 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT /*+ MAPJOIN(a) */ * FROM smb_input1_n3 a RIGHT OUTER JOIN smb_input1_n3 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM smb_input1_n3 a RIGHT OUTER JOIN smb_input2_n3 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM smb_input2_n3 a RIGHT OUTER JOIN smb_input2_n3 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT * FROM myinput1_n8 a JOIN myinput1_n8 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a FULL OUTER JOIN myinput1_n8 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON a.key = b.key and a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON a.key=b.key and a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT * FROM myinput1_n8 a FULL OUTER JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a FULL OUTER JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a FULL OUTER JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT * FROM myinput1_n8 a FULL OUTER JOIN myinput1_n8 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT * from myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1_n8 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT * from myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1_n8 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b RIGHT OUTER JOIN myinput1_n8 c ON a.value = b.value and b.value = c.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; +SELECT * from myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1_n8 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT * from myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1_n8 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b RIGHT OUTER JOIN myinput1_n8 c ON a.value = b.value and b.key = c.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; + +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a JOIN myinput1_n8 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.value = b.value and a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a JOIN myinput1_n8 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a JOIN myinput1_n8 b ON a.value = b.value and a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM myinput1_n8 a LEFT OUTER JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM myinput1_n8 a RIGHT OUTER JOIN myinput1_n8 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT /*+ MAPJOIN(a) */ * FROM smb_input1_n3 a JOIN smb_input1_n3 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM smb_input1_n3 a JOIN smb_input2_n3 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM smb_input2_n3 a JOIN smb_input2_n3 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM smb_input1_n3 a JOIN smb_input1_n3 b ON a.key = b.key AND a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT /*+ MAPJOIN(b) */ * FROM smb_input1_n3 a JOIN smb_input1_n3 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM smb_input1_n3 a JOIN smb_input2_n3 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM smb_input2_n3 a JOIN smb_input2_n3 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM smb_input2_n3 a JOIN smb_input2_n3 b ON a.key = b.key AND a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT /*+ MAPJOIN(b) */ * FROM smb_input1_n3 a LEFT OUTER JOIN smb_input1_n3 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM smb_input1_n3 a LEFT OUTER JOIN smb_input2_n3 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(b) */ * FROM smb_input2_n3 a LEFT OUTER JOIN smb_input2_n3 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT /*+ MAPJOIN(a) */ * FROM smb_input1_n3 a RIGHT OUTER JOIN smb_input1_n3 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM smb_input1_n3 a RIGHT OUTER JOIN smb_input2_n3 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT /*+ MAPJOIN(a) */ * FROM smb_input2_n3 a RIGHT OUTER JOIN smb_input2_n3 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/join_filters_overlap.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/join_filters_overlap.q b/ql/src/test/queries/clientpositive/join_filters_overlap.q index a361024..ffe7db0 100644 --- a/ql/src/test/queries/clientpositive/join_filters_overlap.q +++ b/ql/src/test/queries/clientpositive/join_filters_overlap.q @@ -3,28 +3,28 @@ set hive.mapred.mode=nonstrict; -- SORT_QUERY_RESULTS -- HIVE-3411 Filter predicates on outer join overlapped on single alias is not handled properly -create table a as SELECT 100 as key, a.value as value FROM src LATERAL VIEW explode(array(40, 50, 60)) a as value limit 3; +create table a_n4 as SELECT 100 as key, a_n4.value as value FROM src LATERAL VIEW explode(array(40, 50, 60)) a_n4 as value limit 3; --- overlap on a -explain extended select * from a left outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (a.key=c.key AND a.value=60 AND c.value=60); -select * from a left outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (a.key=c.key AND a.value=60 AND c.value=60); -select /*+ MAPJOIN(b,c)*/ * from a left outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (a.key=c.key AND a.value=60 AND c.value=60); +-- overlap on a_n4 +explain extended select * from a_n4 left outer join a_n4 b on (a_n4.key=b.key AND a_n4.value=50 AND b.value=50) left outer join a_n4 c on (a_n4.key=c.key AND a_n4.value=60 AND c.value=60); +select * from a_n4 left outer join a_n4 b on (a_n4.key=b.key AND a_n4.value=50 AND b.value=50) left outer join a_n4 c on (a_n4.key=c.key AND a_n4.value=60 AND c.value=60); +select /*+ MAPJOIN(b,c)*/ * from a_n4 left outer join a_n4 b on (a_n4.key=b.key AND a_n4.value=50 AND b.value=50) left outer join a_n4 c on (a_n4.key=c.key AND a_n4.value=60 AND c.value=60); -- overlap on b -explain extended select * from a right outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (b.key=c.key AND b.value=60 AND c.value=60); -select * from a right outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (b.key=c.key AND b.value=60 AND c.value=60); -select /*+ MAPJOIN(a,c)*/ * from a right outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (b.key=c.key AND b.value=60 AND c.value=60); +explain extended select * from a_n4 right outer join a_n4 b on (a_n4.key=b.key AND a_n4.value=50 AND b.value=50) left outer join a_n4 c on (b.key=c.key AND b.value=60 AND c.value=60); +select * from a_n4 right outer join a_n4 b on (a_n4.key=b.key AND a_n4.value=50 AND b.value=50) left outer join a_n4 c on (b.key=c.key AND b.value=60 AND c.value=60); +select /*+ MAPJOIN(a_n4,c)*/ * from a_n4 right outer join a_n4 b on (a_n4.key=b.key AND a_n4.value=50 AND b.value=50) left outer join a_n4 c on (b.key=c.key AND b.value=60 AND c.value=60); -- overlap on b with two filters for each -explain extended select * from a right outer join a b on (a.key=b.key AND a.value=50 AND b.value=50 AND b.value>10) left outer join a c on (b.key=c.key AND b.value=60 AND b.value>20 AND c.value=60); -select * from a right outer join a b on (a.key=b.key AND a.value=50 AND b.value=50 AND b.value>10) left outer join a c on (b.key=c.key AND b.value=60 AND b.value>20 AND c.value=60); -select /*+ MAPJOIN(a,c)*/ * from a right outer join a b on (a.key=b.key AND a.value=50 AND b.value=50 AND b.value>10) left outer join a c on (b.key=c.key AND b.value=60 AND b.value>20 AND c.value=60); +explain extended select * from a_n4 right outer join a_n4 b on (a_n4.key=b.key AND a_n4.value=50 AND b.value=50 AND b.value>10) left outer join a_n4 c on (b.key=c.key AND b.value=60 AND b.value>20 AND c.value=60); +select * from a_n4 right outer join a_n4 b on (a_n4.key=b.key AND a_n4.value=50 AND b.value=50 AND b.value>10) left outer join a_n4 c on (b.key=c.key AND b.value=60 AND b.value>20 AND c.value=60); +select /*+ MAPJOIN(a_n4,c)*/ * from a_n4 right outer join a_n4 b on (a_n4.key=b.key AND a_n4.value=50 AND b.value=50 AND b.value>10) left outer join a_n4 c on (b.key=c.key AND b.value=60 AND b.value>20 AND c.value=60); --- overlap on a, b -explain extended select * from a full outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (b.key=c.key AND b.value=60 AND c.value=60) left outer join a d on (a.key=d.key AND a.value=40 AND d.value=40); -select * from a full outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (b.key=c.key AND b.value=60 AND c.value=60) left outer join a d on (a.key=d.key AND a.value=40 AND d.value=40); +-- overlap on a_n4, b +explain extended select * from a_n4 full outer join a_n4 b on (a_n4.key=b.key AND a_n4.value=50 AND b.value=50) left outer join a_n4 c on (b.key=c.key AND b.value=60 AND c.value=60) left outer join a_n4 d on (a_n4.key=d.key AND a_n4.value=40 AND d.value=40); +select * from a_n4 full outer join a_n4 b on (a_n4.key=b.key AND a_n4.value=50 AND b.value=50) left outer join a_n4 c on (b.key=c.key AND b.value=60 AND c.value=60) left outer join a_n4 d on (a_n4.key=d.key AND a_n4.value=40 AND d.value=40); --- triple overlap on a -explain extended select * from a left outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (a.key=c.key AND a.value=60 AND c.value=60) left outer join a d on (a.key=d.key AND a.value=40 AND d.value=40); -select * from a left outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (a.key=c.key AND a.value=60 AND c.value=60) left outer join a d on (a.key=d.key AND a.value=40 AND d.value=40); -select /*+ MAPJOIN(b,c, d)*/ * from a left outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (a.key=c.key AND a.value=60 AND c.value=60) left outer join a d on (a.key=d.key AND a.value=40 AND d.value=40); +-- triple overlap on a_n4 +explain extended select * from a_n4 left outer join a_n4 b on (a_n4.key=b.key AND a_n4.value=50 AND b.value=50) left outer join a_n4 c on (a_n4.key=c.key AND a_n4.value=60 AND c.value=60) left outer join a_n4 d on (a_n4.key=d.key AND a_n4.value=40 AND d.value=40); +select * from a_n4 left outer join a_n4 b on (a_n4.key=b.key AND a_n4.value=50 AND b.value=50) left outer join a_n4 c on (a_n4.key=c.key AND a_n4.value=60 AND c.value=60) left outer join a_n4 d on (a_n4.key=d.key AND a_n4.value=40 AND d.value=40); +select /*+ MAPJOIN(b,c, d)*/ * from a_n4 left outer join a_n4 b on (a_n4.key=b.key AND a_n4.value=50 AND b.value=50) left outer join a_n4 c on (a_n4.key=c.key AND a_n4.value=60 AND c.value=60) left outer join a_n4 d on (a_n4.key=d.key AND a_n4.value=40 AND d.value=40);
