Merge branch '10.5' into bb-10.5-release

11 files changed, 134 insertions, 12 deletions

diff --git a/mysql-test/main/backup_locks.result b/mysql-test/main/backup_locks.result
index 1505c39f166..1e567c1a58d 100644
--- a/mysql-test/main/backup_locks.result
+++ b/mysql-test/main/backup_locks.result
@@ -39,6 +39,28 @@ MDL_INTENTION_EXCLUSIVE	Schema metadata lock	test
 select * from t1;
 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
 backup unlock;
+connection con1;
+connection default;
+#
+# Check that BACKUP LOCK blocks some operations
+#
+create sequence seq1;
+create sequence seq2;
+backup lock seq1;
+connection con1;
+CREATE OR REPLACE SEQUENCE seq1 START -28;
+ERROR HY000: Sequence 'test.seq1' values are conflicting
+SET STATEMENT max_statement_time=10 FOR CREATE OR REPLACE SEQUENCE seq1 START 50;
+ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
+SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 NOMAXVALUE;
+ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
+SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 MAXVALUE 1000;
+ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
+SET STATEMENT max_statement_time=10 for rename table seq2 to seq3, seq3 to seq1;
+ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
+connection default;
+backup unlock;
+drop table seq1,seq2;
 #
 # BACKUP LOCK and BACKUP UNLOCK are not allowed in procedures.
 #
@@ -141,7 +163,6 @@ ERROR HY000: Can't execute the given command because you have active locked tabl
 SET STATEMENT max_statement_time=180 FOR BACKUP LOCK test.u;
 # restart
 #
-connection con1;
 connection default;
 disconnect con1;
 show tables;
diff --git a/mysql-test/main/backup_locks.test b/mysql-test/main/backup_locks.test
index d2f3d95d703..1271abfd993 100644
--- a/mysql-test/main/backup_locks.test
+++ b/mysql-test/main/backup_locks.test
@@ -43,10 +43,39 @@ SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.me
 --error ER_LOCK_DEADLOCK
 select * from t1;
 backup unlock;
+connection con1;
+--reap
+connection default;
+
+--echo #
+--echo # Check that BACKUP LOCK blocks some operations
+--echo #
+
+# These test has to be done with timeouts as we want to ensure that the tables
+# doesn't change
+
+create sequence seq1;
+create sequence seq2;
+backup lock seq1;
+connection con1;
+--error ER_SEQUENCE_INVALID_DATA
+CREATE OR REPLACE SEQUENCE seq1 START -28;
+--error ER_STATEMENT_TIMEOUT
+SET STATEMENT max_statement_time=10 FOR CREATE OR REPLACE SEQUENCE seq1 START 50;
+--error ER_STATEMENT_TIMEOUT
+SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 NOMAXVALUE;
+--error ER_STATEMENT_TIMEOUT
+SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 MAXVALUE 1000;
+--error ER_STATEMENT_TIMEOUT
+SET STATEMENT max_statement_time=10 for rename table seq2 to seq3, seq3 to seq1;
+connection default;
+backup unlock;
+drop table seq1,seq2;
 
 --echo #
 --echo # BACKUP LOCK and BACKUP UNLOCK are not allowed in procedures.
 --echo #
+
 delimiter |;
 --error ER_SP_BADSTATEMENT
 CREATE PROCEDURE p_BACKUP_LOCK()
@@ -162,8 +191,6 @@ SET STATEMENT max_statement_time=180 FOR BACKUP LOCK test.u;
 
 --echo #
 
-connection con1;
---reap
 connection default;
 disconnect con1;
 show tables;
diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result
index b316d28422e..4bdacd5e4a1 100644
--- a/mysql-test/main/group_min_max.result
+++ b/mysql-test/main/group_min_max.result
@@ -4027,3 +4027,40 @@ drop table t1;
 #
 # End of 10.1 tests
 #
