Merge 10.3 into 10.4

33 files changed, 4918 insertions, 350 deletions

diff --git a/mysql-test/suite/encryption/r/tempfiles_encrypted.result b/mysql-test/suite/encryption/r/tempfiles_encrypted.result
index d08cb33e0fc..14c754a76bc 100644
--- a/mysql-test/suite/encryption/r/tempfiles_encrypted.result
+++ b/mysql-test/suite/encryption/r/tempfiles_encrypted.result
@@ -4,6 +4,3962 @@
 select @@encrypt_tmp_files;
 @@encrypt_tmp_files
 1
+drop table if exists t1,t2;
+drop view if exists v1;
+# ########################################################################
+# # Parser tests
+# ########################################################################
+#
+#  Check what happens when one attempts to use window function without OVER clause
+create table t1 (a int, b int);
+insert into t1 values (1,1),(2,2);
+select row_number() from t1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from t1' at line 1
+select rank() from t1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from t1' at line 1
+# Attempt to use window function in the WHERE clause
+select * from t1 where 1=rank() over (order by a);
+ERROR HY000: Window function is allowed only in SELECT list and ORDER BY clause
+select * from t1 where 1>row_number() over (partition by b order by a);
+ERROR HY000: Window function is allowed only in SELECT list and ORDER BY clause
+drop table t1;
+# ########################################################################
+# # Functionality tests
+# ########################################################################
+#
+#  Check if ROW_NUMBER() works in basic cases
+create table t1(a int, b int, x char(32));
+insert into t1 values (2, 10, 'xx');
+insert into t1 values (2, 10, 'zz');
+insert into t1 values (2, 20, 'yy');
+insert into t1 values (3, 10, 'xxx');
+insert into t1 values (3, 20, 'vvv');
+select a, row_number() over (partition by a order by b) from t1;
+a	row_number() over (partition by a order by b)
+2	1
+2	2
+2	3
+3	1
+3	2
+select a, b, x, row_number() over (partition by a order by x) from t1;
+a	b	x	row_number() over (partition by a order by x)
+2	10	xx	1
+2	20	yy	2
+2	10	zz	3
+3	20	vvv	1
+3	10	xxx	2
+drop table t1;
+create table t1 (pk int primary key, a int, b int);
+insert into t1 values
+(1, 10, 22),
+(2, 11, 21),
+(3, 12, 20),
+(4, 13, 19),
+(5, 14, 18);
+select
+pk, a, b,
+row_number() over (order by a),
+row_number() over (order by b)
+from t1
+order by b;
+pk	a	b	row_number() over (order by a)	row_number() over (order by b)
+5	14	18	5	1
+4	13	19	4	2
+3	12	20	3	3
+2	11	21	2	4
+1	10	22	1	5
+drop table t1;
+#
+# Try RANK() function
+#
+create table t2 (
+pk int primary key,
+a int
+);
+insert into t2 values
+( 1 , 0),
+( 2 , 0),
+( 3 , 1),
+( 4 , 1),
+( 8 , 2),
+( 5 , 2),
+( 6 , 2),
+( 7 , 2),
+( 9 , 4),
+(10 , 4);
+select pk, a, rank() over (order by a) from t2;
+pk	a	rank() over (order by a)
+1	0	1
+10	4	9
+2	0	1
+3	1	3
+4	1	3
+5	2	5
+6	2	5
+7	2	5
+8	2	5
+9	4	9
+select pk, a, rank() over (order by a desc) from t2;
+pk	a	rank() over (order by a desc)
+1	0	9
+10	4	1
+2	0	9
+3	1	7
+4	1	7
+5	2	3
+6	2	3
+7	2	3
+8	2	3
+9	4	1
+drop table t2;
+#
+# Try Aggregates as window functions. With frames.
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (pk int, c int);
+insert into t1 select a+1,1 from t0;
+update t1 set c=2 where pk not in (1,2,3,4);
+select * from t1;
+pk	c
+1	1
+2	1
+3	1
+4	1
+5	2
+6	2
+7	2
+8	2
+9	2
+10	2
+select
+pk, c,
+count(*) over (partition by c order by pk
+rows between 2 preceding and 2 following) as CNT
+from t1;
+pk	c	CNT
+1	1	3
+2	1	4
+3	1	4
+4	1	3
+5	2	3
+6	2	4
+7	2	5
+8	2	5
+9	2	4
+10	2	3
+select
+pk, c,
+count(*) over (partition by c order by pk
+rows between 1 preceding and 2 following) as CNT
+from t1;
+pk	c	CNT
+1	1	3
+2	1	4
+3	1	3
+4	1	2
+5	2	3
+6	2	4
+7	2	4
+8	2	4
+9	2	3
+10	2	2
+select
+pk, c,
+count(*) over (partition by c order by pk
+rows between 2 preceding and current row) as CNT
+from t1;
+pk	c	CNT
+1	1	1
+2	1	2
+3	1	3
+4	1	3
+5	2	1
+6	2	2
+7	2	3
+8	2	3
+9	2	3
+10	2	3
+select
+pk,c,
+count(*) over (partition by c order by pk rows
+between 1 following and 2 following) as CNT
+from t1;
+pk	c	CNT
+1	1	2
+2	1	2
+3	1	1
+4	1	0
+5	2	2
+6	2	2
+7	2	2
+8	2	2
+9	2	1
+10	2	0
+select
+pk,c,
+count(*) over (partition by c order by pk rows
+between 2 preceding and 1 preceding) as CNT
+from t1;
+pk	c	CNT
+1	1	0
+2	1	1
+3	1	2
+4	1	2
+5	2	0
+6	2	1
+7	2	2
+8	2	2
+9	2	2
+10	2	2
+select
+pk, c,
+count(*) over (partition by c order by pk
+rows between current row and 1 following) as CNT
+from t1;
+pk	c	CNT
+1	1	2
+2	1	2
+3	1	2
+4	1	1
+5	2	2
+6	2	2
+7	2	2
+8	2	2
+9	2	2
+10	2	1
+# Check ORDER BY DESC
+select
+pk, c,
+count(*) over (partition by c order by pk desc
+rows between 2 preceding and 2 following) as CNT
+from t1;
+pk	c	CNT
+4	1	3
+3	1	4
+2	1	4
+1	1	3
+10	2	3
+9	2	4
+8	2	5
+7	2	5
+6	2	4
+5	2	3
+drop table t0,t1;
+#
+# Resolution of window names
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (pk int, c int);
+insert into t1 select a+1,1 from t0;
+update t1 set c=2 where pk not in (1,2,3,4);
+select * from t1;
+pk	c
+1	1
+2	1
+3	1
+4	1
+5	2
+6	2
+7	2
+8	2
+9	2
+10	2
+select
+pk, c,
+count(*) over w1 as CNT
+from t1
+window w1 as (partition by c order by pk
+rows between 2 preceding and 2 following);
+pk	c	CNT
+1	1	3
+2	1	4
+3	1	4
+4	1	3
+5	2	3
+6	2	4
+7	2	5
+8	2	5
+9	2	4
+10	2	3
+select
+pk, c,
+count(*) over (w1 rows between 2 preceding and 2 following) as CNT
+from t1
+window w1 as (partition by c order by pk);
+pk	c	CNT
+1	1	3
+2	1	4
+3	1	4
+4	1	3
+5	2	3
+6	2	4
+7	2	5
+8	2	5
+9	2	4
+10	2	3
+select
+pk, c,
+count(*) over (w1 order by pk rows between 2 preceding and 2 following) as CNT
+from t1
+window w1 as (partition by c);
+pk	c	CNT
+1	1	3
+2	1	4
+3	1	4
+4	1	3
+5	2	3
+6	2	4
+7	2	5
+8	2	5
+9	2	4
+10	2	3
+select
+pk, c,
+count(*) over (w2 rows between 2 preceding and 2 following) as CNT
+from t1
+window w1 as (partition by c), w2 as (w1 order by pk);
+pk	c	CNT
+1	1	3
+2	1	4
+3	1	4
+4	1	3
+5	2	3
+6	2	4
+7	2	5
+8	2	5
+9	2	4
+10	2	3
+select
+pk, c,
+count(*) over w3 as CNT
+from t1
+window
+w1 as (partition by c),
+w2 as (w1 order by pk),
+w3 as (w2 rows between 2 preceding and 2 following);
+pk	c	CNT
+1	1	3
+2	1	4
+3	1	4
+4	1	3
+5	2	3
+6	2	4
+7	2	5
+8	2	5
+9	2	4
+10	2	3
+select
+pk, c,
+count(*) over w as CNT
+from t1
+window w1 as (partition by c order by pk
+rows between 2 preceding and 2 following);
+ERROR HY000: Window specification with name 'w' is not defined
+select
+pk, c,
+count(*) over (w2 rows between 2 preceding and 2 following) as CNT
+from t1
+window w1 as (partition by c), w1 as (order by pk);
+ERROR HY000: Multiple window specifications with the same name 'w1'
+select
+pk, c,
+count(*) over (w2 rows between 2 preceding and 2 following) as CNT
+from t1
+window w1 as (partition by c), w2 as (w partition by c order by pk);
+ERROR HY000: Window specification with name 'w' is not defined
+select
+pk, c,
+count(*) over (w2 rows between 2 preceding and 2 following) as CNT
+from t1
+window w1 as (partition by c), w2 as (w1 partition by c order by pk);
+ERROR HY000: Window specification referencing another one 'w1' cannot contain partition list
+select
+pk, c,
+count(*) over (w2 rows between 2 preceding and 2 following) as CNT
+from t1
+window w1 as (partition by c order by pk), w2 as (w1 order by pk);
+ERROR HY000: Referenced window specification 'w1' already contains order list
+select
+pk, c,
+count(*) over w3 as CNT
+from t1
+window
+w1 as (partition by c),
+w2 as (w1 order by pk rows between 3 preceding and 2 following),
+w3 as (w2 rows between 2 preceding and 2 following);
+ERROR HY000: Referenced window specification 'w2' cannot contain window frame
+select
+pk, c,
+count(*) over w1 as CNT
+from t1
+window w1 as (partition by c order by pk
+rows between unbounded following and 2 following);
+ERROR HY000: Unacceptable combination of window frame bound specifications
+select
+pk, c,
+count(*) over (w1 rows between 2 preceding and unbounded preceding) as CNT
+from t1
+window w1 as (partition by c order by pk);
+ERROR HY000: Unacceptable combination of window frame bound specifications
+select
+pk, c,
+count(*) over (w1 order by pk rows between current row and 2 preceding) as CNT
+from t1
+window w1 as (partition by c);
+ERROR HY000: Unacceptable combination of window frame bound specifications
+select
+pk, c,
+count(*) over (w2 rows between 2 following and current row) as CNT
+from t1
+window w1 as (partition by c), w2 as (w1 order by pk);
+ERROR HY000: Unacceptable combination of window frame bound specifications
+select
+pk, c
+from t1 where rank() over w1 > 2
+window w1 as (partition by c order by pk);
+ERROR HY000: Window function is allowed only in SELECT list and ORDER BY clause
+select
+c, max(pk) as m
+from t1
+group by c + rank() over w1
+window w1 as (order by m);
+ERROR HY000: Window function is allowed only in SELECT list and ORDER BY clause
+select
+c, max(pk) as m, rank() over w1 as r
+from t1
+group by c+r
+window w1 as (order by m);
+ERROR HY000: Window function is allowed only in SELECT list and ORDER BY clause
+select
+c, max(pk) as m, rank() over w1 as r
+from t1
+group by c having c+r > 3
+window w1 as (order by m);
+ERROR HY000: Window function is allowed only in SELECT list and ORDER BY clause
+select
+c, max(pk) as m, rank() over w1 as r,
+rank() over (partition by r+1 order by m)
+from t1
+group by c
+window w1 as (order by m);
+ERROR HY000: Window function is not allowed in window specification
+select
+c, max(pk) as m, rank() over w1 as r,
+rank() over (partition by m order by r)
+from t1
+group by c
+window w1 as (order by m);
+ERROR HY000: Window function is not allowed in window specification
+select
+c, max(pk) as m, rank() over w1 as r, dense_rank() over w2 as dr
+from t1
+group by c
+window w1 as (order by m), w2 as (partition by r order by m);
+ERROR HY000: Window function is not allowed in window specification
+select
+pk, c,
+row_number() over (partition by c order by pk
+range between unbounded preceding and current row) as r
+from t1;
+ERROR HY000: Window frame is not allowed with 'row_number'
+select
+pk, c,
+rank() over w1 as r
+from t1
+window w1 as (partition by c order by pk
+rows between 2 preceding and 2 following);
+ERROR HY000: Window frame is not allowed with 'rank'
+select
+pk, c,
+dense_rank() over (partition by c order by pk
+rows between 1 preceding and 1 following) as r
+from t1;
+ERROR HY000: Window frame is not allowed with 'dense_rank'
+select
+pk, c,
+rank() over w1 as r
+from t1
+window w1 as (partition by c);
+ERROR HY000: No order list in window specification for 'rank'
+select
+pk, c,
+dense_rank() over (partition by c) as r
+from t1;
+ERROR HY000: No order list in window specification for 'dense_rank'
+drop table t0,t1;
+#
+# MDEV-9634: Window function produces incorrect value
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (part_id int, pk int, a int);
+insert into t2 select
+if(a<5, 0, 1), a, if(a<5, NULL, 1) from t0;
+select * from t2;
+part_id	pk	a
+0	0	NULL
+0	1	NULL
+0	2	NULL
+0	3	NULL
+0	4	NULL
+1	5	1
+1	6	1
+1	7	1
+1	8	1
+1	9	1
+select
+part_id, pk, a,
+count(a) over (partition by part_id order by pk
+rows between 1 preceding and 1 following) as CNT
+from t2;
+part_id	pk	a	CNT
+0	0	NULL	0
+0	1	NULL	0
+0	2	NULL	0
+0	3	NULL	0
+0	4	NULL	0
+1	5	1	2
+1	6	1	3
+1	7	1	3
+1	8	1	3
+1	9	1	2
+drop table t0, t2;
+#
+#  RANGE-type bounds
+#
+create table t3 (
+pk int,
+val int
+);
+insert into t3 values
+(0, 1),
+(1, 1),
+(2, 1),
+(3, 2),
+(4, 2),
+(5, 2),
+(6, 2);
+select
+val,
+count(val) over (order by val
+range between current row and
+current row)
+as CNT
+from t3;
+val	CNT
+1	3
+1	3
+1	3
+2	4
+2	4
+2	4
+2	4
+insert into t3 values
+(7, 3),
+(8, 3);
+select
+val,
+count(val) over (order by val
+range between current row and
+current row)
+as CNT
+from t3;
+val	CNT
+1	3
+1	3
+1	3
+2	4
+2	4
+2	4
+2	4
+3	2
+3	2
+drop table t3;
+# Now, check with PARTITION BY
+create table t4 (
+part_id int,
+pk int,
+val int
+);
+insert into t4 values
+(1234, 100, 1),
+(1234, 101, 1),
+(1234, 102, 1),
+(1234, 103, 2),
+(1234, 104, 2),
+(1234, 105, 2),
+(1234, 106, 2),
+(1234, 107, 3),
+(1234, 108, 3),
+(5678, 200, 1),
+(5678, 201, 1),
+(5678, 202, 1),
+(5678, 203, 2),
+(5678, 204, 2),
+(5678, 205, 2),
+(5678, 206, 2),
+(5678, 207, 3),
+(5678, 208, 3);
+select
+part_id,
+val,
+count(val) over (partition by part_id
+order by val
+range between current row and
+current row)
+as CNT
+from t4;
+part_id	val	CNT
+1234	1	3
+1234	1	3
+1234	1	3
+1234	2	4
+1234	2	4
+1234	2	4
+1234	2	4
+1234	3	2
+1234	3	2
+5678	1	3
+5678	1	3
+5678	1	3
+5678	2	4
+5678	2	4
+5678	2	4
+5678	2	4
+5678	3	2
+5678	3	2
+#
+# Try RANGE UNBOUNDED PRECEDING | FOLLOWING
+#
+select
+part_id,
+val,
+count(val) over (partition by part_id
+order by val
+range between unbounded preceding and
+current row)
+as CNT
+from t4;
+part_id	val	CNT
+1234	1	3
+1234	1	3
+1234	1	3
+1234	2	7
+1234	2	7
+1234	2	7
+1234	2	7
+1234	3	9
+1234	3	9
+5678	1	3
+5678	1	3
+5678	1	3
+5678	2	7
+5678	2	7
+5678	2	7
+5678	2	7
+5678	3	9
+5678	3	9
+select
+part_id,
+val,
+count(val) over (partition by part_id
+order by val
+range between current row and
+unbounded following)
+as CNT
+from t4;
+part_id	val	CNT
+1234	1	9
+1234	1	9
+1234	1	9
+1234	2	6
+1234	2	6
+1234	2	6
+1234	2	6
+1234	3	2
+1234	3	2
+5678	1	9
+5678	1	9
+5678	1	9
+5678	2	6
+5678	2	6
+5678	2	6
+5678	2	6
+5678	3	2
+5678	3	2
+select
+part_id,
+val,
+count(val) over (partition by part_id
+order by val
+range between unbounded preceding and
+unbounded following)
+as CNT
+from t4;
+part_id	val	CNT
+1234	1	9
+1234	1	9
+1234	1	9
+1234	2	9
+1234	2	9
+1234	2	9
+1234	2	9
+1234	3	9
+1234	3	9
+5678	1	9
+5678	1	9
+5678	1	9
+5678	2	9
+5678	2	9
+5678	2	9
+5678	2	9
+5678	3	9
+5678	3	9
+drop table t4;
+#
+# MDEV-9695: Wrong window frame when using RANGE BETWEEN N FOLLOWING AND PRECEDING
+#
+create table t1 (pk int, a int, b int);
+insert into t1 values
+( 1 , 0, 1),
+( 2 , 0, 2),
+( 3 , 1, 4),
+( 4 , 1, 8),
+( 5 , 2, 32),
+( 6 , 2, 64),
+( 7 , 2, 128),
+( 8 , 2, 16);
+select pk, a, b,
+bit_or(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as bit_or
+from t1;
+pk	a	b	bit_or
+1	0	1	3
+2	0	2	3
+3	1	4	12
+4	1	8	12
+5	2	32	96
+6	2	64	224
+7	2	128	208
+8	2	16	144
+# Extra ROWS n PRECEDING tests
+select pk, a, b,
+bit_or(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as bit_or
+from t1;
+pk	a	b	bit_or
+1	0	1	0
+2	0	2	1
+3	1	4	0
+4	1	8	4
+5	2	32	0
+6	2	64	32
+7	2	128	64
+8	2	16	128
+drop table t1;
+create table t2 (
+pk int,
+a int,
+b int
+);
+insert into t2 values
+( 1, 0, 1),
+( 2, 0, 2),
+( 3, 0, 4),
+( 4, 0, 8),
+( 5, 1, 16),
+( 6, 1, 32),
+( 7, 1, 64),
+( 8, 1, 128),
+( 9, 2, 256),
+(10, 2, 512),
+(11, 2, 1024),
+(12, 2, 2048);
+select pk, a, b,
+bit_or(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as bit_or
+from t2;
+pk	a	b	bit_or
+1	0	1	0
+2	0	2	1
+3	0	4	2
+4	0	8	4
+5	1	16	0
+6	1	32	16
+7	1	64	32
+8	1	128	64
+9	2	256	0
+10	2	512	256
+11	2	1024	512
+12	2	2048	1024
+select pk, a, b,
+bit_or(b) over (partition by a order by pk ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) as bit_or
+from t2;
+pk	a	b	bit_or
+1	0	1	0
+2	0	2	0
+3	0	4	1
+4	0	8	2
+5	1	16	0
+6	1	32	0
+7	1	64	16
+8	1	128	32
+9	2	256	0
+10	2	512	0
+11	2	1024	256
+12	2	2048	512
+select pk, a, b,
+bit_or(b) over (partition by a order by pk ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) as bit_or
+from t2;
+pk	a	b	bit_or
+1	0	1	0
+2	0	2	1
+3	0	4	3
+4	0	8	6
+5	1	16	0
+6	1	32	16
+7	1	64	48
+8	1	128	96
+9	2	256	0
+10	2	512	256
+11	2	1024	768
+12	2	2048	1536
+# Check CURRENT ROW
+select pk, a, b,
+bit_or(b) over (partition by a order by pk ROWS BETWEEN CURRENT ROW AND CURRENT ROW) as bit_or
+from t2;
+pk	a	b	bit_or
+1	0	1	1
+2	0	2	2
+3	0	4	4
+4	0	8	8
+5	1	16	16
+6	1	32	32
+7	1	64	64
+8	1	128	128
+9	2	256	256
+10	2	512	512
+11	2	1024	1024
+12	2	2048	2048
+drop table t2;
+#
+# Try RANGE PRECEDING|FOLLWING n
+#
+create table t1 (
+part_id int,
+pk int,
+a int
+);
+insert into t1 values
+(10, 1, 1),
+(10, 2, 2),
+(10, 3, 4),
+(10, 4, 8),
+(10, 5,26),
+(10, 6,27),
+(10, 7,40),
+(10, 8,71),
+(10, 9,72);
+select
+pk, a,
+count(a) over (ORDER BY a
+RANGE BETWEEN UNBOUNDED PRECEDING
+AND 10 FOLLOWING) as cnt
+from t1;
+pk	a	cnt
+1	1	4
+2	2	4
+3	4	4
+4	8	4
+5	26	6
+6	27	6
+7	40	7
+8	71	9
+9	72	9
+select
+pk, a,
+count(a) over (ORDER BY a DESC
+RANGE BETWEEN UNBOUNDED PRECEDING
+AND 10 FOLLOWING) as cnt
+from t1;
+pk	a	cnt
+9	72	2
+8	71	2
+7	40	3
+6	27	5
+5	26	5
+4	8	9
+3	4	9
+2	2	9
+1	1	9
+select
+pk, a,
+count(a) over (ORDER BY a
+RANGE BETWEEN UNBOUNDED PRECEDING
+AND 1 FOLLOWING) as cnt
+from t1;
+pk	a	cnt
+1	1	2
+2	2	2
+3	4	3
+4	8	4
+5	26	6
+6	27	6
+7	40	7
+8	71	9
+9	72	9
+select
+pk, a,
+count(a) over (ORDER BY a
+RANGE BETWEEN UNBOUNDED PRECEDING
+AND 10 PRECEDING) as cnt
+from t1;
+pk	a	cnt
+1	1	0
+2	2	0
+3	4	0
+4	8	0
+5	26	4
+6	27	4
+7	40	6
+8	71	7
+9	72	7
+select
+pk, a,
+count(a) over (ORDER BY a DESC
+RANGE BETWEEN UNBOUNDED PRECEDING
+AND 10 PRECEDING) as cnt
+from t1;
+pk	a	cnt
+9	72	0
+8	71	0
+7	40	2
+6	27	3
+5	26	3
+4	8	5
+3	4	5
+2	2	5
+1	1	5
+select
+pk, a,
+count(a) over (ORDER BY a
+RANGE BETWEEN UNBOUNDED PRECEDING
+AND 1 PRECEDING) as cnt
+from t1;
+pk	a	cnt
+1	1	0
+2	2	1
+3	4	2
+4	8	3
+5	26	4
+6	27	5
+7	40	6
+8	71	7
+9	72	8
+select
+pk, a,
+count(a) over (ORDER BY a
+RANGE BETWEEN 1 PRECEDING
+AND CURRENT ROW) as cnt
+from t1;
+pk	a	cnt
+1	1	1
+2	2	2
+3	4	1
+4	8	1
+5	26	1
+6	27	2
+7	40	1
+8	71	1
+9	72	2
+select
+pk, a,
+count(a) over (ORDER BY a DESC
+RANGE BETWEEN 1 PRECEDING
+AND CURRENT ROW) as cnt
+from t1;
+pk	a	cnt
+9	72	1
+8	71	2
+7	40	1
+6	27	1
+5	26	2
+4	8	1
+3	4	1
+2	2	1
+1	1	2
+select
+pk, a,
+count(a) over (ORDER BY a
+RANGE BETWEEN 1 FOLLOWING
+AND 3 FOLLOWING) as cnt
+from t1;
+pk	a	cnt
+1	1	2
+2	2	1
+3	4	0
+4	8	0
+5	26	1
+6	27	0
+7	40	0
+8	71	1
+9	72	0
+# Try CURRENT ROW with[out] DESC
+select
+pk, a,
+count(a) over (ORDER BY a
+RANGE BETWEEN CURRENT ROW
+AND 1 FOLLOWING) as cnt
+from t1;
+pk	a	cnt
+1	1	2
+2	2	1
+3	4	1
+4	8	1
+5	26	2
+6	27	1
+7	40	1
+8	71	2
+9	72	1
+select
+pk, a,
+count(a) over (order by a desc
+range between current row
+and 1 following) as cnt
+from t1;
+pk	a	cnt
+9	72	2
+8	71	1
+7	40	1
+6	27	2
+5	26	1
+4	8	1
+3	4	1
+2	2	2
+1	1	1
+insert into t1 select 22, pk, a from t1;
+select
+part_id, pk, a,
+count(a) over (PARTITION BY part_id
+ORDER BY a
+RANGE BETWEEN UNBOUNDED PRECEDING
+AND 10 FOLLOWING) as cnt
+from t1;
+part_id	pk	a	cnt
+10	1	1	4
+10	2	2	4
+10	3	4	4
+10	4	8	4
+10	5	26	6
+10	6	27	6
+10	7	40	7
+10	8	71	9
+10	9	72	9
+22	1	1	4
+22	2	2	4
+22	3	4	4
+22	4	8	4
+22	5	26	6
+22	6	27	6
+22	7	40	7
+22	8	71	9
+22	9	72	9
+select
+pk, a,
+count(a) over (PARTITION BY part_id
+ORDER BY a
+RANGE BETWEEN UNBOUNDED PRECEDING
+AND 1 PRECEDING) as cnt
+from t1;
+pk	a	cnt
+1	1	0
+2	2	1
+3	4	2
+4	8	3
+5	26	4
+6	27	5
+7	40	6
+8	71	7
+9	72	8
+1	1	0
+2	2	1
+3	4	2
+4	8	3
+5	26	4
+6	27	5
+7	40	6
+8	71	7
+9	72	8
+drop table t1;
+# Try a RANGE frame over non-integer datatype:
+create table t1 (
+col1 int,
+a decimal(5,3)
+);
+insert into t1 values (1, 0.45);
+insert into t1 values (1, 0.5);
+insert into t1 values (1, 0.55);
+insert into t1 values (1, 1.21);
+insert into t1 values (1, 1.22);
+insert into t1 values (1, 3.33);
+select
+a,
+count(col1) over (order by a
+range between 0.1 preceding
+and 0.1 following)
+from t1;
+a	count(col1) over (order by a
+range between 0.1 preceding
+and 0.1 following)
+0.450	3
+0.500	3
+0.550	3
+1.210	2
+1.220	2
+3.330	1
+drop table t1;
+#
+# RANGE-type frames and NULL values
+#
+create table t1 (
+pk int,
+a int,
+b int
+);
+insert into t1 values (1, NULL,1);
+insert into t1 values (2, NULL,1);
+insert into t1 values (3, NULL,1);
+insert into t1 values (4, 10 ,1);
+insert into t1 values (5, 11 ,1);
+insert into t1 values (6, 12 ,1);
+insert into t1 values (7, 13 ,1);
+insert into t1 values (8, 14 ,1);
+select
+pk, a,
+count(b) over (order by a
+range between 2 preceding
+and 2 following) as CNT
+from t1
+order by a, pk;
+pk	a	CNT
+1	NULL	3
+2	NULL	3
+3	NULL	3
+4	10	3
+5	11	4
+6	12	5
+7	13	4
+8	14	3
+drop table t1;
+#
+#  Try ranges that have bound1 > bound2.  The standard actually allows them
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (pk int, c int);
+insert into t1 select a+1,1 from t0;
+update t1 set c=2 where pk not in (1,2,3,4);
+select * from t1;
+pk	c
+1	1
+2	1
+3	1
+4	1
+5	2
+6	2
+7	2
+8	2
+9	2
+10	2
+select
+pk, c,
+count(*) over (partition by c
+order by pk
+rows between 1 preceding
+and 2 preceding)
+as cnt
+from t1;
+pk	c	cnt
+1	1	0
+2	1	0
+3	1	0
+4	1	0
+5	2	0
+6	2	0
+7	2	0
+8	2	0
+9	2	0
+10	2	0
+select
+pk, c,
+sum(c) over (partition by c
+order by pk
+rows between 1 preceding
+and 2 preceding)
+as sum
+from t1;
+pk	c	sum
+1	1	NULL
+2	1	NULL
+3	1	NULL
+4	1	NULL
+5	2	NULL
+6	2	NULL
+7	2	NULL
+8	2	NULL
+9	2	NULL
+10	2	NULL
+select
+pk, c,
+sum(c) over (partition by c
+order by pk
+rows between 2 following
+and 1 following)
+as sum
+from t1;
+pk	c	sum
+1	1	NULL
+2	1	NULL
+3	1	NULL
+4	1	NULL
+5	2	NULL
+6	2	NULL
+7	2	NULL
+8	2	NULL
+9	2	NULL
+10	2	NULL
+select
+pk, c,
+count(*) over (partition by c
+order by pk
+range between 1 preceding
+and 2 preceding)
+as cnt
+from t1;
+pk	c	cnt
+1	1	0
+2	1	0
+3	1	0
+4	1	0
+5	2	0
+6	2	0
+7	2	0
+8	2	0
+9	2	0
+10	2	0
+drop table t0, t1;
+#
+# Error checking for frame bounds
+#
+create table t1 (a int, b int, c varchar(32));
+insert into t1 values (1,1,'foo');
+insert into t1 values (2,2,'bar');
+select
+count(*) over (order by a,b
+range between unbounded preceding and current row)
+from t1;
+ERROR HY000: RANGE-type frame requires ORDER BY clause with single sort key
+select
+count(*) over (order by c
+range between unbounded preceding and current row)
+from t1;
+ERROR HY000: Numeric datatype is required for RANGE-type frame
+select
+count(*) over (order by a
+range between 'abcd' preceding and current row)
+from t1;
+ERROR HY000: Numeric datatype is required for RANGE-type frame
+select
+count(*) over (order by a
+range between current row and 'foo' following)
+from t1;
+ERROR HY000: Numeric datatype is required for RANGE-type frame
+# Try range frame with invalid bounds
+select
+count(*) over (order by a
+rows between 0.5 preceding and current row)
+from t1;
+ERROR HY000: Integer is required for ROWS-type frame
+select
+count(*) over (order by a
+rows between current row and 3.14 following)
+from t1;
+ERROR HY000: Integer is required for ROWS-type frame
+#
+# EXCLUDE clause is parsed but not supported
+#
+select
+count(*) over (order by a
+rows between 1 preceding and 1 following
+exclude current row)
+from t1;
+ERROR HY000: Frame exclusion is not supported yet
+select
+count(*) over (order by a
+range between 1 preceding and 1 following
+exclude ties)
+from t1;
+ERROR HY000: Frame exclusion is not supported yet
+select
+count(*) over (order by a
+range between 1 preceding and 1 following
+exclude group)
+from t1;
+ERROR HY000: Frame exclusion is not supported yet
+select
+count(*) over (order by a
+rows between 1 preceding and 1 following
+exclude no others)
+from t1;
+count(*) over (order by a
+rows between 1 preceding and 1 following
+exclude no others)
+2
+2
+drop table t1;
+#
+#  Window function in grouping query
+#
+create table t1 (
+username  varchar(32),
+amount int
+);
+insert into t1 values
+('user1',1),
+('user1',5),
+('user1',3),
+('user2',10),
+('user2',20),
+('user2',30);
+select
+username,
+sum(amount) as s,
+rank() over (order by s desc)
+from t1
+group by username;
+username	s	rank() over (order by s desc)
+user1	9	2
+user2	60	1
+drop table t1;
+#
+#  mdev-9719: Window function in prepared statement
+#
+create table t1(a int, b int, x char(32));
+insert into t1 values (2, 10, 'xx');
+insert into t1 values (2, 10, 'zz');
+insert into t1 values (2, 20, 'yy');
+insert into t1 values (3, 10, 'xxx');
+insert into t1 values (3, 20, 'vvv');
+prepare stmt from 'select a, row_number() over (partition by a order by b) from t1';
+execute stmt;
+a	row_number() over (partition by a order by b)
+2	1
+2	2
+2	3
+3	1
+3	2
+drop table t1;
+#
+#  mdev-9754: Window name resolution in prepared statement
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (pk int, c int);
+insert into t1 select a+1,1 from t0;
+update t1 set c=2 where pk not in (1,2,3,4);
+select * from t1;
+pk	c
+1	1
+2	1
+3	1
+4	1
+5	2
+6	2
+7	2
+8	2
+9	2
+10	2
+prepare stmt from
+'select
+  pk, c,
+  count(*) over w1 as CNT
+from t1
+window w1 as (partition by c order by pk
+              rows between 2 preceding and 2 following)';
+execute stmt;
+pk	c	CNT
+1	1	3
+2	1	4
+3	1	4
+4	1	3
+5	2	3
+6	2	4
+7	2	5
+8	2	5
+9	2	4
+10	2	3
+drop table t0,t1;
+#
+# EXPLAIN FORMAT=JSON support for window functions
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+explain format=json select rank() over (order by a) from t0;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "window_functions_computation": {
+      "sorts": {
+        "filesort": {
+          "sort_key": "t0.a"
+        }
+      },
+      "temporary_table": {
+        "table": {
+          "table_name": "t0",
+          "access_type": "ALL",
+          "rows": 10,
+          "filtered": 100
+        }
+      }
+    }
+  }
+}
+create table t1 (a int, b int, c int);
+insert into t1 select a,a,a from t0;
+explain format=json
+select
+a,
+rank() over (order by sum(b))
+from t1
+group by a;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "filesort": {
+      "sort_key": "t1.a",
+      "window_functions_computation": {
+        "sorts": {
+          "filesort": {
+            "sort_key": "sum(t1.b)"
+          }
+        },
+        "temporary_table": {
+          "table": {
+            "table_name": "t1",
+            "access_type": "ALL",
+            "rows": 10,
+            "filtered": 100
+          }
+        }
+      }
+    }
+  }
+}
+explain format=json
+select
+a,
+rank() over (order by sum(b))
+from t1
+group by a
+order by null;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "window_functions_computation": {
+      "sorts": {
+        "filesort": {
+          "sort_key": "sum(t1.b)"
+        }
+      },
+      "temporary_table": {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 10,
+          "filtered": 100
+        }
+      }
+    }
+  }
+}
+#
+# Check how window function works together with GROUP BY and HAVING
+#
+select b,max(a) as MX, rank() over (order by b) from t1 group by b having MX in (3,5,7);
+b	MX	rank() over (order by b)
+3	3	1
+5	5	2
+7	7	3
+explain format=json
+select b,max(a) as MX, rank() over (order by b) from t1 group by b having MX in (3,5,7);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "having_condition": "MX in (3,5,7)",
+    "filesort": {
+      "sort_key": "t1.b",
+      "window_functions_computation": {
+        "sorts": {
+          "filesort": {
+            "sort_key": "t1.b"
+          }
+        },
+        "temporary_table": {
+          "table": {
+            "table_name": "t1",
+            "access_type": "ALL",
+            "rows": 10,
+            "filtered": 100
+          }
+        }
+      }
+    }
+  }
+}
+drop table t1;
+drop table t0;
+#
+# Building ordering index for window functions
+#
+create table t1 (
+pk int primary key,
+a int,
+b int,
+c  int
+);
+insert into t1 values
+(101 , 0, 10, 1),
+(102 , 0, 10, 2),
+(103 , 1, 10, 3),
+(104 , 1, 10, 4),
+(108 , 2, 10, 5),
+(105 , 2, 20, 6),
+(106 , 2, 20, 7),
+(107 , 2, 20, 8),
+(109 , 4, 20, 9),
+(110 , 4, 20, 10),
+(111 , 5, NULL, 11),
+(112 , 5, 1, 12),
+(113 , 5, NULL, 13),
+(114 , 5, NULL, 14),
+(115 , 5, NULL, 15),
+(116 , 6, 1, NULL),
+(117 , 6, 1, 10),
+(118 , 6, 1, 1),
+(119 , 6, 1, NULL),
+(120 , 6, 1, NULL),
+(121 , 6, 1, NULL),
+(122 , 6, 1, 2),
+(123 , 6, 1, 20),
+(124 , 6, 1, -10),
+(125 , 6, 1, NULL),
+(126 , 6, 1, NULL),
+(127 , 6, 1, NULL);
+select sum(b) over (partition by a order by b,pk
+rows between unbounded preceding  and current row) as c1,
+avg(b) over (w1 rows between 1 preceding and 1 following) as c2,
+sum(c) over (w2 rows between 1 preceding and 1 following) as c5,
+avg(b) over (w1 rows between 5 preceding and 5 following) as c3,
+sum(b) over (w1 rows between 1 preceding and 1 following) as c4
+from t1
+window w1 as (partition by a order by b,pk),
+w2 as (partition by b order by c,pk);
+c1	c2	c5	c3	c4
+1	1.0000	42	1.0000	1
+1	1.0000	NULL	1.0000	2
+10	1.0000	NULL	1.0000	3
+10	10.0000	3	10.0000	20
+10	10.0000	9	10.0000	20
+10	15.0000	9	17.5000	30
+11	1.0000	NULL	1.0000	3
+12	1.0000	-10	1.0000	2
+2	1.0000	24	1.0000	3
+20	10.0000	12	10.0000	20
+20	10.0000	6	10.0000	20
+20	20.0000	27	20.0000	40
+3	1.0000	-7	1.0000	3
+30	16.6667	13	17.5000	50
+4	1.0000	NULL	1.0000	3
+40	20.0000	19	20.0000	40
+5	1.0000	NULL	1.0000	3
+50	20.0000	21	17.5000	60
+6	1.0000	NULL	1.0000	3
+7	1.0000	13	1.0000	3
+70	20.0000	24	17.5000	40
+8	1.0000	32	1.0000	3
+9	1.0000	-9	1.0000	3
+NULL	1.0000	29	1.0000	1
+NULL	NULL	24	1.0000	NULL
+NULL	NULL	38	1.0000	NULL
+NULL	NULL	42	1.0000	NULL
+drop table t1;
+#
+# MDEV-9848: Window functions: reuse sorting and/or scanning
+#
+create table t1 (a int, b int, c int);
+insert into t1 values
+(1,3,1),
+(2,2,1),
+(3,1,1);
+#  Check using counters
+flush status;
+select
+rank() over (partition by c order by a),
+rank() over (partition by c order by b)
+from t1;
+rank() over (partition by c order by a)	rank() over (partition by c order by b)
+1	3
+2	2
+3	1
+show status like '%sort%';
+Variable_name	Value
+Sort_merge_passes	0
+Sort_priority_queue_sorts	0
+Sort_range	0
+Sort_rows	6
+Sort_scan	2
+flush status;
+select
+rank() over (partition by c order by a),
+rank() over (partition by c order by a)
+from t1;
+rank() over (partition by c order by a)	rank() over (partition by c order by a)
+1	1
+2	2
+3	3
+show status like '%sort%';
+Variable_name	Value
+Sort_merge_passes	0
+Sort_priority_queue_sorts	0
+Sort_range	0
+Sort_rows	3
+Sort_scan	1
+explain format=json
+select
+rank() over (partition by c order by a),
+rank() over (partition by c order by a)
+from t1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "window_functions_computation": {
+      "sorts": {
+        "filesort": {
+          "sort_key": "t1.c, t1.a"
+        }
+      },
+      "temporary_table": {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 3,
+          "filtered": 100
+        }
+      }
+    }
+  }
+}
+explain format=json
+select
+rank() over (order by a),
+row_number() over (order by a)
+from t1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "window_functions_computation": {
+      "sorts": {
+        "filesort": {
+          "sort_key": "t1.a"
+        }
+      },
+      "temporary_table": {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 3,
+          "filtered": 100
+        }
+      }
+    }
+  }
+}
+explain format=json
+select
+rank() over (partition by c order by a),
+count(*) over (partition by c)
+from t1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "window_functions_computation": {
+      "sorts": {
+        "filesort": {
+          "sort_key": "t1.c, t1.a"
+        }
+      },
+      "temporary_table": {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 3,
+          "filtered": 100
+        }
+      }
+    }
+  }
+}
+explain format=json
+select
+count(*) over (partition by c),
+rank() over (partition by c order by a)
+from t1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "window_functions_computation": {
+      "sorts": {
+        "filesort": {
+          "sort_key": "t1.c, t1.a"
+        }
+      },
+      "temporary_table": {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 3,
+          "filtered": 100
+        }
+      }
+    }
+  }
+}
+drop table t1;
+#
+# MDEV-9847: Window functions: crash with big_tables=1
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+set @tmp=@@big_tables;
+set big_tables=1;
+select rank() over (order by a) from t1;
+rank() over (order by a)
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+set big_tables=@tmp;
+drop table t1;
+#
+# Check if "ORDER BY window_func" works
+#
+create table t1 (s1 int, s2 char(5));
+insert into t1 values (1,'a');
+insert into t1 values (null,null);
+insert into t1 values (1,null);
+insert into t1 values (null,'a');
+insert into t1 values (2,'b');
+insert into t1 values (-1,'');
+explain format=json
+select *, row_number() over (order by s1, s2) as X from t1 order by X desc;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "filesort": {
+      "sort_key": "row_number() over ( order by t1.s1,t1.s2) desc",
+      "window_functions_computation": {
+        "sorts": {
+          "filesort": {
+            "sort_key": "t1.s1, t1.s2"
+          }
+        },
+        "temporary_table": {
+          "table": {
+            "table_name": "t1",
+            "access_type": "ALL",
+            "rows": 6,
+            "filtered": 100
+          }
+        }
+      }
+    }
+  }
+}
+select *, row_number() over (order by s1, s2) as X from t1 order by X desc;
+s1	s2	X
+2	b	6
+1	a	5
+1	NULL	4
+-1		3
+NULL	a	2
+NULL	NULL	1
+drop table t1;
+#
+# Try window functions that are not directly present in the select list
+#
+create table t1 (a int, b int);
+insert into t1 values
+(1,3),
+(2,2),
+(3,1);
+select
+a, b,
+rank() over (order by a), rank() over (order by b),
+rank() over (order by a) - rank() over (order by b) as diff
+from
+t1;
+a	b	rank() over (order by a)	rank() over (order by b)	diff
+1	3	1	3	-2
+2	2	2	2	0
+3	1	3	1	2
+drop table t1;
+create table t1 (i int);
+insert into t1 values (1),(2);
+SELECT MAX(i) OVER (PARTITION BY (i)) FROM t1;
+MAX(i) OVER (PARTITION BY (i))
+1
+2
+drop table t1;
+#
+# Check the 0 in ROWS 0 PRECEDING
+#
+create table t1 (
+part_id int,
+pk int,
+a int
+);
+insert into t1 values (1, 1, 1);
+insert into t1 values (1, 2, 2);
+insert into t1 values (1, 3, 4);
+insert into t1 values (1, 4, 8);
+select
+pk, a,
+sum(a) over (order by pk rows between 0 preceding and current row)
+from t1;
+pk	a	sum(a) over (order by pk rows between 0 preceding and current row)
+1	1	1
+2	2	2
+3	4	4
+4	8	8
+select
+pk, a,
+sum(a) over (order by pk rows between 1 preceding and 0 preceding)
+from t1;
+pk	a	sum(a) over (order by pk rows between 1 preceding and 0 preceding)
+1	1	1
+2	2	3
+3	4	6
+4	8	12
+insert into t1 values (200, 1, 1);
+insert into t1 values (200, 2, 2);
+insert into t1 values (200, 3, 4);
+insert into t1 values (200, 4, 8);
+select
+part_id, pk, a,
+sum(a) over (partition by part_id order by pk rows between 0 preceding and current row)
+from t1;
+part_id	pk	a	sum(a) over (partition by part_id order by pk rows between 0 preceding and current row)
+1	1	1	1
+1	2	2	2
+1	3	4	4
+1	4	8	8
+200	1	1	1
+200	2	2	2
+200	3	4	4
+200	4	8	8
+select
+part_id, pk, a,
+sum(a) over (partition by part_id order by pk rows between 1 preceding and 0 preceding)
+from t1;
+part_id	pk	a	sum(a) over (partition by part_id order by pk rows between 1 preceding and 0 preceding)
+1	1	1	1
+1	2	2	3
+1	3	4	6
+1	4	8	12
+200	1	1	1
+200	2	2	3
+200	3	4	6
+200	4	8	12
+drop table t1;
+#
+# MDEV-9780, The "DISTINCT must not bet converted into GROUP BY when
+#                 window functions are present" part
+#
+create table t1 (part_id int, a int);
+insert into t1 values
+(100, 1),
+(100, 2),
+(100, 2),
+(100, 3),
+(2000, 1),
+(2000, 2),
+(2000, 3),
+(2000, 3),
+(2000, 3);
+select          rank() over (partition by part_id order by a) from t1;
+rank() over (partition by part_id order by a)
+1
+2
+2
+4
+1
+2
+3
+3
+3
+select distinct rank() over (partition by part_id order by a) from t1;
+rank() over (partition by part_id order by a)
+1
+2
+4
+3
+explain format=json
+select distinct rank() over (partition by part_id order by a) from t1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "duplicate_removal": {
+      "window_functions_computation": {
+        "sorts": {
+          "filesort": {
+            "sort_key": "t1.part_id, t1.a"
+          }
+        },
+        "temporary_table": {
+          "table": {
+            "table_name": "t1",
+            "access_type": "ALL",
+            "rows": 9,
+            "filtered": 100
+          }
+        }
+      }
+    }
+  }
+}
+drop table t1;
+#
+# MDEV-9893: Window functions with different ORDER BY lists,
+#            one of these lists containing an expression
+#
+create table t1 (s1 int, s2 char(5));
+insert into t1 values (1,'a');
+insert into t1 values (null,null);
+insert into t1 values (3,null);
+insert into t1 values (4,'a');
+insert into t1 values (2,'b');
+insert into t1 values (-1,'');
+select
+*,
+ROW_NUMBER() OVER (order by s1),
+CUME_DIST() OVER (order by -s1)
+from t1;
+s1	s2	ROW_NUMBER() OVER (order by s1)	CUME_DIST() OVER (order by -s1)
+-1		2	1.0000000000
+1	a	3	0.8333333333
+2	b	4	0.6666666667
+3	NULL	5	0.5000000000
+4	a	6	0.3333333333
+NULL	NULL	1	0.1666666667
+drop table t1;
+#
+# MDEV-9925: Wrong result with aggregate function as a window function
+#
+create table t1 (i int);
+insert into t1 values (1),(2);
+select i, sum(i) over (partition by i) from t1;
+i	sum(i) over (partition by i)
+1	1
+2	2
+drop table t1;
+#
+# MDEV-9922: Assertion `!join->only_const_tables() && fsort' failed in int create_sort_index
+#
+create view v1 as select 1 as i;
+select rank() over (order by i) from v1;
+rank() over (order by i)
+1
+drop view v1;
+#
+# MDEV-10097: Assertion `count > 0' failed in Item_sum_sum::add_helper(bool)
+#
+CREATE TABLE `orders` (
+`o_orderkey` int(11) NOT NULL,
+`o_custkey` int(11) DEFAULT NULL,
+PRIMARY KEY (`o_orderkey`)
+) DEFAULT CHARSET=latin1;
+INSERT INTO `orders` VALUES (59908,242);
+INSERT INTO `orders` VALUES (59940,238);
+SELECT o_custkey, avg(o_custkey) OVER (PARTITION BY abs(o_custkey)
+ORDER BY o_custkey
+RANGE BETWEEN 15 FOLLOWING
+AND 15 FOLLOWING) from orders;
+o_custkey	avg(o_custkey) OVER (PARTITION BY abs(o_custkey)
+ORDER BY o_custkey
+RANGE BETWEEN 15 FOLLOWING
+AND 15 FOLLOWING)
+238	NULL
+242	NULL
+DROP table orders;
+#
+# MDEV-10842: window functions with the same order column
+#             but different directions
+#
+create table t1 (
+pk int primary key,
+a int,
+b int,
+c char(10)
+);
+insert into t1 values
+( 1, 0, 1, 'one'),
+( 2, 0, 2, 'two'),
+( 3, 0, 3, 'three'),
+( 4, 1, 1, 'one'),
+( 5, 1, 1, 'two'),
+( 6, 1, 2, 'three'),
+( 7, 2, NULL, 'n_one'),
+( 8, 2, 1,    'n_two'),
+( 9, 2, 2,    'n_three'),
+(10, 2, 0,    'n_four'),
+(11, 2, 10,   NULL);
+select pk,
+row_number() over (order by pk desc) as r_desc,
+row_number() over (order by pk asc) as r_asc
+from t1;
+pk	r_desc	r_asc
+11	1	11
+10	2	10
+9	3	9
+8	4	8
+7	5	7
+6	6	6
+5	7	5
+4	8	4
+3	9	3
+2	10	2
+1	11	1
+drop table t1;
+#
+# MDEV-10874: two window functions with compatible sorting
+#
+create table t1 (
+pk int primary key,
+a int,
+b int,
+c char(10),
+d decimal(10, 3),
+e real
+);
+insert into t1 values
+( 1, 0, 1,    'one',    0.1,  0.001),
+( 2, 0, 2,    'two',    0.2,  0.002),
+( 3, 0, 3,    'three',  0.3,  0.003),
+( 4, 1, 2,    'three',  0.4,  0.004),
+( 5, 1, 1,    'two',    0.5,  0.005),
+( 6, 1, 1,    'one',    0.6,  0.006),
+( 7, 2, NULL, 'n_one',  0.5,  0.007),
+( 8, 2, 1,    'n_two',  NULL, 0.008),
+( 9, 2, 2,    NULL,     0.7,  0.009),
+(10, 2, 0,    'n_four', 0.8,  0.010),
+(11, 2, 10,   NULL,     0.9,  NULL);
+select pk, a, d,
+sum(d) over (partition by a order by pk
+ROWS between 1 preceding and current row) as sum_1, 
+sum(d) over (order by a 
+ROWS BETWEEN 1 preceding and 2 following) as sum_2
+from t1;
+pk	a	d	sum_1	sum_2
+1	0	0.100	0.100	0.600
+2	0	0.200	0.300	1.000
+3	0	0.300	0.500	1.400
+4	1	0.400	0.400	1.800
+5	1	0.500	0.900	2.000
+6	1	0.600	1.100	1.600
+7	2	0.500	0.500	1.800
+8	2	NULL	0.500	2.000
+9	2	0.700	0.700	2.400
+10	2	0.800	1.500	2.400
+11	2	0.900	1.700	1.700
+explain format=json
+select pk, a, d,
+sum(d) over (partition by a order by pk
+ROWS between 1 preceding and current row) as sum_1, 
+sum(d) over (order by a 
+ROWS BETWEEN 1 preceding and 2 following) as sum_2
+from t1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "window_functions_computation": {
+      "sorts": {
+        "filesort": {
+          "sort_key": "t1.a, t1.pk"
+        }
+      },
+      "temporary_table": {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 11,
+          "filtered": 100
+        }
+      }
+    }
+  }
+}
+select pk, a, d,
+sum(d) over (partition by a order by pk desc
+ROWS between 1 preceding and current row) as sum_1, 
+sum(d) over (order by a 
+ROWS BETWEEN 1 preceding and 2 following) as sum_2
+from t1;
+pk	a	d	sum_1	sum_2
+3	0	0.300	0.300	0.600
+2	0	0.200	0.500	1.200
+1	0	0.100	0.300	1.400
+6	1	0.600	0.600	1.600
+5	1	0.500	1.100	2.400
+4	1	0.400	0.900	2.600
+11	2	0.900	0.900	2.800
+10	2	0.800	1.700	2.400
+9	2	0.700	1.500	2.000
+8	2	NULL	0.700	1.200
+7	2	0.500	0.500	0.500
+drop table t1;
+#
+# MDEV-9941: two window functions with compatible partitions 
+#
+create table t1 (
+a int,
+b int,
+c int
+);
+insert into t1 values 
+(10, 1, 1),
+(10, 3, 10),
+(10, 1, 10),
+(10, 3, 100),
+(10, 5, 1000),
+(10, 1, 100);
+explain format=json
+select 
+a,b,c,
+row_number() over (partition by a),
+row_number() over (partition by a, b)
+from t1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "window_functions_computation": {
+      "sorts": {
+        "filesort": {
+          "sort_key": "t1.a, t1.b"
+        }
+      },
+      "temporary_table": {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 6,
+          "filtered": 100
+        }
+      }
+    }
+  }
+}
+drop table t1;
+#
+# MDEV-10815: Window Function Expressions Wrong Results
+#
+create table t(a decimal(35,10), b int);
+insert into t(a,b) values(1,1);
+insert into t(a,b) values(2,1);
+insert into t(a,b) values(0,1);
+insert into t(a,b) values(1,  2);
+insert into t(a,b) values(1.5,2);
+insert into t(a,b) values(3,  2);
+insert into t(a,b) values(4.5,2);
+select a, b,
+sum(t.a) over (partition by t.b order by a) as simple_sum,
+sum(t.a) over (partition by t.b order by a) + 1 as sum_and_const,
+sum(t.b) over (partition by t.b order by a) + sum(t.a) over (partition by t.b order by a) as sum_and_sum
+from t
+order by t.b, t.a;
+a	b	simple_sum	sum_and_const	sum_and_sum
+0.0000000000	1	0.0000000000	1.0000000000	1.0000000000
+1.0000000000	1	1.0000000000	2.0000000000	3.0000000000
+2.0000000000	1	3.0000000000	4.0000000000	6.0000000000
+1.0000000000	2	1.0000000000	2.0000000000	3.0000000000
+1.5000000000	2	2.5000000000	3.5000000000	6.5000000000
+3.0000000000	2	5.5000000000	6.5000000000	11.5000000000
+4.5000000000	2	10.0000000000	11.0000000000	18.0000000000
+drop table t;
+#
+# MDEV-10669: Crash in SELECT with window function used
+#
+create table t(a decimal(35,10), b int);
+insert into t(a,b) values(1,1);
+insert into t(a,b) values(2,1);
+insert into t(a,b) values(0,1);
+SELECT (CASE WHEN sum(t.a) over (partition by t.b)=0 THEN null ELSE null END) AS a FROM t;
+a
+NULL
+NULL
+NULL
+SELECT ifnull(((t.a) / CASE WHEN sum(t.a) over(partition by t.b) =0 then null else null end) ,0) from t;
+ifnull(((t.a) / CASE WHEN sum(t.a) over(partition by t.b) =0 then null else null end) ,0)
+0.00000000000000
+0.00000000000000
+0.00000000000000
+SELECT sum(t.a) over (partition by t.b order by a),
+sqrt(ifnull((sum(t.a) over (partition by t.b order by a)), 0))
+from t;
+sum(t.a) over (partition by t.b order by a)	sqrt(ifnull((sum(t.a) over (partition by t.b order by a)), 0))
+0.0000000000	0
+1.0000000000	1
+3.0000000000	1.7320508075688772
+drop table t;
+#
+# MDEV-10868: view definitions with window functions
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (pk int, c int);
+insert into t1 select a+1,1 from t0;
+update t1 set c=2 where pk not in (1,2,3,4);
+select * from t1;
+pk	c
+1	1
+2	1
+3	1
+4	1
+5	2
+6	2
+7	2
+8	2
+9	2
+10	2
+select pk, c, c/count(*) over (partition by c order by pk
+rows between 1 preceding and 2 following) as CNT
+from t1;
+pk	c	CNT
+1	1	0.3333
+2	1	0.2500
+3	1	0.3333
+4	1	0.5000
+5	2	0.6667
+6	2	0.5000
+7	2	0.5000
+8	2	0.5000
+9	2	0.6667
+10	2	1.0000
+create view v1 as select pk, c, c/count(*) over (partition by c order by pk
+rows between 1 preceding and 2 following) as CNT
+from t1;
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk`,`t1`.`c` AS `c`,`t1`.`c` / count(0) over ( partition by `t1`.`c` order by `t1`.`pk` rows between 1 preceding  and 2 following ) AS `CNT` from `t1`	latin1	latin1_swedish_ci
+select * from v1;
+pk	c	CNT
+1	1	0.3333
+2	1	0.2500
+3	1	0.3333
+4	1	0.5000
+5	2	0.6667
+6	2	0.5000
+7	2	0.5000
+8	2	0.5000
+9	2	0.6667
+10	2	1.0000
+select pk, c, c/count(*) over w1 as CNT from t1
+window w1 as (partition by c order by pk rows between 1 preceding and 2 following);
+pk	c	CNT
+1	1	0.3333
+2	1	0.2500
+3	1	0.3333
+4	1	0.5000
+5	2	0.6667
+6	2	0.5000
+7	2	0.5000
+8	2	0.5000
+9	2	0.6667
+10	2	1.0000
+create view v2 as select pk, c, c/count(*) over w1 as CNT from t1
+window w1 as (partition by c order by pk rows between 1 preceding and 2 following);
+show create view v2;
+View	Create View	character_set_client	collation_connection
+v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`pk` AS `pk`,`t1`.`c` AS `c`,`t1`.`c` / count(0) over ( partition by `t1`.`c` order by `t1`.`pk` rows between 1 preceding  and 2 following ) AS `CNT` from `t1`	latin1	latin1_swedish_ci
+select * from v2;
+pk	c	CNT
+1	1	0.3333
+2	1	0.2500
+3	1	0.3333
+4	1	0.5000
+5	2	0.6667
+6	2	0.5000
+7	2	0.5000
+8	2	0.5000
+9	2	0.6667
+10	2	1.0000
+select pk, c, c/count(*) over w1 as CNT from t1
+window w1 as (partition by c order by pk rows unbounded preceding);
+pk	c	CNT
+1	1	1.0000
+2	1	0.5000
+3	1	0.3333
+4	1	0.2500
+5	2	2.0000
+6	2	1.0000
+7	2	0.6667
+8	2	0.5000
+9	2	0.4000
+10	2	0.3333
+create view v3 as select pk, c, c/count(*) over w1 as CNT from t1
+window w1 as (partition by c order by pk rows unbounded preceding);
+show create view v3;
+View	Create View	character_set_client	collation_connection
+v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`pk` AS `pk`,`t1`.`c` AS `c`,`t1`.`c` / count(0) over ( partition by `t1`.`c` order by `t1`.`pk` rows between  unbounded  preceding  and  current row ) AS `CNT` from `t1`	latin1	latin1_swedish_ci
+select * from v3;
+pk	c	CNT
+1	1	1.0000
+2	1	0.5000
+3	1	0.3333
+4	1	0.2500
+5	2	2.0000
+6	2	1.0000
+7	2	0.6667
+8	2	0.5000
+9	2	0.4000
+10	2	0.3333
+select pk, c, c/count(*) over (partition by c order by pk
+range between 3 preceding and current row) as CNT
+from t1;
+pk	c	CNT
+1	1	1.0000
+2	1	0.5000
+3	1	0.3333
+4	1	0.2500
+5	2	2.0000
+6	2	1.0000
+7	2	0.6667
+8	2	0.5000
+9	2	0.5000
+10	2	0.5000
+create view v4 as select pk, c, c/count(*) over (partition by c order by pk
+range between 3 preceding and current row) as CNT
+from t1;
+show create view v4;
+View	Create View	character_set_client	collation_connection
+v4	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `t1`.`pk` AS `pk`,`t1`.`c` AS `c`,`t1`.`c` / count(0) over ( partition by `t1`.`c` order by `t1`.`pk` range between 3 preceding  and  current row ) AS `CNT` from `t1`	latin1	latin1_swedish_ci
+select * from v4;
+pk	c	CNT
+1	1	1.0000
+2	1	0.5000
+3	1	0.3333
+4	1	0.2500
+5	2	2.0000
+6	2	1.0000
+7	2	0.6667
+8	2	0.5000
+9	2	0.5000
+10	2	0.5000
+drop view v1,v2,v3,v4;
+drop table t0,t1;
+#
+# MDEV-10875: window function in subquery
+#
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (3),(1);
+CREATE TABLE t2 (c VARCHAR(8));
+INSERT INTO t2 VALUES ('foo'),('bar'),('foo');
+SELECT COUNT(*) OVER (PARTITION BY c) FROM t2;
+COUNT(*) OVER (PARTITION BY c)
+1
+2
+2
+SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
+i
+1
+DROP TABLE t1, t2;
+#
+# MDEV-9976: window function without PARTITION BY and ORDER BY
+#
+CREATE TABLE t1  (id int, a int);
+INSERT INTO t1 VALUES
+(1,1000), (2,1100), (3,1800), (4,1500), (5,1700), (6,1200),
+(7,2000), (8,2100), (9,1600);
+SELECT id, sum(a) OVER (PARTITION BY id 
+ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+FROM t1;
+id	sum(a) OVER (PARTITION BY id 
+1	1000
+2	1100
+3	1800
+4	1500
+5	1700
+6	1200
+7	2000
+8	2100
+9	1600
+ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+SELECT id, sum(a) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+FROM t1;
+id	sum(a) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+1	14000
+2	13000
+3	5900
+4	10700
+5	7600
+6	11900
+7	4100
+8	2100
+9	9200
+DROP TABLE t1;
+#
+# MDEV-11867: window function with aggregation
+#             over the result of grouping
+#
+create table t1 (
+username  varchar(32),
+amount int
+);
+insert into t1 values
+('user1',1),
+('user1',5),
+('user1',3),
+('user2',10),
+('user2',20),
+('user2',30);
+select username, sum(amount) as s, avg(sum(amount)) over (order by s desc)
+from t1
+group by username;
+username	s	avg(sum(amount)) over (order by s desc)
+user1	9	34.5000
+user2	60	60.0000
+select username, sum(amount), avg(sum(amount)) over (order by sum(amount) desc)
+from t1
+group by username;
+username	sum(amount)	avg(sum(amount)) over (order by sum(amount) desc)
+user1	9	34.5000
+user2	60	60.0000
+drop table t1;
+#
+# MDEV-11594: window function over implicit grouping
+#
+create table t1 (id int);
+insert into t1 values (1), (2), (3), (2);
+select sum(id) over (order by sum(id)) from t1;
+sum(id) over (order by sum(id))
+1
+select sum(sum(id)) over (order by sum(id)) from t1;
+sum(sum(id)) over (order by sum(id))
+8
+drop table t1;
+#
+# MDEV-9923: integer constant in order by list 
+#            of window specification
+#
+create table t1 (id int);
+insert into t1 values (1), (2), (3), (2);
+select rank() over (order by 1) from t1;
+rank() over (order by 1)
+1
+1
+1
+1
+select rank() over (order by 2) from t1;
+rank() over (order by 2)
+1
+1
+1
+1
+select rank() over (partition by id order by 2) from t1;
+rank() over (partition by id order by 2)
+1
+1
+1
+1
+drop table t1;
+#
+# MDEV-10660: view using a simple window function 
+#
+create table t1 (id int);
+insert into t1 values (1), (2), (3), (2);
+create view v1(id,rnk) as
+select id, rank() over (order by id) from t1;
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`id` AS `id`,rank() over ( order by `t1`.`id`) AS `rnk` from `t1`	latin1	latin1_swedish_ci
+select id, rank() over (order by id) from t1;
+id	rank() over (order by id)
+1	1
+2	2
+2	2
+3	4
+select * from v1;
+id	rnk
+1	1
+2	2
+2	2
+3	4
+drop view v1;
+drop table t1;
+#
+# MDEV-11138: window function in the query without tables 
+#
+select row_number() over ();
+row_number() over ()
+1
+select count(*) over ();
+count(*) over ()
+1
+select sum(5) over ();
+sum(5) over ()
+5
+select row_number() over (), sum(5) over ();
+row_number() over ()	sum(5) over ()
+1	5
+select row_number() over (order by 2);
+row_number() over (order by 2)
+1
+select row_number() over (partition by 2);
+row_number() over (partition by 2)
+1
+select row_number() over (partition by 4 order by 1+2);
+row_number() over (partition by 4 order by 1+2)
+1
+#
+# MDEV-11999: execution of prepared statement for  
+#             tableless query with window functions
+#
+prepare stmt from
+"select row_number() over (partition by 4 order by 1+2)";
+execute stmt;
+row_number() over (partition by 4 order by 1+2)
+1
+execute stmt;
+row_number() over (partition by 4 order by 1+2)
+1
+deallocate prepare stmt;
+#
+# MDEV-11745: window function with min/max 
+#
+create table t1 (i int, b int);
+insert into t1 values
+(1,1),(2,1),(3,1),(4,4),(5,4),(6,4),(7,8),(8,8),(9,8),(10,8);
+select b, min(i) over (partition by b) as f 
+from t1 as tt
+order by i;
+b	f
+1	1
+1	1
+1	1
+4	4
+4	4
+4	4
+8	7
+8	7
+8	7
+8	7
+select b, min(i) over (partition by b) as f 
+from (select * from t1) as tt
+order by i;
+b	f
+1	1
+1	1
+1	1
+4	4
+4	4
+4	4
+8	7
+8	7
+8	7
+8	7
+select b, min(i+10) over (partition by b) as f 
+from t1 as tt
+order by i;
+b	f
+1	11
+1	11
+1	11
+4	14
+4	14
+4	14
+8	17
+8	17
+8	17
+8	17
+select b, min(i) over (partition by b) as f 
+from (select i+10 as i, b from t1) as tt
+order by i;
+b	f
+1	11
+1	11
+1	11
+4	14
+4	14
+4	14
+8	17
+8	17
+8	17
+8	17
+select b, min(i+20) over (partition by b) as f 
+from (select i+10 as i, b from t1) as tt
+order by i;
+b	f
+1	31
+1	31
+1	31
+4	34
+4	34
+4	34
+8	37
+8	37
+8	37
+8	37
+select b, max(i) over (partition by b) as f 
+from t1 as tt
+order by i;
+b	f
+1	3
+1	3
+1	3
+4	6
+4	6
+4	6
+8	10
+8	10
+8	10
+8	10
+select b, max(i) over (partition by b) as f 
+from (select * from t1) as tt
+order by i;
+b	f
+1	3
+1	3
+1	3
+4	6
+4	6
+4	6
+8	10
+8	10
+8	10
+8	10
+select b, max(i+10) over (partition by b) as f 
+from t1 as tt
+order by i;
+b	f
+1	13
+1	13
+1	13
+4	16
+4	16
+4	16
+8	20
+8	20
+8	20
+8	20
+select b, max(i) over (partition by b) as f 
+from (select i+10 as i, b from t1) as tt
+order by i;
+b	f
+1	13
+1	13
+1	13
+4	16
+4	16
+4	16
+8	20
+8	20
+8	20
+8	20
+select b, max(i+20) over (partition by b) as f 
+from (select i+10 as i, b from t1) as tt
+order by i;
+b	f
+1	33
+1	33
+1	33
+4	36
+4	36
+4	36
+8	40
+8	40
+8	40
+8	40
+select max(i), max(i), sum(i), count(i)
+from t1 as tt
+group by b;
+max(i)	max(i)	sum(i)	count(i)
+3	3	6	3
+6	6	15	3
+10	10	34	4
+select max(i), min(sum(i)) over (partition by count(i)) f
+from t1 as tt
+group by b;
+max(i)	f
+3	6
+6	6
+10	34
+select max(i), min(sum(i)) over (partition by count(i)) f
+from (select * from t1) as tt
+group by b;
+max(i)	f
+3	6
+6	6
+10	34
+select max(i+10), min(sum(i)+10) over (partition by count(i)) f
+from t1 as tt
+group by b;
+max(i+10)	f
+13	16
+16	16
+20	44
+select max(i), max(i), sum(i), count(i)
+from (select i+10 as i, b from t1) as tt
+group by b;
+max(i)	max(i)	sum(i)	count(i)
+13	13	36	3
+16	16	45	3
+20	20	74	4
+select max(i), min(sum(i)) over (partition by count(i)) f
+from (select i+10 as i, b from t1) as tt
+group by b;
+max(i)	f
+13	36
+16	36
+20	74
+select max(i), min(i), min(max(i)-min(i)) over (partition by count(i)) f
+from (select i+10 as i, b from t1) as tt
+group by b;
+max(i)	min(i)	f
+13	11	2
+16	14	2
+20	17	3
+drop table t1;
+#
+# MDEV-12015: window function over select with WHERE  
+#             that is always FALSE
+#
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (3), (1), (2);
+SELECT i, ROW_NUMBER() OVER () FROM t1 WHERE 1 = 2;
+i	ROW_NUMBER() OVER ()
+SELECT i, COUNT(*) OVER () FROM t1 WHERE 1 = 2;
+i	COUNT(*) OVER ()
+DROP TABLE t1;
+#
+# MDEV-12051: window function in query with implicit grouping 
+#             on always empty set
+#
+create table t1 (a int, b varchar(8));
+insert into t1 values (1,'foo'),(2,'bar');
+select max(a), row_number() over () from t1 where a > 10;
+max(a)	row_number() over ()
+NULL	1
+select max(a), sum(max(a)) over () from t1 where a > 10;
+max(a)	sum(max(a)) over ()
+NULL	NULL
+select max(a), sum(max(a)) over (partition by max(a)) from t1 where a > 10;
+max(a)	sum(max(a)) over (partition by max(a))
+NULL	NULL
+select max(a), row_number() over () from t1 where 1 = 2;
+max(a)	row_number() over ()
+NULL	1
+select max(a), sum(max(a)) over () from t1 where 1 = 2;
+max(a)	sum(max(a)) over ()
+NULL	NULL
+select max(a), sum(max(a)) over (partition by max(a)) from t1 where 1 = 2;
+max(a)	sum(max(a)) over (partition by max(a))
+NULL	NULL
+select max(a), row_number() over () from t1 where 1 = 2
+having max(a) is not null;
+max(a)	row_number() over ()
+select max(a), sum(max(a)) over () from t1 where 1 = 2
+having max(a) is not null;
+max(a)	sum(max(a)) over ()
+drop table t1;
+#
+# MDEV-10885: window function in query with implicit grouping 
+#             with constant condition evaluated to false
+#
+CREATE TABLE t1 (a INT, b VARCHAR(8));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+CREATE TABLE t2 (c INT);
+INSERT INTO t2 VALUES (3),(4);
+CREATE TABLE t3 (d INT);
+INSERT INTO t3 VALUES (5),(6);
+SELECT MAX(a), ROW_NUMBER() OVER (PARTITION BY MAX(a)) FROM t1
+WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
+MAX(a)	ROW_NUMBER() OVER (PARTITION BY MAX(a))
+NULL	1
+SELECT MAX(a), COUNT(MAX(a)) OVER (PARTITION BY MAX(a)) FROM t1
+WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
+MAX(a)	COUNT(MAX(a)) OVER (PARTITION BY MAX(a))
+NULL	0
+SELECT MAX(a), SUM(MAX(a)) OVER (PARTITION BY MAX(a)) FROM t1
+WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
+MAX(a)	SUM(MAX(a)) OVER (PARTITION BY MAX(a))
+NULL	NULL
+SELECT MAX(a), ROW_NUMBER() OVER (PARTITION BY MAX(a)) FROM t1
+WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) )
+HAVING MAX(a) IS NOT NULL;
+MAX(a)	ROW_NUMBER() OVER (PARTITION BY MAX(a))
+SELECT a, MAX(a), ROW_NUMBER() OVER (PARTITION BY b) FROM t1
+WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
+a	MAX(a)	ROW_NUMBER() OVER (PARTITION BY b)
+NULL	NULL	1
+SELECT a, COUNT(a), AVG(a) OVER (PARTITION BY b) FROM t1
+WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
+a	COUNT(a)	AVG(a) OVER (PARTITION BY b)
+NULL	0	NULL
+SELECT a, MAX(a), AVG(a) OVER (PARTITION BY b) FROM t1
+WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
+a	MAX(a)	AVG(a) OVER (PARTITION BY b)
+NULL	NULL	NULL
+DROP TABLE t1,t2,t3;
+#
+# MDEV-10859: Wrong result of aggregate window function in query
+#                  with HAVING and no ORDER BY
+#
+create table empsalary (depname varchar(32), empno smallint primary key, salary int);
+insert into empsalary values
+('develop', 1, 5000), ('develop', 2, 4000),('sales', 3, '6000'),('sales', 4, 5000);
+SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
+depname	empno	salary	avg(salary) OVER (PARTITION BY depname)
+develop	1	5000	4500.0000
+develop	2	4000	4500.0000
+sales	3	6000	5500.0000
+sales	4	5000	5500.0000
+SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname;
+depname	empno	salary	avg(salary) OVER (PARTITION BY depname)
+develop	1	5000	4500.0000
+develop	2	4000	4500.0000
+sales	3	6000	5500.0000
+sales	4	5000	5500.0000
+#
+# These last 2 should have the same row results, ignoring order.
+#
+SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary HAVING empno > 1;
+depname	empno	salary	avg(salary) OVER (PARTITION BY depname)
+develop	2	4000	4000.0000
+sales	3	6000	5500.0000
+sales	4	5000	5500.0000
+SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary HAVING empno > 1 ORDER BY depname;
+depname	empno	salary	avg(salary) OVER (PARTITION BY depname)
+develop	2	4000	4000.0000
+sales	3	6000	5500.0000
+sales	4	5000	5500.0000
+drop table empsalary;
+#
+# MDEV-11868: min(distinct) over () returns wrong value
+#
+create table TDEC (CDEC int, RNUM int);
+create view VDEC as select * from TDEC;
+insert into TDEC (CDEC) values (null),(-1),(0),(1),(0),(10);
+select TDEC.CDEC, min(TDEC.CDEC) over () from TDEC;
+CDEC	min(TDEC.CDEC) over ()
+NULL	-1
+-1	-1
+0	-1
+0	-1
+1	-1
+10	-1
+select VDEC.CDEC, min(VDEC.CDEC) over () from VDEC;
+CDEC	min(VDEC.CDEC) over ()
+NULL	-1
+-1	-1
+0	-1
+0	-1
+1	-1
+10	-1
+select TDEC.CDEC, max(TDEC.CDEC) over () from TDEC;
+CDEC	max(TDEC.CDEC) over ()
+NULL	10
+-1	10
+0	10
+0	10
+1	10
+10	10
+select VDEC.CDEC, max(VDEC.CDEC) over () from VDEC;
+CDEC	max(VDEC.CDEC) over ()
+NULL	10
+-1	10
+0	10
+0	10
+1	10
+10	10
+select TDEC.CDEC, min(distinct TDEC.CDEC) over () from TDEC;
+CDEC	min(distinct TDEC.CDEC) over ()
+NULL	-1
+-1	-1
+0	-1
+0	-1
+1	-1
+10	-1
+select VDEC.CDEC, min(distinct VDEC.CDEC) over () from VDEC;
+CDEC	min(distinct VDEC.CDEC) over ()
+NULL	-1
+-1	-1
+0	-1
+0	-1
+1	-1
+10	-1
+select TDEC.CDEC, max(distinct TDEC.CDEC) over () from TDEC;
+CDEC	max(distinct TDEC.CDEC) over ()
+NULL	10
+-1	10
+0	10
+0	10
+1	10
+10	10
+select VDEC.CDEC, max(distinct VDEC.CDEC) over () from VDEC;
+CDEC	max(distinct VDEC.CDEC) over ()
+NULL	10
+-1	10
+0	10
+0	10
+1	10
+10	10
+#
+# These should be removed once support for them is added.
+#
+select TDEC.CDEC, count(distinct TDEC.CDEC) over () from TDEC;
+ERROR 42000: This version of MariaDB doesn't yet support 'COUNT(DISTINCT) aggregate as window function'
+select VDEC.CDEC, count(distinct VDEC.CDEC) over () from VDEC;
+ERROR 42000: This version of MariaDB doesn't yet support 'COUNT(DISTINCT) aggregate as window function'
+select TDEC.CDEC, sum(distinct TDEC.CDEC) over () from TDEC;
+ERROR 42000: This version of MariaDB doesn't yet support 'SUM(DISTINCT) aggregate as window function'
+select VDEC.CDEC, sum(distinct VDEC.CDEC) over () from VDEC;
+ERROR 42000: This version of MariaDB doesn't yet support 'SUM(DISTINCT) aggregate as window function'
+select TDEC.CDEC, avg(distinct TDEC.CDEC) over () from TDEC;
+ERROR 42000: This version of MariaDB doesn't yet support 'AVG(DISTINCT) aggregate as window function'
+select VDEC.CDEC, avg(distinct VDEC.CDEC) over () from VDEC;
+ERROR 42000: This version of MariaDB doesn't yet support 'AVG(DISTINCT) aggregate as window function'
+select TDEC.CDEC, GROUP_CONCAT(TDEC.CDEC) over () from TDEC;
+ERROR 42000: This version of MariaDB doesn't yet support 'GROUP_CONCAT() aggregate as window function'
+select VDEC.CDEC, GROUP_CONCAT(distinct VDEC.CDEC) over () from VDEC;
+ERROR 42000: This version of MariaDB doesn't yet support 'GROUP_CONCAT() aggregate as window function'
+drop table TDEC;
+drop view VDEC;
+#
+# MDEV-10700: 10.2.2 windowing function returns incorrect result
+#
+create table t(a int,b int, c int , d int);
+insert into t(a,b,c,d) values(1, rand(10)*1000, rand(10)*1000, rand(10)*1000);
+insert into t(a,b,c,d) values(1, rand(10)*1000, rand(10)*1000, rand(10)*1000);
+replace into t(a,b,c,d) select 1, rand(10)*1000, rand(10)*1000, rand(10)*1000 from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15, t t16, t t17;
+select count(distinct s) from (select sum(d) over(partition by a,b,c) as s from t) Z where s > 0;
+count(distinct s)
+993
+select count(distinct s) from (select sum(d) as s from t group by a,b,c) Z where s > 0;
+count(distinct s)
+993
+select count(distinct s) from (select sum(d) over(partition by a,b) as s from t) Z where s > 0;
+count(distinct s)
+993
+select count(distinct s) from (select sum(d) as s from t group by a,b) Z where s > 0;
+count(distinct s)
+993
+select count(distinct s) from (select sum(d) over(partition by a) as s from t) Z where s > 0;
+count(distinct s)
+1
+select count(distinct s) from (select sum(d) as s from t group by a) Z where s > 0;
+count(distinct s)
+1
+drop table t;
+#
+# MDEV-9924: window function in query with group by optimized away 
+#
+create table t1 (i int);
+insert into t1 values (2),(3),(1);
+select row_number() over () from t1 group by 1+2;
+row_number() over ()
+1
+select max(i), row_number() over () from t1 group by 1+2;
+max(i)	row_number() over ()
+3	1
+select rank() over (order by max(i)) from t1 group by 1+2;
+rank() over (order by max(i))
+1
+select i, row_number() over () from t1 group by 1+2;
+i	row_number() over ()
+2	1
+select i, rank() over (order by i) rnk from t1 group by 1+2;
+i	rnk
+2	1
+drop table t1;
+#
+# MDEV-11907: window function as the second operand of division
+#
+create table t1 (pk int, c int);
+insert into t1 values (1,1),(2,1),(3,1),(4,1),(5,2);
+set @sql_mode_save= @@sql_mode;
+set sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
+select pk, c, c/count(*) over 
+(partition by c order by pk
+rows between 1 preceding and 2 following) as CNT
+from t1;
+pk	c	CNT
+1	1	0.3333
+2	1	0.2500
+3	1	0.3333
+4	1	0.5000
+5	2	2.0000
+show warnings;
+Level	Code	Message
+set sql_mode=@sql_mode_save;
+drop table t1;
+#
+# MDEV-12336: several functions over a window function 
+#
+create table t1 (name varchar(10), cnt int);
+insert into t1 values ('Fred', 23), ('Fred', 35), ('Joe', 10);
+select q.name, q.row_cnt, 
+round( 100 * ( q.row_cnt /
+sum(q.row_cnt) over
+( 
+order by q.name 
+rows between
+unbounded preceding and
+unbounded following
+)
+),2
+) pct_of_total
+from 
+( 
+select name, count(*) row_cnt, sum(cnt) sum_cnt 
+from t1 
+group by 1
+) q;
+name	row_cnt	pct_of_total
+Fred	2	66.67
+Joe	1	33.33
+drop table t1;
+#
+# MDEV-11990: window function over min/max aggregation
+#
+create table t1 (id int);
+insert into t1 values (1), (2), (3), (2), (4), (2);
+select sum(max(id)) over (order by max(id)) from t1;
+sum(max(id)) over (order by max(id))
+4
+explain
+select sum(max(id)) over (order by max(id)) from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary
+create index idx on t1(id);
+select sum(max(id)) over (order by max(id)) from t1;
+sum(max(id)) over (order by max(id))
+4
+explain
+select sum(max(id)) over (order by max(id)) from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+select sum(max(id)) over (order by max(id)) from t1 where id < 3;
+sum(max(id)) over (order by max(id))
+2
+select count(max(id)) over (order by max(id)) from t1 where id < 3;
+count(max(id)) over (order by max(id))
+1
+select max(id), rank() over (order by max(id)) from t1 where id < 3;
+max(id)	rank() over (order by max(id))
+2	1
+drop table t1;
+#
+# main.win failure post MDEV-12336
+#
+create table t(a decimal(35,10), b int);
+insert into t values (1, 10), (2, 20), (3, 30);
+prepare stmt from "SELECT (CASE WHEN sum(t.a) over (partition by t.b)=1 THEN 1000 ELSE 300 END) AS a FROM t";
+execute stmt;
+a
+1000
+300
+300
+drop table t;
+#
+# MDEV-12851 case with window functions query crashes server
+#
+create table t1(dt datetime);
+insert into t1 values ('2017-05-17'), ('2017-05-18');
+select dt,
+case when (max(dt) over (order by dt rows between 1 following and 1 following) is null)
+then '9999-12-31 12:00:00'
+       else max(dt) over (order by dt rows between 1 following and 1 following)
+end x,
+case when (max(dt) over (order by dt rows between 1 following and 1 following) is not null)
+then '9999-12-31 12:00:00'
+       else max(dt) over (order by dt rows between 1 following and 1 following)
+end x
+from t1;
+dt	x	x
+2017-05-17 00:00:00	2017-05-18 00:00:00	9999-12-31 12:00:00
+2017-05-18 00:00:00	9999-12-31 12:00:00	NULL
+drop table t1;
+create table t1(i int);
+insert into t1 values (null),(1),(2);
+select max(i) over (order by i),
+max(i) over (order by i) is null,
+max(i) over (order by i) is not null
+from t1;
+max(i) over (order by i)	max(i) over (order by i) is null	max(i) over (order by i) is not null
+NULL	1	0
+1	0	1
+2	0	1
+drop table t1;
+#
+# MDEV-13189: Window functions crash when using INTERVAL function
+#
+create table t1(i int);
+insert into t1 values (1),(2),(10),(20),(30);
+select sum(i) over (order by i), interval(sum(i) over (order by i), 10, 20)
+from t1;
+sum(i) over (order by i)	interval(sum(i) over (order by i), 10, 20)
+1	0
+3	0
+13	1
+33	2
+63	2
+drop table t1;
+#
+# MDEV-13352: Server crashes in st_join_table::remove_duplicates
+#
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT DISTINCT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
+ROW_NUMBER() OVER()	i
+SELECT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
+ROW_NUMBER() OVER()	i
+DROP TABLE t1;
+#
+# MDEV-13344: Server crashes in in AGGR_OP::put_record on subquery 
+#             with window function and constant table
+# (Testcase only)
+#
+CREATE TABLE t1 (c CHAR(8)) ENGINE=MyISAM;
+INSERT IGNORE INTO t1 VALUES ('foo');
+SELECT ('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1);
+('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1)
+0
+DROP TABLE t1;
+#
+# MDEV-13351: Server crashes in st_select_lex::set_explain_type upon UNION with window function 
+#
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT Nth_value(i,1) OVER() FROM t1 
+UNION ALL 
+( SELECT Nth_value(i,2) OVER() FROM t1 LIMIT 0 )
+;
+Nth_value(i,1) OVER()
+1
+1
+DROP TABLE t1;
+#
+# A regression after MDEV-13351:
+# MDEV-13374 : Server crashes in first_linear_tab / st_select_lex::set_explain_type 
+#              upon UNION with aggregate function	
+#
+CREATE TABLE t1 (i INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+SELECT i AS fld FROM t1 UNION SELECT COUNT(*) AS fld FROM t1;
+fld
+1
+2
+DROP TABLE t1;
+#
+# MDEV-13240 Wrong warning with MAX(datetime_field) OVER (...)
+#
+CREATE TABLE t1 (dt DATETIME);
+INSERT INTO t1 VALUES ('2017-05-17');
+SELECT MAX(dt) OVER (ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) FROM t1;
+MAX(dt) OVER (ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
+NULL
+DROP TABLE t1;
+#
+# MDEV-13358 FIRST_VALUE throws SQL Error (1292): Incorrect datetime value
+#
+CREATE TABLE IF NOT EXISTS `fv_test` (
+`SOME_DATE` datetime NOT NULL
+);
+INSERT INTO `fv_test` (`SOME_DATE`) VALUES ('2017-07-20 12:47:56');
+CREATE TABLE fv_result
+SELECT
+FIRST_VALUE(SOME_DATE) OVER(ORDER BY SOME_DATE DESC) AS somedate
+FROM fv_test;
+SHOW CREATE TABLE fv_result;
+Table	Create Table
+fv_result	CREATE TABLE `fv_result` (
+  `somedate` datetime DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM fv_result;
+somedate
+2017-07-20 12:47:56
+DROP TABLE fv_test, fv_result;
+#
+# MDEV-13649: Server crashes in set_field_to_null_with_conversions or in Field::set_notnull
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (0),(1),(2);
+SELECT LEAD(a) OVER (PARTITION BY a ORDER BY a) as lead,
+a AND LEAD(a) OVER (PARTITION BY a ORDER BY a) AS a_and_lead_part
+FROM t1;
+lead	a_and_lead_part
+NULL	0
+NULL	NULL
+NULL	NULL
+SELECT a OR LEAD(a) OVER (ORDER BY a) AS a_or_lead_order
+FROM t1
+ORDER BY a;
+a_or_lead_order
+1
+1
+1
+SELECT a AND LEAD(a) OVER (ORDER BY a) AS a_and_lead_order
+FROM t1
+ORDER BY a;
+a_and_lead_order
+0
+1
+NULL
+SELECT a XOR LEAD(a) OVER (ORDER BY a) AS a_xor_lead_order
+FROM t1
+ORDER BY a;
+a_xor_lead_order
+1
+0
+NULL
+SELECT NOT LEAD(a) OVER (ORDER BY a) AS not_lead_order
+FROM t1
+ORDER BY a;
+not_lead_order
+0
+0
+NULL
+SELECT LEAD(a) OVER (ORDER BY a) is not null AS is_not_null_lead_order
+FROM t1
+ORDER BY a;
+is_not_null_lead_order
+1
+1
+0
+drop table t1;
+#
+# MDEV-13354: Server crashes in find_field_in_tables upon PS with window function and subquery
+#
+CREATE TABLE t1 (i INT, a char);
+INSERT INTO t1 VALUES (1, 'a'),(2, 'b');
+PREPARE stmt FROM "SELECT row_number() over (partition by i order by i), i FROM (SELECT * from t1) as sq";
+EXECUTE stmt;
+row_number() over (partition by i order by i)	i
+1	1
+1	2
+DROP TABLE t1;
+#
+# MDEV-13384: "window" seems like a reserved column name but it's not listed as one
+#
+# Currently we allow window as an identifier, except for table aliases.
+#
+CREATE TABLE door (id INT, window VARCHAR(10));
+SELECT id
+FROM door as window;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'window' at line 2
+SELECT id, window
+FROM door;
+id	window
+SELECT id, window
+FROM door as window;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'window' at line 2
+DROP TABLE door;
+#
+# MDEV-13352: Server crashes in st_join_table::remove_duplicates
+#
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT DISTINCT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
+ROW_NUMBER() OVER()	i
+SELECT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
+ROW_NUMBER() OVER()	i
+DROP TABLE t1;
+#
+# MDEV-15853: Assertion `tab->filesort_result == 0' failed
+#
+CREATE TABLE t1 ( a1 int);
+insert into t1 values (1),(2),(3);
+CREATE TABLE t2 (b1 int, a1 int, a2 int);
+insert into t2 values (1,2,3),(2,3,4),(3,4,5);
+SELECT COUNT(DISTINCT t2.a2),
+rank() OVER (ORDER BY t2.b1)
+FROM t2 ,t1 GROUP BY t2.b1 ORDER BY t1.a1;
+COUNT(DISTINCT t2.a2)	rank() OVER (ORDER BY t2.b1)
+1	1
+1	2
+1	3
+DROP TABLE t1,t2;
+#
+# MDEV-16990: server crashes in base_list_iterator::next
+#
+CREATE TABLE t1(i int);
+insert into t1 values (1),(2);
+SELECT DISTINCT row_number() OVER (), MAX(1) FROM t1;
+row_number() OVER ()	MAX(1)
+1	1
+SELECT DISTINCT BIT_AND(0)  OVER (), MAX(1) FROM t1;
+BIT_AND(0)  OVER ()	MAX(1)
+0	1
+drop table t1;
+#
+# MDEV-17525: Window functions not working in ONLY_FULL_GROUP_BY mode
+#
+CREATE TABLE t1 (name CHAR(10), test CHAR(10), score TINYINT);
+INSERT INTO t1 VALUES
+('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
+('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
+('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
+('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
+SET @save_sql_mode= @@sql_mode;
+SET sql_mode = 'ONLY_FULL_GROUP_BY';
+SELECT name, test, score,
+AVG(score) OVER (PARTITION BY test) AS average_by_test
+FROM t1
+ORDER BY test, name;
+name	test	score	average_by_test
+Chun	SQL	75	65.2500
+Esben	SQL	43	65.2500
+Kaolin	SQL	56	65.2500
+Tatiana	SQL	87	65.2500
+Chun	Tuning	73	68.7500
+Esben	Tuning	31	68.7500
+Kaolin	Tuning	88	68.7500
+Tatiana	Tuning	83	68.7500
+set @@sql_mode= @save_sql_mode;
+SELECT name, test, score,
+AVG(score) OVER (PARTITION BY test) AS average_by_test
+FROM t1
+ORDER BY test, name;
+name	test	score	average_by_test
+Chun	SQL	75	65.2500
+Esben	SQL	43	65.2500
+Kaolin	SQL	56	65.2500
+Tatiana	SQL	87	65.2500
+Chun	Tuning	73	68.7500
+Esben	Tuning	31	68.7500
+Kaolin	Tuning	88	68.7500
+Tatiana	Tuning	83	68.7500
+drop table t1;
+#
+# MDEV-12575: Server crash in AGGR_OP::put_record or in JOIN_CACHE::free
+# or Invalid write in JOIN::make_aggr_tables_info
+#
+SELECT DISTINCT BIT_OR(100) OVER () FROM dual
+GROUP BY LEFT('2018-08-24', 100)  order by 1+2;
+BIT_OR(100) OVER ()
+100
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT * FROM (
+SELECT
+ROW_NUMBER() OVER(), i, sum(i)
+FROM t1
+WHERE 1=0
+limit 0
+) AS sq;
+ROW_NUMBER() OVER()	i	sum(i)
+SELECT * FROM (
+SELECT
+ROW_NUMBER() OVER(), i, sum(i)
+FROM t1
+WHERE 1=0
+GROUP BY i
+) AS sq;
+ROW_NUMBER() OVER()	i	sum(i)
+drop table t1;
+create table t1 (a int);
+explain
+select distinct 1, row_number() over (order by 1) from t1 where a=0 group by a with rollup;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+select distinct 1, row_number() over (order by 1) from t1 where a=0 group by a with rollup;
+1	row_number() over (order by 1)
+drop table t1;
+explain
+SELECT DISTINCT BIT_OR(100) OVER () FROM dual
+GROUP BY LEFT('2018-08-24', 100) WITH ROLLUP
+HAVING @A := 'qwerty';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+SELECT DISTINCT BIT_OR(100) OVER () FROM dual
+GROUP BY LEFT('2018-08-24', 100) WITH ROLLUP
+HAVING @A := 'qwerty';
+BIT_OR(100) OVER ()
+explain
+SELECT DISTINCT BIT_OR(100) OVER () FROM dual
+GROUP BY LEFT('2018-08-24', 100)
+HAVING @A := 'qwerty';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+SELECT DISTINCT BIT_OR(100) OVER () FROM dual
+GROUP BY LEFT('2018-08-24', 100)
+HAVING @A := 'qwerty';
+BIT_OR(100) OVER ()
+create table t1 (a int);
+explain
+SELECT DISTINCT BIT_OR(100) OVER () FROM t1
+GROUP BY LEFT('2018-08-24', 100) having 1=1 limit 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Zero limit
+drop table t1;
+#
+# MDEV-13170: Database service (MySQL) stops after update with trigger
+#
+CREATE TABLE t1 ( t1_id int, point_id int, ml_id int,  UNIQUE KEY t1_ml_u (ml_id,point_id)) ;
+INSERT INTO t1 VALUES (1,1,8884),(2,1,8885);
+CREATE TABLE t2 ( db_time datetime, au_nr int, col_id int, new_val int);
+CREATE TABLE t3 (id1 int, id2 int, d1 int);
+CREATE  TRIGGER t1_aurtrg AFTER UPDATE ON t1 FOR EACH ROW begin
+CREATE OR REPLACE TEMPORARY TABLE trg_u AS
+WITH l AS
+(SELECT a.*,
+Max(t2.col_id) over (PARTITION BY a.d1),
+Max(t2.new_val) over (PARTITION BY a.d1)
+FROM
+(SELECT d1 , id1, id2  FROM t3) a
+JOIN t2  ON (a.d1=t2.db_time AND a.id1=t2.au_nr))
+SELECT 1;
+END;//
+update t1 set ml_id=8884 where point_id=1;
+ERROR 23000: Duplicate entry '8884-1' for key 't1_ml_u'
+update t1 set ml_id=8884 where point_id=1;
+ERROR 23000: Duplicate entry '8884-1' for key 't1_ml_u'
+drop table t1, t2,t3;
+CREATE TABLE t1 (i INT, a char);
+INSERT INTO t1 VALUES (1, 'a'),(2, 'b');
+create view v1 as select * from t1;
+PREPARE stmt FROM "SELECT i, row_number() over (partition by i order by i) FROM v1";
+execute stmt;
+i	row_number() over (partition by i order by i)
+1	1
+2	1
+deallocate prepare stmt;
+drop table t1;
+drop view v1;
+#
+# MDEV-17676: Assertion `inited==NONE || (inited==RND && scan)' failed in handler::ha_rnd_init
+#
+CREATE TABLE t1 (b1 text NOT NULL);
+INSERT INTO t1 VALUES  ('2'),('1');
+EXPLAIN
+SELECT DISTINCT MIN(b1) OVER () FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary
+SELECT DISTINCT MIN(b1) OVER () FROM t1;
+MIN(b1) OVER ()
+1
+drop table t1;
+#
+# MDEV-15424: Unreasonal SQL Error (1356) on select from view
+#
+create table t1 (id int, n1 int);
+insert into t1 values (1,1), (2,1), (3,2), (4,4);
+create view v1 as SELECT ifnull(max(n1) over (partition by n1),'aaa') FROM t1;
+explain select * from v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary
+select * from v1;
+ifnull(max(n1) over (partition by n1),'aaa')
+1
+1
+2
+4
+drop table t1;
+drop view v1;
+#
+# MDEV-18431: Select max + row_number giving incorrect result
+#
+create table t1 (id int, v int);
+insert into t1 values (1, 1), (1,2), (1,3), (2, 1), (2, 2);
+select e.id,
+(select max(t1.v) from t1 where t1.id=e.id) as a,
+row_number() over (partition by e.id order by e.v) as b,
+(select max(t1.v) from t1 where t1.id=e.id) + (row_number() over (partition by e.id order by e.v)) as sum_a_b
+from t1 e;
+id	a	b	sum_a_b
+1	3	1	4
+1	3	2	5
+1	3	3	6
+2	2	1	3
+2	2	2	4
+drop table t1;
+#
+# MDEV-15837: Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM'
+# failed in compare_order_elements function
+#
+CREATE TABLE t1 (a1 int);
+insert into t1 values (1),(2),(3);
+SELECT  rank() OVER (ORDER BY 1), ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4))) FROM t1;
+rank() OVER (ORDER BY 1)	ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4)))
+1	1
+1	2
+1	3
+drop table t1;
+#
+# MDEV-17781: Server crashes in next_linear_tab
+#
+CREATE TABLE t1 (i1 int);
+explain
+(SELECT AVG(0) OVER (), MAX('2') FROM t1)
+UNION ALL
+(SELECT AVG(0) OVER (), MAX('2') FROM t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+(SELECT AVG(0) OVER (), MAX('2') FROM t1)
+UNION ALL
+(SELECT AVG(0) OVER (), MAX('2') FROM t1);
+AVG(0) OVER ()	MAX('2')
+0.0000	NULL
+0.0000	NULL
+drop table t1;
+#
+# MDEV-14791: Crash with order by expression containing window functions
+#
+CREATE TABLE t1 (b1 int, b2 int);
+INSERT INTO t1 VALUES (1,1),(0,0);
+explain
+SELECT b1 from t1 order by row_number() over (ORDER BY b2) + 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+SELECT b1 from t1 order by row_number() over (ORDER BY b2) + 1;
+b1
+0
+1
+explain
+SELECT b1 from t1 order by row_number() over (ORDER BY b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+SELECT b1 from t1 order by row_number() over (ORDER BY b2);
+b1
+0
+1
+DROP TABLE t1;
+CREATE TABLE t1 (a int, b int, c int);
+INSERT INTO t1 VALUES (2,3,207), (1,21,909), (7,13,312), (8,64,248);
+explain
+SELECT * FROM t1 ORDER BY max(t1.a) over (partition by c);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
+SELECT * FROM t1 ORDER BY max(t1.a) over (partition by c);
+a	b	c
+1	21	909
+2	3	207
+7	13	312
+8	64	248
+explain
+SELECT max(t1.a) over (partition by c) as x, b, c from t1 order by max(t1.a) over (partition by c);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
+SELECT max(t1.a) over (partition by c) as x, b, c from t1 order by max(t1.a) over (partition by c);
+x	b	c
+1	21	909
+2	3	207
+7	13	312
+8	64	248
+drop table t1;
+#
+# MDEV-18373: DENSE_RANK is not calculated correctly
+#
+create table t1 (a int, b int);
+insert into t1 values (60, 1515),(60, 2000),(70, 2000),(55, 1600);
+select b, dense_rank() over (order by sum(a)) from t1 group by  b;
+b	dense_rank() over (order by sum(a))
+1515	2
+1600	1
+2000	3
+select b, dense_rank() over (order by sum(a)+1) from t1 group by  b;
+b	dense_rank() over (order by sum(a)+1)
+1515	2
+1600	1
+2000	3
+select b, row_number() over (partition by sum(a)) from t1 group by b;
+b	row_number() over (partition by sum(a))
+1515	1
+1600	1
+2000	1
+select b, row_number() over (partition by sum(a)+1) from t1 group by b;
+b	row_number() over (partition by sum(a)+1)
+1515	1
+1600	1
+2000	1
+drop table t1;
+#
+# MDEV-18015: Assertion `global_status_var.global_memory_used == 0' failed when using UDF,
+# window functions and views
+#
+create table t1 (id int, n1 int);
+insert into t1 values (1,1),(2,1),(3,2),(4,4);
+explain
+select max(n1) over (partition by 'abc') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary
+select max(n1) over (partition by 'abc') from t1;
+max(n1) over (partition by 'abc')
+4
+4
+4
+4
+explain
+select rank() over (partition by 'abc' order by 'xyz') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary
+select rank() over (partition by 'abc' order by 'xyz') from t1;
+rank() over (partition by 'abc' order by 'xyz')
+1
+1
+1
+1
+drop table t1;
+#
+# MDEV-19380: ASAN heap-use-after-free in Protocol::net_store_data
+#
+CREATE TABLE t1 (i int);
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT (SELECT MIN('foo') OVER() FROM t1 LIMIT 1) as x;
+x
+foo
+drop table t1;
+#
+# MDEV-16579: Wrong result of query using DISTINCT COUNT(*) OVER (*)
+#
+CREATE TABLE t1 (i int) ;
+INSERT INTO t1 VALUES (1),(0),(1),(2),(0),(1),(2),(1),(2);
+SELECT  DISTINCT COUNT(*) OVER (), MOD(MIN(i),2) FROM t1 GROUP BY i ;
+COUNT(*) OVER ()	MOD(MIN(i),2)
+3	0
+3	1
+drop table t1;
+#
+# MDEV-21318: Wrong results with window functions and implicit grouping
+#
+CREATE TABLE t1 (a INT);
+#
+#  With empty const table
+#  The expected result here is 1, NULL
+#
+explain
+SELECT row_number() over(), sum(1) FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	Const row not found; Using temporary
+SELECT row_number() over(), sum(1) FROM t1;
+row_number() over()	sum(1)
+1	NULL
+insert into t1 values (2);
+#
+#  Const table has 1 row, but still impossible where
+#  The expected result here is 1, NULL
+#
+EXPLAIN SELECT row_number() over(), sum(1) FROM t1 where a=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT row_number() over(), sum(1) FROM t1 where a=1;
+row_number() over()	sum(1)
+1	NULL
+#
+#  Impossible HAVING
+#  Empty result is expected
+#
+EXPLAIN SELECT row_number() over(), sum(1) FROM t1 where a=1 having 1=0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+SELECT row_number() over(), sum(1) FROM t1 where a=1 having 1=0;
+row_number() over()	sum(1)
+#
+#  const table has 1 row, no impossible where
+#  The expected result here is 1, 2
+#
+EXPLAIN SELECT row_number() over(), sum(a) FROM t1 where a=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	Using temporary
+SELECT row_number() over(), sum(a) FROM t1 where a=2;
+row_number() over()	sum(a)
+1	2
+drop table t1;
+#
+# Impossible Where
+#
+create table t1(a int);
+insert into t1 values (1);
+#
+# Expected result is NULL, 0, NULL
+#
+EXPLAIN SELECT MAX(a) OVER (), COUNT(a), abs(a)  FROM t1 WHERE FALSE;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+SELECT MAX(a) OVER (), COUNT(a), abs(a)  FROM t1 WHERE FALSE;
+MAX(a) OVER ()	COUNT(a)	abs(a)
+NULL	0	NULL
+#
+# Expected result is 1, 0, NULL
+#
+EXPLAIN
+SELECT MAX(1) OVER (), COUNT(a), abs(a)  FROM t1 WHERE FALSE;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+SELECT MAX(1) OVER (), COUNT(a), abs(a)  FROM t1 WHERE FALSE;
+MAX(1) OVER ()	COUNT(a)	abs(a)
+1	0	NULL
+drop table t1;
+#
+# MDEV-22461: JOIN::make_aggr_tables_info(): Assertion `select_options & (1ULL << 17)' failed.
+#
+CREATE TEMPORARY TABLE t0 (a INT PRIMARY KEY ) ;
+INSERT INTO t0 VALUES (1),(2),(3);
+SELECT a FROM t0
+WHERE a < 8
+GROUP BY 1.5
+WINDOW v2 AS ( PARTITION BY a ORDER BY a DESC );
+a
+1
+SELECT a, ROW_NUMBER() OVER v2
+FROM t0
+WHERE a < 8
+GROUP BY 1.5
+WINDOW v2 AS ( PARTITION BY a ORDER BY a DESC );
+a	ROW_NUMBER() OVER v2
+1	1
+drop table t0;
+#
+# MDEV-16230:Server crashes when Analyze format=json is run with a window function with
+# empty PARTITION BY and ORDER BY clauses
+#
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
+ANALYZE FORMAT=JSON SELECT row_number() OVER() FROM t1;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "window_functions_computation": {
+      "sorts": {
+        "filesort": {
+          "sort_key": "`row_number() OVER()`",
+          "r_loops": 1,
+          "r_total_time_ms": "REPLACED",
+          "r_used_priority_queue": false,
+          "r_output_rows": 3,
+          "r_buffer_size": "REPLACED"
+        }
+      },
+      "temporary_table": {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "r_loops": 1,
+          "rows": 3,
+          "r_rows": 3,
+          "r_total_time_ms": "REPLACED",
+          "filtered": 100,
+          "r_filtered": 100
+        }
+      }
+    }
+  }
+}
+SELECT row_number() OVER() FROM t1;
+row_number() OVER()
+1
+2
+3
+DROP TABLE t1;
+#
+# MDEV-22984: Throw an error when arguments to window functions are window functions
+#
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+SELECT NTILE(MAX(a) OVER (PARTITION BY a)) OVER (PARTITION BY a ORDER BY b) FROM t1;
+ERROR HY000: Window functions can not be used as arguments to group functions.
+SELECT FIRST_VALUE(MAX(a) OVER (PARTITION BY a)) OVER (ORDER BY a) AS x FROM t1 GROUP BY a;
+ERROR HY000: Window functions can not be used as arguments to group functions.
+DROP TABLE t1;
+#
+# MDEV-12059: Assertion `precision > 0' failed with a window function or window aggregate function
+#
+CREATE TABLE t1 (d DECIMAL(1,0) UNSIGNED);
+INSERT INTO t1 VALUES (1),(2);
+SELECT MIN(d) OVER () FROM t1;
+MIN(d) OVER ()
+1
+1
+DROP TABLE t1;
+#
+# MDEV-22463: Element_type &Bounds_checked_array<Item *>::operator[](size_t) [Element_type = Item *]:
+# Assertion `n < m_size' failed
+#
+CREATE TABLE t1 (a INT, b INT, c INT, d INT, e INT, f INT, g int, h INT, i INT);
+INSERT INTO t1 SELECT seq,seq,seq,seq, seq,seq,seq,seq,seq FROM seq_1_to_5;
+SELECT ROW_NUMBER() OVER w2 FROM t1 WINDOW w2 AS (PARTITION BY -1,0,1,2,3,4,5,6);
+ROW_NUMBER() OVER w2
+1
+2
+3
+4
+5
+SELECT a FROM t1 ORDER BY ROW_NUMBER() OVER (PARTITION BY -1,1,0,2,3,4,5,6,7,8);
+a
+1
+2
+3
+4
+5
+SELECT a,b FROM t1 WINDOW w2 AS (PARTITION BY -1,1,0,2,3,4);
+a	b
+1	1
+2	2
+3	3
+4	4
+5	5
+SELECT ROW_NUMBER() OVER w2 FROM t1 WINDOW w2 AS (PARTITION BY -1,0,1,2,3,4,5,6);
+ROW_NUMBER() OVER w2
+1
+2
+3
+4
+5
+DROP TABLE t1;
+#
+# MDEV-18916: crash in Window_spec::print_partition() with decimals
+#
+SELECT cast((rank() over w1) as decimal (53,56));
+ERROR 42000: Too big scale 56 specified for 'rank() over w1'. Maximum is 38
+SELECT cast((rank() over w1) as decimal (53,30));
+ERROR HY000: Window specification with name 'w1' is not defined
+#
+# MDEV-15180: server crashed with NTH_VALUE()
+#
+CREATE TABLE t1 (i1 int, a int);
+INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3);
+CREATE TABLE t2 (i2 int);
+INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3);
+CREATE VIEW v1 AS (SELECT * FROM t1,t2 WHERE t1.i1=t2.i2) ;
+SELECT NTH_VALUE(i1, i1) OVER (PARTITION BY i1) FROM v1;
+NTH_VALUE(i1, i1) OVER (PARTITION BY i1)
+1
+1
+NULL
+NULL
+DROP VIEW v1;
+DROP TABLE t1,t2;
+#
+# End of 10.2 tests
+#
+#
+# MDEV-16489 when lead() returns null on a datetime field, the result is treated as the literal string '[NULL]'
+#
+CREATE TABLE t1 (d datetime);
+INSERT INTO t1 VALUES ('2018-01-01 00:00:00'),('2018-02-01 00:00:00');
+SELECT *, LEAD(d) OVER (ORDER BY d) AS x FROM t1;
+d	x
+2018-01-01 00:00:00	2018-02-01 00:00:00
+2018-02-01 00:00:00	NULL
+DROP TABLE t1;
+CREATE TABLE t1 (d time);
+INSERT INTO t1 VALUES ('00:00:01'),('00:00:02');
+SELECT *, LEAD(d) OVER (ORDER BY d) AS x FROM t1;
+d	x
+00:00:01	00:00:02
+00:00:02	NULL
+DROP TABLE t1;
+#
+# MDEV-20351 Window function BIT_OR() OVER (..) return a wrong data type
+#
+CREATE TABLE t1 (pk INT, a INT, b BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (1, 0, 1), (2, 0, 18446744073709551615);
+CREATE TABLE t2 AS
+SELECT pk, a, bit_or(b) AS bit_or FROM t1 GROUP BY pk;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `pk` int(11) DEFAULT NULL,
+  `a` int(11) DEFAULT NULL,
+  `bit_or` bigint(21) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+pk	a	b
+1	0	1
+2	0	18446744073709551615
+DROP TABLE t2;
+CREATE OR REPLACE TABLE t2 AS
+SELECT pk, a, BIT_OR(b) OVER (PARTITION BY a ORDER BY pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS bit_or
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `pk` int(11) DEFAULT NULL,
+  `a` int(11) DEFAULT NULL,
+  `bit_or` bigint(21) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t2;
+pk	a	bit_or
+1	0	18446744073709551615
+2	0	18446744073709551615
+DROP TABLE t2;
+DROP TABLE t1;
+#
+# End of 10.3 tests
+#
+#
+# MDEV-16722: Assertion `type() != NULL_ITEM' failed
+#
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+SELECT  row_number() OVER (order by a) FROM t1  order by NAME_CONST('myname',NULL);
+row_number() OVER (order by a)
+1
+2
+3
+drop table t1;
+#
+# MDEV-23867: select crash in compute_window_func
+#
+set @save_sort_buffer_size=@@sort_buffer_size;
+set sort_buffer_size= 2000;
+CREATE TABLE t1( a INT, b INT, c INT);
+INSERT INTO t1 select seq, seq, seq from seq_1_to_5000;
+CREATE TABLE t2( a INT, b INT, c INT);
+INSERT INTO t2 SELECT a, b, ROW_NUMBER() OVER (PARTITION BY b) FROM t1;
+SELECT COUNT(*), MAX(c) FROM t2;
+COUNT(*)	MAX(c)
+5000	1
+CREATE TABLE t3( a INT, b INT, c INT);
+INSERT INTO t3 SELECT a, b, SUM(a) OVER () FROM t1;
+SELECT COUNT(*), MAX(c) FROM t3;
+COUNT(*)	MAX(c)
+5000	12502500
+set @@sort_buffer_size=@save_sort_buffer_size;
+DROP TABLE t1,t2,t3;
+# end of 10.2 test
 #
 # MDEV-22556: Incorrect result for window function when using encrypt-tmp-files=ON
 #
@@ -16,3 +3972,4 @@ count(*)
 5000
 set @@sort_buffer_size=@save_sort_buffer_size;
 drop table t1;
+# End of 10.4 tests
diff --git a/mysql-test/suite/encryption/t/tempfiles_encrypted.test b/mysql-test/suite/encryption/t/tempfiles_encrypted.test
index 96b981c1c06..7628442f884 100644
--- a/mysql-test/suite/encryption/t/tempfiles_encrypted.test
+++ b/mysql-test/suite/encryption/t/tempfiles_encrypted.test
@@ -5,9 +5,31 @@
 source include/have_file_key_management_plugin.inc;
 source include/have_sequence.inc;
 source include/have_innodb.inc;
- 
+
 select @@encrypt_tmp_files;
 
+--source main/win.test
+
+--echo #
+--echo # MDEV-23867: select crash in compute_window_func
+--echo #
+
+set @save_sort_buffer_size=@@sort_buffer_size;
+
+set sort_buffer_size= 2000;
+CREATE TABLE t1( a INT, b INT, c INT);
+INSERT INTO t1 select seq, seq, seq from seq_1_to_5000;
+CREATE TABLE t2( a INT, b INT, c INT);
+INSERT INTO t2 SELECT a, b, ROW_NUMBER() OVER (PARTITION BY b) FROM t1;
+SELECT COUNT(*), MAX(c) FROM t2;
+CREATE TABLE t3( a INT, b INT, c INT);
+INSERT INTO t3 SELECT a, b, SUM(a) OVER () FROM t1;
+SELECT COUNT(*), MAX(c) FROM t3;
+set @@sort_buffer_size=@save_sort_buffer_size;
+DROP TABLE t1,t2,t3;
+
+--echo # end of 10.2 test
+
 --echo #
 --echo # MDEV-22556: Incorrect result for window function when using encrypt-tmp-files=ON
 --echo #
@@ -21,3 +43,5 @@ select count(*) from (select a, b, c, ROW_NUMBER() OVER (PARTITION BY a) FROM t1
 set @@sort_buffer_size=@save_sort_buffer_size;
  
 drop table t1;
+
+--echo # End of 10.4 tests
diff --git a/mysql-test/suite/galera/r/MDEV-22707.result b/mysql-test/suite/galera/r/MDEV-22707.result
new file mode 100644
index 00000000000..a74844d7e12
--- /dev/null
+++ b/mysql-test/suite/galera/r/MDEV-22707.result
@@ -0,0 +1,26 @@
+CREATE TABLE t1(f2 INT) ENGINE=InnoDB;
+connect node_1_applier_thd, 127.0.0.1, root, , test, $NODE_MYPORT_1;
+SET GLOBAL debug_dbug = "+d,sync.wsrep_apply_cb";
+connection node_2;
+SET SESSION wsrep_sync_wait = 0;
+INSERT INTO t1 (f2) VALUES (2);
+connection node_1_applier_thd;
+SET SESSION DEBUG_SYNC = "now WAIT_FOR sync.wsrep_apply_cb_reached";
+connection node_1;
+SET SESSION wsrep_sync_wait = 0;
+SET DEBUG_SYNC = 'wsrep_before_replication SIGNAL wsrep_before_replication_reached WAIT_FOR continue';
+INSERT INTO t1 (f2) VALUES (1);
+connect node_1_flush_thd, 127.0.0.1, root, , test, $NODE_MYPORT_1;
+SET DEBUG_SYNC="now WAIT_FOR wsrep_before_replication_reached";
+SET GLOBAL debug_dbug = "+d,sync.wsrep_before_mdl_wait";
+FLUSH TABLES;
+connect node_1_sync_release_thd, 127.0.0.1, root, , test, $NODE_MYPORT_1;
+SET GLOBAL debug_dbug = "";
+SET DEBUG_SYNC = "now SIGNAL signal.wsrep_before_mdl_wait";
+SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_cb";
+SET DEBUG_SYNC = "now SIGNAL continue";
+SET DEBUG_SYNC = "RESET";
+connection node_1;
+connection node_1_flush_thd;
+connection node_2;
+DROP TABLE t1;
diff --git a/mysql-test/suite/galera/t/MDEV-22707.test b/mysql-test/suite/galera/t/MDEV-22707.test
new file mode 100644
index 00000000000..19755f7c5ac
--- /dev/null
+++ b/mysql-test/suite/galera/t/MDEV-22707.test
@@ -0,0 +1,51 @@
+#
+# MDEV-22707 galera got stuck after flush tables
+#
+
+--source include/galera_cluster.inc
+--source include/have_innodb.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+
+CREATE TABLE t1(f2 INT) ENGINE=InnoDB;
+
+--connect node_1_applier_thd, 127.0.0.1, root, , test, $NODE_MYPORT_1
+SET GLOBAL debug_dbug = "+d,sync.wsrep_apply_cb";
+
+--connection node_2
+SET SESSION wsrep_sync_wait = 0;
+--send INSERT INTO t1 (f2) VALUES (2)
+
+--connection node_1_applier_thd
+# Wait for `sync.wsrep_apply_cb_reached` signal
+SET SESSION DEBUG_SYNC = "now WAIT_FOR sync.wsrep_apply_cb_reached";
+
+--connection node_1
+SET SESSION wsrep_sync_wait = 0;
+SET DEBUG_SYNC = 'wsrep_before_replication SIGNAL wsrep_before_replication_reached WAIT_FOR continue';
+--send INSERT INTO t1 (f2) VALUES (1)
+
+--connect node_1_flush_thd, 127.0.0.1, root, , test, $NODE_MYPORT_1
+SET DEBUG_SYNC="now WAIT_FOR wsrep_before_replication_reached";
+SET GLOBAL debug_dbug = "+d,sync.wsrep_before_mdl_wait";
+--send FLUSH TABLES
+
+--connect node_1_sync_release_thd, 127.0.0.1, root, , test, $NODE_MYPORT_1
+# First clear all DBUG points
+SET GLOBAL debug_dbug = "";
+# Now signal threads to continue execution
+SET DEBUG_SYNC = "now SIGNAL signal.wsrep_before_mdl_wait";
+SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_cb";
+SET DEBUG_SYNC = "now SIGNAL continue";
+SET DEBUG_SYNC = "RESET";
+
+--connection node_1
+--reap
+
+--connection node_1_flush_thd
+--reap
+
+--connection node_2
+--reap
+
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/include/wait_all_purged.inc b/mysql-test/suite/innodb/include/wait_all_purged.inc
index 992e14f0843..e3a506c7622 100644
--- a/mysql-test/suite/innodb/include/wait_all_purged.inc
+++ b/mysql-test/suite/innodb/include/wait_all_purged.inc
@@ -1,25 +1,17 @@
 # Wait for everything to be purged.
 # The user should have set innodb_purge_rseg_truncate_frequency=1.
 
+--disable_query_log
 if (!$wait_all_purged)
 {
-  let $wait_all_purged= 0;
+  SET GLOBAL innodb_max_purge_lag_wait= 0;
 }
-let $remaining_expect= `select concat('InnoDB		',$wait_all_purged)`;
-
-let $wait_counter= 600;
-while ($wait_counter)
+if ($wait_all_purged)
 {
-    --replace_regex /.*History list length ([0-9]+).*/\1/
-    let $remaining= `SHOW ENGINE INNODB STATUS`;
-    if ($remaining == $remaining_expect)
-    {
-        let $wait_counter= 0;
-    }
-    if ($wait_counter)
-    {
-        real_sleep 0.1;
-        dec $wait_counter;
-    }
+  eval SET GLOBAL innodb_max_purge_lag_wait= $wait_all_purged;
 }
+--enable_query_log
+
+--replace_regex /.*History list length ([0-9]+).*/\1/
+let $remaining= `SHOW ENGINE INNODB STATUS`;
 echo $remaining transactions not purged;
diff --git a/mysql-test/suite/innodb/r/innodb-virtual-columns.result b/mysql-test/suite/innodb/r/innodb-virtual-columns.result
index 4b2df7eb287..484c9b30bd6 100644
--- a/mysql-test/suite/innodb/r/innodb-virtual-columns.result
+++ b/mysql-test/suite/innodb/r/innodb-virtual-columns.result
@@ -34,7 +34,7 @@ grad_degree	CREATE TABLE `grad_degree` (
   `plan` varchar(10) NOT NULL,
   `admit_term` char(4) NOT NULL,
   `wdraw_rsn` varchar(4) NOT NULL DEFAULT '',
-  `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed' else 'Not Completed' end) VIRTUAL,
+  `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed' else 'Not Completed' end) VIRTUAL,
   `deg_start_term` char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data',
   `deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term',
   PRIMARY KEY (`student_id`,`plan`,`admit_term`)
@@ -142,14 +142,14 @@ grad_degree	CREATE TABLE `grad_degree` (
   `plan` varchar(10) NOT NULL,
   `admit_term` char(4) NOT NULL,
   `wdraw_rsn` varchar(4) NOT NULL DEFAULT '',
-  `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed' else 'Not Completed' end) VIRTUAL,
-  `ofis_deg_status2` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress2' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed2' else 'Not Completed2' end) VIRTUAL,
-  `ofis_deg_status3` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress3' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed3' else 'Not Completed3' end) VIRTUAL,
-  `ofis_deg_status4` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress4' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed4' else 'Not Completed4' end) VIRTUAL,
-  `ofis_deg_status5` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress5' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed5' else 'Not Completed5' end) VIRTUAL,
-  `ofis_deg_status6` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress6' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed6' else 'Not Completed6' end) VIRTUAL,
-  `ofis_deg_status7` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress7' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed7' else 'Not Completed7' end) VIRTUAL,
-  `ofis_deg_status8` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress8' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed8' else 'Not Completed8' end) VIRTUAL,
+  `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed' else 'Not Completed' end) VIRTUAL,
+  `ofis_deg_status2` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress2' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed2' else 'Not Completed2' end) VIRTUAL,
+  `ofis_deg_status3` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress3' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed3' else 'Not Completed3' end) VIRTUAL,
+  `ofis_deg_status4` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress4' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed4' else 'Not Completed4' end) VIRTUAL,
+  `ofis_deg_status5` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress5' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed5' else 'Not Completed5' end) VIRTUAL,
+  `ofis_deg_status6` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress6' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed6' else 'Not Completed6' end) VIRTUAL,
+  `ofis_deg_status7` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress7' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed7' else 'Not Completed7' end) VIRTUAL,
+  `ofis_deg_status8` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress8' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed8' else 'Not Completed8' end) VIRTUAL,
   `deg_start_term` char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data',
   `deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term',
   PRIMARY KEY (`student_id`,`plan`,`admit_term`)
