| --! qt:dataset:src1 |
| --! qt:dataset:src |
| set hive.mapred.mode=nonstrict; |
| set hive.auto.convert.join=false; |
| |
| drop table if exists src_10; |
| drop table if exists src1_10; |
| |
| create table src_10 as select * from src limit 2; |
| create table src1_10 as select * from src1 limit 2; |
| |
| select key, count(*) from src1_10 group by key; |
| select key, count(*) from src_10 group by key; |
| -- Right Outer Join should be handled. |
| |
| EXPLAIN |
| SELECT SUM(HASH(key1)), SUM(HASH(cnt1)), SUM(HASH(key2)), SUM(HASH(cnt2)) |
| FROM (SELECT a.key AS key1, a.cnt AS cnt1, b.key AS key2, b.cnt AS cnt2 |
| FROM (SELECT x.key as key, count(x.value) AS cnt FROM src_10 x group by x.key) a |
| RIGHT OUTER JOIN (SELECT y.key as key, count(y.value) AS cnt FROM src1_10 y group by y.key) b |
| ON (a.key = b.key)) tmp; |
| SELECT SUM(HASH(key1)), SUM(HASH(cnt1)), SUM(HASH(key2)), SUM(HASH(cnt2)) |
| FROM (SELECT a.key AS key1, a.cnt AS cnt1, b.key AS key2, b.cnt AS cnt2 |
| FROM (SELECT x.key as key, count(x.value) AS cnt FROM src_10 x group by x.key) a |
| RIGHT OUTER JOIN (SELECT y.key as key, count(y.value) AS cnt FROM src1_10 y group by y.key) b |
| ON (a.key = b.key)) tmp; |
| |
| drop table if exists src_10; |
| drop table if exists src1_10; |
| |
| create table src_10 as select * from src limit 3; |
| create table src1_10 as select * from src1 limit 3; |
| |
| select key, count(*) from src1_10 group by key; |
| select key, count(*) from src_10 group by key; |
| -- Right Outer Join should be handled. |
| |
| set hive.auto.convert.sortmerge.join=true; |
| SELECT SUM(HASH(key1)), SUM(HASH(cnt1)), SUM(HASH(key2)), SUM(HASH(cnt2)) |
| FROM (SELECT a.key AS key1, a.cnt AS cnt1, b.key AS key2, b.cnt AS cnt2 |
| FROM (SELECT x.key as key, count(x.value) AS cnt FROM src_10 x group by x.key) a |
| RIGHT OUTER JOIN (SELECT y.key as key, count(y.value) AS cnt FROM src1_10 y group by y.key) b |
| ON (a.key = b.key)) tmp; |
| |
| |
| drop table if exists src_10; |
| drop table if exists src1_10; |
| |
| create table src_10 as select * from src limit 4; |
| create table src1_10 as select * from src1 limit 4; |
| |
| select key, count(*) from src1_10 group by key; |
| select key, count(*) from src_10 group by key; |
| -- Right Outer Join should be handled. |
| |
| set hive.auto.convert.sortmerge.join=true; |
| SELECT SUM(HASH(key1)), SUM(HASH(cnt1)), SUM(HASH(key2)), SUM(HASH(cnt2)) |
| FROM (SELECT a.key AS key1, a.cnt AS cnt1, b.key AS key2, b.cnt AS cnt2 |
| FROM (SELECT x.key as key, count(x.value) AS cnt FROM src_10 x group by x.key) a |
| RIGHT OUTER JOIN (SELECT y.key as key, count(y.value) AS cnt FROM src1_10 y group by y.key) b |
| ON (a.key = b.key)) tmp; |
| |
| drop table if exists src_10; |
| drop table if exists src1_10; |
| |
| create table src_10 as select * from src limit 5; |
| create table src1_10 as select * from src1 limit 5; |
| |
| select key, count(*) from src1_10 group by key; |
| select key, count(*) from src_10 group by key; |
| -- Right Outer Join should be handled. |
| |
| set hive.auto.convert.sortmerge.join=true; |
| SELECT SUM(HASH(key1)), SUM(HASH(cnt1)), SUM(HASH(key2)), SUM(HASH(cnt2)) |
| FROM (SELECT a.key AS key1, a.cnt AS cnt1, b.key AS key2, b.cnt AS cnt2 |
| FROM (SELECT x.key as key, count(x.value) AS cnt FROM src_10 x group by x.key) a |
| RIGHT OUTER JOIN (SELECT y.key as key, count(y.value) AS cnt FROM src1_10 y group by y.key) b |
| ON (a.key = b.key)) tmp; |
| |
| drop table if exists src_10; |
| drop table if exists src1_10; |
| |
| create table src_10 as select * from src limit 10; |
| create table src1_10 as select * from src1 limit 10; |
| |
| select key, count(*) from src1_10 group by key; |
| select key, count(*) from src_10 group by key; |
| -- Right Outer Join should be handled. |
| |
| set hive.auto.convert.sortmerge.join=true; |
| SELECT SUM(HASH(key1)), SUM(HASH(cnt1)), SUM(HASH(key2)), SUM(HASH(cnt2)) |
| FROM (SELECT a.key AS key1, a.cnt AS cnt1, b.key AS key2, b.cnt AS cnt2 |
| FROM (SELECT x.key as key, count(x.value) AS cnt FROM src_10 x group by x.key) a |
| RIGHT OUTER JOIN (SELECT y.key as key, count(y.value) AS cnt FROM src1_10 y group by y.key) b |
| ON (a.key = b.key)) tmp; |
| |
| |
| |
| |
| CREATE TABLE t1 (c1 INT, c2 CHAR(100)); |
| INSERT INTO t1 VALUES (1,''), (100,'abcdefghij'), (200, 'aa'), (300, 'bbb'); |
| |
| CREATE TABLE t2 (c1 INT); |
| INSERT INTO t2 VALUES (100), (200); |
| |
| explain SELECT c1 FROM t1 WHERE c1 NOT IN (SELECT c1 FROM t2 where t1.c1=t2.c1); |
| SELECT c1 FROM t1 WHERE c1 NOT IN (SELECT c1 FROM t2 where t1.c1=t2.c1); |
| |
| drop table t2; |
| CREATE TABLE t2 (c1 INT); |
| INSERT INTO t2 VALUES (100), (300); |
| |
| explain SELECT c1 FROM t1 WHERE c1 NOT IN (SELECT c1 FROM t2 where t1.c1=t2.c1); |
| SELECT c1 FROM t1 WHERE c1 NOT IN (SELECT c1 FROM t2 where t1.c1=t2.c1); |
| |