+#
+# MDEV-27442 Wrong result upon query with DISTINCT and EXISTS subquery
+#
+CREATE TABLE t1 (a int, b int, KEY b (b,a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0,100),(2,100),(2,101),(3,102);
+# Must not use Using index for group-by
+explain SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	b	10	NULL	4	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 );
+b
+100
+101
+102
+DROP TABLE t1;
+#
+# MDEV-26585 Wrong query results when `using index for group-by`
+#
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL AUTO_INCREMENT,
+`owner_id` int(11) DEFAULT NULL,
+`foo` tinyint(1) DEFAULT 0,
+`whatever` varchar(255) DEFAULT NULL,
+PRIMARY KEY (`id`),
+KEY `index_t1_on_owner_id_and_foo` (`owner_id`,`foo`)
+) engine=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t1 (owner_id, foo, whatever)
+VALUES (1, TRUE, "yello"), (1, FALSE, "yello"), (2, TRUE, "yello"),
+(2, TRUE, "yello"), (2, FALSE, "yello");
+EXPLAIN SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	index_t1_on_owner_id_and_foo	7	NULL	5	Using where; Using index
+SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
+owner_id
+1
+DROP TABLE t1;
diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test
index 526552dda92..b1d912684c6 100644
--- a/mysql-test/main/group_min_max.test
+++ b/mysql-test/main/group_min_max.test
@@ -4,7 +4,7 @@
 #
 
 --source include/default_optimizer_switch.inc
-
+--source include/have_innodb.inc
 #
 # TODO:
 # Add queries with:
@@ -1689,3 +1689,34 @@ drop table t1;
 --echo #
 --echo # End of 10.1 tests
 --echo #
+
+--echo #
+--echo # MDEV-27442 Wrong result upon query with DISTINCT and EXISTS subquery
+--echo #
+
+CREATE TABLE t1 (a int, b int, KEY b (b,a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0,100),(2,100),(2,101),(3,102);
+--echo # Must not use Using index for group-by
+explain SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 );
+SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 );
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-26585 Wrong query results when `using index for group-by`
+--echo #
+
+CREATE TABLE `t1` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `owner_id` int(11) DEFAULT NULL,
+  `foo` tinyint(1) DEFAULT 0,
+  `whatever` varchar(255) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `index_t1_on_owner_id_and_foo` (`owner_id`,`foo`)
+) engine=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO t1 (owner_id, foo, whatever)
+VALUES (1, TRUE, "yello"), (1, FALSE, "yello"), (2, TRUE, "yello"),
+       (2, TRUE, "yello"), (2, FALSE, "yello");
+EXPLAIN SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
+SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/r/ibuf_not_empty.result b/mysql-test/suite/innodb/r/ibuf_not_empty.result
index d1b8203b063..f2da89990b0 100644
--- a/mysql-test/suite/innodb/r/ibuf_not_empty.result
+++ b/mysql-test/suite/innodb/r/ibuf_not_empty.result
@@ -5,6 +5,7 @@ c INT,
 INDEX(b))
 ENGINE=InnoDB STATS_PERSISTENT=0;
 SET GLOBAL innodb_change_buffering_debug = 1;
+SET GLOBAL innodb_change_buffering=all;
 INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_1024;
 # restart: --innodb-force-recovery=6 --innodb-change-buffer-dump
 check table t1;
diff --git a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result
index 678c8c67be5..f676d15b134 100644
--- a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result
+++ b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result
@@ -13,6 +13,7 @@ c INT,
 INDEX(b))
 ENGINE=InnoDB STATS_PERSISTENT=0;
 SET GLOBAL innodb_change_buffering_debug = 1;
+SET GLOBAL innodb_change_buffering = all;
 INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_8192;
 BEGIN;
 SELECT b FROM t1 LIMIT 3;
diff --git a/mysql-test/suite/innodb/t/ibuf_not_empty.test b/mysql-test/suite/innodb/t/ibuf_not_empty.test
index 545a78c887e..96ceb81ac00 100644
--- a/mysql-test/suite/innodb/t/ibuf_not_empty.test
+++ b/mysql-test/suite/innodb/t/ibuf_not_empty.test
@@ -24,6 +24,7 @@ ENGINE=InnoDB STATS_PERSISTENT=0;
 # change buffering is possible, so that the change buffer will be used
 # whenever possible.
 SET GLOBAL innodb_change_buffering_debug = 1;
