--! qt:dataset:src1
--! qt:dataset:src
set hive.mapred.mode=nonstrict;
set hive.optimize.reducededuplication=true;
set hive.optimize.reducededuplication.min.reducer=1;
set hive.map.aggr=true;
set hive.remove.orderby.in.subquery=false;

-- HIVE-2340 deduplicate RS followed by RS
-- hive.optimize.reducededuplication : wherther using this optimization
-- hive.optimize.reducededuplication.min.reducer : number of reducer of deduped RS should be this at least

-- RS-mGBY-RS-rGBY
explain select key, sum(key) from (select * from src distribute by key sort by key, value) Q1 group by key;
explain select key, sum(key), lower(value) from (select * from src order by key) Q1 group by key, lower(value);
explain select key, sum(key), (X + 1) from (select key, (value + 1) as X from src order by key) Q1 group by key, (X + 1);
-- mGBY-RS-rGBY-RS
explain select key, sum(key) as value from src group by key order by key;
-- RS-JOIN-mGBY-RS-rGBY
explain select src.key, sum(src.key) FROM src JOIN src1 ON src.key = src1.key group by src.key, src.value;
-- RS-JOIN-RS
explain select src.key, src.value FROM src JOIN src1 ON src.key = src1.key order by src.key, src.value;
-- mGBY-RS-rGBY-mGBY-RS-rGBY
explain from (select key, value from src group by key, value) s select s.key group by s.key;
explain select key, count(distinct value) from (select key, value from src group by key, value) t group by key;

select key, sum(key) from (select * from src distribute by key sort by key, value) Q1 group by key;
select key, sum(key), lower(value) from (select * from src order by key) Q1 group by key, lower(value);
select key, sum(key), (X + 1) from (select key, (value + 1) as X from src order by key) Q1 group by key, (X + 1);
select key, sum(key) as value from src group by key order by key;
select src.key, sum(src.key) FROM src JOIN src1 ON src.key = src1.key group by src.key, src.value;
select src.key, src.value FROM src JOIN src1 ON src.key = src1.key order by src.key, src.value;
from (select key, value from src group by key, value) s select s.key group by s.key;
select key, count(distinct value) from (select key, value from src group by key, value) t group by key;

set hive.map.aggr=false;

-- RS-RS-GBY
explain select key, sum(key) from (select * from src distribute by key sort by key, value) Q1 group by key;
explain select key, sum(key), lower(value) from (select * from src order by key) Q1 group by key, lower(value);
explain select key, sum(key), (X + 1) from (select key, (value + 1) as X from src order by key) Q1 group by key, (X + 1);
-- RS-GBY-RS
explain select key, sum(key) as value from src group by key order by key;
-- RS-JOIN-RS-GBY
explain select src.key, sum(src.key) FROM src JOIN src1 ON src.key = src1.key group by src.key, src.value;
-- RS-JOIN-RS
explain select src.key, src.value FROM src JOIN src1 ON src.key = src1.key order by src.key, src.value;
-- RS-GBY-RS-GBY
explain from (select key, value from src group by key, value) s select s.key group by s.key;
explain select key, count(distinct value) from (select key, value from src group by key, value) t group by key;

select key, sum(key) from (select * from src distribute by key sort by key, value) Q1 group by key;
select key, sum(key), lower(value) from (select * from src order by key) Q1 group by key, lower(value);
select key, sum(key), (X + 1) from (select key, (value + 1) as X from src order by key) Q1 group by key, (X + 1);
select key, sum(key) as value from src group by key order by key;
select src.key, sum(src.key) FROM src JOIN src1 ON src.key = src1.key group by src.key, src.value;
select src.key, src.value FROM src JOIN src1 ON src.key = src1.key order by src.key, src.value;
from (select key, value from src group by key, value) s select s.key group by s.key;
select key, count(distinct value) from (select key, value from src group by key, value) t group by key;