ql/src/test/queries/clientpositive/groupby_grouping_sets4.q

set hive.explain.user=false;
SET hive.vectorized.execution.enabled=false;
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_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_n143;

-- This tests that cubes and rollups work fine inside sub-queries.
EXPLAIN
SELECT * FROM
(SELECT a, b, count(*) from T1_n143 where a < 3 group by a, b with cube) subq1
join
(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_n143 where a < 3 group by cube(a, b) ) subq1
join
(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_n143 where a < 3 group by a, b with cube) subq1
join
(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;

-- 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_n143 where a < 3 group by a, b with cube) subq1
join
(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_n143 where a < 3 group by a, b with cube) subq1
join
(SELECT a, b, count(*) from T1_n143 where a < 3 group by a, b with cube) subq2
on subq1.a = subq2.a;