Merge 10.9 into 10.10

17 files changed, 884 insertions, 83 deletions

diff --git a/mysql-test/main/bootstrap.result b/mysql-test/main/bootstrap.result
index fd9b4726cb6..cfff13136d6 100644
--- a/mysql-test/main/bootstrap.result
+++ b/mysql-test/main/bootstrap.result
@@ -1,15 +1,28 @@
-drop table if exists t1;
+#
+# test mysqld in bootstrap mode
+#
+#
+# Check that --bootstrap reads from stdin
+#
 # Kill the server
 # restart
 drop table t1;
+#
+# Check that --bootstrap of file with SQL error returns error
+#
 # Kill the server
 # restart
 drop table t1;
 ERROR 42S02: Unknown table 'test.t1'
+#
+# Bootstrap with a large thd->net.max_packet
+#
 # Kill the server
 # restart
 drop table t1;
-End of 5.1 tests
+# 
+# End of 5.1 tests
+#
 #
 # Bug #11766306: 59393: HAVE_INNODB=YES WHEN MYSQLD 
 #  STARTED WITH --SKIP-INNODB
@@ -18,9 +31,23 @@ SELECT 'bug' as '' FROM INFORMATION_SCHEMA.ENGINES WHERE engine='innodb'
  and SUPPORT='YES';
 
 # Kill the server
+#
+# MDEV-13063 Server crashes in intern_plugin_lock or assertion `plugin_ptr->ref_count == 1' fails in plugin_init
+#
+#
+# MDEV-19349 mysql_install_db: segfault at tmp_file_prefix check
+#
 # restart
-End of 5.5 tests
+#
+# End of 5.5 tests
+#
+#
+# Check that --bootstrap can install and uninstall plugins
+#
 # Kill the server
+#
+# Check that installed plugins are *not* automatically loaded in --bootstrap
+#
 # restart
 flush tables;
 show create table t1;
@@ -35,6 +62,18 @@ EXAMPLE	ha_example.so
 truncate table mysql.plugin;
 # Kill the server
 #
+# MDEV-9969 mysql_install_db error processing ignore_db_dirs.
+#
+#
+# MDEV-13397 MariaDB upgrade fail when using default_time_zone
+#
+#
+# MDEV-30818 invalid ssl prevents bootstrap
+#
+#
+# End of 10.3 tests
+#
+#
 # MDEV-28782 mariadb-tzinfo-to-sql to work in bootstrap mode
 #
 #
diff --git a/mysql-test/main/bootstrap.test b/mysql-test/main/bootstrap.test
index f7bddf23408..d7cc8e36d56 100644
--- a/mysql-test/main/bootstrap.test
+++ b/mysql-test/main/bootstrap.test
@@ -1,17 +1,20 @@
+--echo #
+--echo # test mysqld in bootstrap mode
+--echo #
 --source include/not_embedded.inc
-#
-# test mysqld in bootstrap mode
-#
---disable_warnings
-drop table if exists t1;
---enable_warnings
+--source include/have_example_plugin.inc
+
+--let test_bootstrap=$MYSQLTEST_VARDIR/tmp/test_bootstrap.sql
+--write_file $test_bootstrap
+use test;
+EOF
 
 # Add the datadir to the bootstrap command
 let $MYSQLD_DATADIR= `select @@datadir`;
 let $MYSQLD_BOOTSTRAP_CMD= $MYSQLD_BOOTSTRAP_CMD --datadir=$MYSQLD_DATADIR --tmpdir=$MYSQL_TMP_DIR --default-storage-engine=MyISAM --loose-skip-innodb --plugin-maturity=unknown;
-#
-# Check that --bootstrap reads from stdin
-#
+--echo #
+--echo # Check that --bootstrap reads from stdin
+--echo #
 --write_file $MYSQLTEST_VARDIR/tmp/bootstrap_test.sql
 use test;
 CREATE TABLE t1(a int);
@@ -21,9 +24,9 @@ EOF
 --source include/start_mysqld.inc
 drop table t1;
 remove_file $MYSQLTEST_VARDIR/tmp/bootstrap_test.sql;
-#
-# Check that --bootstrap of file with SQL error returns error
-#
+--echo #
+--echo # Check that --bootstrap of file with SQL error returns error
+--echo #
 --write_file $MYSQLTEST_VARDIR/tmp/bootstrap_error.sql
 use test;
 CREATE TABLE t1;