@@ -199,14 +199,14 @@ grad_degree	CREATE TABLE `grad_degree` (
   `plan` varchar(10) NOT NULL,
   `admit_term` char(4) NOT NULL,
   `wdraw_rsn` varchar(4) NOT NULL DEFAULT '',
-  `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed' else 'Not Completed' end) VIRTUAL,
-  `ofis_deg_status2` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress2' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed2' else 'Not Completed2' end) VIRTUAL,
-  `ofis_deg_status3` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress3' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed3' else 'Not Completed3' end) VIRTUAL,
-  `ofis_deg_status4` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress4' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed4' else 'Not Completed4' end) VIRTUAL,
-  `ofis_deg_status5` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress5' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed5' else 'Not Completed5' end) VIRTUAL,
-  `ofis_deg_status6` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress6' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed6' else 'Not Completed6' end) VIRTUAL,
-  `ofis_deg_status7` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress7' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed7' else 'Not Completed7' end) VIRTUAL,
-  `ofis_deg_status8` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress8' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed8' else 'Not Completed8' end) VIRTUAL,
+  `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed' else 'Not Completed' end) VIRTUAL,
+  `ofis_deg_status2` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress2' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed2' else 'Not Completed2' end) VIRTUAL,
+  `ofis_deg_status3` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress3' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed3' else 'Not Completed3' end) VIRTUAL,
+  `ofis_deg_status4` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress4' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed4' else 'Not Completed4' end) VIRTUAL,
+  `ofis_deg_status5` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress5' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed5' else 'Not Completed5' end) VIRTUAL,
+  `ofis_deg_status6` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress6' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed6' else 'Not Completed6' end) VIRTUAL,
+  `ofis_deg_status7` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress7' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed7' else 'Not Completed7' end) VIRTUAL,
+  `ofis_deg_status8` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress8' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed8' else 'Not Completed8' end) VIRTUAL,
   `deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term',
   PRIMARY KEY (`student_id`,`plan`,`admit_term`),
   KEY `grad_degree_as_of_term_ndx` (`deg_as_of_term`)
@@ -278,14 +278,14 @@ grad_degree	CREATE TABLE `grad_degree` (
   `plan` varchar(10) NOT NULL,
   `admit_term` char(4) NOT NULL,
   `wdraw_rsn` varchar(4) NOT NULL DEFAULT '',
-  `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed' else 'Not Completed' end) VIRTUAL,
-  `ofis_deg_status2` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress2' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed2' else 'Not Completed2' end) VIRTUAL,
-  `ofis_deg_status3` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress3' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed3' else 'Not Completed3' end) VIRTUAL,
-  `ofis_deg_status4` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress4' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed4' else 'Not Completed4' end) VIRTUAL,
-  `ofis_deg_status5` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress5' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed5' else 'Not Completed5' end) VIRTUAL,
-  `ofis_deg_status6` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress6' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed6' else 'Not Completed6' end) VIRTUAL,
-  `ofis_deg_status7` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress7' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed7' else 'Not Completed7' end) VIRTUAL,
-  `ofis_deg_status8` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress8' when (`wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC') then 'Completed8' else 'Not Completed8' end) VIRTUAL,
+  `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed' else 'Not Completed' end) VIRTUAL,
+  `ofis_deg_status2` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress2' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed2' else 'Not Completed2' end) VIRTUAL,
+  `ofis_deg_status3` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress3' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed3' else 'Not Completed3' end) VIRTUAL,
+  `ofis_deg_status4` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress4' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed4' else 'Not Completed4' end) VIRTUAL,
+  `ofis_deg_status5` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress5' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed5' else 'Not Completed5' end) VIRTUAL,
+  `ofis_deg_status6` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress6' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed6' else 'Not Completed6' end) VIRTUAL,
+  `ofis_deg_status7` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress7' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed7' else 'Not Completed7' end) VIRTUAL,
+  `ofis_deg_status8` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress8' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed8' else 'Not Completed8' end) VIRTUAL,
   `deg_start_term` char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data',
   `deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term',
   PRIMARY KEY (`student_id`,`plan`,`admit_term`)
