-- disable-warnings

delete from flexviews.mview;

create database if not exists test;
use test;

drop table if exists test.fact, test.dim;

create table test.fact ( c1 int auto_increment primary key,
c2 int default null,
c3 int unsigned default null,
dim_id int not null
) engine=innodb;

create table test.dim (id int auto_increment primary key, 
c1 int,
c2 char(32)
) engine=innodb;

set @id:=0;
insert into test.dim values(null, @id:= @id + 1, md5(@id));
insert into test.dim values(null, @id:= @id + 1, md5(@id));
insert into test.dim values(null, @id:= @id + 1, md5(@id));
insert into test.dim values(null, @id:= @id + 1, md5(@id));
insert into test.dim values(null, @id:= @id + 1, md5(@id));
insert into test.dim values(null, @id:= @id + 1, md5(@id));
insert into test.dim values(null, @id:= @id + 1, md5(@id));
insert into test.dim values(null, @id:= @id + 1, md5(@id));
insert into test.dim values(null, @id:= @id + 1, md5(@id));
insert into test.dim values(null, null,null);
-- insert into test.dim values(null, @id:= @id + 1, md5(@id));
-- insert into test.dim values (null,null,null);
 
insert into test.fact values (null,1,0,1);
insert into test.fact values (null,2,0,2);
insert into test.fact values (null,3,0,3);
insert into test.fact values (null,4,0,4);
insert into test.fact values (null,5,0,5);
insert into test.fact values (null,6,0,6);
insert into test.fact values (null,7,0,7);
insert into test.fact values (null,8,0,8);
insert into test.fact values (null,9,0,9);
insert into test.fact values (null,10,0,10);

#insert into test.fact select null, c1, c1+c1,dim_id from test.fact; 
#insert into test.fact select null, c1, c1+c1,dim_id from test.fact; 
#insert into test.fact select null, c1, c1+c1,dim_id from test.fact; 
#insert into test.fact select null, c1, c1+c1,dim_id from test.fact; 
#insert into test.fact select null, c1, c1+c1,dim_id from test.fact; 
#insert into test.fact select null, c1, c1+c1,dim_id from test.fact; 
#insert into test.fact select null, c1, c1+c1,dim_id from test.fact; 
#insert into test.fact select null, c1, c1+c1,dim_id from test.fact; 
#insert into test.fact select null, c1, c1+c1,dim_id from test.fact; 
#insert into test.fact select null, c1, c1+c1,dim_id from test.fact; 
#insert into test.fact select null, c1, c1+c1,dim_id from test.fact; 

call flexviews.create_mvlog('test','dim');
call flexviews.create_mvlog('test','fact');

call flexviews.create('test','test1','INCREMENTAL');
set @mvid := last_insert_id();

call flexviews.add_table(@mvid,'test','fact','f',null);
call flexviews.add_table(@mvid,'test','dim','d','on f.dim_id = d.id');
call flexviews.add_expr(@mvid,'GROUP','d.c2','grp_c2');
-- call flexviews.add_expr(@mvid,'GROUP','f.dim_id','grp_dim_id');

call flexviews.add_expr(@mvid,'bit_and','f.c1','bit_and_c1');
call flexviews.add_expr(@mvid,'bit_or','f.c1','bit_or_c1');
call flexviews.add_expr(@mvid,'bit_xor','f.c1','bit_xor_c1');
call flexviews.add_expr(@mvid,'count_distinct','f.c1','count_dist_c1');
call flexviews.add_expr(@mvid,'min','f.c1','min_c1');
call flexviews.add_expr(@mvid,'max','f.c1','max_c1');
call flexviews.add_expr(@mvid,'avg','f.c1','avg_c1');
call flexviews.add_expr(@mvid,'stddev_pop','f.c1','std_pop_c1');
call flexviews.add_expr(@mvid,'stddev_samp','f.c1','std_samp_c1');
call flexviews.add_expr(@mvid,'var_pop','f.c1','var_pop_c1');
call flexviews.add_expr(@mvid,'var_samp','f.c1','var_samp_c1');

call flexviews.enable(@mvid);

select @mvid;

select flexviews.get_sql(@mvid);

select * from test.test1;

SELECT NULL as mview$pk, (d.c2) as grp_c2, BIT_AND(f.c1) as bit_and_c1, BIT_OR(f.c1) as bit_or_c1, BIT_XOR(f.c1) as bit_xor_c1, COUNT(DISTINCT (f.c1)) as count_dist_c1, MIN(f.c1) as min_c1, MAX(f.c1) as max_c1, avg(f.c1) as avg_c1, STDDEV_POP(f.c1) as std_pop_c1, STDDEV_SAMP(f.c1) as std_samp_c1, VAR_POP(f.c1) as var_pop_c1, VAR_SAMP(f.c1) as var_samp_c1, COUNT(*) as CNT FROM  test.fact as f JOIN test.dim as d on f.dim_id = d.id GROUP BY (d.c2);

