set hive.stats.column.autogather=false;
set hive.mapred.mode=nonstrict;
set hive.explain.user=false;
set hive.compute.query.using.stats=true;
set hive.stats.autogather=true;
create table over10k_n12(
           t tinyint,
           si smallint,
           i int,
           b bigint,
           f float,
           d double,
           bo boolean,
           s string,
           ts timestamp, 
           `dec` decimal,  
           bin binary)
       row format delimited
       fields terminated by '|'
       TBLPROPERTIES ("hive.serialization.decode.binary.as.base64"="false");

load data local inpath '../../data/files/over10k' into table over10k_n12;

create table stats_tbl(
           t tinyint,
           si smallint,
           i int,
           b bigint,
           f float,
           d double,
           bo boolean,
           s string,
           ts timestamp,
           `dec` decimal,  
           bin binary);

create table stats_tbl_part(
           t tinyint,
           si smallint,
           i int,
           b bigint,
           f float,
           d double,
           bo boolean,
           s string,
           ts timestamp,
           `dec` decimal,  
           bin binary) partitioned by (dt string);


insert overwrite table stats_tbl select * from over10k_n12;

insert into table stats_tbl_part partition (dt='2010') select * from over10k_n12 where t>0 and t<30;
insert into table stats_tbl_part partition (dt='2011') select * from over10k_n12 where t>30 and t<60;
insert into table stats_tbl_part partition (dt='2012') select * from over10k_n12 where t>60;

explain 
select count(*), sum(1), sum(0.2), count(1), count(s), count(bo), count(bin), count(si), max(i), min(b) from stats_tbl;
explain
select count(*), sum(1), sum(0.2), count(1), count(s), count(bo), count(bin), count(si), max(i), min(b) from stats_tbl_part;

explain 
select count(*), '1' as one, sum(1), sum(0.2), 2 as two, count(1), count(s), 3+4.0 as three, count(bo), count(bin), count(si), max(i), min(b) from stats_tbl;
explain
select count(*), '1' as one, sum(1), sum(0.2), 2 as two, count(1), count(s), 3+4.0 as three, count(bo), count(bin), count(si), max(i), min(b) from stats_tbl_part;

analyze table stats_tbl compute statistics for columns t,si,i,b,f,d,bo,s,bin;
analyze table stats_tbl_part partition(dt='2010') compute statistics for columns t,si,i,b,f,d,bo,s,bin;
analyze table stats_tbl_part partition(dt='2011') compute statistics for columns t,si,i,b,f,d,bo,s,bin;
analyze table stats_tbl_part partition(dt='2012') compute statistics for columns t,si,i,b,f,d,bo,s,bin;

explain 
select count(*), sum(1), sum(0.2), count(1), count(s), count(bo), count(bin), count(si) from stats_tbl;
select count(*), sum(1), sum(0.2), count(1), count(s), count(bo), count(bin), count(si) from stats_tbl;
explain
select min(i), max(i), min(b), max(b), min(f), max(f), min(d), max(d) from stats_tbl;
select min(i), max(i), min(b), max(b), min(f), max(f), min(d), max(d) from stats_tbl;

explain
select min(i), '1' as one, max(i), min(b), max(b), min(f), max(f), 3+4.0 as three, min(d), max(d) from stats_tbl;
select min(i), '1' as one, max(i), min(b), max(b), min(f), max(f), 3+4.0 as three, min(d), max(d) from stats_tbl;


explain 
select count(*), sum(1), sum(0.2), count(1), count(s), count(bo), count(bin), count(si) from stats_tbl_part;
select count(*), sum(1), sum(0.2), count(1), count(s), count(bo), count(bin), count(si) from stats_tbl_part;
explain
select min(i), max(i), min(b), max(b), min(f), max(f), min(d), max(d) from stats_tbl_part;
select min(i), max(i), min(b), max(b), min(f), max(f), min(d), max(d) from stats_tbl_part;

explain
select min(i), '1' as one, max(i), min(b), max(b), min(f), max(f), 3+4.0 as three, min(d), max(d) from stats_tbl_part;
select min(i), '1' as one, max(i), min(b), max(b), min(f), max(f), 3+4.0 as three, min(d), max(d) from stats_tbl_part;

explain select count(ts) from stats_tbl_part;

explain select count('1') from stats_tbl group by '1';
select count('1') from stats_tbl group by '1';

explain select count('1') from stats_tbl_part group by '1';
select count('1') from stats_tbl_part group by '1';

drop table stats_tbl;
drop table stats_tbl_part;

set hive.compute.query.using.stats=false;