Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 00BD3200C37 for ; Sun, 19 Mar 2017 07:54:29 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id F36FB160B6E; Sun, 19 Mar 2017 06:54:28 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id B2F42160B94 for ; Sun, 19 Mar 2017 07:54:26 +0100 (CET) Received: (qmail 71071 invoked by uid 500); 19 Mar 2017 06:54:25 -0000 Mailing-List: contact commits-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: hive-dev@hive.apache.org Delivered-To: mailing list commits@hive.apache.org Received: (qmail 70173 invoked by uid 99); 19 Mar 2017 06:54:25 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 19 Mar 2017 06:54:25 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id DC9A4DFE8F; Sun, 19 Mar 2017 06:54:24 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: mmccline@apache.org To: commits@hive.apache.org Date: Sun, 19 Mar 2017 06:54:29 -0000 Message-Id: In-Reply-To: <4bbbb18d0b95475fa12ccf1ca033fe75@git.apache.org> References: <4bbbb18d0b95475fa12ccf1ca033fe75@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [6/7] hive git commit: HIVE-14016 : Vectorization: Add support for Grouping Sets (Matt McCline, reviewed by Gopal Vijayaraghavan, Jesus Camacho Rodriguez) archived-at: Sun, 19 Mar 2017 06:54:29 -0000 http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/groupby_grouping_id1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_grouping_id1.q b/ql/src/test/queries/clientpositive/groupby_grouping_id1.q index d43ea37..9948ce9 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_id1.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_id1.q @@ -2,6 +2,8 @@ CREATE TABLE T1(key STRING, val STRING) STORED AS TEXTFILE; LOAD DATA LOCAL INPATH '../../data/files/T1.txt' INTO TABLE T1; +-- SORT_QUERY_RESULTS + SELECT key, val, GROUPING__ID from T1 group by key, val with cube; SELECT key, val, GROUPING__ID from T1 group by cube(key, val); http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/groupby_grouping_id2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_grouping_id2.q b/ql/src/test/queries/clientpositive/groupby_grouping_id2.q index 77a1638..cc7f9e4 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_id2.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_id2.q @@ -1,3 +1,7 @@ +set hive.explain.user=false; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; + CREATE TABLE T1(key INT, value INT) STORED AS TEXTFILE; LOAD DATA LOCAL INPATH '../../data/files/groupby_groupingid.txt' INTO TABLE T1; http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/groupby_grouping_id3.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_grouping_id3.q b/ql/src/test/queries/clientpositive/groupby_grouping_id3.q index c6746a8..955dbe0 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_id3.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_id3.q @@ -6,6 +6,12 @@ set hive.cbo.enable = false; -- SORT_QUERY_RESULTS +EXPLAIN +SELECT key, value, GROUPING__ID, count(*) +FROM T1 +GROUP BY key, value +GROUPING SETS ((), (key)) +HAVING GROUPING__ID = 1; SELECT key, value, GROUPING__ID, count(*) FROM T1 GROUP BY key, value @@ -14,6 +20,12 @@ HAVING GROUPING__ID = 1; set hive.cbo.enable = true; +EXPLAIN +SELECT key, value, GROUPING__ID, count(*) +FROM T1 +GROUP BY key, value +GROUPING SETS ((), (key)) +HAVING GROUPING__ID = 1; SELECT key, value, GROUPING__ID, count(*) FROM T1 GROUP BY key, value http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/groupby_grouping_sets1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_grouping_sets1.q b/ql/src/test/queries/clientpositive/groupby_grouping_sets1.q index e239a87..c22c97f 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets1.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets1.q @@ -1,19 +1,39 @@ +set hive.explain.user=false; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; + +-- SORT_QUERY_RESULTS + CREATE TABLE T1(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1; SELECT * FROM T1; +EXPLAIN +SELECT a, b, count(*) from T1 group by a, b with cube; SELECT a, b, count(*) from T1 group by a, b with cube; +EXPLAIN +SELECT a, b, count(*) from T1 group by cube(a, b); SELECT a, b, count(*) from T1 group by cube(a, b); +EXPLAIN +SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b), b, ()); SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b), b, ()); +EXPLAIN +SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b)); SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b)); +EXPLAIN +SELECT a FROM T1 GROUP BY a, b, c GROUPING SETS (a, b, c); SELECT a FROM T1 GROUP BY a, b, c GROUPING SETS (a, b, c); +EXPLAIN +SELECT a FROM T1 GROUP BY a GROUPING SETS ((a), (a)); SELECT a FROM T1 GROUP BY a GROUPING SETS ((a), (a)); +EXPLAIN +SELECT a + b, count(*) FROM T1 GROUP BY a + b GROUPING SETS (a+b); SELECT a + b, count(*) FROM T1 GROUP BY a + b GROUPING SETS (a+b); http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/groupby_grouping_sets2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_grouping_sets2.q b/ql/src/test/queries/clientpositive/groupby_grouping_sets2.q index b470964..90e6325 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets2.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets2.q @@ -1,6 +1,10 @@ +set hive.explain.user=false; +set hive.cli.print.header=true; set hive.mapred.mode=nonstrict; set hive.new.job.grouping.set.cardinality=2; +-- SORT_QUERY_RESULTS + CREATE TABLE T1(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1; http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/groupby_grouping_sets3.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_grouping_sets3.q b/ql/src/test/queries/clientpositive/groupby_grouping_sets3.q index 3c1a5e7..16421e8 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets3.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets3.q @@ -1,3 +1,9 @@ +set hive.explain.user=false; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; + +-- SORT_QUERY_RESULTS + -- In this test, 2 files are loaded into table T1. The data contains rows with the same value of a and b, -- with different number of rows for a and b in each file. Since bucketizedHiveInputFormat is used, -- this tests that the aggregate function stores the partial aggregate state correctly even if an http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/groupby_grouping_sets4.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_grouping_sets4.q b/ql/src/test/queries/clientpositive/groupby_grouping_sets4.q index 6e3201c..1074a3b 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets4.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets4.q @@ -1,3 +1,4 @@ +set hive.explain.user=false; set hive.mapred.mode=nonstrict; set hive.merge.mapfiles = false; set hive.merge.mapredfiles = false; http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/groupby_grouping_sets5.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_grouping_sets5.q b/ql/src/test/queries/clientpositive/groupby_grouping_sets5.q index c1c98b3..570d464 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets5.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets5.q @@ -7,6 +7,8 @@ CREATE TABLE T1(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMIN LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1; +-- SORT_QUERY_RESULTS + -- This tests that cubes and rollups work fine where the source is a sub-query EXPLAIN SELECT a, b, count(*) FROM http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/groupby_grouping_sets6.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_grouping_sets6.q b/ql/src/test/queries/clientpositive/groupby_grouping_sets6.q index 5cdb4a5..e537bce 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets6.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets6.q @@ -3,6 +3,8 @@ CREATE TABLE T1(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMIN LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1; +-- SORT_QUERY_RESULTS + set hive.optimize.ppd = false; -- This filter is not pushed down http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/groupby_grouping_sets_grouping.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_grouping_sets_grouping.q b/ql/src/test/queries/clientpositive/groupby_grouping_sets_grouping.q index 7856097..34759ca 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets_grouping.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets_grouping.q @@ -1,3 +1,6 @@ + +-- SORT_QUERY_RESULTS + CREATE TABLE T1(key INT, value INT) STORED AS TEXTFILE; LOAD DATA LOCAL INPATH '../../data/files/groupby_groupingid.txt' INTO TABLE T1; http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/groupby_grouping_sets_limit.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_grouping_sets_limit.q b/ql/src/test/queries/clientpositive/groupby_grouping_sets_limit.q index db88d5f..b6c5143 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets_limit.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets_limit.q @@ -1,3 +1,6 @@ + +-- SORT_QUERY_RESULTS + CREATE TABLE T1(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1; http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/vector_groupby_cube1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/vector_groupby_cube1.q b/ql/src/test/queries/clientpositive/vector_groupby_cube1.q new file mode 100644 index 0000000..fd2f0de --- /dev/null +++ b/ql/src/test/queries/clientpositive/vector_groupby_cube1.q @@ -0,0 +1,55 @@ +set hive.mapred.mode=nonstrict; +set hive.map.aggr=true; +set hive.groupby.skewindata=false; + +-- SORT_QUERY_RESULTS + +CREATE TABLE T1(key STRING, val STRING) STORED AS TEXTFILE; + +LOAD DATA LOCAL INPATH '../../data/files/T1.txt' INTO TABLE T1; + +EXPLAIN +SELECT key, val, count(1) FROM T1 GROUP BY key, val with cube; +EXPLAIN +SELECT key, val, count(1) FROM T1 GROUP BY CUBE(key, val); + +SELECT key, val, count(1) FROM T1 GROUP BY key, val with cube; + +EXPLAIN +SELECT key, val, GROUPING__ID, count(1) FROM T1 GROUP BY key, val with cube; + +SELECT key, val, GROUPING__ID, count(1) FROM T1 GROUP BY key, val with cube; + +EXPLAIN +SELECT key, count(distinct val) FROM T1 GROUP BY key with cube; + +SELECT key, count(distinct val) FROM T1 GROUP BY key with cube; + +set hive.groupby.skewindata=true; + +EXPLAIN +SELECT key, val, count(1) FROM T1 GROUP BY key, val with cube; + +SELECT key, val, count(1) FROM T1 GROUP BY key, val with cube; + +EXPLAIN +SELECT key, count(distinct val) FROM T1 GROUP BY key with cube; + +SELECT key, count(distinct val) FROM T1 GROUP BY key with cube; + + +set hive.multigroupby.singlereducer=true; + +CREATE TABLE T2(key1 STRING, key2 STRING, val INT) STORED AS TEXTFILE; +CREATE TABLE T3(key1 STRING, key2 STRING, val INT) STORED AS TEXTFILE; + +EXPLAIN +FROM T1 +INSERT OVERWRITE TABLE T2 SELECT key, val, count(1) group by key, val with cube +INSERT OVERWRITE TABLE T3 SELECT key, val, sum(1) group by key, val with cube; + + +FROM T1 +INSERT OVERWRITE TABLE T2 SELECT key, val, count(1) group by key, val with cube +INSERT OVERWRITE TABLE T3 SELECT key, val, sum(1) group by key, val with cube; + http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/vector_groupby_grouping_id1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/vector_groupby_grouping_id1.q b/ql/src/test/queries/clientpositive/vector_groupby_grouping_id1.q new file mode 100644 index 0000000..d957433 --- /dev/null +++ b/ql/src/test/queries/clientpositive/vector_groupby_grouping_id1.q @@ -0,0 +1,23 @@ +set hive.explain.user=false; +SET hive.vectorized.execution.enabled=true; +SET hive.vectorized.execution.reduce.enabled=true; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; + +CREATE TABLE T1_text(key STRING, val STRING) STORED AS TEXTFILE; + +LOAD DATA LOCAL INPATH '../../data/files/T1.txt' INTO TABLE T1_text; + +CREATE TABLE T1 STORED AS ORC AS SELECT * FROM T1_text; + +-- SORT_QUERY_RESULTS + +SELECT key, val, GROUPING__ID from T1 group by key, val with cube; +SELECT key, val, GROUPING__ID from T1 group by cube(key, val); + +SELECT GROUPING__ID, key, val from T1 group by key, val with rollup; +SELECT GROUPING__ID, key, val from T1 group by rollup (key, val); + +SELECT key, val, GROUPING__ID, CASE WHEN GROUPING__ID == 0 THEN "0" WHEN GROUPING__ID == 1 THEN "1" WHEN GROUPING__ID == 2 THEN "2" WHEN GROUPING__ID == 3 THEN "3" ELSE "nothing" END from T1 group by key, val with cube; +SELECT key, val, GROUPING__ID, CASE WHEN GROUPING__ID == 0 THEN "0" WHEN GROUPING__ID == 1 THEN "1" WHEN GROUPING__ID == 2 THEN "2" WHEN GROUPING__ID == 3 THEN "3" ELSE "nothing" END from T1 group by cube(key, val); + http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/vector_groupby_grouping_id2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/vector_groupby_grouping_id2.q b/ql/src/test/queries/clientpositive/vector_groupby_grouping_id2.q new file mode 100644 index 0000000..c4f6722 --- /dev/null +++ b/ql/src/test/queries/clientpositive/vector_groupby_grouping_id2.q @@ -0,0 +1,65 @@ +set hive.explain.user=false; +SET hive.vectorized.execution.enabled=true; +SET hive.vectorized.execution.reduce.enabled=true; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; + +CREATE TABLE T1_text(key INT, value INT) STORED AS TEXTFILE; + +LOAD DATA LOCAL INPATH '../../data/files/groupby_groupingid.txt' INTO TABLE T1_text; + +CREATE TABLE T1 STORED AS ORC AS SELECT * FROM T1_text; + +set hive.groupby.skewindata = true; + +-- SORT_QUERY_RESULTS + +SELECT key, value, GROUPING__ID, count(*) from T1 GROUP BY key, value WITH ROLLUP; +SELECT key, value, GROUPING__ID, count(*) from T1 GROUP BY ROLLUP (key, value); + +SELECT GROUPING__ID, count(*) +FROM +( +SELECT key, value, GROUPING__ID, count(*) from T1 GROUP BY key, value WITH ROLLUP +) t +GROUP BY GROUPING__ID; + +SELECT GROUPING__ID, count(*) +FROM +( +SELECT key, value, GROUPING__ID, count(*) from T1 GROUP BY ROLLUP(key, value) +) t +GROUP BY GROUPING__ID; + + +SELECT t1.GROUPING__ID, t2.GROUPING__ID FROM (SELECT GROUPING__ID FROM T1 GROUP BY key,value WITH ROLLUP) t1 +JOIN +(SELECT GROUPING__ID FROM T1 GROUP BY key, value WITH ROLLUP) t2 +ON t1.GROUPING__ID = t2.GROUPING__ID; + +SELECT t1.GROUPING__ID, t2.GROUPING__ID FROM (SELECT GROUPING__ID FROM T1 GROUP BY ROLLUP(key,value)) t1 +JOIN +(SELECT GROUPING__ID FROM T1 GROUP BY ROLLUP(key, value)) t2 +ON t1.GROUPING__ID = t2.GROUPING__ID; + + + + + +set hive.groupby.skewindata = false; + +SELECT key, value, GROUPING__ID, count(*) from T1 GROUP BY key, value WITH ROLLUP; + +SELECT GROUPING__ID, count(*) +FROM +( +SELECT key, value, GROUPING__ID, count(*) from T1 GROUP BY key, value WITH ROLLUP +) t +GROUP BY GROUPING__ID; + +SELECT t1.GROUPING__ID, t2.GROUPING__ID FROM (SELECT GROUPING__ID FROM T1 GROUP BY key,value WITH ROLLUP) t1 +JOIN +(SELECT GROUPING__ID FROM T1 GROUP BY key, value WITH ROLLUP) t2 +ON t1.GROUPING__ID = t2.GROUPING__ID; + + http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/vector_groupby_grouping_id3.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/vector_groupby_grouping_id3.q b/ql/src/test/queries/clientpositive/vector_groupby_grouping_id3.q new file mode 100644 index 0000000..29e9211 --- /dev/null +++ b/ql/src/test/queries/clientpositive/vector_groupby_grouping_id3.q @@ -0,0 +1,42 @@ +set hive.explain.user=false; +SET hive.vectorized.execution.enabled=true; +SET hive.vectorized.execution.reduce.enabled=true; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; + +CREATE TABLE T1_text(key INT, value INT) STORED AS TEXTFILE; + +LOAD DATA LOCAL INPATH '../../data/files/groupby_groupingid.txt' INTO TABLE T1_text; + +CREATE TABLE T1 STORED AS ORC AS SELECT * FROM T1_text; + +set hive.cbo.enable = false; + +-- SORT_QUERY_RESULTS + +EXPLAIN +SELECT key, value, GROUPING__ID, count(*) +FROM T1 +GROUP BY key, value +GROUPING SETS ((), (key)) +HAVING GROUPING__ID = 1; +SELECT key, value, GROUPING__ID, count(*) +FROM T1 +GROUP BY key, value +GROUPING SETS ((), (key)) +HAVING GROUPING__ID = 1; + +set hive.cbo.enable = true; + +EXPLAIN +SELECT key, value, GROUPING__ID, count(*) +FROM T1 +GROUP BY key, value +GROUPING SETS ((), (key)) +HAVING GROUPING__ID = 1; +SELECT key, value, GROUPING__ID, count(*) +FROM T1 +GROUP BY key, value +GROUPING SETS ((), (key)) +HAVING GROUPING__ID = 1; + http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets1.q b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets1.q new file mode 100644 index 0000000..4683c88 --- /dev/null +++ b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets1.q @@ -0,0 +1,43 @@ +set hive.explain.user=false; +SET hive.vectorized.execution.enabled=true; +SET hive.vectorized.execution.reduce.enabled=true; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; + +-- SORT_QUERY_RESULTS + +CREATE TABLE T1_text(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; + +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1_text; + +CREATE TABLE T1 STORED AS ORC AS SELECT * FROM T1_text; + +SELECT * FROM T1; + +EXPLAIN +SELECT a, b, count(*) from T1 group by a, b with cube; +SELECT a, b, count(*) from T1 group by a, b with cube; +EXPLAIN +SELECT a, b, count(*) from T1 group by cube(a, b); +SELECT a, b, count(*) from T1 group by cube(a, b); + +EXPLAIN +SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b), b, ()); +SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b), b, ()); + +EXPLAIN +SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b)); +SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b)); + +EXPLAIN +SELECT a FROM T1 GROUP BY a, b, c GROUPING SETS (a, b, c); +SELECT a FROM T1 GROUP BY a, b, c GROUPING SETS (a, b, c); + +EXPLAIN +SELECT a FROM T1 GROUP BY a GROUPING SETS ((a), (a)); +SELECT a FROM T1 GROUP BY a GROUPING SETS ((a), (a)); + +EXPLAIN +SELECT a + b, count(*) FROM T1 GROUP BY a + b GROUPING SETS (a+b); +SELECT a + b, count(*) FROM T1 GROUP BY a + b GROUPING SETS (a+b); + http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets2.q b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets2.q new file mode 100644 index 0000000..158612c --- /dev/null +++ b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets2.q @@ -0,0 +1,36 @@ +set hive.explain.user=false; +SET hive.vectorized.execution.enabled=true; +SET hive.vectorized.execution.reduce.enabled=true; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; +set hive.mapred.mode=nonstrict; +set hive.new.job.grouping.set.cardinality=2; + +-- SORT_QUERY_RESULTS + +CREATE TABLE T1_text(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; + +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1_text; + +CREATE TABLE T1 STORED AS ORC AS SELECT * FROM T1_text; + +-- Since 4 grouping sets would be generated for the query below, an additional MR job should be created +EXPLAIN +SELECT a, b, count(*) from T1 group by a, b with cube; + +EXPLAIN +SELECT a, b, count(*) from T1 group by cube(a, b); +SELECT a, b, count(*) from T1 group by a, b with cube; + +EXPLAIN +SELECT a, b, sum(c) from T1 group by a, b with cube; +SELECT a, b, sum(c) from T1 group by a, b with cube; + +CREATE TABLE T2(a STRING, b STRING, c int, d int) STORED AS ORC; + +INSERT OVERWRITE TABLE T2 +SELECT a, b, c, c from T1; + +EXPLAIN +SELECT a, b, sum(c+d) from T2 group by a, b with cube; +SELECT a, b, sum(c+d) from T2 group by a, b with cube; http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets3.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets3.q b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets3.q new file mode 100644 index 0000000..b4c0d19 --- /dev/null +++ b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets3.q @@ -0,0 +1,40 @@ +set hive.explain.user=false; +SET hive.vectorized.execution.enabled=true; +SET hive.vectorized.execution.reduce.enabled=true; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; + +-- SORT_QUERY_RESULTS + +-- In this test, 2 files are loaded into table T1. The data contains rows with the same value of a and b, +-- with different number of rows for a and b in each file. Since bucketizedHiveInputFormat is used, +-- this tests that the aggregate function stores the partial aggregate state correctly even if an +-- additional MR job is created for processing the grouping sets. +CREATE TABLE T1_text(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; + +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets1.txt' INTO TABLE T1_text; +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets2.txt' INTO TABLE T1_text; + +CREATE TABLE T1 STORED AS ORC AS SELECT * FROM T1_text; + +set hive.input.format = org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; +set hive.new.job.grouping.set.cardinality = 30; + +-- The query below will execute in a single MR job, since 4 rows are generated per input row +-- (cube of a,b will lead to (a,b), (a, null), (null, b) and (null, null) and +-- hive.new.job.grouping.set.cardinality is more than 4. +EXPLAIN +SELECT a, b, avg(c), count(*) from T1 group by a, b with cube; + +EXPLAIN +SELECT a, b, avg(c), count(*) from T1 group by cube(a, b); +SELECT a, b, avg(c), count(*) from T1 group by a, b with cube; + +set hive.new.job.grouping.set.cardinality=2; + +-- The query below will execute in 2 MR jobs, since hive.new.job.grouping.set.cardinality is set to 2. +-- The partial aggregation state should be maintained correctly across MR jobs. +EXPLAIN +SELECT a, b, avg(c), count(*) from T1 group by a, b with cube; +SELECT a, b, avg(c), count(*) from T1 group by a, b with cube; + http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets4.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets4.q b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets4.q new file mode 100644 index 0000000..ef0d832 --- /dev/null +++ b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets4.q @@ -0,0 +1,57 @@ +set hive.explain.user=false; +SET hive.vectorized.execution.enabled=true; +SET hive.vectorized.execution.reduce.enabled=true; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; +set hive.mapred.mode=nonstrict; +set hive.merge.mapfiles = false; +set hive.merge.mapredfiles = false; + +-- SORT_QUERY_RESULTS + +-- Set merging to false above to make the explain more readable + +CREATE TABLE T1_text(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; + +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1_text; + +CREATE TABLE T1 STORED AS ORC AS SELECT * FROM T1_text; + +-- This tests that cubes and rollups work fine inside sub-queries. +EXPLAIN +SELECT * FROM +(SELECT a, b, count(*) from T1 where a < 3 group by a, b with cube) subq1 +join +(SELECT a, b, count(*) from T1 where a < 3 group by a, b with cube) subq2 +on subq1.a = subq2.a; + +EXPLAIN +SELECT * FROM +(SELECT a, b, count(*) from T1 where a < 3 group by cube(a, b) ) subq1 +join +(SELECT a, b, count(*) from T1 where a < 3 group by cube(a, b) ) subq2 +on subq1.a = subq2.a; + +SELECT * FROM +(SELECT a, b, count(*) from T1 where a < 3 group by a, b with cube) subq1 +join +(SELECT a, b, count(*) from T1 where a < 3 group by a, b with cube) subq2 +on subq1.a = subq2.a; + +set hive.new.job.grouping.set.cardinality=2; + +-- Since 4 grouping sets would be generated for each sub-query, an additional MR job should be created +-- for each of them +EXPLAIN +SELECT * FROM +(SELECT a, b, count(*) from T1 where a < 3 group by a, b with cube) subq1 +join +(SELECT a, b, count(*) from T1 where a < 3 group by a, b with cube) subq2 +on subq1.a = subq2.a; + +SELECT * FROM +(SELECT a, b, count(*) from T1 where a < 3 group by a, b with cube) subq1 +join +(SELECT a, b, count(*) from T1 where a < 3 group by a, b with cube) subq2 +on subq1.a = subq2.a; + http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets5.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets5.q b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets5.q new file mode 100644 index 0000000..15be3f3 --- /dev/null +++ b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets5.q @@ -0,0 +1,39 @@ +set hive.explain.user=false; +SET hive.vectorized.execution.enabled=true; +SET hive.vectorized.execution.reduce.enabled=true; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; +set hive.mapred.mode=nonstrict; +set hive.merge.mapfiles = false; +set hive.merge.mapredfiles = false; +-- Set merging to false above to make the explain more readable + +CREATE TABLE T1_text(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; + +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1_text; + +CREATE TABLE T1 STORED AS ORC AS SELECT * FROM T1_text; + +-- SORT_QUERY_RESULTS + +-- This tests that cubes and rollups work fine where the source is a sub-query +EXPLAIN +SELECT a, b, count(*) FROM +(SELECT a, b, count(1) from T1 group by a, b) subq1 group by a, b with cube; + +EXPLAIN +SELECT a, b, count(*) FROM +(SELECT a, b, count(1) from T1 group by a, b) subq1 group by cube(a, b); + +SELECT a, b, count(*) FROM +(SELECT a, b, count(1) from T1 group by a, b) subq1 group by a, b with cube; + +set hive.new.job.grouping.set.cardinality=2; + +-- Since 4 grouping sets would be generated for the cube, an additional MR job should be created +EXPLAIN +SELECT a, b, count(*) FROM +(SELECT a, b, count(1) from T1 group by a, b) subq1 group by a, b with cube; + +SELECT a, b, count(*) FROM +(SELECT a, b, count(1) from T1 group by a, b) subq1 group by a, b with cube; http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets6.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets6.q b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets6.q new file mode 100644 index 0000000..72c2078 --- /dev/null +++ b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets6.q @@ -0,0 +1,38 @@ +set hive.explain.user=false; +SET hive.vectorized.execution.enabled=true; +SET hive.vectorized.execution.reduce.enabled=true; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; +set hive.mapred.mode=nonstrict; + +CREATE TABLE T1_text(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; + +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1_text; + +CREATE TABLE T1 STORED AS ORC AS SELECT * FROM T1_text; + +-- SORT_QUERY_RESULTS + +set hive.optimize.ppd = false; + +-- This filter is not pushed down +EXPLAIN +SELECT a, b FROM +(SELECT a, b from T1 group by a, b grouping sets ( (a,b),a )) res +WHERE res.a=5; + +SELECT a, b FROM +(SELECT a, b from T1 group by a, b grouping sets ( (a,b),a )) res +WHERE res.a=5; + +set hive.cbo.enable = true; + +-- This filter is pushed down through aggregate with grouping sets by Calcite +EXPLAIN +SELECT a, b FROM +(SELECT a, b from T1 group by a, b grouping sets ( (a,b),a )) res +WHERE res.a=5; + +SELECT a, b FROM +(SELECT a, b from T1 group by a, b grouping sets ( (a,b),a )) res +WHERE res.a=5; http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets_grouping.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets_grouping.q b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets_grouping.q new file mode 100644 index 0000000..7b7c892 --- /dev/null +++ b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets_grouping.q @@ -0,0 +1,99 @@ +set hive.explain.user=false; +SET hive.vectorized.execution.enabled=true; +SET hive.vectorized.execution.reduce.enabled=true; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; + +CREATE TABLE T1_text(key INT, value INT) STORED AS TEXTFILE; + +LOAD DATA LOCAL INPATH '../../data/files/groupby_groupingid.txt' INTO TABLE T1_text; + +CREATE TABLE T1 STORED AS ORC AS SELECT * FROM T1_text; + +-- SORT_QUERY_RESULTS + +explain +select key, value, `grouping__id`, grouping(key), grouping(value) +from T1 +group by rollup(key, value); + +select key, value, `grouping__id`, grouping(key), grouping(value) +from T1 +group by rollup(key, value); + +explain +select key, value, `grouping__id`, grouping(key), grouping(value) +from T1 +group by cube(key, value); + +select key, value, `grouping__id`, grouping(key), grouping(value) +from T1 +group by cube(key, value); + +explain +select key, value +from T1 +group by cube(key, value) +having grouping(key) = 1; + +select key, value +from T1 +group by cube(key, value) +having grouping(key) = 1; + +explain +select key, value, grouping(key)+grouping(value) as x +from T1 +group by cube(key, value) +having grouping(key) = 1 OR grouping(value) = 1 +order by x desc, case when x = 1 then key end; + +select key, value, grouping(key)+grouping(value) as x +from T1 +group by cube(key, value) +having grouping(key) = 1 OR grouping(value) = 1 +order by x desc, case when x = 1 then key end; + +set hive.cbo.enable=false; + +explain +select key, value, `grouping__id`, grouping(key), grouping(value) +from T1 +group by rollup(key, value); + +select key, value, `grouping__id`, grouping(key), grouping(value) +from T1 +group by rollup(key, value); + +explain +select key, value, `grouping__id`, grouping(key), grouping(value) +from T1 +group by cube(key, value); + +select key, value, `grouping__id`, grouping(key), grouping(value) +from T1 +group by cube(key, value); + +explain +select key, value +from T1 +group by cube(key, value) +having grouping(key) = 1; + +select key, value +from T1 +group by cube(key, value) +having grouping(key) = 1; + +explain +select key, value, grouping(key)+grouping(value) as x +from T1 +group by cube(key, value) +having grouping(key) = 1 OR grouping(value) = 1 +order by x desc, case when x = 1 then key end; + +select key, value, grouping(key)+grouping(value) as x +from T1 +group by cube(key, value) +having grouping(key) = 1 OR grouping(value) = 1 +order by x desc, case when x = 1 then key end; http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets_limit.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets_limit.q b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets_limit.q new file mode 100644 index 0000000..fff706b --- /dev/null +++ b/ql/src/test/queries/clientpositive/vector_groupby_grouping_sets_limit.q @@ -0,0 +1,43 @@ +set hive.explain.user=false; +SET hive.vectorized.execution.enabled=true; +SET hive.vectorized.execution.reduce.enabled=true; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; + +CREATE TABLE T1_text(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; + +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1_text; + +CREATE TABLE T1 STORED AS ORC AS SELECT * FROM T1_text; + +-- SORT_QUERY_RESULTS + +EXPLAIN +SELECT a, b, count(*) from T1 group by a, b with cube LIMIT 10; + +SELECT a, b, count(*) from T1 group by a, b with cube LIMIT 10; + +EXPLAIN +SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b), b, ()) LIMIT 10; + +SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b), b, ()) LIMIT 10; + +EXPLAIN +SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b)) LIMIT 10; + +SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b)) LIMIT 10; + +EXPLAIN +SELECT a FROM T1 GROUP BY a, b, c GROUPING SETS (a, b, c) LIMIT 10; + +SELECT a FROM T1 GROUP BY a, b, c GROUPING SETS (a, b, c) LIMIT 10; + +EXPLAIN +SELECT a FROM T1 GROUP BY a GROUPING SETS ((a), (a)) LIMIT 10; + +SELECT a FROM T1 GROUP BY a GROUPING SETS ((a), (a)) LIMIT 10; + +EXPLAIN +SELECT a + b, count(*) FROM T1 GROUP BY a + b GROUPING SETS (a+b) LIMIT 10; + +SELECT a + b, count(*) FROM T1 GROUP BY a + b GROUPING SETS (a+b) LIMIT 10; http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/vector_groupby_grouping_window.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/vector_groupby_grouping_window.q b/ql/src/test/queries/clientpositive/vector_groupby_grouping_window.q new file mode 100644 index 0000000..c025c4f --- /dev/null +++ b/ql/src/test/queries/clientpositive/vector_groupby_grouping_window.q @@ -0,0 +1,21 @@ +set hive.explain.user=false; +SET hive.vectorized.execution.enabled=true; +SET hive.vectorized.execution.reduce.enabled=true; +set hive.fetch.task.conversion=none; +set hive.cli.print.header=true; + +create table t(category int, live int, comments int) stored as orc; +insert into table t select key, 0, 2 from src tablesample(3 rows); + +explain +select category, max(live) live, max(comments) comments, rank() OVER (PARTITION BY category ORDER BY comments) rank1 +FROM t +GROUP BY category +GROUPING SETS ((), (category)) +HAVING max(comments) > 0; + +select category, max(live) live, max(comments) comments, rank() OVER (PARTITION BY category ORDER BY comments) rank1 +FROM t +GROUP BY category +GROUPING SETS ((), (category)) +HAVING max(comments) > 0; http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/queries/clientpositive/vector_groupby_rollup1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/vector_groupby_rollup1.q b/ql/src/test/queries/clientpositive/vector_groupby_rollup1.q new file mode 100644 index 0000000..e08f8b9 --- /dev/null +++ b/ql/src/test/queries/clientpositive/vector_groupby_rollup1.q @@ -0,0 +1,54 @@ +set hive.explain.user=false; +SET hive.vectorized.execution.enabled=true; +SET hive.vectorized.execution.reduce.enabled=true; +set hive.fetch.task.conversion=none; +set hive.mapred.mode=nonstrict; +set hive.map.aggr=true; +set hive.groupby.skewindata=false; + +-- SORT_QUERY_RESULTS + +CREATE TABLE T1_text(key STRING, val STRING) STORED AS TEXTFILE; + +LOAD DATA LOCAL INPATH '../../data/files/T1.txt' INTO TABLE T1_text; + +CREATE TABLE T1 STORED AS ORC AS SELECT * FROM T1_text; + +EXPLAIN +SELECT key, val, count(1) FROM T1 GROUP BY key, val with rollup; + +SELECT key, val, count(1) FROM T1 GROUP BY key, val with rollup; + +EXPLAIN +SELECT key, count(distinct val) FROM T1 GROUP BY key with rollup; + +SELECT key, count(distinct val) FROM T1 GROUP BY key with rollup; + +set hive.groupby.skewindata=true; + +EXPLAIN +SELECT key, val, count(1) FROM T1 GROUP BY key, val with rollup; + +SELECT key, val, count(1) FROM T1 GROUP BY key, val with rollup; + +EXPLAIN +SELECT key, count(distinct val) FROM T1 GROUP BY key with rollup; + +SELECT key, count(distinct val) FROM T1 GROUP BY key with rollup; + + +set hive.multigroupby.singlereducer=true; + +CREATE TABLE T2(key1 STRING, key2 STRING, val INT) STORED AS ORC; +CREATE TABLE T3(key1 STRING, key2 STRING, val INT) STORED AS ORC; + +EXPLAIN +FROM T1 +INSERT OVERWRITE TABLE T2 SELECT key, val, count(1) group by key, val with rollup +INSERT OVERWRITE TABLE T3 SELECT key, val, sum(1) group by rollup(key, val); + + +FROM T1 +INSERT OVERWRITE TABLE T2 SELECT key, val, count(1) group by key, val with rollup +INSERT OVERWRITE TABLE T3 SELECT key, val, sum(1) group by key, val with rollup; + http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/results/clientpositive/groupby_grouping_id1.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/groupby_grouping_id1.q.out b/ql/src/test/results/clientpositive/groupby_grouping_id1.q.out index c2a0393..8b203e7 100644 --- a/ql/src/test/results/clientpositive/groupby_grouping_id1.q.out +++ b/ql/src/test/results/clientpositive/groupby_grouping_id1.q.out @@ -22,24 +22,24 @@ POSTHOOK: query: SELECT key, val, GROUPING__ID from T1 group by key, val with cu POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 3 -NULL 11 2 -NULL 12 2 -NULL 13 2 -NULL 17 2 -NULL 18 2 -NULL 28 2 -1 NULL 1 1 11 0 -2 NULL 1 +1 NULL 1 2 12 0 -3 NULL 1 +2 NULL 1 3 13 0 -7 NULL 1 +3 NULL 1 7 17 0 -8 NULL 1 +7 NULL 1 8 18 0 8 28 0 +8 NULL 1 +NULL 11 2 +NULL 12 2 +NULL 13 2 +NULL 17 2 +NULL 18 2 +NULL 28 2 +NULL NULL 3 PREHOOK: query: SELECT key, val, GROUPING__ID from T1 group by cube(key, val) PREHOOK: type: QUERY PREHOOK: Input: default@t1 @@ -48,24 +48,24 @@ POSTHOOK: query: SELECT key, val, GROUPING__ID from T1 group by cube(key, val) POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 3 -NULL 11 2 -NULL 12 2 -NULL 13 2 -NULL 17 2 -NULL 18 2 -NULL 28 2 -1 NULL 1 1 11 0 -2 NULL 1 +1 NULL 1 2 12 0 -3 NULL 1 +2 NULL 1 3 13 0 -7 NULL 1 +3 NULL 1 7 17 0 -8 NULL 1 +7 NULL 1 8 18 0 8 28 0 +8 NULL 1 +NULL 11 2 +NULL 12 2 +NULL 13 2 +NULL 17 2 +NULL 18 2 +NULL 28 2 +NULL NULL 3 PREHOOK: query: SELECT GROUPING__ID, key, val from T1 group by key, val with rollup PREHOOK: type: QUERY PREHOOK: Input: default@t1 @@ -74,18 +74,18 @@ POSTHOOK: query: SELECT GROUPING__ID, key, val from T1 group by key, val with ro POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -3 NULL NULL -1 1 NULL 0 1 11 -1 2 NULL 0 2 12 -1 3 NULL 0 3 13 -1 7 NULL 0 7 17 -1 8 NULL 0 8 18 0 8 28 +1 1 NULL +1 2 NULL +1 3 NULL +1 7 NULL +1 8 NULL +3 NULL NULL PREHOOK: query: SELECT GROUPING__ID, key, val from T1 group by rollup (key, val) PREHOOK: type: QUERY PREHOOK: Input: default@t1 @@ -94,18 +94,18 @@ POSTHOOK: query: SELECT GROUPING__ID, key, val from T1 group by rollup (key, val POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -3 NULL NULL -1 1 NULL 0 1 11 -1 2 NULL 0 2 12 -1 3 NULL 0 3 13 -1 7 NULL 0 7 17 -1 8 NULL 0 8 18 0 8 28 +1 1 NULL +1 2 NULL +1 3 NULL +1 7 NULL +1 8 NULL +3 NULL NULL PREHOOK: query: SELECT key, val, GROUPING__ID, CASE WHEN GROUPING__ID == 0 THEN "0" WHEN GROUPING__ID == 1 THEN "1" WHEN GROUPING__ID == 2 THEN "2" WHEN GROUPING__ID == 3 THEN "3" ELSE "nothing" END from T1 group by key, val with cube PREHOOK: type: QUERY PREHOOK: Input: default@t1 @@ -114,24 +114,24 @@ POSTHOOK: query: SELECT key, val, GROUPING__ID, CASE WHEN GROUPING__ID == 0 THEN POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 3 3 -NULL 11 2 2 -NULL 12 2 2 -NULL 13 2 2 -NULL 17 2 2 -NULL 18 2 2 -NULL 28 2 2 -1 NULL 1 1 1 11 0 0 -2 NULL 1 1 +1 NULL 1 1 2 12 0 0 -3 NULL 1 1 +2 NULL 1 1 3 13 0 0 -7 NULL 1 1 +3 NULL 1 1 7 17 0 0 -8 NULL 1 1 +7 NULL 1 1 8 18 0 0 8 28 0 0 +8 NULL 1 1 +NULL 11 2 2 +NULL 12 2 2 +NULL 13 2 2 +NULL 17 2 2 +NULL 18 2 2 +NULL 28 2 2 +NULL NULL 3 3 PREHOOK: query: SELECT key, val, GROUPING__ID, CASE WHEN GROUPING__ID == 0 THEN "0" WHEN GROUPING__ID == 1 THEN "1" WHEN GROUPING__ID == 2 THEN "2" WHEN GROUPING__ID == 3 THEN "3" ELSE "nothing" END from T1 group by cube(key, val) PREHOOK: type: QUERY PREHOOK: Input: default@t1 @@ -140,21 +140,21 @@ POSTHOOK: query: SELECT key, val, GROUPING__ID, CASE WHEN GROUPING__ID == 0 THEN POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 3 3 -NULL 11 2 2 -NULL 12 2 2 -NULL 13 2 2 -NULL 17 2 2 -NULL 18 2 2 -NULL 28 2 2 -1 NULL 1 1 1 11 0 0 -2 NULL 1 1 +1 NULL 1 1 2 12 0 0 -3 NULL 1 1 +2 NULL 1 1 3 13 0 0 -7 NULL 1 1 +3 NULL 1 1 7 17 0 0 -8 NULL 1 1 +7 NULL 1 1 8 18 0 0 8 28 0 0 +8 NULL 1 1 +NULL 11 2 2 +NULL 12 2 2 +NULL 13 2 2 +NULL 17 2 2 +NULL 18 2 2 +NULL 28 2 2 +NULL NULL 3 3 http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/results/clientpositive/groupby_grouping_id3.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/groupby_grouping_id3.q.out b/ql/src/test/results/clientpositive/groupby_grouping_id3.q.out index 915e6e1..6670d9c 100644 --- a/ql/src/test/results/clientpositive/groupby_grouping_id3.q.out +++ b/ql/src/test/results/clientpositive/groupby_grouping_id3.q.out @@ -14,6 +14,76 @@ POSTHOOK: query: LOAD DATA LOCAL INPATH '../../data/files/groupby_groupingid.txt POSTHOOK: type: LOAD #### A masked pattern was here #### POSTHOOK: Output: default@t1 +PREHOOK: query: EXPLAIN +SELECT key, value, GROUPING__ID, count(*) +FROM T1 +GROUP BY key, value +GROUPING SETS ((), (key)) +HAVING GROUPING__ID = 1 +PREHOOK: type: QUERY +POSTHOOK: query: EXPLAIN +SELECT key, value, GROUPING__ID, count(*) +FROM T1 +GROUP BY key, value +GROUPING SETS ((), (key)) +HAVING GROUPING__ID = 1 +POSTHOOK: type: QUERY +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: t1 + Statistics: Num rows: 3 Data size: 30 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: key (type: int), value (type: int) + outputColumnNames: key, value + Statistics: Num rows: 3 Data size: 30 Basic stats: COMPLETE Column stats: NONE + Group By Operator + aggregations: count() + keys: key (type: int), value (type: int), 0 (type: int) + mode: hash + outputColumnNames: _col0, _col1, _col2, _col3 + Statistics: Num rows: 6 Data size: 60 Basic stats: COMPLETE Column stats: NONE + Filter Operator + predicate: (_col2 = 1) (type: boolean) + Statistics: Num rows: 3 Data size: 30 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + key expressions: _col0 (type: int), _col1 (type: int), 1 (type: int) + sort order: +++ + Map-reduce partition columns: _col0 (type: int), _col1 (type: int), 1 (type: int) + Statistics: Num rows: 3 Data size: 30 Basic stats: COMPLETE Column stats: NONE + value expressions: _col3 (type: bigint) + Reduce Operator Tree: + Group By Operator + aggregations: count(VALUE._col0) + keys: KEY._col0 (type: int), KEY._col1 (type: int), 1 (type: int) + mode: mergepartial + outputColumnNames: _col0, _col1, _col3 + Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE + pruneGroupingSetId: true + Select Operator + expressions: _col0 (type: int), _col1 (type: int), 1 (type: int), _col3 (type: bigint) + outputColumnNames: _col0, _col1, _col2, _col3 + Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + PREHOOK: query: SELECT key, value, GROUPING__ID, count(*) FROM T1 GROUP BY key, value @@ -34,6 +104,75 @@ POSTHOOK: Input: default@t1 2 NULL 1 1 3 NULL 1 2 4 NULL 1 1 +PREHOOK: query: EXPLAIN +SELECT key, value, GROUPING__ID, count(*) +FROM T1 +GROUP BY key, value +GROUPING SETS ((), (key)) +HAVING GROUPING__ID = 1 +PREHOOK: type: QUERY +POSTHOOK: query: EXPLAIN +SELECT key, value, GROUPING__ID, count(*) +FROM T1 +GROUP BY key, value +GROUPING SETS ((), (key)) +HAVING GROUPING__ID = 1 +POSTHOOK: type: QUERY +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: t1 + Statistics: Num rows: 3 Data size: 30 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: key (type: int), value (type: int) + outputColumnNames: _col0, _col1 + Statistics: Num rows: 3 Data size: 30 Basic stats: COMPLETE Column stats: NONE + Group By Operator + aggregations: count() + keys: _col0 (type: int), _col1 (type: int), 0 (type: int) + mode: hash + outputColumnNames: _col0, _col1, _col2, _col3 + Statistics: Num rows: 6 Data size: 60 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + key expressions: _col0 (type: int), _col1 (type: int), _col2 (type: int) + sort order: +++ + Map-reduce partition columns: _col0 (type: int), _col1 (type: int), _col2 (type: int) + Statistics: Num rows: 6 Data size: 60 Basic stats: COMPLETE Column stats: NONE + value expressions: _col3 (type: bigint) + Reduce Operator Tree: + Group By Operator + aggregations: count(VALUE._col0) + keys: KEY._col0 (type: int), KEY._col1 (type: int), KEY._col2 (type: int) + mode: mergepartial + outputColumnNames: _col0, _col1, _col2, _col3 + Statistics: Num rows: 3 Data size: 30 Basic stats: COMPLETE Column stats: NONE + Filter Operator + predicate: (_col2 = 1) (type: boolean) + Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: _col0 (type: int), _col1 (type: int), 1 (type: int), _col3 (type: bigint) + outputColumnNames: _col0, _col1, _col2, _col3 + Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + PREHOOK: query: SELECT key, value, GROUPING__ID, count(*) FROM T1 GROUP BY key, value http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/results/clientpositive/groupby_grouping_sets1.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/groupby_grouping_sets1.q.out b/ql/src/test/results/clientpositive/groupby_grouping_sets1.q.out index aebba0d..13eac20 100644 --- a/ql/src/test/results/clientpositive/groupby_grouping_sets1.q.out +++ b/ql/src/test/results/clientpositive/groupby_grouping_sets1.q.out @@ -22,12 +22,73 @@ POSTHOOK: query: SELECT * FROM T1 POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -8 1 1 -5 2 2 +t1.a t1.b t1.c 1 1 3 2 2 4 2 3 5 3 2 8 +5 2 2 +8 1 1 +PREHOOK: query: EXPLAIN +SELECT a, b, count(*) from T1 group by a, b with cube +PREHOOK: type: QUERY +POSTHOOK: query: EXPLAIN +SELECT a, b, count(*) from T1 group by a, b with cube +POSTHOOK: type: QUERY +Explain +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: t1 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: a (type: string), b (type: string) + outputColumnNames: a, b + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Group By Operator + aggregations: count() + keys: a (type: string), b (type: string), 0 (type: int) + mode: hash + outputColumnNames: _col0, _col1, _col2, _col3 + Statistics: Num rows: 4 Data size: 144 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int) + sort order: +++ + Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: int) + Statistics: Num rows: 4 Data size: 144 Basic stats: COMPLETE Column stats: NONE + value expressions: _col3 (type: bigint) + Reduce Operator Tree: + Group By Operator + aggregations: count(VALUE._col0) + keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int) + mode: mergepartial + outputColumnNames: _col0, _col1, _col3 + Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE + pruneGroupingSetId: true + Select Operator + expressions: _col0 (type: string), _col1 (type: string), _col3 (type: bigint) + outputColumnNames: _col0, _col1, _col2 + Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + PREHOOK: query: SELECT a, b, count(*) from T1 group by a, b with cube PREHOOK: type: QUERY PREHOOK: Input: default@t1 @@ -36,21 +97,82 @@ POSTHOOK: query: SELECT a, b, count(*) from T1 group by a, b with cube POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 6 -NULL 1 2 -NULL 2 3 -NULL 3 1 -1 NULL 1 +a b _c2 1 1 1 -2 NULL 2 +1 NULL 1 2 2 1 2 3 1 -3 NULL 1 +2 NULL 2 3 2 1 -5 NULL 1 +3 NULL 1 5 2 1 -8 NULL 1 +5 NULL 1 8 1 1 +8 NULL 1 +NULL 1 2 +NULL 2 3 +NULL 3 1 +NULL NULL 6 +PREHOOK: query: EXPLAIN +SELECT a, b, count(*) from T1 group by cube(a, b) +PREHOOK: type: QUERY +POSTHOOK: query: EXPLAIN +SELECT a, b, count(*) from T1 group by cube(a, b) +POSTHOOK: type: QUERY +Explain +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: t1 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: a (type: string), b (type: string) + outputColumnNames: a, b + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Group By Operator + aggregations: count() + keys: a (type: string), b (type: string), 0 (type: int) + mode: hash + outputColumnNames: _col0, _col1, _col2, _col3 + Statistics: Num rows: 4 Data size: 144 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int) + sort order: +++ + Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: int) + Statistics: Num rows: 4 Data size: 144 Basic stats: COMPLETE Column stats: NONE + value expressions: _col3 (type: bigint) + Reduce Operator Tree: + Group By Operator + aggregations: count(VALUE._col0) + keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int) + mode: mergepartial + outputColumnNames: _col0, _col1, _col3 + Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE + pruneGroupingSetId: true + Select Operator + expressions: _col0 (type: string), _col1 (type: string), _col3 (type: bigint) + outputColumnNames: _col0, _col1, _col2 + Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + PREHOOK: query: SELECT a, b, count(*) from T1 group by cube(a, b) PREHOOK: type: QUERY PREHOOK: Input: default@t1 @@ -59,21 +181,82 @@ POSTHOOK: query: SELECT a, b, count(*) from T1 group by cube(a, b) POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 6 -NULL 1 2 -NULL 2 3 -NULL 3 1 -1 NULL 1 +a b _c2 1 1 1 -2 NULL 2 +1 NULL 1 2 2 1 2 3 1 -3 NULL 1 +2 NULL 2 3 2 1 -5 NULL 1 +3 NULL 1 5 2 1 -8 NULL 1 +5 NULL 1 8 1 1 +8 NULL 1 +NULL 1 2 +NULL 2 3 +NULL 3 1 +NULL NULL 6 +PREHOOK: query: EXPLAIN +SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b), b, ()) +PREHOOK: type: QUERY +POSTHOOK: query: EXPLAIN +SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b), b, ()) +POSTHOOK: type: QUERY +Explain +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: t1 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: a (type: string), b (type: string) + outputColumnNames: a, b + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Group By Operator + aggregations: count() + keys: a (type: string), b (type: string), 0 (type: int) + mode: hash + outputColumnNames: _col0, _col1, _col2, _col3 + Statistics: Num rows: 4 Data size: 144 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int) + sort order: +++ + Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: int) + Statistics: Num rows: 4 Data size: 144 Basic stats: COMPLETE Column stats: NONE + value expressions: _col3 (type: bigint) + Reduce Operator Tree: + Group By Operator + aggregations: count(VALUE._col0) + keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int) + mode: mergepartial + outputColumnNames: _col0, _col1, _col3 + Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE + pruneGroupingSetId: true + Select Operator + expressions: _col0 (type: string), _col1 (type: string), _col3 (type: bigint) + outputColumnNames: _col0, _col1, _col2 + Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + PREHOOK: query: SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b), b, ()) PREHOOK: type: QUERY PREHOOK: Input: default@t1 @@ -82,21 +265,82 @@ POSTHOOK: query: SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 6 -NULL 1 2 -NULL 2 3 -NULL 3 1 -1 NULL 1 +a b _c2 1 1 1 -2 NULL 2 +1 NULL 1 2 2 1 2 3 1 -3 NULL 1 +2 NULL 2 3 2 1 -5 NULL 1 +3 NULL 1 5 2 1 -8 NULL 1 +5 NULL 1 8 1 1 +8 NULL 1 +NULL 1 2 +NULL 2 3 +NULL 3 1 +NULL NULL 6 +PREHOOK: query: EXPLAIN +SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b)) +PREHOOK: type: QUERY +POSTHOOK: query: EXPLAIN +SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b)) +POSTHOOK: type: QUERY +Explain +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: t1 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: a (type: string), b (type: string) + outputColumnNames: a, b + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Group By Operator + aggregations: count() + keys: a (type: string), b (type: string), 0 (type: int) + mode: hash + outputColumnNames: _col0, _col1, _col2, _col3 + Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int) + sort order: +++ + Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: int) + Statistics: Num rows: 2 Data size: 72 Basic stats: COMPLETE Column stats: NONE + value expressions: _col3 (type: bigint) + Reduce Operator Tree: + Group By Operator + aggregations: count(VALUE._col0) + keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: int) + mode: mergepartial + outputColumnNames: _col0, _col1, _col3 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + pruneGroupingSetId: true + Select Operator + expressions: _col0 (type: string), _col1 (type: string), _col3 (type: bigint) + outputColumnNames: _col0, _col1, _col2 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + PREHOOK: query: SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b)) PREHOOK: type: QUERY PREHOOK: Input: default@t1 @@ -105,17 +349,75 @@ POSTHOOK: query: SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, ( POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -1 NULL 1 +a b _c2 1 1 1 -2 NULL 2 +1 NULL 1 2 2 1 2 3 1 -3 NULL 1 +2 NULL 2 3 2 1 -5 NULL 1 +3 NULL 1 5 2 1 -8 NULL 1 +5 NULL 1 8 1 1 +8 NULL 1 +PREHOOK: query: EXPLAIN +SELECT a FROM T1 GROUP BY a, b, c GROUPING SETS (a, b, c) +PREHOOK: type: QUERY +POSTHOOK: query: EXPLAIN +SELECT a FROM T1 GROUP BY a, b, c GROUPING SETS (a, b, c) +POSTHOOK: type: QUERY +Explain +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: t1 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: a (type: string), b (type: string), c (type: string) + outputColumnNames: a, b, c + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Group By Operator + keys: a (type: string), b (type: string), c (type: string), 0 (type: int) + mode: hash + outputColumnNames: _col0, _col1, _col2, _col3 + Statistics: Num rows: 3 Data size: 108 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: int) + sort order: ++++ + Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: int) + Statistics: Num rows: 3 Data size: 108 Basic stats: COMPLETE Column stats: NONE + Reduce Operator Tree: + Group By Operator + keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string), KEY._col3 (type: int) + mode: mergepartial + outputColumnNames: _col0, _col1, _col2 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + pruneGroupingSetId: true + Select Operator + expressions: _col0 (type: string) + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + PREHOOK: query: SELECT a FROM T1 GROUP BY a, b, c GROUPING SETS (a, b, c) PREHOOK: type: QUERY PREHOOK: Input: default@t1 @@ -124,6 +426,12 @@ POSTHOOK: query: SELECT a FROM T1 GROUP BY a, b, c GROUPING SETS (a, b, c) POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### +a +1 +2 +3 +5 +8 NULL NULL NULL @@ -133,11 +441,59 @@ NULL NULL NULL NULL -1 -2 -3 -5 -8 +PREHOOK: query: EXPLAIN +SELECT a FROM T1 GROUP BY a GROUPING SETS ((a), (a)) +PREHOOK: type: QUERY +POSTHOOK: query: EXPLAIN +SELECT a FROM T1 GROUP BY a GROUPING SETS ((a), (a)) +POSTHOOK: type: QUERY +Explain +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: t1 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: a (type: string) + outputColumnNames: a + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Group By Operator + keys: a (type: string), 0 (type: int) + mode: hash + outputColumnNames: _col0, _col1 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + key expressions: _col0 (type: string), _col1 (type: int) + sort order: ++ + Map-reduce partition columns: _col0 (type: string), _col1 (type: int) + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Reduce Operator Tree: + Group By Operator + keys: KEY._col0 (type: string), KEY._col1 (type: int) + mode: mergepartial + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + pruneGroupingSetId: true + File Output Operator + compressed: false + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + PREHOOK: query: SELECT a FROM T1 GROUP BY a GROUPING SETS ((a), (a)) PREHOOK: type: QUERY PREHOOK: Input: default@t1 @@ -146,11 +502,72 @@ POSTHOOK: query: SELECT a FROM T1 GROUP BY a GROUPING SETS ((a), (a)) POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### +a 1 2 3 5 8 +PREHOOK: query: EXPLAIN +SELECT a + b, count(*) FROM T1 GROUP BY a + b GROUPING SETS (a+b) +PREHOOK: type: QUERY +POSTHOOK: query: EXPLAIN +SELECT a + b, count(*) FROM T1 GROUP BY a + b GROUPING SETS (a+b) +POSTHOOK: type: QUERY +Explain +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: t1 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: (UDFToDouble(a) + UDFToDouble(b)) (type: double) + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Group By Operator + aggregations: count() + keys: _col0 (type: double), 0 (type: int) + mode: hash + outputColumnNames: _col0, _col1, _col2 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + key expressions: _col0 (type: double), _col1 (type: int) + sort order: ++ + Map-reduce partition columns: _col0 (type: double), _col1 (type: int) + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + value expressions: _col2 (type: bigint) + Reduce Operator Tree: + Group By Operator + aggregations: count(VALUE._col0) + keys: KEY._col0 (type: double), KEY._col1 (type: int) + mode: mergepartial + outputColumnNames: _col0, _col2 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + pruneGroupingSetId: true + Select Operator + expressions: _col0 (type: double), _col2 (type: bigint) + outputColumnNames: _col0, _col1 + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + PREHOOK: query: SELECT a + b, count(*) FROM T1 GROUP BY a + b GROUPING SETS (a+b) PREHOOK: type: QUERY PREHOOK: Input: default@t1 @@ -159,6 +576,7 @@ POSTHOOK: query: SELECT a + b, count(*) FROM T1 GROUP BY a + b GROUPING SETS (a+ POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### +_c0 _c1 2.0 1 4.0 1 5.0 2 http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/results/clientpositive/groupby_grouping_sets2.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/groupby_grouping_sets2.q.out b/ql/src/test/results/clientpositive/groupby_grouping_sets2.q.out index b4f8ce7..93c007b 100644 --- a/ql/src/test/results/clientpositive/groupby_grouping_sets2.q.out +++ b/ql/src/test/results/clientpositive/groupby_grouping_sets2.q.out @@ -20,6 +20,7 @@ PREHOOK: type: QUERY POSTHOOK: query: EXPLAIN SELECT a, b, count(*) from T1 group by a, b with cube POSTHOOK: type: QUERY +Explain STAGE DEPENDENCIES: Stage-1 is a root stage Stage-2 depends on stages: Stage-1 @@ -104,6 +105,7 @@ PREHOOK: type: QUERY POSTHOOK: query: EXPLAIN SELECT a, b, count(*) from T1 group by cube(a, b) POSTHOOK: type: QUERY +Explain STAGE DEPENDENCIES: Stage-1 is a root stage Stage-2 depends on stages: Stage-1 @@ -190,27 +192,29 @@ POSTHOOK: query: SELECT a, b, count(*) from T1 group by a, b with cube POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 6 -NULL 1 2 -NULL 2 3 -NULL 3 1 -1 NULL 1 +a b _c2 1 1 1 -2 NULL 2 +1 NULL 1 2 2 1 2 3 1 -3 NULL 1 +2 NULL 2 3 2 1 -5 NULL 1 +3 NULL 1 5 2 1 -8 NULL 1 +5 NULL 1 8 1 1 +8 NULL 1 +NULL 1 2 +NULL 2 3 +NULL 3 1 +NULL NULL 6 PREHOOK: query: EXPLAIN SELECT a, b, sum(c) from T1 group by a, b with cube PREHOOK: type: QUERY POSTHOOK: query: EXPLAIN SELECT a, b, sum(c) from T1 group by a, b with cube POSTHOOK: type: QUERY +Explain STAGE DEPENDENCIES: Stage-1 is a root stage Stage-2 depends on stages: Stage-1 @@ -297,21 +301,22 @@ POSTHOOK: query: SELECT a, b, sum(c) from T1 group by a, b with cube POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 23.0 -NULL 1 4.0 -NULL 2 14.0 -NULL 3 5.0 -1 NULL 3.0 +a b _c2 1 1 3.0 -2 NULL 9.0 +1 NULL 3.0 2 2 4.0 2 3 5.0 -3 NULL 8.0 +2 NULL 9.0 3 2 8.0 -5 NULL 2.0 +3 NULL 8.0 5 2 2.0 -8 NULL 1.0 +5 NULL 2.0 8 1 1.0 +8 NULL 1.0 +NULL 1 4.0 +NULL 2 14.0 +NULL 3 5.0 +NULL NULL 23.0 PREHOOK: query: CREATE TABLE T2(a STRING, b STRING, c int, d int) PREHOOK: type: CREATETABLE PREHOOK: Output: database:default @@ -334,12 +339,14 @@ POSTHOOK: Lineage: t2.a SIMPLE [(t1)t1.FieldSchema(name:a, type:string, comment: POSTHOOK: Lineage: t2.b SIMPLE [(t1)t1.FieldSchema(name:b, type:string, comment:null), ] POSTHOOK: Lineage: t2.c EXPRESSION [(t1)t1.FieldSchema(name:c, type:string, comment:null), ] POSTHOOK: Lineage: t2.d EXPRESSION [(t1)t1.FieldSchema(name:c, type:string, comment:null), ] +_col0 _col1 _col2 _col3 PREHOOK: query: EXPLAIN SELECT a, b, sum(c+d) from T2 group by a, b with cube PREHOOK: type: QUERY POSTHOOK: query: EXPLAIN SELECT a, b, sum(c+d) from T2 group by a, b with cube POSTHOOK: type: QUERY +Explain STAGE DEPENDENCIES: Stage-1 is a root stage Stage-2 depends on stages: Stage-1 @@ -426,18 +433,19 @@ POSTHOOK: query: SELECT a, b, sum(c+d) from T2 group by a, b with cube POSTHOOK: type: QUERY POSTHOOK: Input: default@t2 #### A masked pattern was here #### -NULL NULL 46 -NULL 1 8 -NULL 2 28 -NULL 3 10 -1 NULL 6 +a b _c2 1 1 6 -2 NULL 18 +1 NULL 6 2 2 8 2 3 10 -3 NULL 16 +2 NULL 18 3 2 16 -5 NULL 4 +3 NULL 16 5 2 4 -8 NULL 2 +5 NULL 4 8 1 2 +8 NULL 2 +NULL 1 8 +NULL 2 28 +NULL 3 10 +NULL NULL 46 http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/results/clientpositive/groupby_grouping_sets3.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/groupby_grouping_sets3.q.out b/ql/src/test/results/clientpositive/groupby_grouping_sets3.q.out index 67cbdcd..f9e44fd 100644 --- a/ql/src/test/results/clientpositive/groupby_grouping_sets3.q.out +++ b/ql/src/test/results/clientpositive/groupby_grouping_sets3.q.out @@ -28,6 +28,7 @@ PREHOOK: type: QUERY POSTHOOK: query: EXPLAIN SELECT a, b, avg(c), count(*) from T1 group by a, b with cube POSTHOOK: type: QUERY +Explain STAGE DEPENDENCIES: Stage-1 is a root stage Stage-0 depends on stages: Stage-1 @@ -87,6 +88,7 @@ PREHOOK: type: QUERY POSTHOOK: query: EXPLAIN SELECT a, b, avg(c), count(*) from T1 group by cube(a, b) POSTHOOK: type: QUERY +Explain STAGE DEPENDENCIES: Stage-1 is a root stage Stage-0 depends on stages: Stage-1 @@ -148,28 +150,30 @@ POSTHOOK: query: SELECT a, b, avg(c), count(*) from T1 group by a, b with cube POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 3.8333333333333335 12 -NULL 1 2.0 5 -NULL 2 5.2 5 -NULL 3 5.0 2 -1 NULL 2.6666666666666665 3 +a b _c2 _c3 1 1 3.0 2 1 2 2.0 1 -2 NULL 5.2 5 +1 NULL 2.6666666666666665 3 2 2 5.333333333333333 3 2 3 5.0 2 -3 NULL 8.0 1 +2 NULL 5.2 5 3 2 8.0 1 -5 NULL 2.0 1 +3 NULL 8.0 1 5 1 2.0 1 -8 NULL 1.0 2 +5 NULL 2.0 1 8 1 1.0 2 +8 NULL 1.0 2 +NULL 1 2.0 5 +NULL 2 5.2 5 +NULL 3 5.0 2 +NULL NULL 3.8333333333333335 12 PREHOOK: query: EXPLAIN SELECT a, b, avg(c), count(*) from T1 group by a, b with cube PREHOOK: type: QUERY POSTHOOK: query: EXPLAIN SELECT a, b, avg(c), count(*) from T1 group by a, b with cube POSTHOOK: type: QUERY +Explain STAGE DEPENDENCIES: Stage-1 is a root stage Stage-2 depends on stages: Stage-1 @@ -256,19 +260,20 @@ POSTHOOK: query: SELECT a, b, avg(c), count(*) from T1 group by a, b with cube POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 3.8333333333333335 12 -NULL 1 2.0 5 -NULL 2 5.2 5 -NULL 3 5.0 2 -1 NULL 2.6666666666666665 3 +a b _c2 _c3 1 1 3.0 2 1 2 2.0 1 -2 NULL 5.2 5 +1 NULL 2.6666666666666665 3 2 2 5.333333333333333 3 2 3 5.0 2 -3 NULL 8.0 1 +2 NULL 5.2 5 3 2 8.0 1 -5 NULL 2.0 1 +3 NULL 8.0 1 5 1 2.0 1 -8 NULL 1.0 2 +5 NULL 2.0 1 8 1 1.0 2 +8 NULL 1.0 2 +NULL 1 2.0 5 +NULL 2 5.2 5 +NULL 3 5.0 2 +NULL NULL 3.8333333333333335 12 http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/results/clientpositive/groupby_grouping_sets5.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/groupby_grouping_sets5.q.out b/ql/src/test/results/clientpositive/groupby_grouping_sets5.q.out index 166f110..46f696d 100644 --- a/ql/src/test/results/clientpositive/groupby_grouping_sets5.q.out +++ b/ql/src/test/results/clientpositive/groupby_grouping_sets5.q.out @@ -202,21 +202,21 @@ POSTHOOK: query: SELECT a, b, count(*) FROM POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 6 -NULL 1 2 -NULL 2 3 -NULL 3 1 -1 NULL 1 1 1 1 -2 NULL 2 +1 NULL 1 2 2 1 2 3 1 -3 NULL 1 +2 NULL 2 3 2 1 -5 NULL 1 +3 NULL 1 5 2 1 -8 NULL 1 +5 NULL 1 8 1 1 +8 NULL 1 +NULL 1 2 +NULL 2 3 +NULL 3 1 +NULL NULL 6 PREHOOK: query: EXPLAIN SELECT a, b, count(*) FROM (SELECT a, b, count(1) from T1 group by a, b) subq1 group by a, b with cube @@ -341,18 +341,18 @@ POSTHOOK: query: SELECT a, b, count(*) FROM POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 6 -NULL 1 2 -NULL 2 3 -NULL 3 1 -1 NULL 1 1 1 1 -2 NULL 2 +1 NULL 1 2 2 1 2 3 1 -3 NULL 1 +2 NULL 2 3 2 1 -5 NULL 1 +3 NULL 1 5 2 1 -8 NULL 1 +5 NULL 1 8 1 1 +8 NULL 1 +NULL 1 2 +NULL 2 3 +NULL 3 1 +NULL NULL 6 http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/results/clientpositive/groupby_grouping_sets6.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/groupby_grouping_sets6.q.out b/ql/src/test/results/clientpositive/groupby_grouping_sets6.q.out index 16f0871..b174e89 100644 --- a/ql/src/test/results/clientpositive/groupby_grouping_sets6.q.out +++ b/ql/src/test/results/clientpositive/groupby_grouping_sets6.q.out @@ -81,8 +81,8 @@ WHERE res.a=5 POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -5 NULL 5 2 +5 NULL PREHOOK: query: EXPLAIN SELECT a, b FROM (SELECT a, b from T1 group by a, b grouping sets ( (a,b),a )) res @@ -150,5 +150,5 @@ WHERE res.a=5 POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -5 NULL 5 2 +5 NULL http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/results/clientpositive/groupby_grouping_sets_grouping.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/groupby_grouping_sets_grouping.q.out b/ql/src/test/results/clientpositive/groupby_grouping_sets_grouping.q.out index 7faf278..b82d9c2 100644 --- a/ql/src/test/results/clientpositive/groupby_grouping_sets_grouping.q.out +++ b/ql/src/test/results/clientpositive/groupby_grouping_sets_grouping.q.out @@ -85,17 +85,17 @@ group by rollup(key, value) POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 3 1 1 +1 1 0 0 0 1 NULL 0 0 0 1 NULL 1 0 1 -1 1 0 0 0 -2 NULL 1 0 1 2 2 0 0 0 +2 NULL 1 0 1 +3 3 0 0 0 3 NULL 0 0 0 3 NULL 1 0 1 -3 3 0 0 0 -4 NULL 1 0 1 4 5 0 0 0 +4 NULL 1 0 1 +NULL NULL 3 1 1 PREHOOK: query: explain select key, value, `grouping__id`, grouping(key), grouping(value) from T1 @@ -167,22 +167,22 @@ group by cube(key, value) POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 2 1 0 -NULL NULL 3 1 1 -NULL 1 2 1 0 -NULL 2 2 1 0 -NULL 3 2 1 0 -NULL 5 2 1 0 +1 1 0 0 0 1 NULL 0 0 0 1 NULL 1 0 1 -1 1 0 0 0 -2 NULL 1 0 1 2 2 0 0 0 +2 NULL 1 0 1 +3 3 0 0 0 3 NULL 0 0 0 3 NULL 1 0 1 -3 3 0 0 0 -4 NULL 1 0 1 4 5 0 0 0 +4 NULL 1 0 1 +NULL 1 2 1 0 +NULL 2 2 1 0 +NULL 3 2 1 0 +NULL 5 2 1 0 +NULL NULL 2 1 0 +NULL NULL 3 1 1 PREHOOK: query: explain select key, value from T1 @@ -261,12 +261,12 @@ having grouping(key) = 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL -NULL NULL NULL 1 NULL 2 NULL 3 NULL 5 +NULL NULL +NULL NULL PREHOOK: query: explain select key, value, grouping(key)+grouping(value) as x from T1 @@ -371,16 +371,16 @@ order by x desc, case when x = 1 then key end POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 2 -NULL 5 1 -NULL 3 1 -NULL 2 1 -NULL 1 1 -NULL NULL 1 1 NULL 1 2 NULL 1 3 NULL 1 4 NULL 1 +NULL 1 1 +NULL 2 1 +NULL 3 1 +NULL 5 1 +NULL NULL 1 +NULL NULL 2 PREHOOK: query: explain select key, value, `grouping__id`, grouping(key), grouping(value) from T1 @@ -452,17 +452,17 @@ group by rollup(key, value) POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 3 1 1 +1 1 0 0 0 1 NULL 0 0 0 1 NULL 1 0 1 -1 1 0 0 0 -2 NULL 1 0 1 2 2 0 0 0 +2 NULL 1 0 1 +3 3 0 0 0 3 NULL 0 0 0 3 NULL 1 0 1 -3 3 0 0 0 -4 NULL 1 0 1 4 5 0 0 0 +4 NULL 1 0 1 +NULL NULL 3 1 1 PREHOOK: query: explain select key, value, `grouping__id`, grouping(key), grouping(value) from T1 @@ -534,22 +534,22 @@ group by cube(key, value) POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 2 1 0 -NULL NULL 3 1 1 -NULL 1 2 1 0 -NULL 2 2 1 0 -NULL 3 2 1 0 -NULL 5 2 1 0 +1 1 0 0 0 1 NULL 0 0 0 1 NULL 1 0 1 -1 1 0 0 0 -2 NULL 1 0 1 2 2 0 0 0 +2 NULL 1 0 1 +3 3 0 0 0 3 NULL 0 0 0 3 NULL 1 0 1 -3 3 0 0 0 -4 NULL 1 0 1 4 5 0 0 0 +4 NULL 1 0 1 +NULL 1 2 1 0 +NULL 2 2 1 0 +NULL 3 2 1 0 +NULL 5 2 1 0 +NULL NULL 2 1 0 +NULL NULL 3 1 1 PREHOOK: query: explain select key, value from T1 @@ -625,12 +625,12 @@ having grouping(key) = 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL -NULL NULL NULL 1 NULL 2 NULL 3 NULL 5 +NULL NULL +NULL NULL PREHOOK: query: explain select key, value, grouping(key)+grouping(value) as x from T1 @@ -735,16 +735,16 @@ order by x desc, case when x = 1 then key end POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 2 -NULL 5 1 -NULL 3 1 -NULL 2 1 -NULL 1 1 -NULL NULL 1 1 NULL 1 2 NULL 1 3 NULL 1 4 NULL 1 +NULL 1 1 +NULL 2 1 +NULL 3 1 +NULL 5 1 +NULL NULL 1 +NULL NULL 2 PREHOOK: query: explain select key, value, grouping(key), grouping(value) from T1 @@ -816,11 +816,11 @@ group by key, value POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -1 NULL 0 0 1 1 0 0 +1 NULL 0 0 2 2 0 0 -3 NULL 0 0 3 3 0 0 +3 NULL 0 0 4 5 0 0 PREHOOK: query: explain select key, value, grouping(value) @@ -893,11 +893,11 @@ group by key, value POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -1 NULL 0 1 1 0 +1 NULL 0 2 2 0 -3 NULL 0 3 3 0 +3 NULL 0 4 5 0 PREHOOK: query: explain select key, value @@ -970,9 +970,9 @@ having grouping(key) = 0 POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -1 NULL 1 1 +1 NULL 2 2 -3 NULL 3 3 +3 NULL 4 5 http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/results/clientpositive/groupby_grouping_sets_limit.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/groupby_grouping_sets_limit.q.out b/ql/src/test/results/clientpositive/groupby_grouping_sets_limit.q.out index e2d9d96..02636eb 100644 --- a/ql/src/test/results/clientpositive/groupby_grouping_sets_limit.q.out +++ b/ql/src/test/results/clientpositive/groupby_grouping_sets_limit.q.out @@ -85,16 +85,16 @@ POSTHOOK: query: SELECT a, b, count(*) from T1 group by a, b with cube LIMIT 10 POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 6 -NULL 1 2 -NULL 2 3 -NULL 3 1 -1 NULL 1 1 1 1 -2 NULL 2 +1 NULL 1 2 2 1 2 3 1 +2 NULL 2 3 NULL 1 +NULL 1 2 +NULL 2 3 +NULL 3 1 +NULL NULL 6 PREHOOK: query: EXPLAIN SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b), b, ()) LIMIT 10 PREHOOK: type: QUERY @@ -166,16 +166,16 @@ POSTHOOK: query: SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -NULL NULL 6 -NULL 1 2 -NULL 2 3 -NULL 3 1 -1 NULL 1 1 1 1 -2 NULL 2 +1 NULL 1 2 2 1 2 3 1 +2 NULL 2 3 NULL 1 +NULL 1 2 +NULL 2 3 +NULL 3 1 +NULL NULL 6 PREHOOK: query: EXPLAIN SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, (a, b)) LIMIT 10 PREHOOK: type: QUERY @@ -247,15 +247,15 @@ POSTHOOK: query: SELECT a, b, count(*) FROM T1 GROUP BY a, b GROUPING SETS (a, ( POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### -1 NULL 1 1 1 1 -2 NULL 2 +1 NULL 1 2 2 1 2 3 1 -3 NULL 1 +2 NULL 2 3 2 1 -5 NULL 1 +3 NULL 1 5 2 1 +5 NULL 1 8 NULL 1 PREHOOK: query: EXPLAIN SELECT a FROM T1 GROUP BY a, b, c GROUPING SETS (a, b, c) LIMIT 10 @@ -325,6 +325,7 @@ POSTHOOK: query: SELECT a FROM T1 GROUP BY a, b, c GROUPING SETS (a, b, c) LIMIT POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### +1 NULL NULL NULL @@ -334,7 +335,6 @@ NULL NULL NULL NULL -1 PREHOOK: query: EXPLAIN SELECT a FROM T1 GROUP BY a GROUPING SETS ((a), (a)) LIMIT 10 PREHOOK: type: QUERY http://git-wip-us.apache.org/repos/asf/hive/blob/47ef02e4/ql/src/test/results/clientpositive/llap/groupby_grouping_id2.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/llap/groupby_grouping_id2.q.out b/ql/src/test/results/clientpositive/llap/groupby_grouping_id2.q.out index be5db9c..d7f83d5 100644 --- a/ql/src/test/results/clientpositive/llap/groupby_grouping_id2.q.out +++ b/ql/src/test/results/clientpositive/llap/groupby_grouping_id2.q.out @@ -22,6 +22,7 @@ POSTHOOK: query: SELECT key, value, GROUPING__ID, count(*) from T1 GROUP BY key, POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### +key value grouping__id _c3 1 1 0 1 1 NULL 0 1 1 NULL 1 2 @@ -41,6 +42,7 @@ POSTHOOK: query: SELECT key, value, GROUPING__ID, count(*) from T1 GROUP BY ROLL POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### +key value grouping__id _c3 1 1 0 1 1 NULL 0 1 1 NULL 1 2 @@ -70,6 +72,7 @@ GROUP BY GROUPING__ID POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### +grouping__id _c1 0 6 1 4 3 1 @@ -91,6 +94,7 @@ GROUP BY GROUPING__ID POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### +grouping__id _c1 0 6 1 4 3 1 @@ -108,6 +112,7 @@ ON t1.GROUPING__ID = t2.GROUPING__ID POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### +t1.grouping__id t2.grouping__id 0 0 0 0 0 0 @@ -175,6 +180,7 @@ ON t1.GROUPING__ID = t2.GROUPING__ID POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### +t1.grouping__id t2.grouping__id 0 0 0 0 0 0 @@ -236,6 +242,7 @@ POSTHOOK: query: SELECT key, value, GROUPING__ID, count(*) from T1 GROUP BY key, POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### +key value grouping__id _c3 1 1 0 1 1 NULL 0 1 1 NULL 1 2 @@ -265,6 +272,7 @@ GROUP BY GROUPING__ID POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### +grouping__id _c1 0 6 1 4 3 1 @@ -282,6 +290,7 @@ ON t1.GROUPING__ID = t2.GROUPING__ID POSTHOOK: type: QUERY POSTHOOK: Input: default@t1 #### A masked pattern was here #### +t1.grouping__id t2.grouping__id 0 0 0 0 0 0