@@ -37,9 +40,9 @@ EOF
 drop table t1;
 remove_file $MYSQLTEST_VARDIR/tmp/bootstrap_error.sql;
 
-#
-# Bootstrap with a large thd->net.max_packet
-#
+--echo #
+--echo # Bootstrap with a large thd->net.max_packet
+--echo #
 --disable_query_log
 create table t1 select 2 as a, concat(repeat('MySQL', @@max_allowed_packet/10), ';') as b;
 eval select * into outfile '$MYSQLTEST_VARDIR/tmp/long_query.sql' from t1;
@@ -51,7 +54,9 @@ remove_file $MYSQLTEST_VARDIR/tmp/long_query.sql;
 --source include/start_mysqld.inc
 drop table t1;
 
---echo End of 5.1 tests
+--echo # 
+--echo # End of 5.1 tests
+--echo #
 
 --echo #
 --echo # Bug #11766306: 59393: HAVE_INNODB=YES WHEN MYSQLD 
@@ -63,29 +68,25 @@ SELECT 'bug' as '' FROM INFORMATION_SCHEMA.ENGINES WHERE engine='innodb'
  and SUPPORT='YES';
 
 --source include/kill_mysqld.inc
-#
-# MDEV-13063 Server crashes in intern_plugin_lock or assertion `plugin_ptr->ref_count == 1' fails in plugin_init
-#
+--echo #
+--echo # MDEV-13063 Server crashes in intern_plugin_lock or assertion `plugin_ptr->ref_count == 1' fails in plugin_init
+--echo #
 --error 1
 --exec $MYSQLD_BOOTSTRAP_CMD --myisam_recover_options=NONE
 
-#
-# MDEV-19349 mysql_install_db: segfault at tmp_file_prefix check
-#
---write_file $MYSQLTEST_VARDIR/tmp/1
-use test;
-EOF
---exec $MYSQLD_BOOTSTRAP_CMD < $MYSQLTEST_VARDIR/tmp/1 >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1
---remove_file $MYSQLTEST_VARDIR/tmp/1
+--echo #
+--echo # MDEV-19349 mysql_install_db: segfault at tmp_file_prefix check
+--echo #
+--exec $MYSQLD_BOOTSTRAP_CMD < $test_bootstrap >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1
 
 --source include/start_mysqld.inc
---echo End of 5.5 tests
+--echo #
+--echo # End of 5.5 tests
+--echo #
 
---source include/not_windows_embedded.inc
---source include/have_example_plugin.inc
-#
-# Check that --bootstrap can install and uninstall plugins
-#
+--echo #
+--echo # Check that --bootstrap can install and uninstall plugins
+--echo #
 let $PLUGIN_DIR=`select @@plugin_dir`;
 --source include/kill_mysqld.inc
 --write_file $MYSQLTEST_VARDIR/tmp/install_plugin.sql
@@ -95,9 +96,9 @@ EOF
 --exec $MYSQLD_BOOTSTRAP_CMD --plugin-dir=$PLUGIN_DIR  < $MYSQLTEST_VARDIR/tmp/install_plugin.sql >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1
 --remove_file $MYSQLTEST_VARDIR/tmp/install_plugin.sql
 
-#
-# Check that installed plugins are *not* automatically loaded in --bootstrap
-#
+--echo #
+--echo # Check that installed plugins are *not* automatically loaded in --bootstrap
+--echo #
 --write_file $MYSQLTEST_VARDIR/tmp/bootstrap_plugins.sql
 SET SQL_MODE="";
 use test;
@@ -113,25 +114,25 @@ drop table t1;
 select * from mysql.plugin;
 truncate table mysql.plugin;
 
-
-#
-# MDEV-9969 mysql_install_db error processing ignore_db_dirs.
-#
---write_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql
-use test;
-EOF
 --source include/kill_mysqld.inc
---exec $MYSQLD_BOOTSTRAP_CMD --ignore-db-dirs='some_dir' --ignore-db-dirs='some_dir' < $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1
---remove_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql
+--echo #
+--echo # MDEV-9969 mysql_install_db error processing ignore_db_dirs.
+--echo #
+--exec $MYSQLD_BOOTSTRAP_CMD --ignore-db-dirs='some_dir' --ignore-db-dirs='some_dir' < $test_bootstrap >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1
 
