summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/win_big.test
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/win_big.test
parentInitial commit. (diff)
downloadmariadb-upstream.tar.xz
mariadb-upstream.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r--mysql-test/main/win_big.test140
1 files changed, 140 insertions, 0 deletions
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;
+