From 3f619478f796eddbba6e39502fe941b285dd97b1 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 20:00:34 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- mysql-test/main/win_big.test | 140 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 140 insertions(+) create mode 100644 mysql-test/main/win_big.test (limited to 'mysql-test/main/win_big.test') diff --git a/mysql-test/main/win_big.test b/mysql-test/main/win_big.test new file mode 100644 index 00000000..e53eaa70 --- /dev/null +++ b/mysql-test/main/win_big.test @@ -0,0 +1,140 @@ +# +# 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; +#enable after fix MDEV-27871 +--disable_view_protocol +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'; +--enable_view_protocol + +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; +#enable after fix MDEV-27871 +--disable_view_protocol +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'; +--enable_view_protocol + +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; +#enable after fix MDEV-27871 +--disable_view_protocol +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'; +--enable_view_protocol + +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; +#enable after fix MDEV-27871 +--disable_view_protocol +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'; +--enable_view_protocol + +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; +#enable after fix MDEV-27871 +--disable_view_protocol +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'; +--enable_view_protocol + +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; +#enable after fix MDEV-27871 +--disable_view_protocol +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'; +--enable_view_protocol + +let $diff_tables= t21, t22; +source include/diff_tables.inc; +drop table t21, t22; +--echo ################################################################# + +drop table t10; +drop table t0,t1; + -- cgit v1.2.3