--! qt:dataset:src1
--! qt:dataset:src
set hive.mapred.mode=nonstrict;
-- SORT_QUERY_RESULTS

-- HIVE-4392, column aliases from expressionRR (GBY, etc.) are not valid name for table

-- group by


explain
create table summary as select *, key + 1, concat(value, value) from src limit 20;
create table summary as select *, key + 1, concat(value, value) from src limit 20;
describe formatted summary;
select * from summary;

-- window functions
explain
create table x4 as select *, rank() over(partition by key order by value) as rr from src1;
create table x4 as select *, rank() over(partition by key order by value) as rr from src1;
describe formatted x4;
select * from x4;

explain
create table x5 as select *, lead(key,1) over(partition by key order by value) as lead1 from src limit 20;
create table x5 as select *, lead(key,1) over(partition by key order by value) as lead1 from src limit 20;
describe formatted x5;
select * from x5;

-- sub queries
explain
create table x6 as select * from (select *, key + 1 from src1) a;
create table x6 as select * from (select *, key + 1 from src1) a;
describe formatted x6;
select * from x6;

explain
create table x7 as select * from (select *, count(value) from src group by key, value) a;
create table x7 as select * from (select *, count(value) from src group by key, value) a;
describe formatted x7;
select * from x7;

explain
create table x8 as select * from (select *, count(value) from src group by key, value having key < 9) a;
create table x8 as select * from (select *, count(value) from src group by key, value having key < 9) a;
describe formatted x8;
select * from x8;

explain
create table x9 as select * from (select max(value),key from src group by key having key < 9 AND max(value) IS NOT NULL) a;
create table x9 as select * from (select max(value),key from src group by key having key < 9 AND max(value) IS NOT NULL) a;
describe formatted x9;
select * from x9;