Merge 10.6 into 10.8

3 files changed, 44 insertions, 21 deletions

diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result
index 2b8b10b29b9..706a4132614 100644
--- a/mysql-test/main/group_min_max.result
+++ b/mysql-test/main/group_min_max.result
@@ -4083,6 +4083,18 @@ MIN(pk)
 1
 DROP TABLE t1, t2;
 #
+# MDEV-30605 Wrong result while using index for group-by
+#
+CREATE TABLE t1 (pk INT primary key, a int, key(a)) engine=innodb;
+INSERT INTO t1 VALUES (1,-1),(2,8),(3,5),(4,-1),(5,10), (6,-1);
+SELECT MIN(pk), a FROM t1 WHERE pk <> 1 GROUP BY a;
+MIN(pk)	a
+4	-1
+3	5
+2	8
+5	10
+DROP TABLE t1;
+#
 # End of 10.5 tests
 #
 #
diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test
index 0fa91e4d72b..1bc334dd3da 100644
--- a/mysql-test/main/group_min_max.test
+++ b/mysql-test/main/group_min_max.test
@@ -1739,6 +1739,17 @@ SELECT MIN(pk) FROM t1, t2;
 DROP TABLE t1, t2;
 
 --echo #
+--echo # MDEV-30605 Wrong result while using index for group-by
+--echo #
+
+CREATE TABLE t1 (pk INT primary key, a int, key(a)) engine=innodb;
+INSERT INTO t1 VALUES (1,-1),(2,8),(3,5),(4,-1),(5,10), (6,-1);
+
+SELECT MIN(pk), a FROM t1 WHERE pk <> 1 GROUP BY a;
+
+DROP TABLE t1;
+
+--echo #
 --echo # End of 10.5 tests
 --echo #
 