insert into fact select null, c2+(select count(*) from test.fact), c2+c1, c1 % 10 from fact;
insert into fact select null, c2+(select count(*) from test.fact), c2+c1, c1 % 10 from fact;
insert into fact select null, c2+(select count(*) from test.fact), 1, 1 from fact;

select sleep(1);

call flexviews.refresh(@mvid, 'BOTH' , NULL);


select * from test.test1;

SELECT NULL as mview$pk, (d.c2) as grp_c2, BIT_AND(f.c1) as bit_and_c1, BIT_OR(f.c1) as bit_or_c1, BIT_XOR(f.c1) as bit_xor_c1, COUNT(DISTINCT (f.c1)) as count_dist_c1, MIN(f.c1) as min_c1, MAX(f.c1) as max_c1, avg(f.c1) as avg_c1, STDDEV_POP(f.c1) as std_pop_c1, STDDEV_SAMP(f.c1) as std_samp_c1, VAR_POP(f.c1) as var_pop_c1, VAR_SAMP(f.c1) as var_samp_c1, COUNT(*) as CNT FROM  test.fact as f JOIN test.dim as d on f.dim_id = d.id GROUP BY (d.c2);
#SELECT NULL as mview$pk, (d.c2) as grp_c2, BIT_AND(f.c1) as bit_and_c1, BIT_OR(f.c1) as bit_or_c1, BIT_XOR(f.c1) as bit_xor_c1, COUNT(DISTINCT (f.c1)) as count_dist_c1, MIN(f.c1) as min_c1, MAX(f.c1) as max_c1, STDDEV_POP(f.c1) as std_pop_c1, STDDEV_SAMP(f.c1) as std_samp_c1, VAR_POP(f.c1) as var_pop_c1, VAR_SAMP(f.c1) as var_samp_c1, COUNT(*) as CNT FROM  test.fact as f JOIN test.dim as d on f.dim_id = d.id GROUP BY (d.c2);

delete from fact limit 40;

select sleep(1);

call flexviews.refresh(@mvid, 'BOTH' , NULL);

select * from test.test1;

SELECT NULL as mview$pk, (d.c2) as grp_c2, BIT_AND(f.c1) as bit_and_c1, BIT_OR(f.c1) as bit_or_c1, BIT_XOR(f.c1) as bit_xor_c1, COUNT(DISTINCT (f.c1)) as count_dist_c1, MIN(f.c1) as min_c1, MAX(f.c1) as max_c1, avg(f.c1) as avg_c1, STDDEV_POP(f.c1) as std_pop_c1, STDDEV_SAMP(f.c1) as std_samp_c1, VAR_POP(f.c1) as var_pop_c1, VAR_SAMP(f.c1) as var_samp_c1, COUNT(*) as CNT FROM  test.fact as f JOIN test.dim as d on f.dim_id = d.id GROUP BY (d.c2);
#SELECT NULL as mview$pk, (d.c2) as grp_c2, BIT_AND(f.c1) as bit_and_c1, BIT_OR(f.c1) as bit_or_c1, BIT_XOR(f.c1) as bit_xor_c1, COUNT(DISTINCT (f.c1)) as count_dist_c1, MIN(f.c1) as min_c1, MAX(f.c1) as max_c1, STDDEV_POP(f.c1) as std_pop_c1, STDDEV_SAMP(f.c1) as std_samp_c1, VAR_POP(f.c1) as var_pop_c1, VAR_SAMP(f.c1) as var_samp_c1, COUNT(*) as CNT FROM  test.fact as f JOIN test.dim as d on f.dim_id = d.id GROUP BY (d.c2);

call flexviews.disable(@mvid);
delete from flexviews.mview;

call flexviews.create('test','test1','INCREMENTAL');
set @mvid := last_insert_id();

call flexviews.add_table(@mvid,'test','fact','f',null);
call flexviews.add_expr(@mvid,'COUNT','*','fact_count');

call flexviews.enable(@mvid);

select fact_count from test.test1;
select count(*) fact_count from test.fact;

delete from test.fact;

select sleep(1);

call flexviews.refresh(@mvid, 'BOTH' , NULL);

select fact_count from test.test1;
select count(*) fact_count from test.fact;

insert into test.fact values (null , 1, 1, 1);

select sleep(1);

call flexviews.refresh(@mvid, 'BOTH' , NULL);

select fact_count from test.test1;
select count(*) fact_count from test.fact;