-#
-# MDEV-13397 MariaDB upgrade fail when using default_time_zone
-#
---write_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql
-use test;
-EOF
---exec $MYSQLD_BOOTSTRAP_CMD --default-time-zone=Europe/Moscow < $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1
---remove_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql
+--echo #
+--echo # MDEV-13397 MariaDB upgrade fail when using default_time_zone
+--echo #
+--exec $MYSQLD_BOOTSTRAP_CMD --default-time-zone=Europe/Moscow < $test_bootstrap >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1
+
+--echo #
+--echo # MDEV-30818 invalid ssl prevents bootstrap
+--echo #
+--exec $MYSQLD_BOOTSTRAP_CMD --ssl-ca=/dev/nonexistent < $test_bootstrap >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1
+
+--echo #
+--echo # End of 10.3 tests
+--echo #
 
 --echo #
 --echo # MDEV-28782 mariadb-tzinfo-to-sql to work in bootstrap mode
@@ -160,3 +161,4 @@ EOF
 
 # restore
 --source include/start_mysqld.inc
+--remove_file $test_bootstrap
diff --git a/mysql-test/main/ctype_uca_partitions.result b/mysql-test/main/ctype_uca_partitions.result
index 677b68e2387..486e848e191 100644
--- a/mysql-test/main/ctype_uca_partitions.result
+++ b/mysql-test/main/ctype_uca_partitions.result
@@ -84,3 +84,43 @@ O
 P
 Y
 DROP TABLE t1;
+#
+# Start of 10.4 tests
+#
+#
+# MDEV-30072 Wrong ORDER BY for a partitioned prefix key + NOPAD
+#
+SET NAMES utf8mb4;
+CREATE TABLE t1
+(
+id INT,
+data VARCHAR(20),
+KEY data_id (data,id)
+) COLLATE utf8mb3_unicode_nopad_ci ENGINE=MyISAM
+PARTITION BY RANGE COLUMNS (id)
+(
+PARTITION p10 VALUES LESS THAN (20),
+PARTITION p20 VALUES LESS THAN MAXVALUE
+);
+INSERT INTO t1 VALUES (30, 'ss '), (10, 'ß ');
+SELECT id FROM t1 WHERE data='ss ' ORDER BY id;
+id
+10
+30
+SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC;
+id
+30
+10
+ALTER TABLE t1 DROP KEY data_id, ADD KEY data_id2(data(10),id);
+SELECT id FROM t1 WHERE data='ss ' ORDER BY id;
+id
+10
+30
+SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC;
+id
+30
+10
+DROP TABLE t1;
+#
+# End of 10.4 tests
+#
diff --git a/mysql-test/main/ctype_uca_partitions.test b/mysql-test/main/ctype_uca_partitions.test
index 5734bb52008..81f1a091574 100644
--- a/mysql-test/main/ctype_uca_partitions.test
+++ b/mysql-test/main/ctype_uca_partitions.test
@@ -36,3 +36,35 @@ SELECT * FROM t1 PARTITION (p0) ORDER BY c1;
 SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
 SELECT * FROM t1 PARTITION (p2) ORDER BY c1;
 DROP TABLE t1;
+
+--echo #
+--echo # Start of 10.4 tests
+--echo #
+
+--echo #
+--echo # MDEV-30072 Wrong ORDER BY for a partitioned prefix key + NOPAD
+--echo #
+
+SET NAMES utf8mb4;
+CREATE TABLE t1
+(
+  id INT,
+  data VARCHAR(20),
+  KEY data_id (data,id)
+) COLLATE utf8mb3_unicode_nopad_ci ENGINE=MyISAM
+PARTITION BY RANGE COLUMNS (id)
+(
+  PARTITION p10 VALUES LESS THAN (20),
+  PARTITION p20 VALUES LESS THAN MAXVALUE
+);
+INSERT INTO t1 VALUES (30, 'ss '), (10, 'ß ');
+SELECT id FROM t1 WHERE data='ss ' ORDER BY id;
+SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC;
+ALTER TABLE t1 DROP KEY data_id, ADD KEY data_id2(data(10),id);
+SELECT id FROM t1 WHERE data='ss ' ORDER BY id;
+SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result
index 3576c1fa356..cda21e275ae 100644
--- a/mysql-test/main/derived.result
+++ b/mysql-test/main/derived.result
@@ -1330,5 +1330,28 @@ a	b
 DROP VIEW v1;
 DROP TABLE t1;
 #
