summaryrefslogtreecommitdiff
path: root/mysql-test/suite
diff options
context:
space:
mode:
authorMarko Mäkelä <marko.makela@mariadb.com>2020-10-29 13:38:38 +0200
committerMarko Mäkelä <marko.makela@mariadb.com>2020-10-29 13:38:38 +0200
commit7b2bb67113bdda470955e3d4d72be0f2c0b38902 (patch)
tree9f9f34da1a4e48c243c56ff161987074ef907cbb /mysql-test/suite
parent27b762e23d8305bbc65dc61d283fe192e2dbd00a (diff)
parent1e778a3b5624f97fdf44a15fd6cee3d615891cff (diff)
downloadmariadb-git-7b2bb67113bdda470955e3d4d72be0f2c0b38902.tar.gz
Merge 10.3 into 10.4
Diffstat (limited to 'mysql-test/suite')
-rw-r--r--mysql-test/suite/encryption/r/tempfiles_encrypted.result3957
-rw-r--r--mysql-test/suite/encryption/t/tempfiles_encrypted.test26
-rw-r--r--mysql-test/suite/galera/r/MDEV-22707.result26
-rw-r--r--mysql-test/suite/galera/t/MDEV-22707.test51
-rw-r--r--mysql-test/suite/innodb/include/wait_all_purged.inc24
-rw-r--r--mysql-test/suite/innodb/r/innodb-virtual-columns.result50
-rw-r--r--mysql-test/suite/innodb/r/instant_alter_crash.result2
-rw-r--r--mysql-test/suite/innodb/r/instant_alter_purge,release.rdiff5
-rw-r--r--mysql-test/suite/innodb/r/instant_alter_purge.result1
-rw-r--r--mysql-test/suite/innodb/r/stats_persistent.result19
-rw-r--r--mysql-test/suite/innodb/t/instant_alter_crash.test2
-rw-r--r--mysql-test/suite/innodb/t/instant_alter_purge.test3
-rw-r--r--mysql-test/suite/innodb/t/stats_persistent.test27
-rw-r--r--mysql-test/suite/mariabackup/ddl_incremental_encrypted.opt7
-rw-r--r--mysql-test/suite/mariabackup/ddl_incremental_encrypted.result26
-rw-r--r--mysql-test/suite/mariabackup/ddl_incremental_encrypted.test66
-rw-r--r--mysql-test/suite/mariabackup/mlog_index_load.result1
-rw-r--r--mysql-test/suite/mariabackup/mlog_index_load.test1
-rw-r--r--mysql-test/suite/plugins/r/server_audit.result55
-rw-r--r--mysql-test/suite/plugins/t/server_audit.test21
-rw-r--r--mysql-test/suite/roles/set_default_role_clear.result1
-rw-r--r--mysql-test/suite/roles/set_default_role_for.result2
-rw-r--r--mysql-test/suite/roles/set_default_role_invalid.result3
-rw-r--r--mysql-test/suite/roles/set_default_role_new_connection.result2
-rw-r--r--mysql-test/suite/sql_sequence/mysqldump.result32
-rw-r--r--mysql-test/suite/sql_sequence/next.result5
-rw-r--r--mysql-test/suite/sql_sequence/next.test7
-rw-r--r--mysql-test/suite/sys_vars/r/session_track_system_variables_basic.result2
-rw-r--r--mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff822
-rw-r--r--mysql-test/suite/sys_vars/r/sysvars_innodb.result16
-rw-r--r--mysql-test/suite/sys_vars/r/sysvars_server_embedded.result2
-rw-r--r--mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result2
-rw-r--r--mysql-test/suite/sys_vars/t/session_track_system_variables_basic.test2
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;