+SET GLOBAL innodb_change_buffering=all;
 
 # Create enough rows for the table, so that the change buffer will be
 # used for modifying the secondary index page. There must be multiple
diff --git a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test
index a12ca43cec1..129037e783b 100644
--- a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test
+++ b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test
@@ -33,6 +33,7 @@ ENGINE=InnoDB STATS_PERSISTENT=0;
 # change buffering is possible, so that the change buffer will be used
 # whenever possible.
 SET GLOBAL innodb_change_buffering_debug = 1;
+SET GLOBAL innodb_change_buffering = all;
 let SEARCH_FILE = $MYSQLTEST_VARDIR/log/mysqld.1.err;
 
 # Create enough rows for the table, so that the change buffer will be
diff --git a/mysql-test/suite/innodb/t/innodb_defragment.test b/mysql-test/suite/innodb/t/innodb_defragment.test
index d9f5f56316e..51ef78377cb 100644
--- a/mysql-test/suite/innodb/t/innodb_defragment.test
+++ b/mysql-test/suite/innodb/t/innodb_defragment.test
@@ -1,6 +1,8 @@
 --source include/have_innodb.inc
 --source include/big_test.inc
 --source include/not_embedded.inc
+# Valgrind is to slow for this test
+--source include/not_valgrind.inc
 
 set global innodb_defragment_stats_accuracy = 80;
 
diff --git a/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result b/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result
index 92e22c6aa34..c11f4ee617c 100644
--- a/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result
+++ b/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result
@@ -1,28 +1,28 @@
 SET @start_global_value = @@global.innodb_change_buffering;
 SELECT @start_global_value;
 @start_global_value
-all
+none
 Valid values are 'all', 'deletes', 'changes', 'inserts', 'none', 'purges'
 select @@global.innodb_change_buffering in ('all', 'deletes', 'changes', 'inserts', 'none', 'purges');
 @@global.innodb_change_buffering in ('all', 'deletes', 'changes', 'inserts', 'none', 'purges')
 1
 select @@global.innodb_change_buffering;
 @@global.innodb_change_buffering
-all
+none
 select @@session.innodb_change_buffering;
 ERROR HY000: Variable 'innodb_change_buffering' is a GLOBAL variable
 show global variables like 'innodb_change_buffering';
 Variable_name	Value
-innodb_change_buffering	all
+innodb_change_buffering	none
 show session variables like 'innodb_change_buffering';
 Variable_name	Value
-innodb_change_buffering	all
+innodb_change_buffering	none
 select * from information_schema.global_variables where variable_name='innodb_change_buffering';
 VARIABLE_NAME	VARIABLE_VALUE
-INNODB_CHANGE_BUFFERING	all
+INNODB_CHANGE_BUFFERING	none
 select * from information_schema.session_variables where variable_name='innodb_change_buffering';
 VARIABLE_NAME	VARIABLE_VALUE
-INNODB_CHANGE_BUFFERING	all
+INNODB_CHANGE_BUFFERING	none
 set global innodb_change_buffering='none';
 select @@global.innodb_change_buffering;
 @@global.innodb_change_buffering
@@ -62,4 +62,4 @@ ERROR 42000: Variable 'innodb_change_buffering' can't be set to the value of 'so
 SET @@global.innodb_change_buffering = @start_global_value;
 SELECT @@global.innodb_change_buffering;
 @@global.innodb_change_buffering
-all
+none
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index c0803ed765b..e8725e044f5 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -311,7 +311,7 @@ READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	OPTIONAL
 VARIABLE_NAME	INNODB_CHANGE_BUFFERING
 SESSION_VALUE	NULL
-DEFAULT_VALUE	all
+DEFAULT_VALUE	none
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	ENUM
 VARIABLE_COMMENT	Buffer changes to secondary indexes.