+# MDEV-28616: derived table over union with order by clause that
+#             contains subquery with unresolvable column reference
+#
+SELECT 1 FROM (
+SELECT 1 UNION SELECT 2 ORDER BY  (SELECT 1 FROM DUAL WHERE xxx = 0)
+) dt;
+ERROR 42S22: Unknown column 'xxx' in 'where clause'
+create table t1 (a int, b int);
+insert into t1 values (3,8), (7,2), (1,4), (5,9);
+create table t2 (a int, b int);
+insert into t2 values (9,1), (7,3), (2,6);
+create table t3 (c int, d int);
+insert into t3 values (7,8), (1,2), (3,8);
+select * from
+(
+select a,b from t1 where t1.a > 3
+union
+select a,b from t2 where t2.b < 6
+order by (a - b / (select a + max(c) from t3  where d = x))
+) dt;
+ERROR 42S22: Unknown column 'x' in 'where clause'
+drop table t1,t2,t3;
+#
 # End of 10.3 tests
 #
diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test
index e25c1c89ad3..f78266b95b0 100644
--- a/mysql-test/main/derived.test
+++ b/mysql-test/main/derived.test
@@ -1144,5 +1144,35 @@ DROP VIEW v1;
 DROP TABLE t1;
 
 --echo #
+--echo # MDEV-28616: derived table over union with order by clause that
+--echo #             contains subquery with unresolvable column reference
+--echo #
+
+--error ER_BAD_FIELD_ERROR
+SELECT 1 FROM (
+  SELECT 1 UNION SELECT 2 ORDER BY  (SELECT 1 FROM DUAL WHERE xxx = 0)
+) dt;
+
+create table t1 (a int, b int);
+insert into t1 values (3,8), (7,2), (1,4), (5,9);
+
+create table t2 (a int, b int);
+insert into t2 values (9,1), (7,3), (2,6);
+
+create table t3 (c int, d int);
+insert into t3 values (7,8), (1,2), (3,8);
+
+--error ER_BAD_FIELD_ERROR
+select * from
+(
+  select a,b from t1 where t1.a > 3
+  union
+  select a,b from t2 where t2.b < 6
+  order by (a - b / (select a + max(c) from t3  where d = x))
+) dt;
+
+drop table t1,t2,t3;
+
+--echo #
 --echo # End of 10.3 tests
 --echo #
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;
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 83bc03fe0f9..70038a5f605 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -9948,6 +9948,401 @@ SELECT a FROM t1 WHERE (a,b) in (SELECT @c,@d);
 a
 DROP TABLE t1;
 #
