set hive.mapred.mode=nonstrict;

drop table employee;

create table employee (department_id int, gender varchar(10), education_level int);

explain cbo
select count(distinct 0), count(distinct null) from employee;

select count(distinct 0), count(distinct null) from employee;

insert into employee values (1, 'M', 1),(1, 'M', 1),(2, 'F', 1),(1, 'F', 3),(1, 'M', 2),(4, 'M', 1),(2, 'F', 1),(2, 'F', 3),(3, 'M', 2),(null, 'M', 1),(null, null, 1),(null, null, null);

select count(distinct 0), count(distinct null) from employee;

explain select count(distinct department_id), count(distinct gender), count(distinct education_level) from employee;

select count(distinct gender), count(distinct department_id), count(distinct education_level) from employee;

select count(distinct gender), count(distinct department_id), count(distinct gender), count(distinct education_level) from employee;

select count(distinct department_id), count(distinct gender), count(distinct education_level) from employee;

select count(distinct department_id), count(distinct gender), count(distinct education_level), count(distinct education_level) from employee;

select count(distinct department_id), count(distinct gender), count(distinct education_level), 
count(distinct education_level, department_id) from employee;

select count(distinct gender), count(distinct department_id), count(distinct gender), count(distinct education_level),
count(distinct education_level, department_id), count(distinct department_id, education_level) from employee;

explain select count(distinct gender), count(distinct department_id), count(distinct gender), count(distinct education_level),
count(distinct education_level, department_id), count(distinct department_id, education_level), count(distinct department_id, education_level, gender) from employee;

select count(distinct gender), count(distinct department_id), count(distinct gender), count(distinct education_level),
count(distinct education_level, department_id), count(distinct department_id, education_level), count(distinct department_id, education_level, gender) from employee;

select 
count(case when i=3 and department_id is not null then 1 else null end) as c0, 
count(case when i=5 and gender is not null then 1 else null end) as c1, 
count(case when i=6 and education_level is not null then 1 else null end) as c2 from 
(select grouping__id as i, department_id, gender, 
education_level from employee group by department_id, gender, education_level grouping sets 
(department_id, gender, education_level))subq;

select grouping__id as i, department_id, gender, education_level from employee 
group by department_id, gender, education_level grouping sets 
(department_id, gender, education_level, (education_level, department_id));