--! qt:dataset:src
set hive.mapred.mode=nonstrict;
set hive.explain.user=false;
set hive.auto.convert.join.noconditionaltask.size=60000000;
set hive.log.trace.id=mrrTest;

-- simple query with multiple reduce stages
-- SORT_QUERY_RESULTS

EXPLAIN SELECT key, count(value) as cnt FROM src GROUP BY key ORDER BY cnt;
SELECT key, count(value) as cnt FROM src GROUP BY key ORDER BY cnt;

set hive.auto.convert.join=false;
-- join query with multiple reduce stages;
EXPLAIN SELECT s2.key, count(distinct s2.value) as cnt FROM src s1 join src s2 on (s1.key = s2.key) GROUP BY s2.key ORDER BY cnt,s2.key;
SELECT s2.key, count(distinct s2.value) as cnt FROM src s1 join src s2 on (s1.key = s2.key) GROUP BY s2.key ORDER BY cnt,s2.key;

set hive.auto.convert.join=true;
-- same query with broadcast join
EXPLAIN SELECT s2.key, count(distinct s2.value) as cnt FROM src s1 join src s2 on (s1.key = s2.key) GROUP BY s2.key ORDER BY cnt,s2.key;
SELECT s2.key, count(distinct s2.value) as cnt FROM src s1 join src s2 on (s1.key = s2.key) GROUP BY s2.key ORDER BY cnt,s2.key;

set hive.auto.convert.join=false;
-- query with multiple branches in the task dag
EXPLAIN
SELECT * 
FROM
  (SELECT key, count(value) as cnt 
  FROM src GROUP BY key ORDER BY cnt) s1
  JOIN
  (SELECT key, count(value) as cnt 
  FROM src GROUP BY key ORDER BY cnt) s2
  JOIN
  (SELECT key, count(value) as cnt 
  FROM src GROUP BY key ORDER BY cnt) s3
  ON (s1.key = s2.key and s1.key = s3.key)
WHERE
  s1.cnt > 1
ORDER BY s1.key;

SELECT * 
FROM
  (SELECT key, count(value) as cnt 
  FROM src GROUP BY key ORDER BY cnt) s1
  JOIN
  (SELECT key, count(value) as cnt 
  FROM src GROUP BY key ORDER BY cnt) s2
  JOIN
  (SELECT key, count(value) as cnt 
  FROM src GROUP BY key ORDER BY cnt) s3
  ON (s1.key = s2.key and s1.key = s3.key)
WHERE
  s1.cnt > 1
ORDER BY s1.key;

set hive.log.trace.id=Test2;
set hive.auto.convert.join=true;
set hive.stats.fetch.column.stats=false;
-- query with broadcast join in the reduce stage
EXPLAIN
SELECT *
FROM
  (SELECT key, count(value) as cnt FROM src GROUP BY key) s1
  JOIN src ON (s1.key = src.key) order by s1.key;

SELECT *
FROM
  (SELECT key, count(value) as cnt FROM src GROUP BY key) s1
  JOIN src ON (s1.key = src.key) order by s1.key;