diff --git a/mysql-test/suite/innodb/r/instant_alter_crash.result b/mysql-test/suite/innodb/r/instant_alter_crash.result
index cfcb24f8bb2..ef0e310492a 100644
--- a/mysql-test/suite/innodb/r/instant_alter_crash.result
+++ b/mysql-test/suite/innodb/r/instant_alter_crash.result
@@ -40,6 +40,7 @@ ALTER TABLE t2 DROP COLUMN c3, ADD COLUMN c5 TEXT DEFAULT 'naturam abhorrere';
 connection default;
 SET DEBUG_SYNC='now WAIT_FOR ddl';
 SET GLOBAL innodb_flush_log_at_trx_commit=1;
+SET debug_dbug='+d,dict_sys_mutex_avoid';
 UPDATE t1 SET c2=c2+1;
 # Kill the server
 disconnect ddl;
@@ -68,6 +69,7 @@ ALTER TABLE t2 ADD COLUMN (c4 TEXT NOT NULL DEFAULT ' et malorum');
 connection default;
 SET DEBUG_SYNC='now WAIT_FOR ddl';
 SET GLOBAL innodb_flush_log_at_trx_commit=1;
+SET debug_dbug='+d,dict_sys_mutex_avoid';
 DELETE FROM t1;
 # Kill the server
 disconnect ddl;
