#
# Tests for window functions over big datasets.
#  "Big" here is "big enough so that filesort result doesn't fit in a 
#   memory  buffer".
#
#

create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1(a int);
insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;

create table t10 (a int, b int, c int);
insert into t10 
select 
  A.a + 1000*B.a,
  A.a + 1000*B.a,
  A.a + 1000*B.a
from t1 A, t0 B
order by A.a+1000*B.a;

--echo #################################################################
--echo ## Try a basic example
flush status;
create table t21 as
select 
  sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B
from
  t10;
select variable_name,
       case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end
from information_schema.session_status
where variable_name like 'Sort_merge_passes';

set sort_buffer_size=1024;
flush status;
create table t22 as
select 
  sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B
from
  t10;
select variable_name,
       case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end
from information_schema.session_status
where variable_name like 'Sort_merge_passes';

let $diff_tables= t21, t22;
source include/diff_tables.inc;
drop table t21, t22;

--echo #################################################################
--echo # Try many cursors
set sort_buffer_size=default;
flush status;
create table t21 as
select 
  sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B1,
  sum(b) over (order by a rows between 5 preceding and 5 following) as SUM_B2,
  sum(b) over (order by a rows between 20 preceding and 20 following) as SUM_B3
from
  t10;
select variable_name,
       case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end
from information_schema.session_status
where variable_name like 'Sort_merge_passes';

set sort_buffer_size=1024;
flush status;
create table t22 as
select 
  sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B1,
  sum(b) over (order by a rows between 5 preceding and 5 following) as SUM_B2,
  sum(b) over (order by a rows between 20 preceding and 20 following) as SUM_B3
from
  t10;
select variable_name,
       case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end
from information_schema.session_status
where variable_name like 'Sort_merge_passes';

let $diff_tables= t21, t22;
source include/diff_tables.inc;
drop table t21, t22;

--echo #################################################################
--echo # Try having cursors pointing at different IO_CACHE pages
--echo # in the IO_CACHE
set sort_buffer_size=default;
flush status;
create table t21 as
select 
  a,
  sum(b) over (order by a range between 5000 preceding and 5000 following) as SUM_B1
from
  t10;

select variable_name,
       case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end
from information_schema.session_status
where variable_name like 'Sort_merge_passes';

set sort_buffer_size=1024;
flush status;
create table t22 as
select
  a,
  sum(b) over (order by a range between 5000 preceding and 5000 following) as SUM_B1
from
  t10;
select variable_name,
       case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end
from information_schema.session_status
where variable_name like 'Sort_merge_passes';

let $diff_tables= t21, t22;
source include/diff_tables.inc;
drop table t21, t22;
--echo #################################################################

drop table t10;
drop table t0,t1;