MDEV-9736: Window functions: multiple cursors to read filesort result

Add support for having multiple IO_CACHEs with type=READ_CACHE to share
the file they are reading from.
Each IO_CACHE keeps its own in-memory buffer. When doing a read or seek
operation on the file, it notifies other IO_CACHEs that the file position
has been changed.

Make Rowid_seq_cursor use cloned IO_CACHE when reading filesort result.

2 files changed, 197 insertions, 0 deletions

diff --git a/mysql-test/r/win_big.result b/mysql-test/r/win_big.result
new file mode 100644
index 00000000000..7ea044e702c
--- /dev/null
+++ b/mysql-test/r/win_big.result
@@ -0,0 +1,93 @@
+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;
+#################################################################
+## 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;
+show status like 'Sort_merge_passes';
+Variable_name	Value
+Sort_merge_passes	0
+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;
+show status like 'Sort_merge_passes';
+Variable_name	Value
+Sort_merge_passes	35
+include/diff_tables.inc [t21, t22]
+drop table t21, t22;
+#################################################################
+# 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;
+show status like 'Sort_merge_passes';
+Variable_name	Value
+Sort_merge_passes	0
+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;
+show status like 'Sort_merge_passes';
+Variable_name	Value
+Sort_merge_passes	35
+include/diff_tables.inc [t21, t22]
+drop table t21, t22;
+#################################################################
+# Try having cursors pointing at different IO_CACHE pages
+# 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;
+show status like 'Sort_merge_passes';
+Variable_name	Value
+Sort_merge_passes	0
+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;
+show status like 'Sort_merge_passes';
+Variable_name	Value
+Sort_merge_passes	35
+include/diff_tables.inc [t21, t22]
+drop table t21, t22;
+#################################################################
+drop table t10;
+drop table t0,t1;
diff --git a/mysql-test/t/win_big.test b/mysql-test/t/win_big.test
new file mode 100644
index 00000000000..a0398126eb3
--- /dev/null
+++ b/mysql-test/t/win_big.test
@@ -0,0 +1,104 @@
+#
+# 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;
+show status 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;
+show status 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;
+show status 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;
+show status 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;
+show status 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;
+show status 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;
+