diff --git a/mysql-test/suite/innodb/r/instant_alter_purge,release.rdiff b/mysql-test/suite/innodb/r/instant_alter_purge,release.rdiff
index 53d2be18f9c..8722b724c6e 100644
--- a/mysql-test/suite/innodb/r/instant_alter_purge,release.rdiff
+++ b/mysql-test/suite/innodb/r/instant_alter_purge,release.rdiff
@@ -1,6 +1,6 @@
 --- instant_alter_purge.result
 +++ instant_alter_purge,release.result
-@@ -32,15 +32,11 @@
+@@ -32,16 +32,11 @@
  START TRANSACTION WITH CONSISTENT SNAPSHOT;
  connection default;
  DELETE FROM t1;
@@ -9,7 +9,8 @@
  connection purge_control;
 -SET DEBUG_SYNC='now WAIT_FOR go';
  COMMIT;
- InnoDB		0 transactions not purged
+ SET GLOBAL innodb_max_purge_lag_wait= 0;
+-InnoDB		0 transactions not purged
 -SET DEBUG_SYNC='now SIGNAL do';
  disconnect purge_control;
  connection default;
diff --git a/mysql-test/suite/innodb/r/instant_alter_purge.result b/mysql-test/suite/innodb/r/instant_alter_purge.result
index a3643610f04..8adae3d645f 100644
--- a/mysql-test/suite/innodb/r/instant_alter_purge.result
+++ b/mysql-test/suite/innodb/r/instant_alter_purge.result
@@ -37,6 +37,7 @@ ALTER TABLE t1 ADD COLUMN f3 INT;
 connection purge_control;
 SET DEBUG_SYNC='now WAIT_FOR go';
 COMMIT;
