--
-- ### Advanced view aggregation checks
--

drop if exists view v1;
drop if exists view v2;
drop if exists view v3;

drop if exists table t1;
drop if exists table t2;

create table t1 ( a long, b string(20));
insert into t1 values ( 1, 'XXX');
insert into t1 values ( 2, 'YYY');
insert into t1 values ( 3, 'XXX');

create table t2 ( a int, b int, c string(20));
insert into t2 values ( 1, 22, 'XXX');
insert into t2 values ( 2, 44, 'YYY');
insert into t2 values ( 3, 66, 'XXX');

select count(*) from t1 tx;

select t.a as a,
( select count(a) from t2 tx) as b from t1 t;

create view v1 as select t.a as a,
       ( select count(*) from t2 tx) as b from t1 t;

select b from v1;

select sum(a) + sum(b) as mergesum, c from t2 group by c;

select sum(a), sum(b), count(*) as c from t2;

create view v3 as select sum(a) + sum(b) as ms, c from t2 group by c;

desc view v3;

select ms from v3;

select case when ms = 92 then 'XXX' else ms end from v3;
