http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby8.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby8.q b/ql/src/test/queries/clientpositive/groupby8.q index cb9f12c..4f25cf6 100644 --- a/ql/src/test/queries/clientpositive/groupby8.q +++ b/ql/src/test/queries/clientpositive/groupby8.q @@ -4,31 +4,31 @@ set hive.groupby.skewindata=true; -- SORT_QUERY_RESULTS -CREATE TABLE DEST1(key INT, value STRING) STORED AS TEXTFILE; -CREATE TABLE DEST2(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE DEST1_n71(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE DEST2_n15(key INT, value STRING) STORED AS TEXTFILE; EXPLAIN FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; +INSERT OVERWRITE TABLE DEST1_n71 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n15 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; +INSERT OVERWRITE TABLE DEST1_n71 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n15 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; -SELECT DEST1.* FROM DEST1; -SELECT DEST2.* FROM DEST2; +SELECT DEST1_n71.* FROM DEST1_n71; +SELECT DEST2_n15.* FROM DEST2_n15; set hive.multigroupby.singlereducer=false; EXPLAIN FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; +INSERT OVERWRITE TABLE DEST1_n71 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n15 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; +INSERT OVERWRITE TABLE DEST1_n71 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n15 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; -SELECT DEST1.* FROM DEST1; -SELECT DEST2.* FROM DEST2; +SELECT DEST1_n71.* FROM DEST1_n71; +SELECT DEST2_n15.* FROM DEST2_n15;
http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby8_map.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby8_map.q b/ql/src/test/queries/clientpositive/groupby8_map.q index 96286d3..1323dff 100644 --- a/ql/src/test/queries/clientpositive/groupby8_map.q +++ b/ql/src/test/queries/clientpositive/groupby8_map.q @@ -5,18 +5,18 @@ set mapred.reduce.tasks=31; -- SORT_QUERY_RESULTS -CREATE TABLE DEST1(key INT, value STRING) STORED AS TEXTFILE; -CREATE TABLE DEST2(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE DEST1_n136(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE DEST2_n35(key INT, value STRING) STORED AS TEXTFILE; EXPLAIN FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; +INSERT OVERWRITE TABLE DEST1_n136 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n35 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; +INSERT OVERWRITE TABLE DEST1_n136 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n35 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; -SELECT DEST1.* FROM DEST1; -SELECT DEST2.* FROM DEST2; +SELECT DEST1_n136.* FROM DEST1_n136; +SELECT DEST2_n35.* FROM DEST2_n35; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby8_map_skew.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby8_map_skew.q b/ql/src/test/queries/clientpositive/groupby8_map_skew.q index 4938b28..5e5a71f 100644 --- a/ql/src/test/queries/clientpositive/groupby8_map_skew.q +++ b/ql/src/test/queries/clientpositive/groupby8_map_skew.q @@ -5,18 +5,18 @@ set mapred.reduce.tasks=31; -- SORT_QUERY_RESULTS -CREATE TABLE DEST1(key INT, value STRING) STORED AS TEXTFILE; -CREATE TABLE DEST2(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE DEST1_n87(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE DEST2_n22(key INT, value STRING) STORED AS TEXTFILE; EXPLAIN FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; +INSERT OVERWRITE TABLE DEST1_n87 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n22 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; +INSERT OVERWRITE TABLE DEST1_n87 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n22 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; -SELECT DEST1.* FROM DEST1; -SELECT DEST2.* FROM DEST2; +SELECT DEST1_n87.* FROM DEST1_n87; +SELECT DEST2_n22.* FROM DEST2_n22; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby8_noskew.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby8_noskew.q b/ql/src/test/queries/clientpositive/groupby8_noskew.q index 3d2fa39..8c0328c 100644 --- a/ql/src/test/queries/clientpositive/groupby8_noskew.q +++ b/ql/src/test/queries/clientpositive/groupby8_noskew.q @@ -6,17 +6,17 @@ set mapred.reduce.tasks=31; -- SORT_QUERY_RESULTS -CREATE TABLE DEST1(key INT, value STRING) STORED AS TEXTFILE; -CREATE TABLE DEST2(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE DEST1_n48(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE DEST2_n9(key INT, value STRING) STORED AS TEXTFILE; EXPLAIN FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; +INSERT OVERWRITE TABLE DEST1_n48 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n9 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; +INSERT OVERWRITE TABLE DEST1_n48 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n9 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key; -SELECT DEST1.* FROM DEST1; -SELECT DEST2.* FROM DEST2; +SELECT DEST1_n48.* FROM DEST1_n48; +SELECT DEST2_n9.* FROM DEST2_n9; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby9.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby9.q b/ql/src/test/queries/clientpositive/groupby9.q index 5608df5..96111b5 100644 --- a/ql/src/test/queries/clientpositive/groupby9.q +++ b/ql/src/test/queries/clientpositive/groupby9.q @@ -1,69 +1,69 @@ --! qt:dataset:src -- SORT_QUERY_RESULTS -CREATE TABLE DEST1(key INT, value STRING) STORED AS TEXTFILE; -CREATE TABLE DEST2(key INT, val1 STRING, val2 STRING) STORED AS TEXTFILE; +CREATE TABLE DEST1_n117(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE DEST2_n31(key INT, val1 STRING, val2 STRING) STORED AS TEXTFILE; EXPLAIN FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key, SRC.value; +INSERT OVERWRITE TABLE DEST1_n117 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n31 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key, SRC.value; FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key, SRC.value; +INSERT OVERWRITE TABLE DEST1_n117 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n31 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key, SRC.value; -SELECT DEST1.* FROM DEST1; -SELECT DEST2.* FROM DEST2; +SELECT DEST1_n117.* FROM DEST1_n117; +SELECT DEST2_n31.* FROM DEST2_n31; EXPLAIN FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.value, SRC.key; +INSERT OVERWRITE TABLE DEST1_n117 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n31 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.value, SRC.key; FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.value, SRC.key; +INSERT OVERWRITE TABLE DEST1_n117 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n31 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.value, SRC.key; -SELECT DEST1.* FROM DEST1; -SELECT DEST2.* FROM DEST2; +SELECT DEST1_n117.* FROM DEST1_n117; +SELECT DEST2_n31.* FROM DEST2_n31; set hive.multigroupby.singlereducer=false; EXPLAIN FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key, SRC.value; +INSERT OVERWRITE TABLE DEST1_n117 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n31 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key, SRC.value; FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key, SRC.value; +INSERT OVERWRITE TABLE DEST1_n117 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n31 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key, SRC.value; -SELECT DEST1.* FROM DEST1; -SELECT DEST2.* FROM DEST2; +SELECT DEST1_n117.* FROM DEST1_n117; +SELECT DEST2_n31.* FROM DEST2_n31; EXPLAIN FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, SRC.value, COUNT(SUBSTR(SRC.value,5)) GROUP BY SRC.key, SRC.value; +INSERT OVERWRITE TABLE DEST1_n117 SELECT SRC.key, COUNT(SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n31 SELECT SRC.key, SRC.value, COUNT(SUBSTR(SRC.value,5)) GROUP BY SRC.key, SRC.value; FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, SRC.value, COUNT(SUBSTR(SRC.value,5)) GROUP BY SRC.key, SRC.value; +INSERT OVERWRITE TABLE DEST1_n117 SELECT SRC.key, COUNT(SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n31 SELECT SRC.key, SRC.value, COUNT(SUBSTR(SRC.value,5)) GROUP BY SRC.key, SRC.value; -SELECT DEST1.* FROM DEST1; -SELECT DEST2.* FROM DEST2; +SELECT DEST1_n117.* FROM DEST1_n117; +SELECT DEST2_n31.* FROM DEST2_n31; EXPLAIN FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.value, SRC.key; +INSERT OVERWRITE TABLE DEST1_n117 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n31 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.value, SRC.key; FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key -INSERT OVERWRITE TABLE DEST2 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.value, SRC.key; +INSERT OVERWRITE TABLE DEST1_n117 SELECT SRC.key, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.key +INSERT OVERWRITE TABLE DEST2_n31 SELECT SRC.key, SRC.value, COUNT(DISTINCT SUBSTR(SRC.value,5)) GROUP BY SRC.value, SRC.key; -SELECT DEST1.* FROM DEST1; -SELECT DEST2.* FROM DEST2; +SELECT DEST1_n117.* FROM DEST1_n117; +SELECT DEST2_n31.* FROM DEST2_n31; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_complex_types.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_complex_types.q b/ql/src/test/queries/clientpositive/groupby_complex_types.q index 7ea9f59..87eec2f 100644 --- a/ql/src/test/queries/clientpositive/groupby_complex_types.q +++ b/ql/src/test/queries/clientpositive/groupby_complex_types.q @@ -1,22 +1,22 @@ --! qt:dataset:src -- SORT_QUERY_RESULTS -CREATE TABLE DEST1(key ARRAY<STRING>, value BIGINT) STORED AS TEXTFILE; -CREATE TABLE DEST2(key MAP<STRING, STRING>, value BIGINT) STORED AS TEXTFILE; -CREATE TABLE DEST3(key STRUCT<col1:STRING, col2:STRING>, value BIGINT) STORED AS TEXTFILE; +CREATE TABLE DEST1_n163(key ARRAY<STRING>, value BIGINT) STORED AS TEXTFILE; +CREATE TABLE DEST2_n41(key MAP<STRING, STRING>, value BIGINT) STORED AS TEXTFILE; +CREATE TABLE DEST3_n7(key STRUCT<col1:STRING, col2:STRING>, value BIGINT) STORED AS TEXTFILE; EXPLAIN FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT ARRAY(SRC.key), COUNT(1) GROUP BY ARRAY(SRC.key) -INSERT OVERWRITE TABLE DEST2 SELECT MAP(SRC.key, SRC.value), COUNT(1) GROUP BY MAP(SRC.key, SRC.value) -INSERT OVERWRITE TABLE DEST3 SELECT STRUCT(SRC.key, SRC.value), COUNT(1) GROUP BY STRUCT(SRC.key, SRC.value); +INSERT OVERWRITE TABLE DEST1_n163 SELECT ARRAY(SRC.key), COUNT(1) GROUP BY ARRAY(SRC.key) +INSERT OVERWRITE TABLE DEST2_n41 SELECT MAP(SRC.key, SRC.value), COUNT(1) GROUP BY MAP(SRC.key, SRC.value) +INSERT OVERWRITE TABLE DEST3_n7 SELECT STRUCT(SRC.key, SRC.value), COUNT(1) GROUP BY STRUCT(SRC.key, SRC.value); FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT ARRAY(SRC.key), COUNT(1) GROUP BY ARRAY(SRC.key) -INSERT OVERWRITE TABLE DEST2 SELECT MAP(SRC.key, SRC.value), COUNT(1) GROUP BY MAP(SRC.key, SRC.value) -INSERT OVERWRITE TABLE DEST3 SELECT STRUCT(SRC.key, SRC.value), COUNT(1) GROUP BY STRUCT(SRC.key, SRC.value); +INSERT OVERWRITE TABLE DEST1_n163 SELECT ARRAY(SRC.key), COUNT(1) GROUP BY ARRAY(SRC.key) +INSERT OVERWRITE TABLE DEST2_n41 SELECT MAP(SRC.key, SRC.value), COUNT(1) GROUP BY MAP(SRC.key, SRC.value) +INSERT OVERWRITE TABLE DEST3_n7 SELECT STRUCT(SRC.key, SRC.value), COUNT(1) GROUP BY STRUCT(SRC.key, SRC.value); -SELECT DEST1.* FROM DEST1; -SELECT DEST2.* FROM DEST2; -SELECT DEST3.* FROM DEST3; +SELECT DEST1_n163.* FROM DEST1_n163; +SELECT DEST2_n41.* FROM DEST2_n41; +SELECT DEST3_n7.* FROM DEST3_n7; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_complex_types_multi_single_reducer.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_complex_types_multi_single_reducer.q b/ql/src/test/queries/clientpositive/groupby_complex_types_multi_single_reducer.q index 26236ab..82ee158 100644 --- a/ql/src/test/queries/clientpositive/groupby_complex_types_multi_single_reducer.q +++ b/ql/src/test/queries/clientpositive/groupby_complex_types_multi_single_reducer.q @@ -3,18 +3,18 @@ set hive.multigroupby.singlereducer=true; -- SORT_QUERY_RESULTS -CREATE TABLE DEST1(key ARRAY<STRING>, value BIGINT) STORED AS TEXTFILE; -CREATE TABLE DEST2(key MAP<STRING, STRING>, value BIGINT) STORED AS TEXTFILE; +CREATE TABLE DEST1_n47(key ARRAY<STRING>, value BIGINT) STORED AS TEXTFILE; +CREATE TABLE DEST2_n8(key MAP<STRING, STRING>, value BIGINT) STORED AS TEXTFILE; EXPLAIN FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT ARRAY(SRC.key) as keyarray, COUNT(1) GROUP BY ARRAY(SRC.key) ORDER BY keyarray limit 10 -INSERT OVERWRITE TABLE DEST2 SELECT MAP(SRC.key, SRC.value) as kvmap, COUNT(1) GROUP BY MAP(SRC.key, SRC.value) ORDER BY kvmap limit 10; +INSERT OVERWRITE TABLE DEST1_n47 SELECT ARRAY(SRC.key) as keyarray, COUNT(1) GROUP BY ARRAY(SRC.key) ORDER BY keyarray limit 10 +INSERT OVERWRITE TABLE DEST2_n8 SELECT MAP(SRC.key, SRC.value) as kvmap, COUNT(1) GROUP BY MAP(SRC.key, SRC.value) ORDER BY kvmap limit 10; FROM SRC -INSERT OVERWRITE TABLE DEST1 SELECT ARRAY(SRC.key) as keyarray, COUNT(1) GROUP BY ARRAY(SRC.key) ORDER BY keyarray limit 10 -INSERT OVERWRITE TABLE DEST2 SELECT MAP(SRC.key, SRC.value) as kvmap, COUNT(1) GROUP BY MAP(SRC.key, SRC.value) ORDER BY kvmap limit 10; +INSERT OVERWRITE TABLE DEST1_n47 SELECT ARRAY(SRC.key) as keyarray, COUNT(1) GROUP BY ARRAY(SRC.key) ORDER BY keyarray limit 10 +INSERT OVERWRITE TABLE DEST2_n8 SELECT MAP(SRC.key, SRC.value) as kvmap, COUNT(1) GROUP BY MAP(SRC.key, SRC.value) ORDER BY kvmap limit 10; -SELECT DEST1.* FROM DEST1; -SELECT DEST2.* FROM DEST2; +SELECT DEST1_n47.* FROM DEST1_n47; +SELECT DEST2_n8.* FROM DEST2_n8; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_cube1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_cube1.q b/ql/src/test/queries/clientpositive/groupby_cube1.q index 92456d0..0250c60 100644 --- a/ql/src/test/queries/clientpositive/groupby_cube1.q +++ b/ql/src/test/queries/clientpositive/groupby_cube1.q @@ -5,52 +5,52 @@ set hive.groupby.skewindata=false; -- SORT_QUERY_RESULTS -CREATE TABLE T1(key STRING, val STRING) STORED AS TEXTFILE; +CREATE TABLE T1_n82(key STRING, val STRING) STORED AS TEXTFILE; -LOAD DATA LOCAL INPATH '../../data/files/T1.txt' INTO TABLE T1; +LOAD DATA LOCAL INPATH '../../data/files/T1.txt' INTO TABLE T1_n82; EXPLAIN -SELECT key, val, count(1) FROM T1 GROUP BY key, val with cube; +SELECT key, val, count(1) FROM T1_n82 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_n82 GROUP BY CUBE(key, val); -SELECT key, val, count(1) FROM T1 GROUP BY key, val with cube; +SELECT key, val, count(1) FROM T1_n82 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_n82 GROUP BY key, val with cube; -SELECT key, val, GROUPING__ID, count(1) FROM T1 GROUP BY key, val with cube; +SELECT key, val, GROUPING__ID, count(1) FROM T1_n82 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_n82 GROUP BY key with cube; -SELECT key, count(distinct val) FROM T1 GROUP BY key with cube; +SELECT key, count(distinct val) FROM T1_n82 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_n82 GROUP BY key, val with cube; -SELECT key, val, count(1) FROM T1 GROUP BY key, val with cube; +SELECT key, val, count(1) FROM T1_n82 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_n82 GROUP BY key with cube; -SELECT key, count(distinct val) FROM T1 GROUP BY key with cube; +SELECT key, count(distinct val) FROM T1_n82 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; +CREATE TABLE T2_n51(key1 STRING, key2 STRING, val INT) STORED AS TEXTFILE; +CREATE TABLE T3_n16(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_n82 +INSERT OVERWRITE TABLE T2_n51 SELECT key, val, count(1) group by key, val with cube +INSERT OVERWRITE TABLE T3_n16 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; +FROM T1_n82 +INSERT OVERWRITE TABLE T2_n51 SELECT key, val, count(1) group by key, val with cube +INSERT OVERWRITE TABLE T3_n16 SELECT key, val, sum(1) group by key, val with cube; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_cube_multi_gby.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_cube_multi_gby.q b/ql/src/test/queries/clientpositive/groupby_cube_multi_gby.q index b2a0dfa..535c3b1 100644 --- a/ql/src/test/queries/clientpositive/groupby_cube_multi_gby.q +++ b/ql/src/test/queries/clientpositive/groupby_cube_multi_gby.q @@ -1,13 +1,13 @@ --! qt:dataset:src set hive.multigroupby.singlereducer=false; -create table t1 like src; -create table t2 like src; +create table t1_n21 like src; +create table t2_n13 like src; explain from src -insert into table t1 select +insert into table t1_n21 select key, GROUPING__ID group by cube(key, value) -insert into table t2 select +insert into table t2_n13 select key, value group by key, value grouping sets ((key), (key, value)); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_distinct_samekey.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_distinct_samekey.q b/ql/src/test/queries/clientpositive/groupby_distinct_samekey.q index 7a40d01..d923d89 100644 --- a/ql/src/test/queries/clientpositive/groupby_distinct_samekey.q +++ b/ql/src/test/queries/clientpositive/groupby_distinct_samekey.q @@ -3,16 +3,16 @@ set hive.mapred.mode=nonstrict; -- This test covers HIVE-2332 -- SORT_QUERY_RESULTS -create table t1 (int1 int, int2 int, str1 string, str2 string); +create table t1_n60 (int1_n60 int, int2 int, str1 string, str2 string); set hive.optimize.reducededuplication=false; --disabled RS-dedup for keeping intention of test -insert into table t1 select cast(key as int), cast(key as int), value, value from src where key < 6; -explain select Q1.int1, sum(distinct Q1.int1) from (select * from t1 order by int1) Q1 group by Q1.int1; -explain select int1, sum(distinct int1) from t1 group by int1; +insert into table t1_n60 select cast(key as int), cast(key as int), value, value from src where key < 6; +explain select Q1.int1_n60, sum(distinct Q1.int1_n60) from (select * from t1_n60 order by int1_n60) Q1 group by Q1.int1_n60; +explain select int1_n60, sum(distinct int1_n60) from t1_n60 group by int1_n60; -select Q1.int1, sum(distinct Q1.int1) from (select * from t1 order by int1) Q1 group by Q1.int1; -select int1, sum(distinct int1) from t1 group by int1; +select Q1.int1_n60, sum(distinct Q1.int1_n60) from (select * from t1_n60 order by int1_n60) Q1 group by Q1.int1_n60; +select int1_n60, sum(distinct int1_n60) from t1_n60 group by int1_n60; -drop table t1; +drop table t1_n60; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_duplicate_key.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_duplicate_key.q b/ql/src/test/queries/clientpositive/groupby_duplicate_key.q index 72e38f4..ce5a091 100644 --- a/ql/src/test/queries/clientpositive/groupby_duplicate_key.q +++ b/ql/src/test/queries/clientpositive/groupby_duplicate_key.q @@ -5,13 +5,13 @@ select distinct key, "" as dummy1, "" as dummy2 from src tablesample (10 rows); select distinct key, "" as dummy1, "" as dummy2 from src tablesample (10 rows); explain -create table dummy as +create table dummy_n6 as select distinct key, "X" as dummy1, "X" as dummy2 from src tablesample (10 rows); -create table dummy as +create table dummy_n6 as select distinct key, "X" as dummy1, "X" as dummy2 from src tablesample (10 rows); -select key,dummy1,dummy2 from dummy; +select key,dummy1,dummy2 from dummy_n6; explain select max('pants'), max('pANTS') from src group by key limit 1; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_empty.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_empty.q b/ql/src/test/queries/clientpositive/groupby_empty.q index 2ce33ae..30604c3 100644 --- a/ql/src/test/queries/clientpositive/groupby_empty.q +++ b/ql/src/test/queries/clientpositive/groupby_empty.q @@ -1,9 +1,9 @@ -create table t (a int); +create table t_n34 (a int); -insert into t values (1),(1),(2); +insert into t_n34 values (1),(1),(2); -explain select count(*) from t group by (); +explain select count(*) from t_n34 group by (); -select count(*) from t group by (); +select count(*) from t_n34 group by (); http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/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 7068d21..d4948b9 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_id1.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_id1.q @@ -1,17 +1,17 @@ SET hive.vectorized.execution.enabled=false; -CREATE TABLE T1(key STRING, val STRING) STORED AS TEXTFILE; +CREATE TABLE T1_n158(key STRING, val STRING) STORED AS TEXTFILE; -LOAD DATA LOCAL INPATH '../../data/files/T1.txt' INTO TABLE T1; +LOAD DATA LOCAL INPATH '../../data/files/T1.txt' INTO TABLE T1_n158; -- 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 key, val, GROUPING__ID from T1_n158 group by key, val with cube; +SELECT key, val, GROUPING__ID from T1_n158 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 GROUPING__ID, key, val from T1_n158 group by key, val with rollup; +SELECT GROUPING__ID, key, val from T1_n158 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); +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_n158 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_n158 group by cube(key, val); http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/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 ba755c4..778d4b9 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_id2.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_id2.q @@ -3,40 +3,40 @@ SET hive.vectorized.execution.enabled=false; set hive.fetch.task.conversion=none; set hive.cli.print.header=true; -CREATE TABLE T1(key INT, value INT) STORED AS TEXTFILE; +CREATE TABLE T1_n123(key INT, value INT) STORED AS TEXTFILE; -LOAD DATA LOCAL INPATH '../../data/files/groupby_groupingid.txt' INTO TABLE T1; +LOAD DATA LOCAL INPATH '../../data/files/groupby_groupingid.txt' INTO TABLE T1_n123; 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 key, value, GROUPING__ID, count(*) from T1_n123 GROUP BY key, value WITH ROLLUP; +SELECT key, value, GROUPING__ID, count(*) from T1_n123 GROUP BY ROLLUP (key, value); SELECT GROUPING__ID, count(*) FROM ( -SELECT key, value, GROUPING__ID, count(*) from T1 GROUP BY key, value WITH ROLLUP +SELECT key, value, GROUPING__ID, count(*) from T1_n123 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) +SELECT key, value, GROUPING__ID, count(*) from T1_n123 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 +SELECT t1.GROUPING__ID, t2.GROUPING__ID FROM (SELECT GROUPING__ID FROM T1_n123 GROUP BY key,value WITH ROLLUP) t1 JOIN -(SELECT GROUPING__ID FROM T1 GROUP BY key, value WITH ROLLUP) t2 +(SELECT GROUPING__ID FROM T1_n123 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 +SELECT t1.GROUPING__ID, t2.GROUPING__ID FROM (SELECT GROUPING__ID FROM T1_n123 GROUP BY ROLLUP(key,value)) t1 JOIN -(SELECT GROUPING__ID FROM T1 GROUP BY ROLLUP(key, value)) t2 +(SELECT GROUPING__ID FROM T1_n123 GROUP BY ROLLUP(key, value)) t2 ON t1.GROUPING__ID = t2.GROUPING__ID; @@ -45,18 +45,18 @@ 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 key, value, GROUPING__ID, count(*) from T1_n123 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 +SELECT key, value, GROUPING__ID, count(*) from T1_n123 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 +SELECT t1.GROUPING__ID, t2.GROUPING__ID FROM (SELECT GROUPING__ID FROM T1_n123 GROUP BY key,value WITH ROLLUP) t1 JOIN -(SELECT GROUPING__ID FROM T1 GROUP BY key, value WITH ROLLUP) t2 +(SELECT GROUPING__ID FROM T1_n123 GROUP BY key, value WITH ROLLUP) t2 ON t1.GROUPING__ID = t2.GROUPING__ID; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/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 29b2f15..b1e765d 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_id3.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_id3.q @@ -1,8 +1,8 @@ SET hive.vectorized.execution.enabled=false; -CREATE TABLE T1(key INT, value INT) STORED AS TEXTFILE; +CREATE TABLE T1_n86(key INT, value INT) STORED AS TEXTFILE; -LOAD DATA LOCAL INPATH '../../data/files/groupby_groupingid.txt' INTO TABLE T1; +LOAD DATA LOCAL INPATH '../../data/files/groupby_groupingid.txt' INTO TABLE T1_n86; set hive.cbo.enable = false; @@ -10,12 +10,12 @@ set hive.cbo.enable = false; EXPLAIN SELECT key, value, GROUPING__ID, count(*) -FROM T1 +FROM T1_n86 GROUP BY key, value GROUPING SETS ((), (key)) HAVING GROUPING__ID = 1; SELECT key, value, GROUPING__ID, count(*) -FROM T1 +FROM T1_n86 GROUP BY key, value GROUPING SETS ((), (key)) HAVING GROUPING__ID = 1; @@ -24,12 +24,12 @@ set hive.cbo.enable = true; EXPLAIN SELECT key, value, GROUPING__ID, count(*) -FROM T1 +FROM T1_n86 GROUP BY key, value GROUPING SETS ((), (key)) HAVING GROUPING__ID = 1; SELECT key, value, GROUPING__ID, count(*) -FROM T1 +FROM T1_n86 GROUP BY key, value GROUPING SETS ((), (key)) HAVING GROUPING__ID = 1; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/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 86c5246..57b61f8 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets1.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets1.q @@ -5,36 +5,36 @@ 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; +CREATE TABLE T1_n41(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; +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1_n41; -SELECT * FROM T1; +SELECT * FROM T1_n41; 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; +SELECT a, b, count(*) from T1_n41 group by a, b with cube; +SELECT a, b, count(*) from T1_n41 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); +SELECT a, b, count(*) from T1_n41 group by cube(a, b); +SELECT a, b, count(*) from T1_n41 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, ()); +SELECT a, b, count(*) FROM T1_n41 GROUP BY a, b GROUPING SETS (a, (a, b), b, ()); +SELECT a, b, count(*) FROM T1_n41 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)); +SELECT a, b, count(*) FROM T1_n41 GROUP BY a, b GROUPING SETS (a, (a, b)); +SELECT a, b, count(*) FROM T1_n41 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); +SELECT a FROM T1_n41 GROUP BY a, b, c GROUPING SETS (a, b, c); +SELECT a FROM T1_n41 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)); +SELECT a FROM T1_n41 GROUP BY a GROUPING SETS ((a), (a)); +SELECT a FROM T1_n41 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); +SELECT a + b, count(*) FROM T1_n41 GROUP BY a + b GROUPING SETS (a+b); +SELECT a + b, count(*) FROM T1_n41 GROUP BY a + b GROUPING SETS (a+b); http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/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 1934321..b24c1e0 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets2.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets2.q @@ -6,27 +6,27 @@ 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; +CREATE TABLE T1_n81(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; +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1_n81; -- 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; +SELECT a, b, count(*) from T1_n81 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; +SELECT a, b, count(*) from T1_n81 group by cube(a, b); +SELECT a, b, count(*) from T1_n81 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; +SELECT a, b, sum(c) from T1_n81 group by a, b with cube; +SELECT a, b, sum(c) from T1_n81 group by a, b with cube; -CREATE TABLE T2(a STRING, b STRING, c int, d int); +CREATE TABLE T2_n50(a STRING, b STRING, c int, d int); -INSERT OVERWRITE TABLE T2 -SELECT a, b, c, c from T1; +INSERT OVERWRITE TABLE T2_n50 +SELECT a, b, c, c from T1_n81; 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; +SELECT a, b, sum(c+d) from T2_n50 group by a, b with cube; +SELECT a, b, sum(c+d) from T2_n50 group by a, b with cube; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/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 81267dc..5021a7a 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets3.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets3.q @@ -5,14 +5,14 @@ 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, +-- In this test, 2 files are loaded into table T1_n118. 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(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; +CREATE TABLE T1_n118(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; -LOAD DATA LOCAL INPATH '../../data/files/grouping_sets2.txt' INTO TABLE T1; +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets1.txt' INTO TABLE T1_n118; +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets2.txt' INTO TABLE T1_n118; set hive.input.format = org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; set hive.new.job.grouping.set.cardinality = 30; @@ -21,17 +21,17 @@ set hive.new.job.grouping.set.cardinality = 30; -- (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; +SELECT a, b, avg(c), count(*) from T1_n118 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; +SELECT a, b, avg(c), count(*) from T1_n118 group by cube(a, b); +SELECT a, b, avg(c), count(*) from T1_n118 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; +SELECT a, b, avg(c), count(*) from T1_n118 group by a, b with cube; +SELECT a, b, avg(c), count(*) from T1_n118 group by a, b with cube; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/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 fa62992..86e8773 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets4.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets4.q @@ -8,29 +8,29 @@ set hive.merge.mapredfiles = false; -- Set merging to false above to make the explain more readable -CREATE TABLE T1(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; +CREATE TABLE T1_n143(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; +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1_n143; -- 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 +(SELECT a, b, count(*) from T1_n143 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 +(SELECT a, b, count(*) from T1_n143 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 +(SELECT a, b, count(*) from T1_n143 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 +(SELECT a, b, count(*) from T1_n143 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 +(SELECT a, b, count(*) from T1_n143 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 +(SELECT a, b, count(*) from T1_n143 where a < 3 group by a, b with cube) subq2 on subq1.a = subq2.a; set hive.new.job.grouping.set.cardinality=2; @@ -39,14 +39,14 @@ set hive.new.job.grouping.set.cardinality=2; -- for each of them EXPLAIN SELECT * FROM -(SELECT a, b, count(*) from T1 where a < 3 group by a, b with cube) subq1 +(SELECT a, b, count(*) from T1_n143 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 +(SELECT a, b, count(*) from T1_n143 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 +(SELECT a, b, count(*) from T1_n143 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 +(SELECT a, b, count(*) from T1_n143 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/38d3b8e1/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 829a0c2..25676f5 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets5.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets5.q @@ -4,30 +4,30 @@ set hive.merge.mapfiles = false; set hive.merge.mapredfiles = false; -- Set merging to false above to make the explain more readable -CREATE TABLE T1(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; +CREATE TABLE T1_n24(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; +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1_n24; -- 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; +(SELECT a, b, count(1) from T1_n24 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(1) from T1_n24 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; +(SELECT a, b, count(1) from T1_n24 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(1) from T1_n24 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; +(SELECT a, b, count(1) from T1_n24 group by a, b) subq1 group by a, b with cube; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/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 515dce3..5c0bb75 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets6.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets6.q @@ -1,9 +1,9 @@ set hive.mapred.mode=nonstrict; SET hive.vectorized.execution.enabled=false; -CREATE TABLE T1(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; +CREATE TABLE T1_n75(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; +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1_n75; -- SORT_QUERY_RESULTS @@ -12,11 +12,11 @@ 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 +(SELECT a, b from T1_n75 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 +(SELECT a, b from T1_n75 group by a, b grouping sets ( (a,b),a )) res WHERE res.a=5; set hive.cbo.enable = true; @@ -24,9 +24,9 @@ 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 +(SELECT a, b from T1_n75 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 +(SELECT a, b from T1_n75 group by a, b grouping sets ( (a,b),a )) res WHERE res.a=5; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/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 3f437a4..d400848 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets_grouping.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets_grouping.q @@ -2,48 +2,48 @@ SET hive.vectorized.execution.enabled=false; -- SORT_QUERY_RESULTS -CREATE TABLE T1(key INT, value INT) STORED AS TEXTFILE; +CREATE TABLE T1_n64(key INT, value INT) STORED AS TEXTFILE; -LOAD DATA LOCAL INPATH '../../data/files/groupby_groupingid.txt' INTO TABLE T1; +LOAD DATA LOCAL INPATH '../../data/files/groupby_groupingid.txt' INTO TABLE T1_n64; explain select key, value, `grouping__id`, grouping(key), grouping(value) -from T1 +from T1_n64 group by rollup(key, value); select key, value, `grouping__id`, grouping(key), grouping(value) -from T1 +from T1_n64 group by rollup(key, value); explain select key, value, `grouping__id`, grouping(key), grouping(value) -from T1 +from T1_n64 group by cube(key, value); select key, value, `grouping__id`, grouping(key), grouping(value) -from T1 +from T1_n64 group by cube(key, value); explain select key, value -from T1 +from T1_n64 group by cube(key, value) having grouping(key) = 1; select key, value -from T1 +from T1_n64 group by cube(key, value) having grouping(key) = 1; explain select key, value, grouping(key)+grouping(value) as x -from T1 +from T1_n64 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 +from T1_n64 group by cube(key, value) having grouping(key) = 1 OR grouping(value) = 1 order by x desc, case when x = 1 then key end; @@ -52,107 +52,107 @@ set hive.cbo.enable=false; explain select key, value, `grouping__id`, grouping(key), grouping(value) -from T1 +from T1_n64 group by rollup(key, value); select key, value, `grouping__id`, grouping(key), grouping(value) -from T1 +from T1_n64 group by rollup(key, value); explain select key, value, `grouping__id`, grouping(key), grouping(value) -from T1 +from T1_n64 group by cube(key, value); select key, value, `grouping__id`, grouping(key), grouping(value) -from T1 +from T1_n64 group by cube(key, value); explain select key, value -from T1 +from T1_n64 group by cube(key, value) having grouping(key) = 1; select key, value -from T1 +from T1_n64 group by cube(key, value) having grouping(key) = 1; explain select key, value, grouping(key)+grouping(value) as x -from T1 +from T1_n64 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 +from T1_n64 group by cube(key, value) having grouping(key) = 1 OR grouping(value) = 1 order by x desc, case when x = 1 then key end; explain select key, value, grouping(key), grouping(value) -from T1 +from T1_n64 group by key, value; select key, value, grouping(key), grouping(value) -from T1 +from T1_n64 group by key, value; explain select key, value, grouping(value) -from T1 +from T1_n64 group by key, value; select key, value, grouping(value) -from T1 +from T1_n64 group by key, value; explain select key, value -from T1 +from T1_n64 group by key, value having grouping(key) = 0; select key, value -from T1 +from T1_n64 group by key, value having grouping(key) = 0; explain select key, value, `grouping__id`, grouping(key, value) -from T1 +from T1_n64 group by cube(key, value); select key, value, `grouping__id`, grouping(key, value) -from T1 +from T1_n64 group by cube(key, value); explain select key, value, `grouping__id`, grouping(value, key) -from T1 +from T1_n64 group by cube(key, value); select key, value, `grouping__id`, grouping(value, key) -from T1 +from T1_n64 group by cube(key, value); explain select key, value, `grouping__id`, grouping(key, value) -from T1 +from T1_n64 group by rollup(key, value); select key, value, `grouping__id`, grouping(key, value) -from T1 +from T1_n64 group by rollup(key, value); explain select key, value, `grouping__id`, grouping(value, key) -from T1 +from T1_n64 group by rollup(key, value); select key, value, `grouping__id`, grouping(value, key) -from T1 +from T1_n64 group by rollup(key, value); http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/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 b6c5143..cda9c03 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_sets_limit.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_sets_limit.q @@ -1,36 +1,36 @@ -- SORT_QUERY_RESULTS -CREATE TABLE T1(a STRING, b STRING, c STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE; +CREATE TABLE T1_n141(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; +LOAD DATA LOCAL INPATH '../../data/files/grouping_sets.txt' INTO TABLE T1_n141; EXPLAIN -SELECT a, b, count(*) from T1 group by a, b with cube LIMIT 10; +SELECT a, b, count(*) from T1_n141 group by a, b with cube LIMIT 10; -SELECT a, b, count(*) from T1 group by a, b with cube LIMIT 10; +SELECT a, b, count(*) from T1_n141 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_n141 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; +SELECT a, b, count(*) FROM T1_n141 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_n141 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; +SELECT a, b, count(*) FROM T1_n141 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_n141 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; +SELECT a FROM T1_n141 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_n141 GROUP BY a GROUPING SETS ((a), (a)) LIMIT 10; -SELECT a FROM T1 GROUP BY a GROUPING SETS ((a), (a)) LIMIT 10; +SELECT a FROM T1_n141 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_n141 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; +SELECT a + b, count(*) FROM T1_n141 GROUP BY a + b GROUPING SETS (a+b) LIMIT 10; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_grouping_window.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_grouping_window.q b/ql/src/test/queries/clientpositive/groupby_grouping_window.q index 8a5c290..7ec8752 100644 --- a/ql/src/test/queries/clientpositive/groupby_grouping_window.q +++ b/ql/src/test/queries/clientpositive/groupby_grouping_window.q @@ -1,29 +1,29 @@ --! qt:dataset:src -create table t(category int, live int, comments int); -insert into table t select key, 0, 2 from src tablesample(3 rows); +create table t_n33(category int, live int, comments int); +insert into table t_n33 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 +FROM t_n33 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 +FROM t_n33 GROUP BY category GROUPING SETS ((), (category)) HAVING max(comments) > 0; SELECT grouping(category), lead(live) over(partition by grouping(category)) -FROM t +FROM t_n33 GROUP BY category, live GROUPING SETS ((), (category)); SELECT grouping(category), lead(live) over(partition by grouping(category)) -FROM t +FROM t_n33 GROUP BY category, live; SELECT grouping(category), lag(live) over(partition by grouping(category)) -FROM t +FROM t_n33 GROUP BY category, live; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_map_ppr.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_map_ppr.q b/ql/src/test/queries/clientpositive/groupby_map_ppr.q index d42416e..4f3a5a1 100644 --- a/ql/src/test/queries/clientpositive/groupby_map_ppr.q +++ b/ql/src/test/queries/clientpositive/groupby_map_ppr.q @@ -7,19 +7,19 @@ set mapred.reduce.tasks=31; -- SORT_QUERY_RESULTS -CREATE TABLE dest1(key STRING, c1 INT, c2 STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n144(key STRING, c1 INT, c2 STRING) STORED AS TEXTFILE; EXPLAIN EXTENDED FROM srcpart src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n144 SELECT substr(src.key,1,1), count(DISTINCT substr(src.value,5)), concat(substr(src.key,1,1),sum(substr(src.value,5))) WHERE src.ds = '2008-04-08' GROUP BY substr(src.key,1,1); FROM srcpart src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n144 SELECT substr(src.key,1,1), count(DISTINCT substr(src.value,5)), concat(substr(src.key,1,1),sum(substr(src.value,5))) WHERE src.ds = '2008-04-08' GROUP BY substr(src.key,1,1); -SELECT dest1.* FROM dest1; +SELECT dest1_n144.* FROM dest1_n144; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_map_ppr_multi_distinct.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_map_ppr_multi_distinct.q b/ql/src/test/queries/clientpositive/groupby_map_ppr_multi_distinct.q index 5d96983..2c2fbc4 100644 --- a/ql/src/test/queries/clientpositive/groupby_map_ppr_multi_distinct.q +++ b/ql/src/test/queries/clientpositive/groupby_map_ppr_multi_distinct.q @@ -7,19 +7,19 @@ set mapred.reduce.tasks=31; -- SORT_QUERY_RESULTS -CREATE TABLE dest1(key STRING, c1 INT, c2 STRING, C3 INT, c4 INT) STORED AS TEXTFILE; +CREATE TABLE dest1_n174(key STRING, c1 INT, c2 STRING, C3 INT, c4 INT) STORED AS TEXTFILE; EXPLAIN EXTENDED FROM srcpart src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n174 SELECT substr(src.key,1,1), count(DISTINCT substr(src.value,5)), concat(substr(src.key,1,1),sum(substr(src.value,5))), sum(DISTINCT substr(src.value, 5)), count(DISTINCT src.value) WHERE src.ds = '2008-04-08' GROUP BY substr(src.key,1,1); FROM srcpart src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n174 SELECT substr(src.key,1,1), count(DISTINCT substr(src.value,5)), concat(substr(src.key,1,1),sum(substr(src.value,5))), sum(DISTINCT substr(src.value, 5)), count(DISTINCT src.value) WHERE src.ds = '2008-04-08' GROUP BY substr(src.key,1,1); -SELECT dest1.* FROM dest1; +SELECT dest1_n174.* FROM dest1_n174; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_multi_insert_common_distinct.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_multi_insert_common_distinct.q b/ql/src/test/queries/clientpositive/groupby_multi_insert_common_distinct.q index 1d6d8dc..a76ce46 100644 --- a/ql/src/test/queries/clientpositive/groupby_multi_insert_common_distinct.q +++ b/ql/src/test/queries/clientpositive/groupby_multi_insert_common_distinct.q @@ -3,19 +3,19 @@ set hive.map.aggr=true; -- SORT_QUERY_RESULTS -create table dest1(key int, cnt int); -create table dest2(key int, cnt int); +create table dest1_n99(key int, cnt int); +create table dest2_n27(key int, cnt int); explain from src -insert overwrite table dest1 select key, count(distinct value) group by key -insert overwrite table dest2 select key+key, count(distinct value) group by key+key; +insert overwrite table dest1_n99 select key, count(distinct value) group by key +insert overwrite table dest2_n27 select key+key, count(distinct value) group by key+key; from src -insert overwrite table dest1 select key, count(distinct value) group by key -insert overwrite table dest2 select key+key, count(distinct value) group by key+key; +insert overwrite table dest1_n99 select key, count(distinct value) group by key +insert overwrite table dest2_n27 select key+key, count(distinct value) group by key+key; -select * from dest1 where key < 10; -select * from dest2 where key < 20 order by key limit 10; +select * from dest1_n99 where key < 10; +select * from dest2_n27 where key < 20 order by key limit 10; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_multi_single_reducer2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_multi_single_reducer2.q b/ql/src/test/queries/clientpositive/groupby_multi_single_reducer2.q index 5a5d149..1721172 100644 --- a/ql/src/test/queries/clientpositive/groupby_multi_single_reducer2.q +++ b/ql/src/test/queries/clientpositive/groupby_multi_single_reducer2.q @@ -1,22 +1,22 @@ --! qt:dataset:src set hive.multigroupby.singlereducer=true; -CREATE TABLE dest_g2(key STRING, c1 INT) STORED AS TEXTFILE; -CREATE TABLE dest_g3(key STRING, c1 INT, c2 INT) STORED AS TEXTFILE; +CREATE TABLE dest_g2_n4(key STRING, c1 INT) STORED AS TEXTFILE; +CREATE TABLE dest_g3_n0(key STRING, c1 INT, c2 INT) STORED AS TEXTFILE; -- SORT_QUERY_RESULTS EXPLAIN FROM src -INSERT OVERWRITE TABLE dest_g2 SELECT substr(src.key,1,1), count(DISTINCT src.key) WHERE substr(src.key,1,1) >= 5 GROUP BY substr(src.key,1,1) -INSERT OVERWRITE TABLE dest_g3 SELECT substr(src.key,1,1), count(DISTINCT src.key), count(src.value) WHERE substr(src.key,1,1) < 5 GROUP BY substr(src.key,1,1); +INSERT OVERWRITE TABLE dest_g2_n4 SELECT substr(src.key,1,1), count(DISTINCT src.key) WHERE substr(src.key,1,1) >= 5 GROUP BY substr(src.key,1,1) +INSERT OVERWRITE TABLE dest_g3_n0 SELECT substr(src.key,1,1), count(DISTINCT src.key), count(src.value) WHERE substr(src.key,1,1) < 5 GROUP BY substr(src.key,1,1); FROM src -INSERT OVERWRITE TABLE dest_g2 SELECT substr(src.key,1,1), count(DISTINCT src.key) WHERE substr(src.key,1,1) >= 5 GROUP BY substr(src.key,1,1) -INSERT OVERWRITE TABLE dest_g3 SELECT substr(src.key,1,1), count(DISTINCT src.key), count(src.value) WHERE substr(src.key,1,1) < 5 GROUP BY substr(src.key,1,1); +INSERT OVERWRITE TABLE dest_g2_n4 SELECT substr(src.key,1,1), count(DISTINCT src.key) WHERE substr(src.key,1,1) >= 5 GROUP BY substr(src.key,1,1) +INSERT OVERWRITE TABLE dest_g3_n0 SELECT substr(src.key,1,1), count(DISTINCT src.key), count(src.value) WHERE substr(src.key,1,1) < 5 GROUP BY substr(src.key,1,1); -SELECT * FROM dest_g2; -SELECT * FROM dest_g3; +SELECT * FROM dest_g2_n4; +SELECT * FROM dest_g3_n0; -DROP TABLE dest_g2; -DROP TABLE dest_g3; +DROP TABLE dest_g2_n4; +DROP TABLE dest_g3_n0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_multi_single_reducer3.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_multi_single_reducer3.q b/ql/src/test/queries/clientpositive/groupby_multi_single_reducer3.q index 224b67c..94aea35 100644 --- a/ql/src/test/queries/clientpositive/groupby_multi_single_reducer3.q +++ b/ql/src/test/queries/clientpositive/groupby_multi_single_reducer3.q @@ -3,103 +3,103 @@ -- SORT_QUERY_RESULTS -create table e1 (key string, count int); -create table e2 (key string, count int); +create table e1_n1 (key string, count int); +create table e2_n2 (key string, count int); explain from src -insert overwrite table e1 +insert overwrite table e1_n1 select key, count(*) where src.value in ('val_100', 'val_200', 'val_300') AND key in (100, 150, 200) group by key -insert overwrite table e2 +insert overwrite table e2_n2 select key, count(*) where src.value in ('val_400', 'val_500') AND key in (400, 450) group by key; from src -insert overwrite table e1 +insert overwrite table e1_n1 select key, count(*) where src.value in ('val_100', 'val_200', 'val_300') AND key in (100, 150, 200) group by key -insert overwrite table e2 +insert overwrite table e2_n2 select key, count(*) where src.value in ('val_400', 'val_500') AND key in (400, 450) group by key; -select * from e1; -select * from e2; +select * from e1_n1; +select * from e2_n2; explain from src -insert overwrite table e1 +insert overwrite table e1_n1 select value, count(*) where src.key + src.key = 200 or src.key - 100 = 100 or src.key = 300 AND VALUE IS NOT NULL group by value -insert overwrite table e2 +insert overwrite table e2_n2 select value, count(*) where src.key + src.key = 400 or src.key - 100 = 500 AND VALUE IS NOT NULL group by value; from src -insert overwrite table e1 +insert overwrite table e1_n1 select value, count(*) where src.key + src.key = 200 or src.key - 100 = 100 or src.key = 300 AND VALUE IS NOT NULL group by value -insert overwrite table e2 +insert overwrite table e2_n2 select value, count(*) where src.key + src.key = 400 or src.key - 100 = 500 AND VALUE IS NOT NULL group by value; -select * from e1; -select * from e2; +select * from e1_n1; +select * from e2_n2; set hive.optimize.ppd=false; explain from src -insert overwrite table e1 +insert overwrite table e1_n1 select key, count(*) where src.value in ('val_100', 'val_200', 'val_300') AND key in (100, 150, 200) group by key -insert overwrite table e2 +insert overwrite table e2_n2 select key, count(*) where src.value in ('val_400', 'val_500') AND key in (400, 450) group by key; from src -insert overwrite table e1 +insert overwrite table e1_n1 select key, count(*) where src.value in ('val_100', 'val_200', 'val_300') AND key in (100, 150, 200) group by key -insert overwrite table e2 +insert overwrite table e2_n2 select key, count(*) where src.value in ('val_400', 'val_500') AND key in (400, 450) group by key; -select * from e1; -select * from e2; +select * from e1_n1; +select * from e2_n2; explain from src -insert overwrite table e1 +insert overwrite table e1_n1 select value, count(*) where src.key + src.key = 200 or src.key - 100 = 100 or src.key = 300 AND VALUE IS NOT NULL group by value -insert overwrite table e2 +insert overwrite table e2_n2 select value, count(*) where src.key + src.key = 400 or src.key - 100 = 500 AND VALUE IS NOT NULL group by value; from src -insert overwrite table e1 +insert overwrite table e1_n1 select value, count(*) where src.key + src.key = 200 or src.key - 100 = 100 or src.key = 300 AND VALUE IS NOT NULL group by value -insert overwrite table e2 +insert overwrite table e2_n2 select value, count(*) where src.key + src.key = 400 or src.key - 100 = 500 AND VALUE IS NOT NULL group by value; -select * from e1; -select * from e2; +select * from e1_n1; +select * from e2_n2; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_multialias.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_multialias.q b/ql/src/test/queries/clientpositive/groupby_multialias.q index b0a0171..80a2f60 100644 --- a/ql/src/test/queries/clientpositive/groupby_multialias.q +++ b/ql/src/test/queries/clientpositive/groupby_multialias.q @@ -1,7 +1,7 @@ -create table t1 (a int); +create table t1_n150 (a int); explain -select t1.a as a1, min(t1.a) as a -from t1 -group by t1.a; +select t1_n150.a as a1, min(t1_n150.a) as a +from t1_n150 +group by t1_n150.a; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_ppd.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_ppd.q b/ql/src/test/queries/clientpositive/groupby_ppd.q index b304dfb..1801b42 100644 --- a/ql/src/test/queries/clientpositive/groupby_ppd.q +++ b/ql/src/test/queries/clientpositive/groupby_ppd.q @@ -1,5 +1,5 @@ set hive.mapred.mode=nonstrict; -- see HIVE-2382 -create table invites (id int, foo int, bar int); -explain select * from (select foo, bar from (select bar, foo from invites c union all select bar, foo from invites d) b) a group by bar, foo having bar=1; -drop table invites; \ No newline at end of file +create table invites_n0 (id int, foo int, bar int); +explain select * from (select foo, bar from (select bar, foo from invites_n0 c union all select bar, foo from invites_n0 d) b) a group by bar, foo having bar=1; +drop table invites_n0; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_ppr.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_ppr.q b/ql/src/test/queries/clientpositive/groupby_ppr.q index 8cad28f..fb5f235 100644 --- a/ql/src/test/queries/clientpositive/groupby_ppr.q +++ b/ql/src/test/queries/clientpositive/groupby_ppr.q @@ -6,19 +6,19 @@ set hive.groupby.skewindata=false; -- SORT_QUERY_RESULTS -CREATE TABLE dest1(key STRING, c1 INT, c2 STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n79(key STRING, c1 INT, c2 STRING) STORED AS TEXTFILE; EXPLAIN EXTENDED FROM srcpart src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n79 SELECT substr(src.key,1,1), count(DISTINCT substr(src.value,5)), concat(substr(src.key,1,1),sum(substr(src.value,5))) WHERE src.ds = '2008-04-08' GROUP BY substr(src.key,1,1); FROM srcpart src -INSERT OVERWRITE TABLE dest1 +INSERT OVERWRITE TABLE dest1_n79 SELECT substr(src.key,1,1), count(DISTINCT substr(src.value,5)), concat(substr(src.key,1,1),sum(substr(src.value,5))) WHERE src.ds = '2008-04-08' GROUP BY substr(src.key,1,1); -SELECT dest1.* FROM dest1; +SELECT dest1_n79.* FROM dest1_n79; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_rollup1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_rollup1.q b/ql/src/test/queries/clientpositive/groupby_rollup1.q index 94f533c..4e34be3 100644 --- a/ql/src/test/queries/clientpositive/groupby_rollup1.q +++ b/ql/src/test/queries/clientpositive/groupby_rollup1.q @@ -5,45 +5,45 @@ set hive.groupby.skewindata=false; -- SORT_QUERY_RESULTS -CREATE TABLE T1(key STRING, val STRING) STORED AS TEXTFILE; +CREATE TABLE T1_n91(key STRING, val STRING) STORED AS TEXTFILE; -LOAD DATA LOCAL INPATH '../../data/files/T1.txt' INTO TABLE T1; +LOAD DATA LOCAL INPATH '../../data/files/T1.txt' INTO TABLE T1_n91; EXPLAIN -SELECT key, val, count(1) FROM T1 GROUP BY key, val with rollup; +SELECT key, val, count(1) FROM T1_n91 GROUP BY key, val with rollup; -SELECT key, val, count(1) FROM T1 GROUP BY key, val with rollup; +SELECT key, val, count(1) FROM T1_n91 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_n91 GROUP BY key with rollup; -SELECT key, count(distinct val) FROM T1 GROUP BY key with rollup; +SELECT key, count(distinct val) FROM T1_n91 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_n91 GROUP BY key, val with rollup; -SELECT key, val, count(1) FROM T1 GROUP BY key, val with rollup; +SELECT key, val, count(1) FROM T1_n91 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_n91 GROUP BY key with rollup; -SELECT key, count(distinct val) FROM T1 GROUP BY key with rollup; +SELECT key, count(distinct val) FROM T1_n91 GROUP BY key with rollup; 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; +CREATE TABLE T2_n56(key1 STRING, key2 STRING, val INT) STORED AS TEXTFILE; +CREATE TABLE T3_n20(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 rollup -INSERT OVERWRITE TABLE T3 SELECT key, val, sum(1) group by rollup(key, val); +FROM T1_n91 +INSERT OVERWRITE TABLE T2_n56 SELECT key, val, count(1) group by key, val with rollup +INSERT OVERWRITE TABLE T3_n20 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; +FROM T1_n91 +INSERT OVERWRITE TABLE T2_n56 SELECT key, val, count(1) group by key, val with rollup +INSERT OVERWRITE TABLE T3_n20 SELECT key, val, sum(1) group by key, val with rollup; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/groupby_rollup_empty.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/groupby_rollup_empty.q b/ql/src/test/queries/clientpositive/groupby_rollup_empty.q index b64eef9..8ba8d2a 100644 --- a/ql/src/test/queries/clientpositive/groupby_rollup_empty.q +++ b/ql/src/test/queries/clientpositive/groupby_rollup_empty.q @@ -1,78 +1,78 @@ set hive.vectorized.execution.enabled=false; -drop table if exists tx1; -drop table if exists tx2; -create table tx1 (a integer,b integer,c integer); +drop table if exists tx1_n2; +drop table if exists tx2_n1; +create table tx1_n2 (a integer,b integer,c integer); select sum(c) -from tx1 +from tx1_n2 ; select sum(c), grouping(b), 'NULL,1' as expected -from tx1 +from tx1_n2 where a<0 group by a,b grouping sets ((), b, a); select sum(c), grouping(b), 'NULL,1' as expected -from tx1 +from tx1_n2 where a<0 group by rollup (b); -select '2 rows expected',sum(c) from tx1 group by rollup (a) +select '2 rows expected',sum(c) from tx1_n2 group by rollup (a) union all -select '2 rows expected',sum(c) from tx1 group by rollup (a); +select '2 rows expected',sum(c) from tx1_n2 group by rollup (a); -- non-empty table -insert into tx1 values (1,1,1); +insert into tx1_n2 values (1,1,1); select sum(c), grouping(b), 'NULL,1' as expected -from tx1 +from tx1_n2 where a<0 group by rollup (b); select sum(c), grouping(b), '1,1 and 1,0' as expected -from tx1 +from tx1_n2 group by rollup (b); set hive.vectorized.execution.enabled=true; -create table tx2 (a integer,b integer,c integer,d double,u string,bi binary) stored as orc; +create table tx2_n1 (a integer,b integer,c integer,d double,u string,bi binary) stored as orc; explain select sum(c), grouping(b), 'NULL,1' as expected -from tx2 +from tx2_n1 where a<0 group by a,b grouping sets ((), b, a); select sum(c),'NULL' as expected -from tx2; +from tx2_n1; select sum(c), max(u), 'asd', grouping(b), 'NULL,1' as expected -from tx2 +from tx2_n1 where a<0 group by a,b,d grouping sets ((), b, a, d); -select '2 rows expected',sum(c) from tx2 group by rollup (a) +select '2 rows expected',sum(c) from tx2_n1 group by rollup (a) union all -select '2 rows expected',sum(c) from tx2 group by rollup (a); +select '2 rows expected',sum(c) from tx2_n1 group by rollup (a); -insert into tx2 values +insert into tx2_n1 values (1,2,3,1.1,'x','b'), (3,2,3,1.1,'y','b'); @@ -82,6 +82,6 @@ select sum(a), 'asd', grouping(bi), 'NULL,1' as expected -from tx2 +from tx2_n1 where a=2 group by a,u,bi grouping sets ( u, (), bi);