+SET GLOBAL innodb_max_purge_lag_wait= 0;
 InnoDB		0 transactions not purged
 SET DEBUG_SYNC='now SIGNAL do';
 disconnect purge_control;
diff --git a/mysql-test/suite/innodb/r/stats_persistent.result b/mysql-test/suite/innodb/r/stats_persistent.result
new file mode 100644
index 00000000000..3ae10e31cb3
--- /dev/null
+++ b/mysql-test/suite/innodb/r/stats_persistent.result
@@ -0,0 +1,19 @@
+#
+# MDEV-23991 dict_table_stats_lock() has unnecessarily long scope
+#
+CREATE TABLE t1(a INT) ENGINE=INNODB STATS_PERSISTENT=1;
+SET DEBUG_SYNC='dict_stats_update_persistent SIGNAL stop WAIT_FOR go';
+ANALYZE TABLE t1;
+connect con1, localhost, root;
+SET DEBUG_SYNC='now WAIT_FOR stop';
+SELECT ENGINE,SUM(DATA_LENGTH+INDEX_LENGTH),COUNT(ENGINE),SUM(DATA_LENGTH),SUM(INDEX_LENGTH) FROM information_schema.TABLES WHERE ENGINE='InnoDB';
+ENGINE	SUM(DATA_LENGTH+INDEX_LENGTH)	COUNT(ENGINE)	SUM(DATA_LENGTH)	SUM(INDEX_LENGTH)
+InnoDB	114688	4	65536	49152
+SET DEBUG_SYNC='now SIGNAL go';
+disconnect con1;
+connection default;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/t/instant_alter_crash.test b/mysql-test/suite/innodb/t/instant_alter_crash.test
index 13ff292d9ff..f9b18aa589f 100644
--- a/mysql-test/suite/innodb/t/instant_alter_crash.test
+++ b/mysql-test/suite/innodb/t/instant_alter_crash.test
@@ -55,6 +55,7 @@ ALTER TABLE t2 DROP COLUMN c3, ADD COLUMN c5 TEXT DEFAULT 'naturam abhorrere';
 connection default;
 SET DEBUG_SYNC='now WAIT_FOR ddl';
 SET GLOBAL innodb_flush_log_at_trx_commit=1;
