--! qt:dataset:cbo_t1
set hive.compute.query.using.stats=false;
set hive.mapred.mode=nonstrict;
set hive.cbo.enable=true;
set hive.exec.check.crossproducts=false;

set hive.stats.fetch.column.stats=true;
set hive.auto.convert.join=false;

-- SORT_QUERY_RESULTS

-- 8. Test UDF/UDAF
select count(*), count(c_int), sum(c_int), avg(c_int), max(c_int), min(c_int) from cbo_t1;
select count(*), count(c_int) as a, sum(c_int), avg(c_int), max(c_int), min(c_int), case c_int when 0  then 1 when 1 then 2 else 3 end, sum(case c_int when 0  then 1 when 1 then 2 else 3 end) from cbo_t1 group by c_int order by a;
select * from (select count(*) as a, count(distinct c_int) as b, sum(c_int) as c, avg(c_int) as d, max(c_int) as e, min(c_int) as f from cbo_t1) cbo_t1;
select * from (select count(*) as a, count(distinct c_int) as b, sum(c_int) as c, avg(c_int) as d, max(c_int) as e, min(c_int) as f, case c_int when 0  then 1 when 1 then 2 else 3 end as g, sum(case c_int when 0  then 1 when 1 then 2 else 3 end) as h from cbo_t1 group by c_int) cbo_t1 order by a;
select f,a,e,b from (select count(*) as a, count(c_int) as b, sum(c_int) as c, avg(c_int) as d, max(c_int) as e, min(c_int) as f from cbo_t1) cbo_t1;
select f,a,e,b from (select count(*) as a, count(distinct c_int) as b, sum(distinct c_int) as c, avg(distinct c_int) as d, max(distinct c_int) as e, min(distinct c_int) as f from cbo_t1) cbo_t1;
select key,count(c_int) as a, avg(c_float) from cbo_t1 group by key order by a;
select count(distinct c_int) as a, avg(c_float) from cbo_t1 group by c_float order by a;
select count(distinct c_int) as a, avg(c_float) from cbo_t1 group by c_int order by a;
select count(distinct c_int) as a, avg(c_float) from cbo_t1 group by c_float, c_int order by a;