diff --git a/mysql-test/main/mysql_tzinfo_to_sql_symlink.result b/mysql-test/main/mysql_tzinfo_to_sql_symlink.result
index b6b35e44988..97548768a2d 100644
--- a/mysql-test/main/mysql_tzinfo_to_sql_symlink.result
+++ b/mysql-test/main/mysql_tzinfo_to_sql_symlink.result
@@ -10,13 +10,13 @@ CREATE TABLE time_zone_leap_second LIKE mysql.time_zone_leap_second;
 set @wsrep_is_on=(select coalesce(sum(SESSION_VALUE='ON'), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_on');
 SET STATEMENT SQL_MODE='' FOR SELECT concat('%', GROUP_CONCAT(OPTION), '%') INTO @replicate_opt  FROM   (SELECT DISTINCT concat('REPLICATE_', UPPER(ENGINE)) AS OPTION    FROM information_schema.TABLES    WHERE TABLE_SCHEMA=DATABASE()      AND TABLE_NAME IN ('time_zone',                         'time_zone_name',                         'time_zone_transition',                         'time_zone_transition_type',                         'time_zone_leap_second')      AND ENGINE in ('MyISAM',                     'Aria')) AS o ORDER BY OPTION DESC;
 set @wsrep_cannot_replicate_tz=@wsrep_is_on AND (select coalesce(sum(GLOBAL_VALUE NOT LIKE @replicate_opt), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_mode');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_name'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_name''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_name ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition_type'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition_type''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition_type ENGINE=InnoDB', 'do 0');
 TRUNCATE TABLE time_zone;
 TRUNCATE TABLE time_zone_name;
@@ -59,13 +59,13 @@ execute immediate if(@wsrep_cannot_replicate_tz, concat('ALTER TABLE time_zone_t
 set @wsrep_is_on=(select coalesce(sum(SESSION_VALUE='ON'), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_on');
 SET STATEMENT SQL_MODE='' FOR SELECT concat('%', GROUP_CONCAT(OPTION), '%') INTO @replicate_opt  FROM   (SELECT DISTINCT concat('REPLICATE_', UPPER(ENGINE)) AS OPTION    FROM information_schema.TABLES    WHERE TABLE_SCHEMA=DATABASE()      AND TABLE_NAME IN ('time_zone',                         'time_zone_name',                         'time_zone_transition',                         'time_zone_transition_type',                         'time_zone_leap_second')      AND ENGINE in ('MyISAM',                     'Aria')) AS o ORDER BY OPTION DESC;
 set @wsrep_cannot_replicate_tz=@wsrep_is_on AND (select coalesce(sum(GLOBAL_VALUE NOT LIKE @replicate_opt), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_mode');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_name'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_name''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_name ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition_type'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition_type''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition_type ENGINE=InnoDB', 'do 0');
 TRUNCATE TABLE time_zone;
 TRUNCATE TABLE time_zone_name;
@@ -191,13 +191,13 @@ TRUNCATE TABLE time_zone_leap_second;
 set @wsrep_is_on=(select coalesce(sum(SESSION_VALUE='ON'), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_on');
 SET STATEMENT SQL_MODE='' FOR SELECT concat('%', GROUP_CONCAT(OPTION), '%') INTO @replicate_opt  FROM   (SELECT DISTINCT concat('REPLICATE_', UPPER(ENGINE)) AS OPTION    FROM information_schema.TABLES    WHERE TABLE_SCHEMA=DATABASE()      AND TABLE_NAME IN ('time_zone',                         'time_zone_name',                         'time_zone_transition',                         'time_zone_transition_type',                         'time_zone_leap_second')      AND ENGINE in ('MyISAM',                     'Aria')) AS o ORDER BY OPTION DESC;
 set @wsrep_cannot_replicate_tz=@wsrep_is_on AND (select coalesce(sum(GLOBAL_VALUE NOT LIKE @replicate_opt), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_mode');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_name'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_name''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_name ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition_type'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition_type''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition_type ENGINE=InnoDB', 'do 0');
 /*M!100602 execute immediate if(@wsrep_cannot_replicate_tz, 'start transaction', 'LOCK TABLES time_zone WRITE,
   time_zone_leap_second WRITE,
@@ -313,20 +313,20 @@ TRUNCATE TABLE time_zone_leap_second;
 set @wsrep_is_on=(select coalesce(sum(SESSION_VALUE='ON'), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_on');
 SET STATEMENT SQL_MODE='' FOR SELECT concat('%', GROUP_CONCAT(OPTION), '%') INTO @replicate_opt  FROM   (SELECT DISTINCT concat('REPLICATE_', UPPER(ENGINE)) AS OPTION    FROM information_schema.TABLES    WHERE TABLE_SCHEMA=DATABASE()      AND TABLE_NAME IN ('time_zone',                         'time_zone_name',                         'time_zone_transition',                         'time_zone_transition_type',                         'time_zone_leap_second')      AND ENGINE in ('MyISAM',                     'Aria')) AS o ORDER BY OPTION DESC;
 set @wsrep_cannot_replicate_tz=@wsrep_is_on AND (select coalesce(sum(GLOBAL_VALUE NOT LIKE @replicate_opt), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_mode');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_name'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_name''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_name ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition_type'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition_type''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition_type ENGINE=InnoDB', 'do 0');
 /*M!100602 execute immediate if(@wsrep_cannot_replicate_tz, 'start transaction', 'LOCK TABLES time_zone WRITE,
   time_zone_leap_second WRITE,
   time_zone_name WRITE,
   time_zone_transition WRITE,
   time_zone_transition_type WRITE')*/;
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_leap_second_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_leap_second'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_leap_second_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_leap_second''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_leap_second ENGINE=InnoDB', 'do 0');
 TRUNCATE TABLE time_zone_leap_second;
 execute immediate if(@wsrep_cannot_replicate_tz, concat('ALTER TABLE time_zone_leap_second ENGINE=', @time_zone_leap_second_engine), 'do 0');
@@ -497,13 +497,13 @@ set sql_mode=default;
 set @wsrep_is_on=(select coalesce(sum(SESSION_VALUE='ON'), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_on');
 SET STATEMENT SQL_MODE='' FOR SELECT concat('%', GROUP_CONCAT(OPTION), '%') INTO @replicate_opt  FROM   (SELECT DISTINCT concat('REPLICATE_', UPPER(ENGINE)) AS OPTION    FROM information_schema.TABLES    WHERE TABLE_SCHEMA=DATABASE()      AND TABLE_NAME IN ('time_zone',                         'time_zone_name',                         'time_zone_transition',                         'time_zone_transition_type',                         'time_zone_leap_second')      AND ENGINE in ('MyISAM',                     'Aria')) AS o ORDER BY OPTION DESC;
 set @wsrep_cannot_replicate_tz=@wsrep_is_on AND (select coalesce(sum(GLOBAL_VALUE NOT LIKE @replicate_opt), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_mode');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_name'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_name''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_name ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition ENGINE=InnoDB', 'do 0');
-execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition_type'", 'do 0');
+execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition_type''', 'do 0');
 execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition_type ENGINE=InnoDB', 'do 0');
 TRUNCATE TABLE time_zone;
 TRUNCATE TABLE time_zone_name;