+SET debug_dbug='+d,dict_sys_mutex_avoid';
 UPDATE t1 SET c2=c2+1;
 
 --source include/kill_mysqld.inc
@@ -83,6 +84,7 @@ ALTER TABLE t2 ADD COLUMN (c4 TEXT NOT NULL DEFAULT ' et malorum');
 connection default;
 SET DEBUG_SYNC='now WAIT_FOR ddl';
 SET GLOBAL innodb_flush_log_at_trx_commit=1;
+SET debug_dbug='+d,dict_sys_mutex_avoid';
 DELETE FROM t1;
 
 --source include/kill_mysqld.inc
diff --git a/mysql-test/suite/innodb/t/instant_alter_purge.test b/mysql-test/suite/innodb/t/instant_alter_purge.test
index 152d200d977..5ccce2ad1d6 100644
--- a/mysql-test/suite/innodb/t/instant_alter_purge.test
+++ b/mysql-test/suite/innodb/t/instant_alter_purge.test
@@ -59,8 +59,9 @@ if ($have_debug) {
 SET DEBUG_SYNC='now WAIT_FOR go';
 }
 COMMIT;
---source include/wait_all_purged.inc
+SET GLOBAL innodb_max_purge_lag_wait= 0;
 if ($have_debug) {
+--source include/wait_all_purged.inc
 SET DEBUG_SYNC='now SIGNAL do';
 }
 disconnect purge_control;