+# MDEV-31085: multi-update using view with optimizer trace enabled
+#
+SET SESSION optimizer_trace = 'enabled=on';
+CREATE TABLE t (a int, b int);
+CREATE VIEW v AS SELECT 1 AS c UNION SELECT 2 AS c;
+INSERT INTO t VALUES (0,4),(5,6);
+UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a;
+SELECT * FROM information_schema.optimizer_trace;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
+UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a	{
+  "steps": [
+    {
+      "view": {
+        "table": "v",
+        "select_id": 2,
+        "algorithm": "materialized"
+      }
+    },
+    {
+      "join_preparation": {
+        "select_id": 2,
+        "steps": [
+          {
+            "expanded_query": "/* select#2 */ select 1 AS c"
+          }
+        ]
+      }
+    },
+    {
+      "join_preparation": {
+        "select_id": 3,
+        "steps": [
+          {
+            "expanded_query": "/* select#3 */ select 2 AS c"
+          }
+        ]
+      }
+    },
+    {
+      "join_preparation": {
+        "select_id": 1,
+        "steps": [
+          {
+            "expanded_query": "/* select#1 */ update t join v set t.b = t.a,t.a = v.c where v.c < t.a"
+          }
+        ]
+      }
+    },
+    {
+      "join_optimization": {
+        "select_id": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "v.c < t.a",
+              "steps": [
+                {
+                  "transformation": "equality_propagation",
+                  "resulting_condition": "v.c < t.a"
+                },
+                {
+                  "transformation": "constant_propagation",
+                  "resulting_condition": "v.c < t.a"
+                },
+                {
+                  "transformation": "trivial_condition_removal",
+                  "resulting_condition": "v.c < t.a"
+                }
+              ]
+            }
+          },
+          {
+            "join_optimization": {
+              "select_id": 2,
+              "steps": []
+            }
+          },
+          {
+            "join_optimization": {
+              "select_id": 3,
+              "steps": []
+            }
+          },
+          {
+            "table_dependencies": [
+              {
+                "table": "t",
+                "row_may_be_null": false,
+                "map_bit": 0,
+                "depends_on_map_bits": []
+              },
+              {
+                "table": "<derived2>",
+                "row_may_be_null": false,
+                "map_bit": 1,
+                "depends_on_map_bits": []
+              }
+            ]
+          },
+          {
+            "ref_optimizer_key_uses": []
+          },
+          {
+            "rows_estimation": [
+              {
+                "table": "t",
+                "table_scan": {
+                  "rows": 2,
+                  "cost": 2.004394531
+                }
+              },
+              {
+                "table": "<derived2>",
+                "table_scan": {
+                  "rows": 2,
+                  "cost": 2
+                }
+              }
+            ]
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "plan_prefix": [],
+                "get_costs_for_tables": [
+                  {
+                    "best_access_path": {
+                      "table": "t",
+                      "considered_access_paths": [
+                        {
+                          "access_type": "scan",
+                          "resulting_rows": 2,
+                          "cost": 2.004394531,
+                          "chosen": true
+                        }
+                      ],
+                      "chosen_access_method": {
+                        "type": "scan",
+                        "records": 2,
+                        "cost": 2.004394531,
+                        "uses_join_buffering": false
+                      }
+                    }
+                  },
+                  {
+                    "best_access_path": {
+                      "table": "<derived2>",
+                      "considered_access_paths": [
+                        {
+                          "access_type": "scan",
+                          "resulting_rows": 2,
+                          "cost": 2,
+                          "chosen": true
+                        }
+                      ],
+                      "chosen_access_method": {
+                        "type": "scan",
+                        "records": 2,
+                        "cost": 2,
+                        "uses_join_buffering": false
+                      }
+                    }
+                  }
+                ]
+              },
+              {
+                "plan_prefix": [],
+                "table": "t",
+                "rows_for_plan": 2,
+                "cost_for_plan": 2.404394531,
+                "rest_of_plan": [
+                  {
+                    "plan_prefix": ["t"],
+                    "get_costs_for_tables": [
+                      {
+                        "best_access_path": {
+                          "table": "<derived2>",
+                          "considered_access_paths": [
+                            {
+                              "access_type": "scan",
+                              "resulting_rows": 2,
+                              "cost": 2,
+                              "chosen": true
+                            }
+                          ],
+                          "chosen_access_method": {
+                            "type": "scan",
+                            "records": 2,
+                            "cost": 2,
+                            "uses_join_buffering": true
+                          }
+                        }
+                      }
+                    ]
+                  },
+                  {
+                    "plan_prefix": ["t"],
+                    "table": "<derived2>",
+                    "rows_for_plan": 4,
+                    "cost_for_plan": 5.204394531
+                  }
+                ]
+              },
+              {
+                "plan_prefix": [],
+                "table": "<derived2>",
+                "rows_for_plan": 2,
+                "cost_for_plan": 2.4,
+                "rest_of_plan": [
+                  {
+                    "plan_prefix": ["<derived2>"],
+                    "get_costs_for_tables": [
+                      {
+                        "best_access_path": {
+                          "table": "t",
+                          "considered_access_paths": [
+                            {
+                              "access_type": "scan",
+                              "resulting_rows": 2,
+                              "cost": 2.004394531,
+                              "chosen": true
+                            }
+                          ],
+                          "chosen_access_method": {
+                            "type": "scan",
+                            "records": 2,
+                            "cost": 2.004394531,
+                            "uses_join_buffering": true
+                          }
+                        }
+                      }
+                    ]
+                  },
+                  {
+                    "plan_prefix": ["<derived2>"],
+                    "table": "t",
+                    "rows_for_plan": 4,
+                    "cost_for_plan": 5.204394531,
+                    "pruned_by_cost": true,
+                    "current_cost": 5.204394531,
+                    "best_cost": 5.204394531
+                  }
+                ]
+              }
+            ]
+          },
+          {
+            "best_join_order": ["t", "<derived2>"]
+          },
+          {
+            "substitute_best_equal": {
+              "condition": "WHERE",
+              "resulting_condition": "v.c < t.a"
+            }
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "attached_conditions_computation": [],
+              "attached_conditions_summary": [
+                {
+                  "table": "t",
+                  "attached": null
+                },
+                {
+                  "table": "<derived2>",
+                  "attached": "v.c < t.a"
+                }
+              ]
+            }
+          }
+        ]
+      }
+    },
+    {
+      "join_execution": {
+        "select_id": 1,
+        "steps": [
+          {
+            "join_execution": {
+              "select_id": 2,
+              "steps": []
+            }
+          },
+          {
+            "join_execution": {
+              "select_id": 3,
+              "steps": []
+            }
+          },
+          {
+            "join_preparation": {
+              "select_id": "fake",
+              "steps": [
+                {
+                  "expanded_query": "select c AS c from dual"
+                }
+              ]
+            }
+          },
+          {
+            "join_optimization": {
+              "select_id": "fake",
+              "steps": [
+                {
+                  "table_dependencies": [
+                    {
+                      "table": "union",
+                      "row_may_be_null": false,
+                      "map_bit": 0,
+                      "depends_on_map_bits": []
+                    }
+                  ]
+                },
+                {
+                  "rows_estimation": [
+                    {
+                      "table": "union",
+                      "table_scan": {
+                        "rows": 2,
+                        "cost": 10.1
+                      }
+                    }
+                  ]
+                },
+                {
+                  "considered_execution_plans": [
+                    {
+                      "plan_prefix": [],
+                      "get_costs_for_tables": [
+                        {
+                          "best_access_path": {
+                            "table": "union",
+                            "considered_access_paths": [
+                              {
+                                "access_type": "scan",
+                                "resulting_rows": 2,
+                                "cost": 10.1,
+                                "chosen": true
+                              }
+                            ],
+                            "chosen_access_method": {
+                              "type": "scan",
+                              "records": 2,
+                              "cost": 10.1,
+                              "uses_join_buffering": false
+                            }
+                          }
+                        }
+                      ]
+                    },
+                    {
+                      "plan_prefix": [],
+                      "table": "union",
+                      "rows_for_plan": 2,
+                      "cost_for_plan": 10.5
+                    }
+                  ]
+                },
+                {
+                  "best_join_order": ["union"]
+                },
+                {
+                  "attaching_conditions_to_tables": {
+                    "attached_conditions_computation": [],
+                    "attached_conditions_summary": [
+                      {
+                        "table": "union",
+                        "attached": null
+                      }
+                    ]
+                  }
+                }
+              ]
+            }
+          },
+          {
+            "join_execution": {
+              "select_id": "fake",
+              "steps": []
+            }
+          }
+        ]
+      }
+    }
+  ]
+}	0	0
+SELECT * FROM t;
+a	b
+0	4
+1	5
+SET optimizer_trace=DEFAULT;
+DROP VIEW v;
+DROP TABLE t;
+#
 # End of 10.4 tests
 #
 set optimizer_trace='enabled=on';
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index d07afb2dfce..0cacc4a60ea 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -683,6 +683,25 @@ SELECT a FROM t1 WHERE (a,b) in (SELECT @c,@d);
 DROP TABLE t1;
 
 --echo #
