http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mapjoin46.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapjoin46.q b/ql/src/test/queries/clientpositive/mapjoin46.q index b7aa092..9de7113 100644 --- a/ql/src/test/queries/clientpositive/mapjoin46.q +++ b/ql/src/test/queries/clientpositive/mapjoin46.q @@ -3,263 +3,263 @@ set hive.auto.convert.join=true; 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_n4 (key INT, value INT, col_1 STRING); +INSERT INTO test1_n4 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_n2 (key INT, value INT, col_2 STRING); +INSERT INTO test2_n2 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_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value); SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.value=test2.value); +FROM test1_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.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_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + AND test1_n4.key between 100 and 102 + AND test2_n2.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_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + AND test1_n4.key between 100 and 102 + AND test2_n2.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_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.key between 100 and 102 + AND test2_n2.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_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.key between 100 and 102 + AND test2_n2.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_n4 RIGHT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value AND true); SELECT * -FROM test1 RIGHT OUTER JOIN test2 -ON (test1.value=test2.value AND true); +FROM test1_n4 RIGHT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.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_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.key between 100 and 102); SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.key between 100 and 102); +FROM test1_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.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_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test1_n4.key between 100 and 102 + OR test2_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 - OR test2.key between 100 and 102); +FROM test1_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test1_n4.key between 100 and 102 + OR test2_n2.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_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test1_n4.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_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test1_n4.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_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test2_n2.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_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test2_n2.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_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + AND (test1_n4.key between 100 and 102 + OR test2_n2.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_n4 LEFT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + AND (test1_n4.key between 100 and 102 + OR test2_n2.key between 100 and 102)); -- 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_n4 RIGHT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test1_n4.key between 100 and 102 + OR test2_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 - OR test2.key between 100 and 102); +FROM test1_n4 RIGHT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test1_n4.key between 100 and 102 + OR test2_n2.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_n4 RIGHT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test1_n4.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_n4 RIGHT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test1_n4.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_n4 RIGHT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test2_n2.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_n4 RIGHT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test2_n2.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_n4 RIGHT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + AND (test1_n4.key between 100 and 102 + OR test2_n2.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_n4 RIGHT OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + AND (test1_n4.key between 100 and 102 + OR test2_n2.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_n4 FULL OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test1_n4.key between 100 and 102 + OR test2_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 - OR test2.key between 100 and 102); +FROM test1_n4 FULL OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test1_n4.key between 100 and 102 + OR test2_n2.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_n4 FULL OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test1_n4.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_n4 FULL OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test1_n4.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_n4 FULL OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test2_n2.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_n4 FULL OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + OR test2_n2.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_n4 FULL OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + AND (test1_n4.key between 100 and 102 + OR test2_n2.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_n4 FULL OUTER JOIN test2_n2 +ON (test1_n4.value=test2_n2.value + AND (test1_n4.key between 100 and 102 + OR test2_n2.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_n4.key AS key1, test1_n4.value AS value1, test1_n4.col_1 AS col_1, + test2_n2.key AS key2, test2_n2.value AS value2, test2_n2.col_2 AS col_2 + FROM test1_n4 RIGHT OUTER JOIN test2_n2 + ON (test1_n4.value=test2_n2.value + AND (test1_n4.key between 100 and 102 + OR test2_n2.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_n4.key AS key3, test1_n4.value AS value3, test1_n4.col_1 AS col_3, + test2_n2.key AS key4, test2_n2.value AS value4, test2_n2.col_2 AS col_4 + FROM test1_n4 LEFT OUTER JOIN test2_n2 + ON (test1_n4.value=test2_n2.value + AND (test1_n4.key between 100 and 102 + OR test2_n2.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_n4.key AS key1, test1_n4.value AS value1, test1_n4.col_1 AS col_1, + test2_n2.key AS key2, test2_n2.value AS value2, test2_n2.col_2 AS col_2 + FROM test1_n4 RIGHT OUTER JOIN test2_n2 + ON (test1_n4.value=test2_n2.value + AND (test1_n4.key between 100 and 102 + OR test2_n2.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_n4.key AS key3, test1_n4.value AS value3, test1_n4.col_1 AS col_3, + test2_n2.key AS key4, test2_n2.value AS value4, test2_n2.col_2 AS col_4 + FROM test1_n4 LEFT OUTER JOIN test2_n2 + ON (test1_n4.value=test2_n2.value + AND (test1_n4.key between 100 and 102 + OR test2_n2.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/mapjoin_addjar.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapjoin_addjar.q b/ql/src/test/queries/clientpositive/mapjoin_addjar.q index cc8bb8b..6b72678 100644 --- a/ql/src/test/queries/clientpositive/mapjoin_addjar.q +++ b/ql/src/test/queries/clientpositive/mapjoin_addjar.q @@ -4,12 +4,12 @@ set hive.auto.convert.join.use.nonstaged=false; ADD JAR ${system:maven.local.repository}/org/apache/hive/hive-it-test-serde/${system:hive.version}/hive-it-test-serde-${system:hive.version}.jar; -CREATE TABLE t1(KEY STRING, VALUE STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.TestSerDe' STORED AS TEXTFILE; -LOAD DATA LOCAL INPATH '../../data/files/kv1_cb.txt' INTO TABLE t1; +CREATE TABLE t1_n66(KEY STRING, VALUE STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.TestSerDe' STORED AS TEXTFILE; +LOAD DATA LOCAL INPATH '../../data/files/kv1_cb.txt' INTO TABLE t1_n66; -select * from t1 l join t1 r on l.key =r.key; +select * from t1_n66 l join t1_n66 r on l.key =r.key; -drop table t1; +drop table t1_n66; DELETE JAR ${system:maven.local.repository}/org/apache/hive/hive-it-test-serde/${system:hive.version}/hive-it-test-serde-${system:hive.version}.jar; set hive.auto.convert.join=false; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mapjoin_decimal.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapjoin_decimal.q b/ql/src/test/queries/clientpositive/mapjoin_decimal.q index 29eae50..c84267a 100644 --- a/ql/src/test/queries/clientpositive/mapjoin_decimal.q +++ b/ql/src/test/queries/clientpositive/mapjoin_decimal.q @@ -7,7 +7,7 @@ set hive.auto.convert.join.noconditionaltask.size=10000000; -- SORT_QUERY_RESULTS -CREATE TABLE over1k(t tinyint, +CREATE TABLE over1k_n5(t tinyint, si smallint, i int, b bigint, @@ -21,20 +21,20 @@ CREATE TABLE over1k(t tinyint, ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE; -LOAD DATA LOCAL INPATH '../../data/files/over1k' OVERWRITE INTO TABLE over1k; +LOAD DATA LOCAL INPATH '../../data/files/over1k' OVERWRITE INTO TABLE over1k_n5; -CREATE TABLE t1(`dec` decimal(4,2)) STORED AS ORC; -INSERT INTO TABLE t1 select `dec` from over1k; -CREATE TABLE t2(`dec` decimal(4,0)) STORED AS ORC; -INSERT INTO TABLE t2 select `dec` from over1k; +CREATE TABLE t1_n95(`dec` decimal(4,2)) STORED AS ORC; +INSERT INTO TABLE t1_n95 select `dec` from over1k_n5; +CREATE TABLE t2_n59(`dec` decimal(4,0)) STORED AS ORC; +INSERT INTO TABLE t2_n59 select `dec` from over1k_n5; explain -select t1.`dec`, t2.`dec` from t1 join t2 on (t1.`dec`=t2.`dec`) order by t1.`dec`; +select t1_n95.`dec`, t2_n59.`dec` from t1_n95 join t2_n59 on (t1_n95.`dec`=t2_n59.`dec`) order by t1_n95.`dec`; set hive.mapjoin.optimized.hashtable=false; -select t1.`dec`, t2.`dec` from t1 join t2 on (t1.`dec`=t2.`dec`) order by t1.`dec`; +select t1_n95.`dec`, t2_n59.`dec` from t1_n95 join t2_n59 on (t1_n95.`dec`=t2_n59.`dec`) order by t1_n95.`dec`; set hive.mapjoin.optimized.hashtable=true; -select t1.`dec`, t2.`dec` from t1 join t2 on (t1.`dec`=t2.`dec`) order by t1.`dec`; +select t1_n95.`dec`, t2_n59.`dec` from t1_n95 join t2_n59 on (t1_n95.`dec`=t2_n59.`dec`) order by t1_n95.`dec`; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mapjoin_emit_interval.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapjoin_emit_interval.q b/ql/src/test/queries/clientpositive/mapjoin_emit_interval.q index fe2a32a..5bbeb17 100644 --- a/ql/src/test/queries/clientpositive/mapjoin_emit_interval.q +++ b/ql/src/test/queries/clientpositive/mapjoin_emit_interval.q @@ -2,8 +2,8 @@ set hive.auto.convert.join=true; 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_n0 (key INT, value INT, col_1 STRING); +INSERT INTO test1_n0 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); @@ -14,19 +14,19 @@ INSERT INTO test2 VALUES (102, 2, 'Del'), (103, 2, 'Ema'), -- 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_n0 LEFT OUTER JOIN test2 +ON (test1_n0.value=test2.value AND test1_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); +FROM test1_n0 LEFT OUTER JOIN test2 +ON (test1_n0.value=test2.value AND test1_n0.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_n0 LEFT OUTER JOIN test2 +ON (test1_n0.key between 100 and 102); SELECT * -FROM test1 LEFT OUTER JOIN test2 -ON (test1.key between 100 and 102); +FROM test1_n0 LEFT OUTER JOIN test2 +ON (test1_n0.key between 100 and 102); http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mapjoin_hint.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapjoin_hint.q b/ql/src/test/queries/clientpositive/mapjoin_hint.q index 21b4896..7189f07 100644 --- a/ql/src/test/queries/clientpositive/mapjoin_hint.q +++ b/ql/src/test/queries/clientpositive/mapjoin_hint.q @@ -16,41 +16,41 @@ set hive.stats.fetch.column.stats=true; set hive.tez.bloom.filter.factor=1.0f; -- Create Tables -create table srcpart_date (key string, value string) partitioned by (ds string ) stored as ORC; -CREATE TABLE srcpart_small(key1 STRING, value1 STRING) partitioned by (ds string) STORED as ORC; +create table srcpart_date_n5 (key string, value string) partitioned by (ds string ) stored as ORC; +CREATE TABLE srcpart_small_n1(key1 STRING, value1 STRING) partitioned by (ds string) STORED as ORC; -- Add Partitions -alter table srcpart_date add partition (ds = "2008-04-08"); -alter table srcpart_date add partition (ds = "2008-04-09"); +alter table srcpart_date_n5 add partition (ds = "2008-04-08"); +alter table srcpart_date_n5 add partition (ds = "2008-04-09"); -alter table srcpart_small add partition (ds = "2008-04-08"); -alter table srcpart_small add partition (ds = "2008-04-09"); +alter table srcpart_small_n1 add partition (ds = "2008-04-08"); +alter table srcpart_small_n1 add partition (ds = "2008-04-09"); -- Load -insert overwrite table srcpart_date partition (ds = "2008-04-08" ) select key, value from srcpart where ds = "2008-04-08"; -insert overwrite table srcpart_date partition (ds = "2008-04-09") select key, value from srcpart where ds = "2008-04-09"; -insert overwrite table srcpart_small partition (ds = "2008-04-09") select key, value from srcpart where ds = "2008-04-09" limit 20; +insert overwrite table srcpart_date_n5 partition (ds = "2008-04-08" ) select key, value from srcpart where ds = "2008-04-08"; +insert overwrite table srcpart_date_n5 partition (ds = "2008-04-09") select key, value from srcpart where ds = "2008-04-09"; +insert overwrite table srcpart_small_n1 partition (ds = "2008-04-09") select key, value from srcpart where ds = "2008-04-09" limit 20; -analyze table srcpart_date compute statistics for columns; -analyze table srcpart_small compute statistics for columns; +analyze table srcpart_date_n5 compute statistics for columns; +analyze table srcpart_small_n1 compute statistics for columns; set hive.auto.convert.join=true; set hive.auto.convert.join.noconditionaltask=true; set hive.auto.convert.join.noconditionaltask.size=100000000000; --HIVE-17475 -EXPLAIN select /*+ mapjoin(None)*/ count(*) from srcpart_date join srcpart_small on (srcpart_date.key = srcpart_small.key1); -EXPLAIN select count(*) from srcpart_date join srcpart_small on (srcpart_date.key = srcpart_small.key1); +EXPLAIN select /*+ mapjoin(None)*/ count(*) from srcpart_date_n5 join srcpart_small_n1 on (srcpart_date_n5.key = srcpart_small_n1.key1); +EXPLAIN select count(*) from srcpart_date_n5 join srcpart_small_n1 on (srcpart_date_n5.key = srcpart_small_n1.key1); -- Ensure that hint works even with CBO on, on a query with subquery. -create table tnull(i int, c char(2)); -insert into tnull values(NULL, NULL), (NULL, NULL); +create table tnull_n1(i int, c char(2)); +insert into tnull_n1 values(NULL, NULL), (NULL, NULL); -create table tempty(c char(2)); +create table tempty_n1(c char(2)); -CREATE TABLE part_null( +CREATE TABLE part_null_n1( p_partkey INT, p_name STRING, p_mfgr STRING, @@ -64,9 +64,9 @@ p_comment STRING ROW FORMAT DELIMITED FIELDS TERMINATED BY "," ; -LOAD DATA LOCAL INPATH '../../data/files/part_tiny_nulls.txt' overwrite into table part_null; +LOAD DATA LOCAL INPATH '../../data/files/part_tiny_nulls.txt' overwrite into table part_null_n1; -insert into part_null values(78487,NULL,'Manufacturer#6','Brand#52','LARGE BRUSHED BRASS', 23, 'MED BAG',1464.48,'hely blith'); +insert into part_null_n1 values(78487,NULL,'Manufacturer#6','Brand#52','LARGE BRUSHED BRASS', 23, 'MED BAG',1464.48,'hely blith'); -explain select /*+ mapjoin(None)*/ * from part where p_name = (select p_name from part_null where p_name is null); -explain select * from part where p_name = (select p_name from part_null where p_name is null); +explain select /*+ mapjoin(None)*/ * from part where p_name = (select p_name from part_null_n1 where p_name is null); +explain select * from part where p_name = (select p_name from part_null_n1 where p_name is null); http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mapjoin_hook.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapjoin_hook.q b/ql/src/test/queries/clientpositive/mapjoin_hook.q index 3dc479c..c929356 100644 --- a/ql/src/test/queries/clientpositive/mapjoin_hook.q +++ b/ql/src/test/queries/clientpositive/mapjoin_hook.q @@ -4,17 +4,17 @@ set hive.stats.column.autogather=false; set hive.exec.post.hooks = org.apache.hadoop.hive.ql.hooks.MapJoinCounterHook,org.apache.hadoop.hive.ql.hooks.PrintCompletedTasksHook; -drop table dest1; -CREATE TABLE dest1(key INT, value STRING) STORED AS TEXTFILE; +drop table dest1_n171; +CREATE TABLE dest1_n171(key INT, value STRING) STORED AS TEXTFILE; set hive.auto.convert.join = true; -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n171 SELECT /*+ MAPJOIN(x) */ x.key, count(1) FROM src1 x JOIN src y ON (x.key = y.key) group by x.key; FROM src src1 JOIN src src2 ON (src1.key = src2.key) JOIN src src3 ON (src1.key = src3.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.key, src3.value; +INSERT OVERWRITE TABLE dest1_n171 SELECT src1.key, src3.value; set hive.mapjoin.localtask.max.memory.usage = 0.0001; @@ -23,12 +23,12 @@ set hive.auto.convert.join.noconditionaltask = false; FROM srcpart src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.key, src2.value +INSERT OVERWRITE TABLE dest1_n171 SELECT src1.key, src2.value where (src1.ds = '2008-04-08' or src1.ds = '2008-04-09' )and (src1.hr = '12' or src1.hr = '11'); FROM src src1 JOIN src src2 ON (src1.key = src2.key) JOIN src src3 ON (src1.key + src2.key = src3.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.key, src3.value; +INSERT OVERWRITE TABLE dest1_n171 SELECT src1.key, src3.value; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mapjoin_test_outer.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapjoin_test_outer.q b/ql/src/test/queries/clientpositive/mapjoin_test_outer.q index 96275ab..3c6383e 100644 --- a/ql/src/test/queries/clientpositive/mapjoin_test_outer.q +++ b/ql/src/test/queries/clientpositive/mapjoin_test_outer.q @@ -9,50 +9,50 @@ create table dest_1 (key STRING, value STRING) stored as textfile; insert overwrite table dest_1 select * from src1 order by src1.value limit 8; insert into table dest_1 select "333444","555666" from src1 limit 1; -create table dest_2 (key STRING, value STRING) stored as textfile; +create table dest_2_n0 (key STRING, value STRING) stored as textfile; -insert into table dest_2 select * from dest_1; +insert into table dest_2_n0 select * from dest_1; SELECT * FROM src1 RIGHT OUTER JOIN dest_1 src2 ON (src1.key = src2.key) - JOIN dest_2 src3 ON (src2.key = src3.key) + JOIN dest_2_n0 src3 ON (src2.key = src3.key) SORT BY src1.key, src1.value, src2.key, src2.value, src3.key, src3.value; explain SELECT /*+ mapjoin(src1, src2) */ * FROM src1 RIGHT OUTER JOIN dest_1 src2 ON (src1.key = src2.key) - JOIN dest_2 src3 ON (src2.key = src3.key) + JOIN dest_2_n0 src3 ON (src2.key = src3.key) SORT BY src1.key, src1.value, src2.key, src2.value, src3.key, src3.value; SELECT /*+ mapjoin(src1, src2) */ * FROM src1 RIGHT OUTER JOIN dest_1 src2 ON (src1.key = src2.key) - JOIN dest_2 src3 ON (src2.key = src3.key) + JOIN dest_2_n0 src3 ON (src2.key = src3.key) SORT BY src1.key, src1.value, src2.key, src2.value, src3.key, src3.value; SELECT /*+ mapjoin(src1, src2) */ * FROM src1 RIGHT OUTER JOIN dest_1 src2 ON (src1.key = src2.key) - JOIN dest_2 src3 ON (src1.key = src3.key) + JOIN dest_2_n0 src3 ON (src1.key = src3.key) SORT BY src1.key, src1.value, src2.key, src2.value, src3.key, src3.value; set hive.auto.convert.join = true; SELECT * FROM src1 LEFT OUTER JOIN dest_1 src2 ON (src1.key = src2.key) - JOIN dest_2 src3 ON (src1.key = src3.key) + JOIN dest_2_n0 src3 ON (src1.key = src3.key) SORT BY src1.key, src1.value, src2.key, src2.value, src3.key, src3.value; SELECT * FROM src1 LEFT OUTER JOIN dest_1 src2 ON (src1.key = src2.key) - JOIN dest_2 src3 ON (src2.key = src3.key) + JOIN dest_2_n0 src3 ON (src2.key = src3.key) SORT BY src1.key, src1.value, src2.key, src2.value, src3.key, src3.value; explain SELECT * FROM src1 RIGHT OUTER JOIN dest_1 src2 ON (src1.key = src2.key) - JOIN dest_2 src3 ON (src2.key = src3.key) + JOIN dest_2_n0 src3 ON (src2.key = src3.key) SORT BY src1.key, src1.value, src2.key, src2.value, src3.key, src3.value; SELECT * FROM src1 RIGHT OUTER JOIN dest_1 src2 ON (src1.key = src2.key) - JOIN dest_2 src3 ON (src2.key = src3.key) + JOIN dest_2_n0 src3 ON (src2.key = src3.key) SORT BY src1.key, src1.value, src2.key, src2.value, src3.key, src3.value; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mapreduce1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapreduce1.q b/ql/src/test/queries/clientpositive/mapreduce1.q index 06c7c53..e806d56 100644 --- a/ql/src/test/queries/clientpositive/mapreduce1.q +++ b/ql/src/test/queries/clientpositive/mapreduce1.q @@ -1,10 +1,10 @@ --! qt:dataset:src set hive.explain.user=false; -CREATE TABLE dest1(key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n120(key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; EXPLAIN FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n120 MAP src.key, CAST(src.key / 10 AS INT), CAST(src.key % 10 AS INT), src.value USING 'cat' AS (tkey, ten, one, tvalue) DISTRIBUTE BY tvalue, tkey @@ -12,11 +12,11 @@ SORT BY ten, one; FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n120 MAP src.key, CAST(src.key / 10 AS INT), CAST(src.key % 10 AS INT), src.value USING 'cat' AS (tkey, ten, one, tvalue) DISTRIBUTE BY tvalue, tkey SORT BY ten, one; -SELECT dest1.* FROM dest1; +SELECT dest1_n120.* FROM dest1_n120; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mapreduce2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapreduce2.q b/ql/src/test/queries/clientpositive/mapreduce2.q index dfb8ec3..b1b7bdc 100644 --- a/ql/src/test/queries/clientpositive/mapreduce2.q +++ b/ql/src/test/queries/clientpositive/mapreduce2.q @@ -1,20 +1,20 @@ --! qt:dataset:src set hive.mapred.mode=nonstrict; set hive.explain.user=false; -CREATE TABLE dest1(key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n162(key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; EXPLAIN FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n162 MAP src.key, CAST(src.key / 10 AS INT), CAST(src.key % 10 AS INT), src.value USING 'cat' AS (tkey, ten, one, tvalue) DISTRIBUTE BY tvalue, tkey; FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n162 MAP src.key, CAST(src.key / 10 AS INT), CAST(src.key % 10 AS INT), src.value USING 'cat' AS (tkey, ten, one, tvalue) DISTRIBUTE BY tvalue, tkey; -SELECT * FROM (SELECT dest1.* FROM dest1 DISTRIBUTE BY key SORT BY key, ten, one, value) T ORDER BY key; +SELECT * FROM (SELECT dest1_n162.* FROM dest1_n162 DISTRIBUTE BY key SORT BY key, ten, one, value) T ORDER BY key; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mapreduce3.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapreduce3.q b/ql/src/test/queries/clientpositive/mapreduce3.q index ad9908f..d5b3b4e 100644 --- a/ql/src/test/queries/clientpositive/mapreduce3.q +++ b/ql/src/test/queries/clientpositive/mapreduce3.q @@ -1,18 +1,18 @@ --! qt:dataset:src -CREATE TABLE dest1(key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n23(key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; EXPLAIN FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n23 MAP src.key, CAST(src.key / 10 AS INT), CAST(src.key % 10 AS INT), src.value USING 'cat' AS (tkey, ten, one, tvalue) SORT BY tvalue, tkey; FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n23 MAP src.key, CAST(src.key / 10 AS INT), CAST(src.key % 10 AS INT), src.value USING 'cat' AS (tkey, ten, one, tvalue) SORT BY tvalue, tkey; -SELECT dest1.* FROM dest1; +SELECT dest1_n23.* FROM dest1_n23; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mapreduce4.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapreduce4.q b/ql/src/test/queries/clientpositive/mapreduce4.q index 74e907e..d8f8a9b 100644 --- a/ql/src/test/queries/clientpositive/mapreduce4.q +++ b/ql/src/test/queries/clientpositive/mapreduce4.q @@ -1,9 +1,9 @@ --! qt:dataset:src -CREATE TABLE dest1(key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n93(key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; EXPLAIN FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n93 MAP src.key, CAST(src.key / 10 AS INT), CAST(src.key % 10 AS INT), src.value USING 'cat' AS (tkey, ten, one, tvalue) DISTRIBUTE BY tvalue, tkey @@ -11,11 +11,11 @@ SORT BY ten DESC, one ASC; FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n93 MAP src.key, CAST(src.key / 10 AS INT), CAST(src.key % 10 AS INT), src.value USING 'cat' AS (tkey, ten, one, tvalue) DISTRIBUTE BY tvalue, tkey SORT BY ten DESC, one ASC; -SELECT dest1.* FROM dest1; +SELECT dest1_n93.* FROM dest1_n93; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mapreduce5.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapreduce5.q b/ql/src/test/queries/clientpositive/mapreduce5.q index 841ead5..ba6a6f8 100644 --- a/ql/src/test/queries/clientpositive/mapreduce5.q +++ b/ql/src/test/queries/clientpositive/mapreduce5.q @@ -1,18 +1,18 @@ --! qt:dataset:src -CREATE TABLE dest1(key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n133(key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; EXPLAIN FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n133 SELECT src.key as c1, CAST(src.key / 10 AS INT) as c2, CAST(src.key % 10 AS INT) as c3, src.value as c4 DISTRIBUTE BY c4, c1 SORT BY c2 DESC, c3 ASC; FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n133 SELECT src.key as c1, CAST(src.key / 10 AS INT) as c2, CAST(src.key % 10 AS INT) as c3, src.value as c4 DISTRIBUTE BY c4, c1 SORT BY c2 DESC, c3 ASC; -SELECT dest1.* FROM dest1; +SELECT dest1_n133.* FROM dest1_n133; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mapreduce6.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapreduce6.q b/ql/src/test/queries/clientpositive/mapreduce6.q index 213db77..f035c46 100644 --- a/ql/src/test/queries/clientpositive/mapreduce6.q +++ b/ql/src/test/queries/clientpositive/mapreduce6.q @@ -1,18 +1,18 @@ --! qt:dataset:src -CREATE TABLE dest1(key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n169(key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; EXPLAIN FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n169 SELECT src.key, CAST(src.key / 10 AS INT) as c2, CAST(src.key % 10 AS INT) as c3, src.value DISTRIBUTE BY value, key SORT BY c2 DESC, c3 ASC; FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n169 SELECT src.key, CAST(src.key / 10 AS INT) as c2, CAST(src.key % 10 AS INT) as c3, src.value DISTRIBUTE BY value, key SORT BY c2 DESC, c3 ASC; -SELECT dest1.* FROM dest1; +SELECT dest1_n169.* FROM dest1_n169; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mapreduce7.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapreduce7.q b/ql/src/test/queries/clientpositive/mapreduce7.q index 77b82ef..aaf08fa 100644 --- a/ql/src/test/queries/clientpositive/mapreduce7.q +++ b/ql/src/test/queries/clientpositive/mapreduce7.q @@ -1,18 +1,18 @@ --! qt:dataset:src -CREATE TABLE dest1(k STRING, v STRING, key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n37(k STRING, v STRING, key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; EXPLAIN FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n37 MAP src.*, src.key, CAST(src.key / 10 AS INT), CAST(src.key % 10 AS INT), src.value USING 'cat' AS (k, v, tkey, ten, one, tvalue) SORT BY tvalue, tkey; FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n37 MAP src.*, src.key, CAST(src.key / 10 AS INT), CAST(src.key % 10 AS INT), src.value USING 'cat' AS (k, v, tkey, ten, one, tvalue) SORT BY tvalue, tkey; -SELECT dest1.* FROM dest1; +SELECT dest1_n37.* FROM dest1_n37; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mapreduce8.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mapreduce8.q b/ql/src/test/queries/clientpositive/mapreduce8.q index 7db7510..dd8abc3 100644 --- a/ql/src/test/queries/clientpositive/mapreduce8.q +++ b/ql/src/test/queries/clientpositive/mapreduce8.q @@ -1,9 +1,9 @@ --! qt:dataset:src -CREATE TABLE dest1(k STRING, v STRING, key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n158(k STRING, v STRING, key INT, ten INT, one INT, value STRING) STORED AS TEXTFILE; EXPLAIN FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n158 MAP src.*, src.key, CAST(src.key / 10 AS INT), CAST(src.key % 10 AS INT), src.value USING 'cat' AS (k, v, tkey, ten, one, tvalue) DISTRIBUTE BY rand(3) @@ -11,10 +11,10 @@ SORT BY tvalue, tkey; FROM src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n158 MAP src.*, src.key, CAST(src.key / 10 AS INT), CAST(src.key % 10 AS INT), src.value USING 'cat' AS (k, v, tkey, ten, one, tvalue) DISTRIBUTE BY rand(3) SORT BY tvalue, tkey; -SELECT dest1.* FROM dest1; +SELECT dest1_n158.* FROM dest1_n158; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/masking_1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/masking_1.q b/ql/src/test/queries/clientpositive/masking_1.q index 6e4f6a7..858162c 100644 --- a/ql/src/test/queries/clientpositive/masking_1.q +++ b/ql/src/test/queries/clientpositive/masking_1.q @@ -3,27 +3,27 @@ set hive.mapred.mode=nonstrict; set hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactoryForTest; -create table masking_test as select cast(key as int) as key, value from src; +create table masking_test_n8 as select cast(key as int) as key, value from src; -explain select * from masking_test; -select * from masking_test; +explain select * from masking_test_n8; +select * from masking_test_n8; -explain select * from masking_test where key > 0; -select * from masking_test where key > 0; +explain select * from masking_test_n8 where key > 0; +select * from masking_test_n8 where key > 0; -explain select key from masking_test where key > 0; -select key from masking_test where key > 0; +explain select key from masking_test_n8 where key > 0; +select key from masking_test_n8 where key > 0; -explain select value from masking_test where key > 0; -select value from masking_test where key > 0; +explain select value from masking_test_n8 where key > 0; +select value from masking_test_n8 where key > 0; -explain select * from masking_test join srcpart on (masking_test.key = srcpart.key); -select * from masking_test join srcpart on (masking_test.key = srcpart.key); +explain select * from masking_test_n8 join srcpart on (masking_test_n8.key = srcpart.key); +select * from masking_test_n8 join srcpart on (masking_test_n8.key = srcpart.key); -explain select * from default.masking_test where key > 0; -select * from default.masking_test where key > 0; +explain select * from default.masking_test_n8 where key > 0; +select * from default.masking_test_n8 where key > 0; -explain select * from masking_test where masking_test.key > 0; -select * from masking_test where masking_test.key > 0; +explain select * from masking_test_n8 where masking_test_n8.key > 0; +select * from masking_test_n8 where masking_test_n8.key > 0; explain select key, value from (select key, value from (select key, upper(value) as value from src where key > 0) t where key < 10) t2 where key % 2 = 0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/masking_12.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/masking_12.q b/ql/src/test/queries/clientpositive/masking_12.q index 707d53a..6bb941e 100644 --- a/ql/src/test/queries/clientpositive/masking_12.q +++ b/ql/src/test/queries/clientpositive/masking_12.q @@ -2,24 +2,24 @@ set hive.mapred.mode=nonstrict; set hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactoryForTest; -create table `masking_test` as select cast(key as int) as key, value from src; +create table `masking_test_n5` as select cast(key as int) as key, value from src; -create view `v0` as select * from `masking_test`; +create view `v0` as select * from `masking_test_n5`; explain select * from `v0`; select * from `v0`; -create table `masking_test_subq` as select cast(key as int) as key, value from src; +create table `masking_test_subq_n1` as select cast(key as int) as key, value from src; -create view `v1` as select * from `masking_test_subq`; +create view `v1_n9` as select * from `masking_test_subq_n1`; explain -select * from `v1` +select * from `v1_n9` limit 20; -select * from `v1` +select * from `v1_n9` limit 20; create view `masking_test_view` as select key from `v0`; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/masking_1_newdb.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/masking_1_newdb.q b/ql/src/test/queries/clientpositive/masking_1_newdb.q index 7438349..6626fac 100644 --- a/ql/src/test/queries/clientpositive/masking_1_newdb.q +++ b/ql/src/test/queries/clientpositive/masking_1_newdb.q @@ -6,13 +6,13 @@ create database newdb; use newdb; -create table masking_test as select cast(key as int) as key, value from default.src; +create table masking_test_n12 as select cast(key as int) as key, value from default.src; use default; -explain select * from newdb.masking_test; -select * from newdb.masking_test; +explain select * from newdb.masking_test_n12; +select * from newdb.masking_test_n12; -explain select * from newdb.masking_test where key > 0; -select * from newdb.masking_test where key > 0; +explain select * from newdb.masking_test_n12 where key > 0; +select * from newdb.masking_test_n12 where key > 0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/masking_2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/masking_2.q b/ql/src/test/queries/clientpositive/masking_2.q index 3192992..ece1518 100644 --- a/ql/src/test/queries/clientpositive/masking_2.q +++ b/ql/src/test/queries/clientpositive/masking_2.q @@ -2,17 +2,17 @@ set hive.mapred.mode=nonstrict; set hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactoryForTest; -create view masking_test as select cast(key as int) as key, value from src; +create view masking_test_n1 as select cast(key as int) as key, value from src; -explain select * from masking_test; -select * from masking_test; +explain select * from masking_test_n1; +select * from masking_test_n1; -explain select * from masking_test where key > 0; -select * from masking_test where key > 0; +explain select * from masking_test_n1 where key > 0; +select * from masking_test_n1 where key > 0; -explain select * from src a join masking_test b on a.key = b.value where b.key > 0; +explain select * from src a join masking_test_n1 b on a.key = b.value where b.key > 0; -explain select a.*, b.key from masking_test a join masking_test b on a.key = b.value where b.key > 0; +explain select a.*, b.key from masking_test_n1 a join masking_test_n1 b on a.key = b.value where b.key > 0; -explain select * from masking_test a union select b.* from masking_test b where b.key > 0; +explain select * from masking_test_n1 a union select b.* from masking_test_n1 b where b.key > 0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/masking_3.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/masking_3.q b/ql/src/test/queries/clientpositive/masking_3.q index 018f998..42706d0 100644 --- a/ql/src/test/queries/clientpositive/masking_3.q +++ b/ql/src/test/queries/clientpositive/masking_3.q @@ -3,27 +3,27 @@ set hive.mapred.mode=nonstrict; set hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactoryForTest; -create table masking_test_subq as select cast(key as int) as key, value from src; +create table masking_test_subq_n3 as select cast(key as int) as key, value from src; -explain select * from masking_test_subq; -select * from masking_test_subq; +explain select * from masking_test_subq_n3; +select * from masking_test_subq_n3; -explain select * from masking_test_subq where key > 0; -select * from masking_test_subq where key > 0; +explain select * from masking_test_subq_n3 where key > 0; +select * from masking_test_subq_n3 where key > 0; -explain select key from masking_test_subq where key > 0; -select key from masking_test_subq where key > 0; +explain select key from masking_test_subq_n3 where key > 0; +select key from masking_test_subq_n3 where key > 0; -explain select value from masking_test_subq where key > 0; -select value from masking_test_subq where key > 0; +explain select value from masking_test_subq_n3 where key > 0; +select value from masking_test_subq_n3 where key > 0; -explain select * from masking_test_subq join srcpart on (masking_test_subq.key = srcpart.key); -select * from masking_test_subq join srcpart on (masking_test_subq.key = srcpart.key); +explain select * from masking_test_subq_n3 join srcpart on (masking_test_subq_n3.key = srcpart.key); +select * from masking_test_subq_n3 join srcpart on (masking_test_subq_n3.key = srcpart.key); -explain select * from default.masking_test_subq where key > 0; -select * from default.masking_test_subq where key > 0; +explain select * from default.masking_test_subq_n3 where key > 0; +select * from default.masking_test_subq_n3 where key > 0; -explain select * from masking_test_subq where masking_test_subq.key > 0; -select * from masking_test_subq where masking_test_subq.key > 0; +explain select * from masking_test_subq_n3 where masking_test_subq_n3.key > 0; +select * from masking_test_subq_n3 where masking_test_subq_n3.key > 0; explain select key, value from (select key, value from (select key, upper(value) as value from src where key > 0) t where key < 10) t2 where key % 2 = 0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/masking_4.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/masking_4.q b/ql/src/test/queries/clientpositive/masking_4.q index 49af1cc..48fe33c 100644 --- a/ql/src/test/queries/clientpositive/masking_4.q +++ b/ql/src/test/queries/clientpositive/masking_4.q @@ -2,8 +2,8 @@ set hive.mapred.mode=nonstrict; set hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactoryForTest; -create table masking_test as select cast(key as int) as key, value from src; -create table masking_test_subq as select cast(key as int) as key, value from src; +create table masking_test_n11 as select cast(key as int) as key, value from src; +create table masking_test_subq_n2 as select cast(key as int) as key, value from src; explain @@ -12,20 +12,20 @@ q2 as ( select key from src where key = '5') select * from (select key from q1) a; ---should mask masking_test +--should mask masking_test_n11 explain -with q1 as ( select * from masking_test where key = '5') +with q1 as ( select * from masking_test_n11 where key = '5') select * from q1; ---should not mask masking_test_subq +--should not mask masking_test_subq_n2 explain -with masking_test_subq as ( select * from masking_test where key = '5') -select * from masking_test_subq; +with masking_test_subq_n2 as ( select * from masking_test_n11 where key = '5') +select * from masking_test_subq_n2; ---should mask masking_test_subq +--should mask masking_test_subq_n2 explain -with q1 as ( select * from masking_test where key = '5') -select * from masking_test_subq; \ No newline at end of file +with q1 as ( select * from masking_test_n11 where key = '5') +select * from masking_test_subq_n2; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/masking_5.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/masking_5.q b/ql/src/test/queries/clientpositive/masking_5.q index e8f392f..e24bb93 100644 --- a/ql/src/test/queries/clientpositive/masking_5.q +++ b/ql/src/test/queries/clientpositive/masking_5.q @@ -2,22 +2,22 @@ set hive.mapred.mode=nonstrict; set hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactoryForTest; -create table masking_test as select cast(key as int) as key, value from src; +create table masking_test_n6 as select cast(key as int) as key, value from src; -explain select * from masking_test tablesample (10 rows); -select * from masking_test tablesample (10 rows); +explain select * from masking_test_n6 tablesample (10 rows); +select * from masking_test_n6 tablesample (10 rows); explain -select * from masking_test tablesample(1 percent); -select * from masking_test tablesample(1 percent); +select * from masking_test_n6 tablesample(1 percent); +select * from masking_test_n6 tablesample(1 percent); -drop table masking_test; +drop table masking_test_n6; -CREATE TABLE masking_test(key int, value string) CLUSTERED BY (key) INTO 2 BUCKETS; +CREATE TABLE masking_test_n6(key int, value string) CLUSTERED BY (key) INTO 2 BUCKETS; -insert overwrite table masking_test +insert overwrite table masking_test_n6 select * from src; explain -select * from masking_test tablesample (bucket 1 out of 2) s; -select * from masking_test tablesample (bucket 1 out of 2) s; +select * from masking_test_n6 tablesample (bucket 1 out of 2) s; +select * from masking_test_n6 tablesample (bucket 1 out of 2) s; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/masking_6.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/masking_6.q b/ql/src/test/queries/clientpositive/masking_6.q index f773ec1..ddbe476 100644 --- a/ql/src/test/queries/clientpositive/masking_6.q +++ b/ql/src/test/queries/clientpositive/masking_6.q @@ -2,28 +2,28 @@ set hive.mapred.mode=nonstrict; set hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactoryForTest; -drop view masking_test; +drop view masking_test_n0; -create view masking_test as select cast(key as int) as key, value, '12' from src; +create view masking_test_n0 as select cast(key as int) as key, value, '12' from src; -explain select * from masking_test; +explain select * from masking_test_n0; -select * from masking_test; +select * from masking_test_n0; -explain select * from masking_test where key > 0; +explain select * from masking_test_n0 where key > 0; -select * from masking_test where key > 0; +select * from masking_test_n0 where key > 0; -drop view masking_test; +drop view masking_test_n0; -create view masking_test as select cast(key as int) as key, '12', +create view masking_test_n0 as select cast(key as int) as key, '12', '12', '12', '12', '12', '12', '12', '12', '12', '12', '12' from src; -explain select * from masking_test; +explain select * from masking_test_n0; -select * from masking_test; +select * from masking_test_n0; -explain select * from masking_test where key > 0; +explain select * from masking_test_n0 where key > 0; -select * from masking_test where key > 0; +select * from masking_test_n0 where key > 0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/masking_7.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/masking_7.q b/ql/src/test/queries/clientpositive/masking_7.q index 1655221..e4d6387 100644 --- a/ql/src/test/queries/clientpositive/masking_7.q +++ b/ql/src/test/queries/clientpositive/masking_7.q @@ -2,28 +2,28 @@ set hive.mapred.mode=nonstrict; set hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactoryForTest; -drop view masking_test; +drop view masking_test_n14; -create view masking_test as select cast(key as int) as key, value, '12', ROW__ID from src; +create view masking_test_n14 as select cast(key as int) as key, value, '12', ROW__ID from src; -explain select * from masking_test; +explain select * from masking_test_n14; -select * from masking_test; +select * from masking_test_n14; -explain select * from masking_test where key > 0; +explain select * from masking_test_n14 where key > 0; -select * from masking_test where key > 0; +select * from masking_test_n14 where key > 0; -drop view masking_test; +drop view masking_test_n14; -create view masking_test as select cast(key as int) as key, '12', ROW__ID, +create view masking_test_n14 as select cast(key as int) as key, '12', ROW__ID, '12', '12', '12', '12', '12', '12', '12', '12', '12', '12' from src; -explain select * from masking_test; +explain select * from masking_test_n14; -select * from masking_test; +select * from masking_test_n14; -explain select * from masking_test where key > 0; +explain select * from masking_test_n14 where key > 0; -select * from masking_test where key > 0; +select * from masking_test_n14 where key > 0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/masking_8.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/masking_8.q b/ql/src/test/queries/clientpositive/masking_8.q index 2db0652..94e4106 100644 --- a/ql/src/test/queries/clientpositive/masking_8.q +++ b/ql/src/test/queries/clientpositive/masking_8.q @@ -2,36 +2,36 @@ set hive.mapred.mode=nonstrict; set hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactoryForTest; -drop table masking_test; +drop table masking_test_n2; -create table masking_test as select cast(key as int) as key, value, '12' from src; +create table masking_test_n2 as select cast(key as int) as key, value, '12' from src; -explain select *, ROW__ID from masking_test; +explain select *, ROW__ID from masking_test_n2; -select *, ROW__ID from masking_test; +select *, ROW__ID from masking_test_n2; -explain select * from masking_test; +explain select * from masking_test_n2; -select * from masking_test; +select * from masking_test_n2; -explain select INPUT__FILE__NAME, *, ROW__ID from masking_test; +explain select INPUT__FILE__NAME, *, ROW__ID from masking_test_n2; -select INPUT__FILE__NAME, *, ROW__ID from masking_test; +select INPUT__FILE__NAME, *, ROW__ID from masking_test_n2; -drop table masking_test; +drop table masking_test_n2; -create table masking_test as select cast(key as int) as key, '12' +create table masking_test_n2 as select cast(key as int) as key, '12' '12', '12', '12', '12', '12', '12', '12', '12', '12', '12' from src; -explain select ROW__ID, * from masking_test; +explain select ROW__ID, * from masking_test_n2; -select ROW__ID, * from masking_test; +select ROW__ID, * from masking_test_n2; -drop table masking_test; +drop table masking_test_n2; -create table masking_test as select cast(key as int) as key, '12' +create table masking_test_n2 as select cast(key as int) as key, '12' '12', '12', '12', '12', '12', INPUT__FILE__NAME, '12', '12', '12', '12', '12' from src; -select INPUT__FILE__NAME, *, ROW__ID from masking_test; +select INPUT__FILE__NAME, *, ROW__ID from masking_test_n2; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/masking_acid_no_masking.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/masking_acid_no_masking.q b/ql/src/test/queries/clientpositive/masking_acid_no_masking.q index 2d19826..75de085 100644 --- a/ql/src/test/queries/clientpositive/masking_acid_no_masking.q +++ b/ql/src/test/queries/clientpositive/masking_acid_no_masking.q @@ -6,7 +6,7 @@ set hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.autho set hive.support.concurrency=true; set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; -create table nonacid (key int, value string) stored as orc; +create table nonacid_n0 (key int, value string) stored as orc; create table masking_acid_no_masking (key int, value string) clustered by (value) into 2 buckets stored as orc @@ -16,7 +16,7 @@ update masking_acid_no_masking set key=1 where value='ddd'; delete from masking_acid_no_masking where value='ddd'; -MERGE INTO masking_acid_no_masking as t using nonacid as s ON t.key = s.key +MERGE INTO masking_acid_no_masking as t using nonacid_n0 as s ON t.key = s.key WHEN MATCHED AND s.key < 5 THEN DELETE WHEN MATCHED AND s.key < 3 THEN UPDATE set key = 1 WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.value); http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/masking_disablecbo_1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/masking_disablecbo_1.q b/ql/src/test/queries/clientpositive/masking_disablecbo_1.q index aaf17e0..0a4e09b 100644 --- a/ql/src/test/queries/clientpositive/masking_disablecbo_1.q +++ b/ql/src/test/queries/clientpositive/masking_disablecbo_1.q @@ -4,27 +4,27 @@ set hive.cbo.enable=false; set hive.mapred.mode=nonstrict; set hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactoryForTest; -create table masking_test as select cast(key as int) as key, value from src; +create table masking_test_n10 as select cast(key as int) as key, value from src; -explain select * from masking_test; -select * from masking_test; +explain select * from masking_test_n10; +select * from masking_test_n10; -explain select * from masking_test where key > 0; -select * from masking_test where key > 0; +explain select * from masking_test_n10 where key > 0; +select * from masking_test_n10 where key > 0; -explain select key from masking_test where key > 0; -select key from masking_test where key > 0; +explain select key from masking_test_n10 where key > 0; +select key from masking_test_n10 where key > 0; -explain select value from masking_test where key > 0; -select value from masking_test where key > 0; +explain select value from masking_test_n10 where key > 0; +select value from masking_test_n10 where key > 0; -explain select * from masking_test join srcpart on (masking_test.key = srcpart.key); -select * from masking_test join srcpart on (masking_test.key = srcpart.key); +explain select * from masking_test_n10 join srcpart on (masking_test_n10.key = srcpart.key); +select * from masking_test_n10 join srcpart on (masking_test_n10.key = srcpart.key); -explain select * from default.masking_test where key > 0; -select * from default.masking_test where key > 0; +explain select * from default.masking_test_n10 where key > 0; +select * from default.masking_test_n10 where key > 0; -explain select * from masking_test where masking_test.key > 0; -select * from masking_test where masking_test.key > 0; +explain select * from masking_test_n10 where masking_test_n10.key > 0; +select * from masking_test_n10 where masking_test_n10.key > 0; explain select key, value from (select key, value from (select key, upper(value) as value from src where key > 0) t where key < 10) t2 where key % 2 = 0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/masking_disablecbo_2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/masking_disablecbo_2.q b/ql/src/test/queries/clientpositive/masking_disablecbo_2.q index 039ffe9..a4129c2 100644 --- a/ql/src/test/queries/clientpositive/masking_disablecbo_2.q +++ b/ql/src/test/queries/clientpositive/masking_disablecbo_2.q @@ -3,17 +3,17 @@ set hive.cbo.enable=false; set hive.mapred.mode=nonstrict; set hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactoryForTest; -create view masking_test as select cast(key as int) as key, value from src; +create view masking_test_n13 as select cast(key as int) as key, value from src; -explain select * from masking_test; -select * from masking_test; +explain select * from masking_test_n13; +select * from masking_test_n13; -explain select * from masking_test where key > 0; -select * from masking_test where key > 0; +explain select * from masking_test_n13 where key > 0; +select * from masking_test_n13 where key > 0; -explain select * from src a join masking_test b on a.key = b.value where b.key > 0; +explain select * from src a join masking_test_n13 b on a.key = b.value where b.key > 0; -explain select a.*, b.key from masking_test a join masking_test b on a.key = b.value where b.key > 0; +explain select a.*, b.key from masking_test_n13 a join masking_test_n13 b on a.key = b.value where b.key > 0; -explain select * from masking_test a union select b.* from masking_test b where b.key > 0; +explain select * from masking_test_n13 a union select b.* from masking_test_n13 b where b.key > 0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/masking_disablecbo_4.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/masking_disablecbo_4.q b/ql/src/test/queries/clientpositive/masking_disablecbo_4.q index 9981969..d81fd3b 100644 --- a/ql/src/test/queries/clientpositive/masking_disablecbo_4.q +++ b/ql/src/test/queries/clientpositive/masking_disablecbo_4.q @@ -3,8 +3,8 @@ set hive.cbo.enable=false; set hive.mapred.mode=nonstrict; set hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactoryForTest; -create table masking_test as select cast(key as int) as key, value from src; -create table masking_test_subq as select cast(key as int) as key, value from src; +create table masking_test_n3 as select cast(key as int) as key, value from src; +create table masking_test_subq_n0 as select cast(key as int) as key, value from src; explain @@ -13,20 +13,20 @@ q2 as ( select key from src where key = '5') select * from (select key from q1) a; ---should mask masking_test +--should mask masking_test_n3 explain -with q1 as ( select * from masking_test where key = '5') +with q1 as ( select * from masking_test_n3 where key = '5') select * from q1; ---should not mask masking_test_subq +--should not mask masking_test_subq_n0 explain -with masking_test_subq as ( select * from masking_test where key = '5') -select * from masking_test_subq; +with masking_test_subq_n0 as ( select * from masking_test_n3 where key = '5') +select * from masking_test_subq_n0; ---should mask masking_test_subq +--should mask masking_test_subq_n0 explain -with q1 as ( select * from masking_test where key = '5') -select * from masking_test_subq; +with q1 as ( select * from masking_test_n3 where key = '5') +select * from masking_test_subq_n0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_create.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_create.q b/ql/src/test/queries/clientpositive/materialized_view_create.q index 8452d16..bca3bd7 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_create.q +++ b/ql/src/test/queries/clientpositive/materialized_view_create.q @@ -1,25 +1,25 @@ set hive.vectorized.execution.enabled=false; -create table cmv_basetable (a int, b varchar(256), c decimal(10,2)); +create table cmv_basetable_n4 (a int, b varchar(256), c decimal(10,2)); -insert into cmv_basetable values (1, 'alfred', 10.30),(2, 'bob', 3.14),(2, 'bonnie', 172342.2),(3, 'calvin', 978.76),(3, 'charlie', 9.8); +insert into cmv_basetable_n4 values (1, 'alfred', 10.30),(2, 'bob', 3.14),(2, 'bonnie', 172342.2),(3, 'calvin', 978.76),(3, 'charlie', 9.8); -create materialized view cmv_mat_view as select a, b, c from cmv_basetable; +create materialized view cmv_mat_view_n4 as select a, b, c from cmv_basetable_n4; -desc formatted cmv_mat_view; +desc formatted cmv_mat_view_n4; -select * from cmv_mat_view; +select * from cmv_mat_view_n4; -create materialized view if not exists cmv_mat_view2 as select a, c from cmv_basetable; +create materialized view if not exists cmv_mat_view2_n1 as select a, c from cmv_basetable_n4; -desc formatted cmv_mat_view2; +desc formatted cmv_mat_view2_n1; -select * from cmv_mat_view2; +select * from cmv_mat_view2_n1; -create materialized view if not exists cmv_mat_view3 as select * from cmv_basetable where a > 1; +create materialized view if not exists cmv_mat_view3 as select * from cmv_basetable_n4 where a > 1; select * from cmv_mat_view3; -create materialized view cmv_mat_view4 comment 'this is a comment' as select a, sum(c) from cmv_basetable group by a; +create materialized view cmv_mat_view4 comment 'this is a comment' as select a, sum(c) from cmv_basetable_n4 group by a; select * from cmv_mat_view4; @@ -29,14 +29,14 @@ create table cmv_basetable2 (d int, e varchar(256), f decimal(10,2)); insert into cmv_basetable2 values (4, 'alfred', 100.30),(4, 'bob', 6133.14),(5, 'bonnie', 172.2),(6, 'calvin', 8.76),(17, 'charlie', 13144339.8); -create materialized view cmv_mat_view5 tblproperties ('key'='value') as select a, b, d, c, f from cmv_basetable t1 join cmv_basetable2 t2 on (t1.b = t2.e); +create materialized view cmv_mat_view5 tblproperties ('key'='value') as select a, b, d, c, f from cmv_basetable_n4 t1 join cmv_basetable2 t2 on (t1.b = t2.e); select * from cmv_mat_view5; show tblproperties cmv_mat_view5; -drop materialized view cmv_mat_view; -drop materialized view cmv_mat_view2; +drop materialized view cmv_mat_view_n4; +drop materialized view cmv_mat_view2_n1; drop materialized view cmv_mat_view3; drop materialized view cmv_mat_view4; drop materialized view cmv_mat_view5; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_create_rewrite.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite.q b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite.q index ede548d..b67b888 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite.q +++ b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite.q @@ -6,82 +6,82 @@ set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.strict.checks.cartesian.product=false; set hive.materializedview.rewriting=true; -create table cmv_basetable (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); +create table cmv_basetable_n10 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into cmv_basetable values +insert into cmv_basetable_n10 values (1, 'alfred', 10.30, 2), (2, 'bob', 3.14, 3), (2, 'bonnie', 172342.2, 3), (3, 'calvin', 978.76, 3), (3, 'charlie', 9.8, 1); -analyze table cmv_basetable compute statistics for columns; +analyze table cmv_basetable_n10 compute statistics for columns; -create materialized view cmv_mat_view enable rewrite -as select a, b, c from cmv_basetable where a = 2; +create materialized view cmv_mat_view_n10 enable rewrite +as select a, b, c from cmv_basetable_n10 where a = 2; -select * from cmv_mat_view; +select * from cmv_mat_view_n10; -show tblproperties cmv_mat_view; +show tblproperties cmv_mat_view_n10; -create materialized view if not exists cmv_mat_view2 enable rewrite -as select a, c from cmv_basetable where a = 3; +create materialized view if not exists cmv_mat_view2_n4 enable rewrite +as select a, c from cmv_basetable_n10 where a = 3; -select * from cmv_mat_view2; +select * from cmv_mat_view2_n4; -show tblproperties cmv_mat_view2; +show tblproperties cmv_mat_view2_n4; explain -select a, c from cmv_basetable where a = 3; +select a, c from cmv_basetable_n10 where a = 3; -select a, c from cmv_basetable where a = 3; +select a, c from cmv_basetable_n10 where a = 3; -alter materialized view cmv_mat_view2 disable rewrite; +alter materialized view cmv_mat_view2_n4 disable rewrite; explain select * from ( - (select a, c from cmv_basetable where a = 3) table1 + (select a, c from cmv_basetable_n10 where a = 3) table1 join - (select a, c from cmv_basetable where d = 3) table2 + (select a, c from cmv_basetable_n10 where d = 3) table2 on table1.a = table2.a); select * from ( - (select a, c from cmv_basetable where a = 3) table1 + (select a, c from cmv_basetable_n10 where a = 3) table1 join - (select a, c from cmv_basetable where d = 3) table2 + (select a, c from cmv_basetable_n10 where d = 3) table2 on table1.a = table2.a); explain -alter materialized view cmv_mat_view2 enable rewrite; +alter materialized view cmv_mat_view2_n4 enable rewrite; -alter materialized view cmv_mat_view2 enable rewrite; +alter materialized view cmv_mat_view2_n4 enable rewrite; explain select * from ( - (select a, c from cmv_basetable where a = 3) table1 + (select a, c from cmv_basetable_n10 where a = 3) table1 join - (select a, c from cmv_basetable where d = 3) table2 + (select a, c from cmv_basetable_n10 where d = 3) table2 on table1.a = table2.a); select * from ( - (select a, c from cmv_basetable where a = 3) table1 + (select a, c from cmv_basetable_n10 where a = 3) table1 join - (select a, c from cmv_basetable where d = 3) table2 + (select a, c from cmv_basetable_n10 where d = 3) table2 on table1.a = table2.a); -drop materialized view cmv_mat_view2; +drop materialized view cmv_mat_view2_n4; explain select * from ( - (select a, c from cmv_basetable where a = 3) table1 + (select a, c from cmv_basetable_n10 where a = 3) table1 join - (select a, c from cmv_basetable where d = 3) table2 + (select a, c from cmv_basetable_n10 where d = 3) table2 on table1.a = table2.a); select * from ( - (select a, c from cmv_basetable where a = 3) table1 + (select a, c from cmv_basetable_n10 where a = 3) table1 join - (select a, c from cmv_basetable where d = 3) table2 + (select a, c from cmv_basetable_n10 where d = 3) table2 on table1.a = table2.a); -drop materialized view cmv_mat_view; +drop materialized view cmv_mat_view_n10; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_2.q b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_2.q index 82c06e4..21579d4 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_2.q +++ b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_2.q @@ -4,90 +4,90 @@ set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.strict.checks.cartesian.product=false; set hive.materializedview.rewriting=true; -create table cmv_basetable (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); +create table cmv_basetable_n9 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into cmv_basetable values +insert into cmv_basetable_n9 values (1, 'alfred', 10.30, 2), (2, 'bob', 3.14, 3), (2, 'bonnie', 172342.2, 3), (3, 'calvin', 978.76, 3), (3, 'charlie', 9.8, 1); -analyze table cmv_basetable compute statistics for columns; +analyze table cmv_basetable_n9 compute statistics for columns; -create materialized view cmv_mat_view enable rewrite -as select b from cmv_basetable where c > 10.0 group by a, b, c; +create materialized view cmv_mat_view_n9 enable rewrite +as select b from cmv_basetable_n9 where c > 10.0 group by a, b, c; -- CANNOT BE TRIGGERED explain -select b from cmv_basetable where c > 20.0 group by a, b; +select b from cmv_basetable_n9 where c > 20.0 group by a, b; -select b from cmv_basetable where c > 20.0 group by a, b; +select b from cmv_basetable_n9 where c > 20.0 group by a, b; create materialized view cmv_mat_view_2 enable rewrite -as select b, c from cmv_basetable where c > 10.0 group by a, b, c; +as select b, c from cmv_basetable_n9 where c > 10.0 group by a, b, c; -- CANNOT BE TRIGGERED explain -select b from cmv_basetable where c > 20.0 group by a, b; +select b from cmv_basetable_n9 where c > 20.0 group by a, b; -select b from cmv_basetable where c > 20.0 group by a, b; +select b from cmv_basetable_n9 where c > 20.0 group by a, b; create materialized view cmv_mat_view_3 enable rewrite -as select a, b, c from cmv_basetable where c > 10.0 group by a, b, c; +as select a, b, c from cmv_basetable_n9 where c > 10.0 group by a, b, c; -- CAN BE TRIGGERED explain -select b from cmv_basetable where c > 20.0 group by a, b; +select b from cmv_basetable_n9 where c > 20.0 group by a, b; -select b from cmv_basetable where c > 20.0 group by a, b; +select b from cmv_basetable_n9 where c > 20.0 group by a, b; create materialized view cmv_mat_view_4 enable rewrite -as select a, b from cmv_basetable group by a, b; +as select a, b from cmv_basetable_n9 group by a, b; -- CAN BE TRIGGERED explain -select b from cmv_basetable group by b; +select b from cmv_basetable_n9 group by b; -select b from cmv_basetable group by b; +select b from cmv_basetable_n9 group by b; -create table cmv_basetable_2 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); +create table cmv_basetable_2_n4 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into cmv_basetable_2 values +insert into cmv_basetable_2_n4 values (1, 'alfred', 10.30, 2), (3, 'calvin', 978.76, 3); -analyze table cmv_basetable_2 compute statistics for columns; +analyze table cmv_basetable_2_n4 compute statistics for columns; create materialized view cmv_mat_view_5 enable rewrite -as select cmv_basetable.a, cmv_basetable_2.c - from cmv_basetable join cmv_basetable_2 on (cmv_basetable.a = cmv_basetable_2.a) - where cmv_basetable_2.c > 10.0 - group by cmv_basetable.a, cmv_basetable_2.c; +as select cmv_basetable_n9.a, cmv_basetable_2_n4.c + from cmv_basetable_n9 join cmv_basetable_2_n4 on (cmv_basetable_n9.a = cmv_basetable_2_n4.a) + where cmv_basetable_2_n4.c > 10.0 + group by cmv_basetable_n9.a, cmv_basetable_2_n4.c; explain -select cmv_basetable.a -from cmv_basetable join cmv_basetable_2 on (cmv_basetable.a = cmv_basetable_2.a) -where cmv_basetable_2.c > 10.10 -group by cmv_basetable.a, cmv_basetable_2.c; +select cmv_basetable_n9.a +from cmv_basetable_n9 join cmv_basetable_2_n4 on (cmv_basetable_n9.a = cmv_basetable_2_n4.a) +where cmv_basetable_2_n4.c > 10.10 +group by cmv_basetable_n9.a, cmv_basetable_2_n4.c; -select cmv_basetable.a -from cmv_basetable join cmv_basetable_2 on (cmv_basetable.a = cmv_basetable_2.a) -where cmv_basetable_2.c > 10.10 -group by cmv_basetable.a, cmv_basetable_2.c; +select cmv_basetable_n9.a +from cmv_basetable_n9 join cmv_basetable_2_n4 on (cmv_basetable_n9.a = cmv_basetable_2_n4.a) +where cmv_basetable_2_n4.c > 10.10 +group by cmv_basetable_n9.a, cmv_basetable_2_n4.c; explain -select cmv_basetable.a -from cmv_basetable join cmv_basetable_2 on (cmv_basetable.a = cmv_basetable_2.a) -where cmv_basetable_2.c > 10.10 -group by cmv_basetable.a; +select cmv_basetable_n9.a +from cmv_basetable_n9 join cmv_basetable_2_n4 on (cmv_basetable_n9.a = cmv_basetable_2_n4.a) +where cmv_basetable_2_n4.c > 10.10 +group by cmv_basetable_n9.a; -select cmv_basetable.a -from cmv_basetable join cmv_basetable_2 on (cmv_basetable.a = cmv_basetable_2.a) -where cmv_basetable_2.c > 10.10 -group by cmv_basetable.a; +select cmv_basetable_n9.a +from cmv_basetable_n9 join cmv_basetable_2_n4 on (cmv_basetable_n9.a = cmv_basetable_2_n4.a) +where cmv_basetable_2_n4.c > 10.10 +group by cmv_basetable_n9.a; -drop materialized view cmv_mat_view; +drop materialized view cmv_mat_view_n9; drop materialized view cmv_mat_view_2; drop materialized view cmv_mat_view_3; drop materialized view cmv_mat_view_4; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_4.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_4.q b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_4.q index ff1290b..ec0413f 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_4.q +++ b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_4.q @@ -4,162 +4,162 @@ set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.strict.checks.cartesian.product=false; set hive.materializedview.rewriting=true; -create table cmv_basetable (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); +create table cmv_basetable_n5 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into cmv_basetable values +insert into cmv_basetable_n5 values (1, 'alfred', 10.30, 2), (2, 'bob', 3.14, 3), (2, 'bonnie', 172342.2, 3), (3, 'calvin', 978.76, 3), (3, 'charlie', 9.8, 1); -analyze table cmv_basetable compute statistics for columns; +analyze table cmv_basetable_n5 compute statistics for columns; -create table cmv_basetable_2 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); +create table cmv_basetable_2_n2 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into cmv_basetable_2 values +insert into cmv_basetable_2_n2 values (1, 'alfred', 10.30, 2), (3, 'calvin', 978.76, 3); -analyze table cmv_basetable_2 compute statistics for columns; +analyze table cmv_basetable_2_n2 compute statistics for columns; -- CREATE VIEW WITH REWRITE DISABLED EXPLAIN -CREATE MATERIALIZED VIEW cmv_mat_view TBLPROPERTIES ('transactional'='true') AS - SELECT cmv_basetable.a, cmv_basetable_2.c, sum(cmv_basetable_2.d) - FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) - WHERE cmv_basetable_2.c > 10.0 - GROUP BY cmv_basetable.a, cmv_basetable_2.c; +CREATE MATERIALIZED VIEW cmv_mat_view_n5 TBLPROPERTIES ('transactional'='true') AS + SELECT cmv_basetable_n5.a, cmv_basetable_2_n2.c, sum(cmv_basetable_2_n2.d) + FROM cmv_basetable_n5 JOIN cmv_basetable_2_n2 ON (cmv_basetable_n5.a = cmv_basetable_2_n2.a) + WHERE cmv_basetable_2_n2.c > 10.0 + GROUP BY cmv_basetable_n5.a, cmv_basetable_2_n2.c; -CREATE MATERIALIZED VIEW cmv_mat_view TBLPROPERTIES ('transactional'='true') AS - SELECT cmv_basetable.a, cmv_basetable_2.c, sum(cmv_basetable_2.d) - FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) - WHERE cmv_basetable_2.c > 10.0 - GROUP BY cmv_basetable.a, cmv_basetable_2.c; +CREATE MATERIALIZED VIEW cmv_mat_view_n5 TBLPROPERTIES ('transactional'='true') AS + SELECT cmv_basetable_n5.a, cmv_basetable_2_n2.c, sum(cmv_basetable_2_n2.d) + FROM cmv_basetable_n5 JOIN cmv_basetable_2_n2 ON (cmv_basetable_n5.a = cmv_basetable_2_n2.a) + WHERE cmv_basetable_2_n2.c > 10.0 + GROUP BY cmv_basetable_n5.a, cmv_basetable_2_n2.c; -analyze table cmv_mat_view compute statistics for columns; +analyze table cmv_mat_view_n5 compute statistics for columns; -DESCRIBE FORMATTED cmv_mat_view; +DESCRIBE FORMATTED cmv_mat_view_n5; -- CANNOT USE THE VIEW, IT IS DISABLED FOR REWRITE EXPLAIN -SELECT cmv_basetable.a, sum(cmv_basetable_2.d) -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n5.a, sum(cmv_basetable_2_n2.d) +FROM cmv_basetable_n5 join cmv_basetable_2_n2 ON (cmv_basetable_n5.a = cmv_basetable_2_n2.a) +WHERE cmv_basetable_2_n2.c > 10.10 +GROUP BY cmv_basetable_n5.a, cmv_basetable_2_n2.c; -SELECT cmv_basetable.a, sum(cmv_basetable_2.d) -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n5.a, sum(cmv_basetable_2_n2.d) +FROM cmv_basetable_n5 JOIN cmv_basetable_2_n2 ON (cmv_basetable_n5.a = cmv_basetable_2_n2.a) +WHERE cmv_basetable_2_n2.c > 10.10 +GROUP BY cmv_basetable_n5.a, cmv_basetable_2_n2.c; -insert into cmv_basetable_2 values +insert into cmv_basetable_2_n2 values (3, 'charlie', 15.8, 1); -analyze table cmv_basetable_2 compute statistics for columns; +analyze table cmv_basetable_2_n2 compute statistics for columns; -- ENABLE FOR REWRITE EXPLAIN -ALTER MATERIALIZED VIEW cmv_mat_view ENABLE REWRITE; +ALTER MATERIALIZED VIEW cmv_mat_view_n5 ENABLE REWRITE; -ALTER MATERIALIZED VIEW cmv_mat_view ENABLE REWRITE; +ALTER MATERIALIZED VIEW cmv_mat_view_n5 ENABLE REWRITE; -DESCRIBE FORMATTED cmv_mat_view; +DESCRIBE FORMATTED cmv_mat_view_n5; -- CANNOT USE THE VIEW, IT IS OUTDATED EXPLAIN -SELECT cmv_basetable.a, sum(cmv_basetable_2.d) -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n5.a, sum(cmv_basetable_2_n2.d) +FROM cmv_basetable_n5 join cmv_basetable_2_n2 ON (cmv_basetable_n5.a = cmv_basetable_2_n2.a) +WHERE cmv_basetable_2_n2.c > 10.10 +GROUP BY cmv_basetable_n5.a, cmv_basetable_2_n2.c; -SELECT cmv_basetable.a, sum(cmv_basetable_2.d) -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n5.a, sum(cmv_basetable_2_n2.d) +FROM cmv_basetable_n5 JOIN cmv_basetable_2_n2 ON (cmv_basetable_n5.a = cmv_basetable_2_n2.a) +WHERE cmv_basetable_2_n2.c > 10.10 +GROUP BY cmv_basetable_n5.a, cmv_basetable_2_n2.c; -- REBUILD EXPLAIN -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n5 REBUILD; -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n5 REBUILD; -DESCRIBE FORMATTED cmv_mat_view; +DESCRIBE FORMATTED cmv_mat_view_n5; -- NOW IT CAN BE USED AGAIN EXPLAIN -SELECT cmv_basetable.a, sum(cmv_basetable_2.d) -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n5.a, sum(cmv_basetable_2_n2.d) +FROM cmv_basetable_n5 join cmv_basetable_2_n2 ON (cmv_basetable_n5.a = cmv_basetable_2_n2.a) +WHERE cmv_basetable_2_n2.c > 10.10 +GROUP BY cmv_basetable_n5.a, cmv_basetable_2_n2.c; -SELECT cmv_basetable.a, sum(cmv_basetable_2.d) -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n5.a, sum(cmv_basetable_2_n2.d) +FROM cmv_basetable_n5 JOIN cmv_basetable_2_n2 ON (cmv_basetable_n5.a = cmv_basetable_2_n2.a) +WHERE cmv_basetable_2_n2.c > 10.10 +GROUP BY cmv_basetable_n5.a, cmv_basetable_2_n2.c; -- NOW AN UPDATE -UPDATE cmv_basetable_2 SET a=2 WHERE a=1; +UPDATE cmv_basetable_2_n2 SET a=2 WHERE a=1; -- INCREMENTAL REBUILD CANNOT BE TRIGGERED EXPLAIN -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n5 REBUILD; -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n5 REBUILD; -- MV CAN BE USED EXPLAIN -SELECT cmv_basetable.a, sum(cmv_basetable_2.d) -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n5.a, sum(cmv_basetable_2_n2.d) +FROM cmv_basetable_n5 join cmv_basetable_2_n2 ON (cmv_basetable_n5.a = cmv_basetable_2_n2.a) +WHERE cmv_basetable_2_n2.c > 10.10 +GROUP BY cmv_basetable_n5.a, cmv_basetable_2_n2.c; -SELECT cmv_basetable.a, sum(cmv_basetable_2.d) -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n5.a, sum(cmv_basetable_2_n2.d) +FROM cmv_basetable_n5 JOIN cmv_basetable_2_n2 ON (cmv_basetable_n5.a = cmv_basetable_2_n2.a) +WHERE cmv_basetable_2_n2.c > 10.10 +GROUP BY cmv_basetable_n5.a, cmv_basetable_2_n2.c; -- NOW A DELETE -DELETE FROM cmv_basetable_2 WHERE a=2; +DELETE FROM cmv_basetable_2_n2 WHERE a=2; -- INCREMENTAL REBUILD CANNOT BE TRIGGERED EXPLAIN -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n5 REBUILD; -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n5 REBUILD; -- MV CAN BE USED EXPLAIN -SELECT cmv_basetable.a, sum(cmv_basetable_2.d) -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n5.a, sum(cmv_basetable_2_n2.d) +FROM cmv_basetable_n5 join cmv_basetable_2_n2 ON (cmv_basetable_n5.a = cmv_basetable_2_n2.a) +WHERE cmv_basetable_2_n2.c > 10.10 +GROUP BY cmv_basetable_n5.a, cmv_basetable_2_n2.c; -SELECT cmv_basetable.a, sum(cmv_basetable_2.d) -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n5.a, sum(cmv_basetable_2_n2.d) +FROM cmv_basetable_n5 JOIN cmv_basetable_2_n2 ON (cmv_basetable_n5.a = cmv_basetable_2_n2.a) +WHERE cmv_basetable_2_n2.c > 10.10 +GROUP BY cmv_basetable_n5.a, cmv_basetable_2_n2.c; -- NOW AN INSERT -insert into cmv_basetable_2 values +insert into cmv_basetable_2_n2 values (1, 'charlie', 15.8, 1); -- INCREMENTAL REBUILD CAN BE TRIGGERED AGAIN EXPLAIN -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n5 REBUILD; -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n5 REBUILD; -- MV CAN BE USED EXPLAIN -SELECT cmv_basetable.a, sum(cmv_basetable_2.d) -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n5.a, sum(cmv_basetable_2_n2.d) +FROM cmv_basetable_n5 join cmv_basetable_2_n2 ON (cmv_basetable_n5.a = cmv_basetable_2_n2.a) +WHERE cmv_basetable_2_n2.c > 10.10 +GROUP BY cmv_basetable_n5.a, cmv_basetable_2_n2.c; -SELECT cmv_basetable.a, sum(cmv_basetable_2.d) -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n5.a, sum(cmv_basetable_2_n2.d) +FROM cmv_basetable_n5 JOIN cmv_basetable_2_n2 ON (cmv_basetable_n5.a = cmv_basetable_2_n2.a) +WHERE cmv_basetable_2_n2.c > 10.10 +GROUP BY cmv_basetable_n5.a, cmv_basetable_2_n2.c; -drop materialized view cmv_mat_view; +drop materialized view cmv_mat_view_n5;