diff --git a/mysql-test/suite/innodb/t/stats_persistent.test b/mysql-test/suite/innodb/t/stats_persistent.test
new file mode 100644
index 00000000000..ac412d56d0d
--- /dev/null
+++ b/mysql-test/suite/innodb/t/stats_persistent.test
@@ -0,0 +1,27 @@
+--source include/have_innodb.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+--source include/count_sessions.inc
+
+--echo #
+--echo # MDEV-23991 dict_table_stats_lock() has unnecessarily long scope
+--echo #
+CREATE TABLE t1(a INT) ENGINE=INNODB STATS_PERSISTENT=1;
+
+SET DEBUG_SYNC='dict_stats_update_persistent SIGNAL stop WAIT_FOR go';
+--send ANALYZE TABLE t1
+
+--connect(con1, localhost, root)
+SET DEBUG_SYNC='now WAIT_FOR stop';
+
+SELECT ENGINE,SUM(DATA_LENGTH+INDEX_LENGTH),COUNT(ENGINE),SUM(DATA_LENGTH),SUM(INDEX_LENGTH) FROM information_schema.TABLES WHERE ENGINE='InnoDB';
+
+SET DEBUG_SYNC='now SIGNAL go';
+--disconnect con1
+
+--connection default
+--reap
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE t1;
+
+--source include/wait_until_count_sessions.inc
diff --git a/mysql-test/suite/mariabackup/ddl_incremental_encrypted.opt b/mysql-test/suite/mariabackup/ddl_incremental_encrypted.opt
new file mode 100644
index 00000000000..1de4aa13350
--- /dev/null
+++ b/mysql-test/suite/mariabackup/ddl_incremental_encrypted.opt
@@ -0,0 +1,7 @@
+--plugin-load-add=$FILE_KEY_MANAGEMENT_SO
+--innodb-file-per-table
+--innodb-encryption-threads=4
+--innodb-encrypt-tables
+--innodb-encrypt-log
+--loose-file-key-management
+--loose-file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys.txt
diff --git a/mysql-test/suite/mariabackup/ddl_incremental_encrypted.result b/mysql-test/suite/mariabackup/ddl_incremental_encrypted.result
new file mode 100644
index 00000000000..f42b6469db6
--- /dev/null
+++ b/mysql-test/suite/mariabackup/ddl_incremental_encrypted.result
@@ -0,0 +1,26 @@
+SET @old_innodb_log_optimize_ddl=@@global.innodb_log_optimize_ddl;
+SET GLOBAL innodb_log_optimize_ddl=ON;
+SET @old_debug_dbug=@@global.debug_dbug;
+SET GLOBAL debug_dbug="+d,ib_log_checkpoint_avoid";
+SET @old_innodb_page_cleaner_disabled_debug=@@global.innodb_page_cleaner_disabled_debug;
+SET GLOBAL innodb_page_cleaner_disabled_debug=ON;
+CREATE TABLE t1 (c INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1);
+# backup
+SET GLOBAL debug_dbug="+d,ib_do_not_log_crypt_data";
+INSERT INTO t1 VALUES (2);
+# incremental backup
+# prepare
+# incremental prepare
+SET GLOBAL innodb_log_optimize_ddl=@old_innodb_log_optimize_ddl;
+SET GLOBAL innodb_page_cleaner_disabled_debug=@old_innodb_page_cleaner_disabled_debug;
+SET GLOBAL debug_dbug=@old_debug_dbug;
+# Restore and check results
+# shutdown server
+# remove datadir
+# xtrabackup move back
+# restart
+SELECT count(*) FROM t1;
+count(*)
+2
+DROP TABLE t1;
diff --git a/mysql-test/suite/mariabackup/ddl_incremental_encrypted.test b/mysql-test/suite/mariabackup/ddl_incremental_encrypted.test
new file mode 100644
index 00000000000..196201f61c7
--- /dev/null
+++ b/mysql-test/suite/mariabackup/ddl_incremental_encrypted.test
@@ -0,0 +1,66 @@
+#
+# If MDEV-20755 bug is no fixed, incremental prepare will fail.
+#
+--source include/have_debug.inc
+--source include/have_file_key_management.inc
+
+--let $base_dir=$MYSQLTEST_VARDIR/tmp/backup
+--let $inc_dir=$MYSQLTEST_VARDIR/tmp/backup_inc
+
+SET @old_innodb_log_optimize_ddl=@@global.innodb_log_optimize_ddl;
+SET GLOBAL innodb_log_optimize_ddl=ON;
+
+# Disable pages flushing to allow redo log records to be executed on --prepare.
+SET @old_debug_dbug=@@global.debug_dbug;
+SET GLOBAL debug_dbug="+d,ib_log_checkpoint_avoid";
+SET @old_innodb_page_cleaner_disabled_debug=@@global.innodb_page_cleaner_disabled_debug;
+SET GLOBAL innodb_page_cleaner_disabled_debug=ON;
+
+CREATE TABLE t1 (c INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1);
+
+--echo # backup
+--disable_result_log
+--exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$base_dir
+--enable_result_log
+
+# Do not log crypt data to avoid it's execution on --prepare.
+SET GLOBAL debug_dbug="+d,ib_do_not_log_crypt_data";
+INSERT INTO t1 VALUES (2);
+
+--disable_result_log
+
+# Execute OPTIMIZE TABLE after the table is copied to execute optimized ddl
+# callback during backup, which, in turns, will create t1.new file in backup
+# directory.
+--let after_copy_test_t1=OPTIMIZE TABLE test.t1;
+
+--echo # incremental backup
+--exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$inc_dir --incremental-basedir=$base_dir --dbug=+d,mariabackup_events
+
+--echo # prepare
+--exec $XTRABACKUP --prepare --target-dir=$base_dir
+
+# If the tablespace is created during delta tablespace open procedure, then
+# crypt data will be not written, and page corruption test will not pass
+# when some redo log event is executed, and --prepare will fail.
+--echo # incremental prepare
+--exec $XTRABACKUP --prepare --target-dir=$base_dir --incremental-dir=$inc_dir
+
+--enable_result_log
+
+SET GLOBAL innodb_log_optimize_ddl=@old_innodb_log_optimize_ddl;
+SET GLOBAL innodb_page_cleaner_disabled_debug=@old_innodb_page_cleaner_disabled_debug;
+SET GLOBAL debug_dbug=@old_debug_dbug;
+
+--echo # Restore and check results
+--let $targetdir=$base_dir
+--source include/restart_and_restore.inc
+--enable_result_log
+
+SELECT count(*) FROM t1;
+
+# Cleanup
+DROP TABLE t1;
+--rmdir $base_dir
+--rmdir $inc_dir
diff --git a/mysql-test/suite/mariabackup/mlog_index_load.result b/mysql-test/suite/mariabackup/mlog_index_load.result
index 1748bc920e8..b2367d52dce 100644
--- a/mysql-test/suite/mariabackup/mlog_index_load.result
+++ b/mysql-test/suite/mariabackup/mlog_index_load.result
@@ -1,5 +1,6 @@
 CREATE TABLE t1(i INT PRIMARY KEY auto_increment, a int) ENGINE INNODB;
 INSERT INTO t1(a) SELECT * from seq_1_to_10000;
+SET GLOBAL innodb_log_optimize_ddl=ON;
 # xtrabackup backup
 t1.frm
 t1.ibd
diff --git a/mysql-test/suite/mariabackup/mlog_index_load.test b/mysql-test/suite/mariabackup/mlog_index_load.test
index fb29041fd3c..b9fd6f8ca32 100644
--- a/mysql-test/suite/mariabackup/mlog_index_load.test
+++ b/mysql-test/suite/mariabackup/mlog_index_load.test
@@ -2,6 +2,7 @@
 
 CREATE TABLE t1(i INT PRIMARY KEY auto_increment, a int) ENGINE INNODB;
 INSERT INTO t1(a) SELECT * from seq_1_to_10000;
+SET GLOBAL innodb_log_optimize_ddl=ON;
 
 let $targetdir=$MYSQLTEST_VARDIR/tmp/backup;
 
diff --git a/mysql-test/suite/plugins/r/server_audit.result b/mysql-test/suite/plugins/r/server_audit.result
index 82e16c9be25..ab726a0e22f 100644
--- a/mysql-test/suite/plugins/r/server_audit.result
+++ b/mysql-test/suite/plugins/r/server_audit.result
@@ -227,6 +227,21 @@ set global server_audit_logging= on;
 disconnect cn1;
 drop user user1@localhost;
 set global server_audit_events='';
+CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest';
+CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
+connect(localhost,plug,plug_dest,test,MYSQL_PORT,MYSQL_SOCK);
+connect plug_con,localhost,plug,plug_dest;
+ERROR 28000: Access denied for user 'plug'@'localhost' (using password: YES)
+GRANT PROXY ON plug_dest TO plug;
+connect plug_con,localhost,plug,plug_dest;
+connection plug_con;
+select USER(),CURRENT_USER();
+USER()	CURRENT_USER()
+plug@localhost	plug_dest@%
+connection default;
+disconnect plug_con;
+DROP USER plug;
+DROP USER plug_dest;
 set global server_audit_query_log_limit= 15;
 select (1), (2), (3), (4);
 1	2	3	4
@@ -416,6 +431,46 @@ TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv,
 TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping,
 TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv,
 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_events=\'\'',0
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'CREATE USER plug IDENTIFIED WITH \'test_plugin_server\' AS \'plug_dest\'',0
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'CREATE USER plug_dest IDENTIFIED BY *****',0
+TIME,HOSTNAME,plug,localhost,ID,0,FAILED_CONNECT,,,ID
+TIME,HOSTNAME,plug,localhost,ID,0,DISCONNECT,,,0
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'GRANT PROXY ON plug_dest TO plug',0
+TIME,HOSTNAME,plug,localhost,ID,0,PROXY_CONNECT,test,`plug_dest`@`%`,0
+TIME,HOSTNAME,plug,localhost,ID,0,CONNECT,test,,0
+TIME,HOSTNAME,plug,localhost,ID,0,DISCONNECT,test,,0
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'DROP USER plug',0
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping,
+TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv,
+TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'DROP USER plug_dest',0
 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global serv',0
 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select (1), (2)',0
 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select \'A\', ',0