+--echo # MDEV-31085: multi-update using view with optimizer trace enabled
+--echo #
+
+SET SESSION optimizer_trace = 'enabled=on';
+
+CREATE TABLE t (a int, b int);
+CREATE VIEW v AS SELECT 1 AS c UNION SELECT 2 AS c;
+INSERT INTO t VALUES (0,4),(5,6);
+UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a;
+SELECT * FROM information_schema.optimizer_trace;
+
+SELECT * FROM t;
+
+SET optimizer_trace=DEFAULT;
+
+DROP VIEW v;
+DROP TABLE t;
+
+--echo #
 --echo # End of 10.4 tests
 --echo #
 
diff --git a/mysql-test/main/parser.result b/mysql-test/main/parser.result
index 50be9dc49c8..89732e20b40 100644
--- a/mysql-test/main/parser.result
+++ b/mysql-test/main/parser.result
@@ -1910,12 +1910,32 @@ SET @@sql_mode=@save_sql_mode;
 #
 # MDEV-30151 parse error 1=2 not between/in
 #
-select 1=2 not in (3,4);
-1=2 not in (3,4)
+SELECT 1=2 NOT IN (3,4);
+1=2 NOT IN (3,4)
 1
-select 1=2 not between 3 and 4;
-1=2 not between 3 and 4
+SELECT 1=2 NOT BETWEEN 3 AND 4;
+1=2 NOT BETWEEN 3 AND 4
 1
