1# 2# Tests for window functions over big datasets. 3# "Big" here is "big enough so that filesort result doesn't fit in a 4# memory buffer". 5# 6# 7 8create table t0 (a int); 9insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 10 11create table t1(a int); 12insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; 13 14create table t10 (a int, b int, c int); 15insert into t10 16select 17 A.a + 1000*B.a, 18 A.a + 1000*B.a, 19 A.a + 1000*B.a 20from t1 A, t0 B 21order by A.a+1000*B.a; 22 23--echo ################################################################# 24--echo ## Try a basic example 25flush status; 26create table t21 as 27select 28 sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B 29from 30 t10; 31select variable_name, 32 case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end 33from information_schema.session_status 34where variable_name like 'Sort_merge_passes'; 35 36set sort_buffer_size=1024; 37flush status; 38create table t22 as 39select 40 sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B 41from 42 t10; 43select variable_name, 44 case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end 45from information_schema.session_status 46where variable_name like 'Sort_merge_passes'; 47 48let $diff_tables= t21, t22; 49source include/diff_tables.inc; 50drop table t21, t22; 51 52--echo ################################################################# 53--echo # Try many cursors 54set sort_buffer_size=default; 55flush status; 56create table t21 as 57select 58 sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B1, 59 sum(b) over (order by a rows between 5 preceding and 5 following) as SUM_B2, 60 sum(b) over (order by a rows between 20 preceding and 20 following) as SUM_B3 61from 62 t10; 63select variable_name, 64 case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end 65from information_schema.session_status 66where variable_name like 'Sort_merge_passes'; 67 68set sort_buffer_size=1024; 69flush status; 70create table t22 as 71select 72 sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B1, 73 sum(b) over (order by a rows between 5 preceding and 5 following) as SUM_B2, 74 sum(b) over (order by a rows between 20 preceding and 20 following) as SUM_B3 75from 76 t10; 77select variable_name, 78 case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end 79from information_schema.session_status 80where variable_name like 'Sort_merge_passes'; 81 82let $diff_tables= t21, t22; 83source include/diff_tables.inc; 84drop table t21, t22; 85 86--echo ################################################################# 87--echo # Try having cursors pointing at different IO_CACHE pages 88--echo # in the IO_CACHE 89set sort_buffer_size=default; 90flush status; 91create table t21 as 92select 93 a, 94 sum(b) over (order by a range between 5000 preceding and 5000 following) as SUM_B1 95from 96 t10; 97 98select variable_name, 99 case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end 100from information_schema.session_status 101where variable_name like 'Sort_merge_passes'; 102 103set sort_buffer_size=1024; 104flush status; 105create table t22 as 106select 107 a, 108 sum(b) over (order by a range between 5000 preceding and 5000 following) as SUM_B1 109from 110 t10; 111select variable_name, 112 case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end 113from information_schema.session_status 114where variable_name like 'Sort_merge_passes'; 115 116let $diff_tables= t21, t22; 117source include/diff_tables.inc; 118drop table t21, t22; 119--echo ################################################################# 120 121drop table t10; 122drop table t0,t1; 123 124