diff --git a/mysql-test/suite/plugins/t/server_audit.test b/mysql-test/suite/plugins/t/server_audit.test
index 2a76c1eaadc..787541f7ca0 100644
--- a/mysql-test/suite/plugins/t/server_audit.test
+++ b/mysql-test/suite/plugins/t/server_audit.test
@@ -1,4 +1,4 @@
-
+--source include/have_plugin_auth.inc
 --source include/not_embedded.inc
 
 if (!$SERVER_AUDIT_SO) {
@@ -174,6 +174,25 @@ drop user user1@localhost;
 
 set global server_audit_events='';
 
+CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest';
+CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
+--sleep 2
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+--error ER_ACCESS_DENIED_ERROR : this should fail : no grant
+connect(plug_con,localhost,plug,plug_dest);
+--sleep 2
+GRANT PROXY ON plug_dest TO plug;
+--sleep 2
+connect(plug_con,localhost,plug,plug_dest);
+connection plug_con;
+select USER(),CURRENT_USER();
+connection default;
+disconnect plug_con;
+--sleep 2
+--sleep 2
+DROP USER plug;
+DROP USER plug_dest;
+
 set global server_audit_query_log_limit= 15;
 select (1), (2), (3), (4);
 select 'A', 'B', 'C', 'D';
diff --git a/mysql-test/suite/roles/set_default_role_clear.result b/mysql-test/suite/roles/set_default_role_clear.result
index 6441dfc2aa4..281ed7e45ea 100644
--- a/mysql-test/suite/roles/set_default_role_clear.result
+++ b/mysql-test/suite/roles/set_default_role_clear.result
@@ -17,6 +17,7 @@ Grants for test_user@localhost
 GRANT `test_role` TO `test_user`@`localhost`
 GRANT USAGE ON *.* TO `test_user`@`localhost`
 GRANT SELECT ON *.* TO `test_role`
+SET DEFAULT ROLE test_role FOR 'test_user'@'localhost'
 select user, host, default_role from mysql.user where user='test_user';
 User	Host	default_role
 test_user	localhost	test_role
diff --git a/mysql-test/suite/roles/set_default_role_for.result b/mysql-test/suite/roles/set_default_role_for.result
index 533a646e6e3..fec43b8e763 100644
--- a/mysql-test/suite/roles/set_default_role_for.result
+++ b/mysql-test/suite/roles/set_default_role_for.result
@@ -21,6 +21,7 @@ Grants for user_a@localhost
 GRANT `role_a` TO `user_a`@`localhost`
 GRANT USAGE ON *.* TO `user_a`@`localhost`
 GRANT SELECT ON *.* TO `role_a`
+SET DEFAULT ROLE role_a FOR 'user_a'@'localhost'
 select user, host, default_role from mysql.user where user like 'user_%';
 User	Host	default_role
 user_a	localhost	role_a
@@ -42,6 +43,7 @@ Grants for user_b@localhost
 GRANT `role_b` TO `user_b`@`localhost`
 GRANT USAGE ON *.* TO `user_b`@`localhost`
 GRANT INSERT, UPDATE ON *.* TO `role_b`
+SET DEFAULT ROLE role_b FOR 'user_b'@'localhost'
 select user, host, default_role from mysql.user where user like 'user_%';
 ERROR 42000: SELECT command denied to user 'user_b'@'localhost' for table 'user'
 set default role NONE for user_a@localhost;
diff --git a/mysql-test/suite/roles/set_default_role_invalid.result b/mysql-test/suite/roles/set_default_role_invalid.result
index c8789594593..08087acc51f 100644
--- a/mysql-test/suite/roles/set_default_role_invalid.result
+++ b/mysql-test/suite/roles/set_default_role_invalid.result
@@ -24,6 +24,7 @@ Grants for test_user@localhost
 GRANT `test_role` TO `test_user`@`localhost`
 GRANT USAGE ON *.* TO `test_user`@`localhost`
 GRANT SELECT ON *.* TO `test_role`
+SET DEFAULT ROLE test_role FOR 'test_user'@'localhost'
 select user, host, default_role from mysql.user where user='test_user';
 User	Host	default_role
 test_user	localhost	test_role
@@ -71,6 +72,7 @@ GRANT `r1` TO `b`@`%`
 GRANT `r2` TO `b`@`%`
 GRANT USAGE ON *.* TO `b`@`%`
 GRANT SELECT ON `mysql`.* TO `b`@`%`
+SET DEFAULT ROLE r2 FOR 'b'@'%'
 SET DEFAULT ROLE r1 FOR a;
 ERROR 42000: Access denied for user 'b'@'%' to database 'mysql'
 SELECT CURRENT_ROLE;
@@ -96,6 +98,7 @@ GRANT `r1` TO `b`@`%`
 GRANT `r2` TO `b`@`%`
 GRANT USAGE ON *.* TO `b`@`%`
 GRANT SELECT, UPDATE ON `mysql`.* TO `b`@`%`
+SET DEFAULT ROLE r2 FOR 'b'@'%'
 SET DEFAULT ROLE r1 FOR a;
 ERROR OP000: User `a@%` has not been granted role `r1`
 SET DEFAULT ROLE invalid_role;
diff --git a/mysql-test/suite/roles/set_default_role_new_connection.result b/mysql-test/suite/roles/set_default_role_new_connection.result
index dbf0db863c6..71035737f99 100644
--- a/mysql-test/suite/roles/set_default_role_new_connection.result
+++ b/mysql-test/suite/roles/set_default_role_new_connection.result
@@ -23,6 +23,7 @@ Grants for test_user@localhost
 GRANT `test_role` TO `test_user`@`localhost`
 GRANT USAGE ON *.* TO `test_user`@`localhost`
 GRANT SELECT ON *.* TO `test_role`
+SET DEFAULT ROLE test_role FOR 'test_user'@'localhost'
 select user, host, default_role from mysql.user where user = 'test_user';
 User	Host	default_role
 test_user	localhost	test_role
@@ -51,6 +52,7 @@ Grants for test_user@localhost
 GRANT `test_role` TO `test_user`@`localhost`
 GRANT USAGE ON *.* TO `test_user`@`localhost`
 GRANT SELECT ON *.* TO `test_role`
+SET DEFAULT ROLE test_role FOR 'test_user'@'localhost'
 select user, host, default_role from mysql.user where user = 'test_user';
 User	Host	default_role
 test_user	localhost	test_role
diff --git a/mysql-test/suite/sql_sequence/mysqldump.result b/mysql-test/suite/sql_sequence/mysqldump.result
index 5a3711ebda3..e6aedb57ea6 100644
--- a/mysql-test/suite/sql_sequence/mysqldump.result
+++ b/mysql-test/suite/sql_sequence/mysqldump.result
@@ -2,20 +2,8 @@ CREATE SEQUENCE a1 engine=aria;
 CREATE TABLE t1(a INT, KEY (a)) KEY_BLOCK_SIZE=1024;
 insert into t1 values (1),(2);
 CREATE SEQUENCE x1 engine=innodb;
-/*!40101 SET @saved_cs_client     = @@character_set_client */;
-/*!40101 SET character_set_client = utf8 */;
-CREATE TABLE `a1` (
-  `next_not_cached_value` bigint(21) NOT NULL,
-  `minimum_value` bigint(21) NOT NULL,
-  `maximum_value` bigint(21) NOT NULL,
-  `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
-  `increment` bigint(21) NOT NULL COMMENT 'increment value',
-  `cache_size` bigint(21) unsigned NOT NULL,
-  `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
-  `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
-) ENGINE=Aria SEQUENCE=1;
-/*!40101 SET character_set_client = @saved_cs_client */;
-INSERT INTO `a1` VALUES (1,1,9223372036854775806,1,1,1000,0,0);
+CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria;
+SELECT SETVAL(`a1`, 1, 0);
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `t1` (
@@ -24,20 +12,8 @@ CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1024;
 /*!40101 SET character_set_client = @saved_cs_client */;
 INSERT INTO `t1` VALUES (1),(2);
-/*!40101 SET @saved_cs_client     = @@character_set_client */;
-/*!40101 SET character_set_client = utf8 */;
-CREATE TABLE `x1` (
-  `next_not_cached_value` bigint(21) NOT NULL,
-  `minimum_value` bigint(21) NOT NULL,
-  `maximum_value` bigint(21) NOT NULL,
-  `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
-  `increment` bigint(21) NOT NULL COMMENT 'increment value',
-  `cache_size` bigint(21) unsigned NOT NULL,
-  `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
-  `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
-) ENGINE=InnoDB SEQUENCE=1;
-/*!40101 SET character_set_client = @saved_cs_client */;
-INSERT INTO `x1` VALUES (1,1,9223372036854775806,1,1,1000,0,0);
+CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
+SELECT SETVAL(`x1`, 1, 0);
 DROP TABLE a1,t1,x1;
 set default_storage_engine=InnoDB;
 create sequence t1;
diff --git a/mysql-test/suite/sql_sequence/next.result b/mysql-test/suite/sql_sequence/next.result
index fe761baa032..76991fbe68c 100644
--- a/mysql-test/suite/sql_sequence/next.result
+++ b/mysql-test/suite/sql_sequence/next.result
@@ -541,5 +541,10 @@ CREATE VIEW v AS SELECT 1;
 LOCK TABLE v READ;
 SELECT NEXT VALUE FOR v;
 ERROR 42S02: 'test.v' is not a SEQUENCE
+#
+# MDEV-24018: SIGSEGV in Item_func_nextval::update_table on SELECT SETVAL
+#
+SELECT SETVAL (v,0);
+ERROR 42S02: 'test.v' is not a SEQUENCE
 UNLOCK TABLES;
 DROP VIEW v;
diff --git a/mysql-test/suite/sql_sequence/next.test b/mysql-test/suite/sql_sequence/next.test
index 5eac3b668ba..9f0eebdf774 100644
--- a/mysql-test/suite/sql_sequence/next.test
+++ b/mysql-test/suite/sql_sequence/next.test
@@ -288,5 +288,12 @@ CREATE VIEW v AS SELECT 1;
 LOCK TABLE v READ;
 --error ER_NOT_SEQUENCE
 SELECT NEXT VALUE FOR v;
+
+--echo #
+--echo # MDEV-24018: SIGSEGV in Item_func_nextval::update_table on SELECT SETVAL
+--echo #
+--error ER_NOT_SEQUENCE
+SELECT SETVAL (v,0);
+
 UNLOCK TABLES;
 DROP VIEW v;
diff --git a/mysql-test/suite/sys_vars/r/session_track_system_variables_basic.result b/mysql-test/suite/sys_vars/r/session_track_system_variables_basic.result
index 90d2fec3d0a..377b50e4481 100644
--- a/mysql-test/suite/sys_vars/r/session_track_system_variables_basic.result
+++ b/mysql-test/suite/sys_vars/r/session_track_system_variables_basic.result
@@ -153,6 +153,8 @@ SELECT @@session.session_track_system_variables;
 @@session.session_track_system_variables
 
 
+# MDEV-22524 SIGABRT in safe_mutex_unlock with session_track_system_variables and max_relay_log_size.
+SET SESSION session_track_system_variables="sql_slave_skip_counter", sql_slave_skip_counter= 0;
 # Restoring the original values.
 SET @@global.session_track_system_variables = @global_saved_tmp;
 # End of tests.
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff
index 16cda279c3e..50a1d1f197a 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff
@@ -1,272 +1,550 @@
-52c52
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-64c64
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-76c76
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-88c88
-< VARIABLE_TYPE	BIGINT
----
-> VARIABLE_TYPE	INT
-160c160
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-163c163
-< NUMERIC_MAX_VALUE	9223372036854775807
----
-> NUMERIC_MAX_VALUE	2147483647
-196c196
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-232c232
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-292c292
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-388c388
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-424c424
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-448c448
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-460c460
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-463c463
-< NUMERIC_MAX_VALUE	18446744073709551615
----
-> NUMERIC_MAX_VALUE	4294967295
-688c688
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-784c784
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-832c832
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-844c844
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-868c868
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-892c892
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-940c940
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-964c964
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1000c1000
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1012c1012
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1024c1024
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1036c1036
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1039c1039
-< NUMERIC_MAX_VALUE	18446744073709551615
----
-> NUMERIC_MAX_VALUE	4294967295
-1060c1060
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1072c1072
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1096c1096
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1120c1120
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1123c1123
-< NUMERIC_MAX_VALUE	18446744073709551615
----
-> NUMERIC_MAX_VALUE	4294967295
-1130c1130
-< DEFAULT_VALUE	18446744073709551615
----
-> DEFAULT_VALUE	4294967295
-1132c1132
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1135c1135
-< NUMERIC_MAX_VALUE	18446744073709551615
----
-> NUMERIC_MAX_VALUE	4294967295
-1192c1192
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1204c1204
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1207c1207
-< NUMERIC_MAX_VALUE	9223372036854775807
----
-> NUMERIC_MAX_VALUE	2147483647
-1252c1252
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1300c1300
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1312c1312
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1315c1315
-< NUMERIC_MAX_VALUE	18446744073709551615
----
-> NUMERIC_MAX_VALUE	4294967295
-1360c1360
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1363c1363
-< NUMERIC_MAX_VALUE	18446744073709551615
----
-> NUMERIC_MAX_VALUE	4294967295
-1372c1372
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1492c1492
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1495c1495
-< NUMERIC_MAX_VALUE	9223372036854775807
----
-> NUMERIC_MAX_VALUE	2147483647
-1516c1516
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1540c1540
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1552c1552
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1588c1588
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1600c1600
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1612c1612
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1636c1636
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1648c1648
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1672c1672
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1675c1675
-< NUMERIC_MAX_VALUE	18446744073709551615
----
-> NUMERIC_MAX_VALUE	4294967295
-1696c1696
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1708c1708
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1756c1756
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1936c1936
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1960c1960
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-1963c1963
-< NUMERIC_MAX_VALUE	18446744073709551615
----
-> NUMERIC_MAX_VALUE	4294967295
-1996c1996
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-2008c2008
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-2068c2068
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-2092c2092
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
-2116c2116
-< VARIABLE_TYPE	BIGINT UNSIGNED
----
-> VARIABLE_TYPE	INT UNSIGNED
+@@ -49,7 +49,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	8
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of InnoDB Adaptive Hash Index Partitions (default 8)
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	512
+@@ -61,7 +61,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	150000
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	The upper limit of the sleep delay in usec. Value of 0 disables it.
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	1000000
+@@ -73,7 +73,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	64
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Data file autoextend increment in megabytes
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	1000
+@@ -85,7 +85,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	1
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT
++VARIABLE_TYPE	INT
+ VARIABLE_COMMENT	The AUTOINC lock modes supported by InnoDB: 0 => Old style AUTOINC locking (for backward compatibility); 1 => New style AUTOINC locking; 2 => No AUTOINC locking (unsafe for SBR)
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	2
+@@ -157,10 +157,10 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	134217728
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Size of a single memory chunk within each buffer pool instance for resizing buffer pool. Online buffer pool resizing happens at this granularity. 0 means disable resizing buffer pool.
+ NUMERIC_MIN_VALUE	1048576
+-NUMERIC_MAX_VALUE	9223372036854775807
++NUMERIC_MAX_VALUE	2147483647
+ NUMERIC_BLOCK_SIZE	1048576
+ ENUM_VALUE_LIST	NULL
+ READ_ONLY	YES
+@@ -193,7 +193,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	25
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Dump only the hottest N% of each buffer pool, defaults to 25
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	100
+@@ -229,7 +229,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	0
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of buffer pool instances, set to higher value on high-end machines to increase scalability
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	64
+@@ -289,7 +289,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	0
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	A number between [0, 100] that tells how oftern buffer pool dump status in percentages should be printed. E.g. 10 means that buffer pool dump status is printed when every 10% of number of buffer pool pages are dumped. Default is 0 (only start and end status is printed).
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	100
+@@ -397,7 +397,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	0
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Helps in performance tuning in heavily concurrent environments.
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	1000
+@@ -433,7 +433,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	5
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	If the compression failure rate of a table is greater than this number more padding is added to the pages to reduce the failures. A value of zero implies no padding
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	100
+@@ -457,7 +457,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	50
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Percentage of empty space on a data page that can be reserved to make the page compressible.
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	75
+@@ -469,10 +469,10 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	5000
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket
+ NUMERIC_MIN_VALUE	1
+-NUMERIC_MAX_VALUE	18446744073709551615
++NUMERIC_MAX_VALUE	4294967295
+ NUMERIC_BLOCK_SIZE	0
+ ENUM_VALUE_LIST	NULL
+ READ_ONLY	NO
+@@ -697,7 +697,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	120
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of pages reserved in doublewrite buffer for batch flushing
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	127
+@@ -793,7 +793,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	600
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Maximum number of seconds that semaphore times out in InnoDB.
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	4294967295
+@@ -841,7 +841,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	0
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Make the first page of the given tablespace dirty.
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	4294967295
+@@ -853,7 +853,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	30
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of iterations over which the background flushing is averaged.
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	1000
+@@ -877,7 +877,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	1
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Controls the durability/speed trade-off for commits. Set to 0 (write and flush redo log to disk only once per second), 1 (flush to disk at each commit), 2 (write to log at commit but flush to disk only once per second) or 3 (flush to disk at prepare and at commit, slower and usually redundant). 1 and 3 guarantees that after a crash, committed transactions will not be lost and will be consistent with the binlog and other transactional engines. 2 can get inconsistent and lose transactions if there is a power failure or kernel crash but not if mysqld crashes. 0 has no guarantees in case of crash. 0 and 2 can be faster than 1 or 3.
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	3
+@@ -901,7 +901,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	1
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Set to 0 (don't flush neighbors from buffer pool), 1 (flush contiguous neighbors from buffer pool) or 2 (flush neighbors from buffer pool), when flushing a block
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	2
+@@ -949,7 +949,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	0
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Helps to save your data in case the disk image of the database becomes corrupt.
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	6
+@@ -973,7 +973,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	8000000
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	InnoDB Fulltext search cache size in bytes
+ NUMERIC_MIN_VALUE	1600000
+ NUMERIC_MAX_VALUE	80000000
+@@ -1009,7 +1009,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	84
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	InnoDB Fulltext search maximum token size in characters
+ NUMERIC_MIN_VALUE	10
+ NUMERIC_MAX_VALUE	84
+@@ -1021,7 +1021,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	3
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	InnoDB Fulltext search minimum token size in characters
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	16
+@@ -1033,7 +1033,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	2000
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	InnoDB Fulltext search number of words to optimize for each optimize table call 
+ NUMERIC_MIN_VALUE	1000
+ NUMERIC_MAX_VALUE	10000
+@@ -1045,10 +1045,10 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	2000000000
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	InnoDB Fulltext search query result cache limit in bytes
+ NUMERIC_MIN_VALUE	1000000
+-NUMERIC_MAX_VALUE	18446744073709551615
++NUMERIC_MAX_VALUE	4294967295
+ NUMERIC_BLOCK_SIZE	0
+ ENUM_VALUE_LIST	NULL
+ READ_ONLY	NO
+@@ -1069,7 +1069,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	2
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	InnoDB Fulltext search parallel sort degree, will round up to nearest power of 2 number
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	16
+@@ -1081,7 +1081,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	640000000
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Total memory allocated for InnoDB Fulltext Search cache
+ NUMERIC_MIN_VALUE	32000000
+ NUMERIC_MAX_VALUE	1600000000
+@@ -1105,7 +1105,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	100
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Up to what percentage of dirty pages should be flushed when innodb finds it has spare resources to do so.
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	100
+@@ -1141,22 +1141,22 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	200
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of IOPs the server can do. Tunes the background IO rate
+ NUMERIC_MIN_VALUE	100
+-NUMERIC_MAX_VALUE	18446744073709551615
++NUMERIC_MAX_VALUE	4294967295
+ NUMERIC_BLOCK_SIZE	0
+ ENUM_VALUE_LIST	NULL
+ READ_ONLY	NO
+ COMMAND_LINE_ARGUMENT	REQUIRED
+ VARIABLE_NAME	INNODB_IO_CAPACITY_MAX
+ SESSION_VALUE	NULL
+-DEFAULT_VALUE	18446744073709551615
++DEFAULT_VALUE	4294967295
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Limit to which innodb_io_capacity can be inflated.
+ NUMERIC_MIN_VALUE	100
+-NUMERIC_MAX_VALUE	18446744073709551615
++NUMERIC_MAX_VALUE	4294967295
+ NUMERIC_BLOCK_SIZE	0
+ ENUM_VALUE_LIST	NULL
+ READ_ONLY	NO
+@@ -1213,7 +1213,7 @@
+ SESSION_VALUE	50
+ DEFAULT_VALUE	50
+ VARIABLE_SCOPE	SESSION
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Timeout in seconds an InnoDB transaction may wait for a lock before being rolled back. Values above 100000000 disable the timeout.
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	1073741824
+@@ -1225,10 +1225,10 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	16777216
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	The size of the buffer which InnoDB uses to write log to the log files on disk.
+ NUMERIC_MIN_VALUE	262144
+-NUMERIC_MAX_VALUE	9223372036854775807
++NUMERIC_MAX_VALUE	2147483647
+ NUMERIC_BLOCK_SIZE	1024
+ ENUM_VALUE_LIST	NULL
+ READ_ONLY	YES
+@@ -1273,7 +1273,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	2
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of log files in the log group. InnoDB writes to the files in a circular fashion.
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	100
+@@ -1321,7 +1321,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	8192
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Redo log write ahead unit size to avoid read-on-write, it should match the OS cache block IO size
+ NUMERIC_MIN_VALUE	512
+ NUMERIC_MAX_VALUE	16384
+@@ -1333,10 +1333,10 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	1024
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	How deep to scan LRU to keep it clean
+ NUMERIC_MIN_VALUE	100
+-NUMERIC_MAX_VALUE	18446744073709551615
++NUMERIC_MAX_VALUE	4294967295
+ NUMERIC_BLOCK_SIZE	0
+ ENUM_VALUE_LIST	NULL
+ READ_ONLY	NO
+@@ -1381,10 +1381,10 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	0
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Desired maximum length of the purge queue (0 = no limit)
+ NUMERIC_MIN_VALUE	0
+-NUMERIC_MAX_VALUE	18446744073709551615
++NUMERIC_MAX_VALUE	4294967295
+ NUMERIC_BLOCK_SIZE	0
+ ENUM_VALUE_LIST	NULL
+ READ_ONLY	NO
+@@ -1393,7 +1393,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	0
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Maximum delay of user threads in micro-seconds
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	10000000
+@@ -1525,10 +1525,10 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	0
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	How many files at the maximum InnoDB keeps open at the same time.
+ NUMERIC_MIN_VALUE	0
+-NUMERIC_MAX_VALUE	9223372036854775807
++NUMERIC_MAX_VALUE	2147483647
+ NUMERIC_BLOCK_SIZE	0
+ ENUM_VALUE_LIST	NULL
+ READ_ONLY	YES
+@@ -1549,7 +1549,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	4
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Page cleaner threads can be from 1 to 64. Default is 4.
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	64
+@@ -1573,7 +1573,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	16
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of rw_locks protecting buffer pool page_hash. Rounded up to the next power of 2
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	1024
+@@ -1585,7 +1585,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	16384
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Page size to use for all InnoDB tablespaces.
+ NUMERIC_MIN_VALUE	4096
+ NUMERIC_MAX_VALUE	65536
+@@ -1621,7 +1621,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	300
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of UNDO log pages to purge in one batch from the history list.
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	5000
+@@ -1633,7 +1633,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	128
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Dictates rate at which UNDO records are purged. Value N means purge rollback segment(s) on every Nth iteration of purge invocation
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	128
+@@ -1645,7 +1645,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	4
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Purge threads can be from 1 to 32. Default is 4.
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	32
+@@ -1669,7 +1669,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	56
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of pages that must be accessed sequentially for InnoDB to trigger a readahead.
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	64
+@@ -1681,7 +1681,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	4
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of background read I/O threads in InnoDB.
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	64
+@@ -1705,10 +1705,10 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	0
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Replication thread delay (ms) on the slave server if innodb_thread_concurrency is reached (0 by default)
+ NUMERIC_MIN_VALUE	0
+-NUMERIC_MAX_VALUE	18446744073709551615
++NUMERIC_MAX_VALUE	4294967295
+ NUMERIC_BLOCK_SIZE	0
+ ENUM_VALUE_LIST	NULL
+ READ_ONLY	NO
+@@ -1729,7 +1729,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	128
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of undo logs to use (deprecated).
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	128
+@@ -1741,7 +1741,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	0
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	An InnoDB page number.
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	4294967295
+@@ -1789,7 +1789,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	1048576
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Memory buffer size for index creation
+ NUMERIC_MIN_VALUE	65536
+ NUMERIC_MAX_VALUE	67108864
+@@ -1969,7 +1969,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	1
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Size of the mutex/lock wait array.
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	1024
+@@ -1993,10 +1993,10 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	30
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Count of spin-loop rounds in InnoDB mutexes (30 by default)
+ NUMERIC_MIN_VALUE	0
+-NUMERIC_MAX_VALUE	18446744073709551615
++NUMERIC_MAX_VALUE	4294967295
+ NUMERIC_BLOCK_SIZE	0
+ ENUM_VALUE_LIST	NULL
+ READ_ONLY	NO
+@@ -2029,7 +2029,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	0
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Helps in performance tuning in heavily concurrent environments. Sets the maximum number of threads allowed inside InnoDB. Value 0 will disable the thread throttling.
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	1000
+@@ -2041,7 +2041,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	10000
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Time of innodb thread sleeping before joining InnoDB queue (usec). Value 0 disable a sleep
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	1000000
+@@ -2101,7 +2101,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	128
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of undo logs to use.
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	128
+@@ -2125,7 +2125,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	0
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of undo tablespaces to use.
+ NUMERIC_MIN_VALUE	0
+ NUMERIC_MAX_VALUE	127
+@@ -2149,7 +2149,7 @@
+ SESSION_VALUE	NULL
+ DEFAULT_VALUE	4
+ VARIABLE_SCOPE	GLOBAL
+-VARIABLE_TYPE	BIGINT UNSIGNED
++VARIABLE_TYPE	INT UNSIGNED
+ VARIABLE_COMMENT	Number of background write I/O threads in InnoDB.
+ NUMERIC_MIN_VALUE	1
+ NUMERIC_MAX_VALUE	64
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index 2da3ae77b9f..849937ce3a4 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -1307,10 +1307,10 @@ READ_ONLY	YES
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	INNODB_LOG_OPTIMIZE_DDL
 SESSION_VALUE	NULL
-DEFAULT_VALUE	ON
+DEFAULT_VALUE	OFF
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BOOLEAN
-VARIABLE_COMMENT	Reduce redo logging when natively creating indexes or rebuilding tables. Setting this OFF avoids delay due to page flushing and allows concurrent backup.
+VARIABLE_COMMENT	DEPRECATED. Ignored in MariaDB 10.5. Reduce redo logging when natively creating indexes or rebuilding tables. Enabling this may slow down backup and cause delay due to page flushing.
 NUMERIC_MIN_VALUE	NULL
 NUMERIC_MAX_VALUE	NULL
 NUMERIC_BLOCK_SIZE	NULL
@@ -1401,6 +1401,18 @@ NUMERIC_BLOCK_SIZE	0
 ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
+VARIABLE_NAME	INNODB_MAX_PURGE_LAG_WAIT
+SESSION_VALUE	NULL
+DEFAULT_VALUE	4294967295
+VARIABLE_SCOPE	GLOBAL
+VARIABLE_TYPE	INT UNSIGNED
+VARIABLE_COMMENT	Wait until History list length is below the specified limit
+NUMERIC_MIN_VALUE	0
+NUMERIC_MAX_VALUE	4294967295
+NUMERIC_BLOCK_SIZE	0
+ENUM_VALUE_LIST	NULL
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	INNODB_MAX_UNDO_LOG_SIZE
 SESSION_VALUE	NULL
 DEFAULT_VALUE	10485760
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
index d2e1710a298..37cb483e50d 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
@@ -2337,7 +2337,7 @@ VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BIGINT
 VARIABLE_COMMENT	Size of the statement digest. Use 0 to disable, -1 for automated sizing.
 NUMERIC_MIN_VALUE	-1
-NUMERIC_MAX_VALUE	200
+NUMERIC_MAX_VALUE	1048576
 NUMERIC_BLOCK_SIZE	1
 ENUM_VALUE_LIST	NULL
 READ_ONLY	YES
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index 2543cb12453..083469406eb 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -2497,7 +2497,7 @@ VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BIGINT
 VARIABLE_COMMENT	Size of the statement digest. Use 0 to disable, -1 for automated sizing.
 NUMERIC_MIN_VALUE	-1
-NUMERIC_MAX_VALUE	200
+NUMERIC_MAX_VALUE	1048576
 NUMERIC_BLOCK_SIZE	1
 ENUM_VALUE_LIST	NULL
 READ_ONLY	YES
diff --git a/mysql-test/suite/sys_vars/t/session_track_system_variables_basic.test b/mysql-test/suite/sys_vars/t/session_track_system_variables_basic.test
index 2cb51e3e968..ceb2353d823 100644
--- a/mysql-test/suite/sys_vars/t/session_track_system_variables_basic.test
+++ b/mysql-test/suite/sys_vars/t/session_track_system_variables_basic.test
@@ -119,6 +119,8 @@ SELECT @@global.session_track_system_variables;
 SELECT @@session.session_track_system_variables;
 --echo
 
+--echo # MDEV-22524 SIGABRT in safe_mutex_unlock with session_track_system_variables and max_relay_log_size.
+SET SESSION session_track_system_variables="sql_slave_skip_counter", sql_slave_skip_counter= 0;
 
 --echo # Restoring the original values.
 SET @@global.session_track_system_variables = @global_saved_tmp;