+CREATE TABLE t1 ( f INT AS ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f` int(11) GENERATED ALWAYS AS (1 = 2 not between 3 and 4) VIRTUAL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE t1;
+CREATE TABLE t1 ( f INT, CHECK ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f` int(11) DEFAULT NULL,
+  CONSTRAINT `CONSTRAINT_1` CHECK (1 = 2 not between 3 and 4)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE t1;
+CREATE VIEW v1 AS SELECT 1 IN ( 2 NOT BETWEEN 3 AND 4 );
+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 1 = 2 not between 3 and 4 AS `1 IN ( 2 NOT BETWEEN 3 AND 4 )`	latin1	latin1_swedish_ci
+DROP VIEW v1;
 #
 # End of 10.3 tests
 #
diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test
index 48cda68b416..dcad30b36b8 100644
--- a/mysql-test/main/parser.test
+++ b/mysql-test/main/parser.test
@@ -1687,8 +1687,21 @@ SET @@sql_mode=@save_sql_mode;
 --echo #
 --echo # MDEV-30151 parse error 1=2 not between/in
 --echo #
-select 1=2 not in (3,4);
-select 1=2 not between 3 and 4;
+
+SELECT 1=2 NOT IN (3,4);
+SELECT 1=2 NOT BETWEEN 3 AND 4;
+
+CREATE TABLE t1 ( f INT AS ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 ( f INT, CHECK ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE VIEW v1 AS SELECT 1 IN ( 2 NOT BETWEEN 3 AND 4 );
+SHOW CREATE VIEW v1;
+DROP VIEW v1;
 
 --echo #
 --echo # End of 10.3 tests
diff --git a/mysql-test/main/sql_mode_pad_char_to_full_length.result b/mysql-test/main/sql_mode_pad_char_to_full_length.result
new file mode 100644
index 00000000000..6f68aade613
--- /dev/null
+++ b/mysql-test/main/sql_mode_pad_char_to_full_length.result
@@ -0,0 +1,94 @@
+#
+# Start of 10.4 tests
+#
+#
+# MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic
+#
+SET default_storage_engine=MyISAM;
+#
+# MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic
+#
+CREATE TABLE t1 (a INT,b CHAR(20));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` char(20) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+CREATE UNIQUE INDEX bi USING HASH ON t1 (b);
+INSERT INTO t1 VALUES (0,0);
+SET sql_mode='pad_char_to_full_length';
+DELETE FROM t1;
+DROP TABLE t1;
+SET sql_mode='';
+CREATE TABLE t1 (a INT,b CHAR(20));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` char(20) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+CREATE UNIQUE INDEX bi USING HASH ON t1 (b);
+SET sql_mode='pad_char_to_full_length';
+INSERT INTO t1 VALUES (0,0);
+DELETE FROM t1;
+DROP TABLE t1;
+SET sql_mode='';
+CREATE OR REPLACE TABLE t1 (a CHAR(20),b CHAR(20));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` char(20) DEFAULT NULL,
+  `b` char(20) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+CREATE UNIQUE INDEX bi USING HASH ON t1 (b);
+INSERT INTO t1 VALUES (0,0);
+SET sql_mode='pad_char_to_full_length';
+DELETE FROM t1;
+DROP TABLE t1;
+SET default_storage_engine=MEMORY;
+#
+# MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic
+#
+CREATE TABLE t1 (a INT,b CHAR(20));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` char(20) DEFAULT NULL
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+CREATE UNIQUE INDEX bi USING HASH ON t1 (b);
+INSERT INTO t1 VALUES (0,0);
+SET sql_mode='pad_char_to_full_length';
+DELETE FROM t1;
+DROP TABLE t1;
+SET sql_mode='';
+CREATE TABLE t1 (a INT,b CHAR(20));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` char(20) DEFAULT NULL
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+CREATE UNIQUE INDEX bi USING HASH ON t1 (b);
+SET sql_mode='pad_char_to_full_length';
+INSERT INTO t1 VALUES (0,0);
+DELETE FROM t1;
+DROP TABLE t1;
+SET sql_mode='';
+CREATE OR REPLACE TABLE t1 (a CHAR(20),b CHAR(20));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` char(20) DEFAULT NULL,
+  `b` char(20) DEFAULT NULL
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+CREATE UNIQUE INDEX bi USING HASH ON t1 (b);
+INSERT INTO t1 VALUES (0,0);
+SET sql_mode='pad_char_to_full_length';
+DELETE FROM t1;
+DROP TABLE t1;
+SET default_storage_engine=DEFAULT;
+#
+# End of 10.4 tests
+#
diff --git a/mysql-test/main/sql_mode_pad_char_to_full_length.test b/mysql-test/main/sql_mode_pad_char_to_full_length.test
new file mode 100644
index 00000000000..4d492bc1b70
--- /dev/null
+++ b/mysql-test/main/sql_mode_pad_char_to_full_length.test
@@ -0,0 +1,19 @@
+--echo #
+--echo # Start of 10.4 tests
+--echo #
+
+--echo #
+--echo # MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic
+--echo #
+
+SET default_storage_engine=MyISAM;
+--source include/sql_mode_pad_char_to_full_length.inc
+
+SET default_storage_engine=MEMORY;
+--source include/sql_mode_pad_char_to_full_length.inc
+
+SET default_storage_engine=DEFAULT;
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
diff --git a/mysql-test/main/update.result b/mysql-test/main/update.result
index f5edf1c6be3..b9b80fb48de 100644
--- a/mysql-test/main/update.result
+++ b/mysql-test/main/update.result
@@ -734,3 +734,32 @@ UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2;
 ERROR 22007: Incorrect datetime value: '19' for column `test`.`t1`.`i1` at row 1
 DROP TABLE t1,t2;
 # End of MariaDB 10.2 tests
+#
+# MDEV-20773: UPDATE with LIKE predicate over non-indexed column
+#             of VARCHAR type
+#
+create table t1 (a1 varchar(30), a2 varchar(30) collate utf8_bin);
+insert into t1 values
+('aa','zzz'), ('b','xxaa'), ('ccc','yyy'), ('ddd','xxb');
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+explain extended
+update t1 set a1 = 'u'
+  where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	50.00	Using where
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	4	50.00	Using where
+Warnings:
+Note	1003	/* select#1 */ update `test`.`t1` set `test`.`t1`.`a1` = 'u' where `test`.`t1`.`a2` like 'xx%'
+update t1 set a1 = 'u'
+  where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c');
+select * from t1;
+a1	a2
+aa	zzz
+u	xxaa
+ccc	yyy
+u	xxb
+drop table t1;
+# End of MariaDB 10.4 tests
diff --git a/mysql-test/main/update.test b/mysql-test/main/update.test
index 8a6949447ee..147d69d50c9 100644
--- a/mysql-test/main/update.test
+++ b/mysql-test/main/update.test
@@ -676,3 +676,26 @@ UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2;
 DROP TABLE t1,t2;
 
 --echo # End of MariaDB 10.2 tests
+
+--echo #
+--echo # MDEV-20773: UPDATE with LIKE predicate over non-indexed column
+--echo #             of VARCHAR type
+--echo #
+
+create table t1 (a1 varchar(30), a2 varchar(30) collate utf8_bin);
+insert into t1 values
+  ('aa','zzz'), ('b','xxaa'), ('ccc','yyy'), ('ddd','xxb');
+analyze table t1 persistent for all;
+
+explain extended
+update t1 set a1 = 'u'
+  where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c');
+
+update t1 set a1 = 'u'
+  where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c');
+
+select * from t1;
+
+drop table t1;
+
+--echo # End of MariaDB 10.4 tests