Merge branch '10.2' into 10.3

38 files changed, 994 insertions, 73 deletions

diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result
index 1bd731c956b..84537c0e280 100644
--- a/mysql-test/main/cte_nonrecursive.result
+++ b/mysql-test/main/cte_nonrecursive.result
@@ -571,7 +571,7 @@ with t as (select a from t1 where b >= 'c')
 select * from t2,t where t2.c=t.a;
 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 with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`b` >= 'c')select `t2`.`c` AS `c`,`t`.`a` AS `a` from (`t2` join `t`) where `t2`.`c` = `t`.`a`	latin1	latin1_swedish_ci
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `test`.`t2`.`c` AS `c`,`t`.`a` AS `a` from (`test`.`t2` join `t`) where `test`.`t2`.`c` = `t`.`a`	latin1	latin1_swedish_ci
 select * from v1;
 c	a
 4	4
@@ -588,7 +588,7 @@ with t as (select a, count(*) from t1 where b >= 'c' group by a)
 select * from t2,t where t2.c=t.a;
 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 with t as (select `t1`.`a` AS `a`,count(0) AS `count(*)` from `t1` where `t1`.`b` >= 'c' group by `t1`.`a`)select `t2`.`c` AS `c`,`t`.`a` AS `a`,`t`.`count(*)` AS `count(*)` from (`t2` join `t`) where `t2`.`c` = `t`.`a`	latin1	latin1_swedish_ci
+v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `test`.`t1`.`a` AS `a`,count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` >= 'c' group by `test`.`t1`.`a`)select `test`.`t2`.`c` AS `c`,`t`.`a` AS `a`,`t`.`count(*)` AS `count(*)` from (`test`.`t2` join `t`) where `test`.`t2`.`c` = `t`.`a`	latin1	latin1_swedish_ci
 select * from v2;
 c	a	count(*)
 4	4	2
@@ -606,7 +606,7 @@ with t(c) as (select a from t1 where b >= 'c')
 select * from t r1 where r1.c=4;
 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 with t(c) as (select `t1`.`a` AS `c` from `t1` where `t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4	latin1	latin1_swedish_ci
+v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t(c) as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4	latin1	latin1_swedish_ci
 select * from v3;
 c
 4
@@ -1349,7 +1349,7 @@ r.r_regionkey in
 select r_regionkey from t where r_name <> "ASIA");
 show create view v;
 View	Create View	character_set_client	collation_connection
-v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `n`.`n_nationkey` AS `n_nationkey`,`n`.`n_name` AS `n_name`,`n`.`n_regionkey` AS `n_regionkey`,`r`.`r_regionkey` AS `r_regionkey`,`r`.`r_name` AS `r_name` from (`nation` `n` join `region` `r`) where `n`.`n_regionkey` = `r`.`r_regionkey` and `r`.`r_regionkey` in (with t as (select `region`.`r_regionkey` AS `r_regionkey`,`region`.`r_name` AS `r_name` from `region` where `region`.`r_regionkey` <= 3)select `t`.`r_regionkey` from `t` where `t`.`r_name` <> 'ASIA')	latin1	latin1_swedish_ci
+v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `n`.`n_nationkey` AS `n_nationkey`,`n`.`n_name` AS `n_name`,`n`.`n_regionkey` AS `n_regionkey`,`r`.`r_regionkey` AS `r_regionkey`,`r`.`r_name` AS `r_name` from (`test`.`nation` `n` join `test`.`region` `r`) where `n`.`n_regionkey` = `r`.`r_regionkey` and `r`.`r_regionkey` in (with t as (select `test`.`region`.`r_regionkey` AS `r_regionkey`,`test`.`region`.`r_name` AS `r_name` from `test`.`region` where `test`.`region`.`r_regionkey` <= 3)select `t`.`r_regionkey` from `t` where `t`.`r_name` <> 'ASIA')	latin1	latin1_swedish_ci
 select * from v;
 n_nationkey	n_name	n_regionkey	r_regionkey	r_name
 0	ALGERIA	0	0	AFRICA
@@ -1690,6 +1690,41 @@ ERROR 3D000: No database selected
 DROP TABLE test.t;
 connection default;
 disconnect con1;
+#
+# MDEV-22781: create view with CTE without default database
+#
+drop database test;
+create database db1;
+create table db1.t1 (a int);
+insert into db1.t1 values (3),(7),(1);
+create view db1.v1 as with t as (select * from db1.t1) select * from t;
+show create view db1.v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS with t as (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`)select `t`.`a` AS `a` from `t`	latin1	latin1_swedish_ci
+select * from db1.v1;
+a
+3
+7
+1
+drop view db1.v1;
+prepare stmt from "
+create view db1.v1 as with t as (select * from db1.t1) select * from t;
+";
+execute stmt;
+deallocate prepare stmt;
+show create view db1.v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS with t as (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`)select `t`.`a` AS `a` from `t`	latin1	latin1_swedish_ci
+select * from db1.v1;
+a
+3
+7
+1
+drop view db1.v1;
+drop table db1.t1;
+drop database db1;
+create database test;
+use test;
 # End of 10.2 tests
 #
 # MDEV-21673: several references to CTE that uses
diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test
index 00242e3d6ae..aecec0f5496 100644
--- a/mysql-test/main/cte_nonrecursive.test
+++ b/mysql-test/main/cte_nonrecursive.test
@@ -1201,6 +1201,35 @@ DROP TABLE test.t;
 --connection default
 --disconnect con1
 
+--echo #
+--echo # MDEV-22781: create view with CTE without default database
+--echo #
+
+drop database test;
+create database db1;
+create table db1.t1 (a int);
+insert into db1.t1 values (3),(7),(1);
+
+create view db1.v1 as with t as (select * from db1.t1) select * from t;
+show create view db1.v1;
+select * from db1.v1;
+drop view db1.v1;
+
+prepare stmt from "
+create view db1.v1 as with t as (select * from db1.t1) select * from t;
+";
+execute stmt;
+deallocate prepare stmt;
+show create view db1.v1;
+select * from db1.v1;
+drop view db1.v1;
+
+drop table db1.t1;
+drop database db1;
+
+create database test;
+use test;
+
 --echo # End of 10.2 tests
 
 --echo #
diff --git a/mysql-test/main/cte_nonrecursive_not_embedded.result b/mysql-test/main/cte_nonrecursive_not_embedded.result
new file mode 100644
index 00000000000..c96a1ec2849
--- /dev/null
+++ b/mysql-test/main/cte_nonrecursive_not_embedded.result
@@ -0,0 +1,48 @@
+#
+# MDEV-20751: query using many CTEs with grant_tables enabled
+#
+connection default;
+CREATE DATABASE db;
+USE db;
+CREATE TABLE t1 (a int) ENGINE=MYISAM;
+INSERT INTO t1 VALUES (3), (7), (1);
+CREATE TABLE t2 (a int) ENGINE=MYISAM;
+INSERT INTO t2 VALUES (2), (8), (4);
+CREATE USER 'u1'@'localhost';
+GRANT USAGE ON db.* TO 'u1'@'localhost';
+GRANT SELECT ON db.t1 TO 'u1'@'localhost';
+FLUSH PRIVILEGES;
+connect  u1,'localhost',u1,,;
+connection u1;
+USE db;
+WITH
+cte1 AS
+(SELECT a FROM t1),
+cte2 AS
+(SELECT cte1.a FROM t1,cte1 WHERE cte1.a = t1.a),
+cte3 AS
+(SELECT cte2.a FROM t1,cte1,cte2 WHERE cte1.a = t1.a AND t1.a = cte2.a),
+cte4 AS
+(SELECT cte2.a FROM t1,cte2 WHERE cte2.a = t1.a)
+SELECT * FROM cte4 as r;
+a
+3
+7
+1
+WITH
+cte1 AS
+(SELECT a FROM t2),
+cte2 AS
+(SELECT cte1.a FROM t2,cte1 WHERE cte1.a = t2.a),
+cte3 AS
+(SELECT cte2.a FROM t2,cte1,cte2 WHERE cte1.a = t2.a AND t2.a = cte2.a),
+cte4 AS
+(SELECT cte2.a FROM t2,cte2 WHERE cte2.a = t2.a)
+SELECT * FROM cte4 as r;
+ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 't2'
+disconnect u1;
+connection default;
+DROP USER 'u1'@'localhost';
+DROP DATABASE db;
+USE test;
+# End of 10.2 tests
diff --git a/mysql-test/main/cte_nonrecursive_not_embedded.test b/mysql-test/main/cte_nonrecursive_not_embedded.test
new file mode 100644
index 00000000000..e80baeaf591
--- /dev/null
+++ b/mysql-test/main/cte_nonrecursive_not_embedded.test
@@ -0,0 +1,58 @@
+-- source include/not_embedded.inc
+
+--echo #
+--echo # MDEV-20751: query using many CTEs with grant_tables enabled
+--echo #
+
+--connection default
+
+CREATE DATABASE db;
+USE db;
+
+CREATE TABLE t1 (a int) ENGINE=MYISAM;
+INSERT INTO t1 VALUES (3), (7), (1);
+CREATE TABLE t2 (a int) ENGINE=MYISAM;
+INSERT INTO t2 VALUES (2), (8), (4);
+
+
+CREATE USER 'u1'@'localhost';
+GRANT USAGE ON db.* TO 'u1'@'localhost';
+GRANT SELECT ON db.t1 TO 'u1'@'localhost';
+FLUSH PRIVILEGES;
+
+--connect (u1,'localhost',u1,,)
+--connection u1
+USE db;
+
+WITH
+cte1 AS
+(SELECT a FROM t1),
+cte2 AS
+(SELECT cte1.a FROM t1,cte1 WHERE cte1.a = t1.a),
+cte3 AS
+(SELECT cte2.a FROM t1,cte1,cte2 WHERE cte1.a = t1.a AND t1.a = cte2.a),
+cte4 AS
+(SELECT cte2.a FROM t1,cte2 WHERE cte2.a = t1.a)
+SELECT * FROM cte4 as r;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+WITH
+cte1 AS
+(SELECT a FROM t2),
+cte2 AS
+(SELECT cte1.a FROM t2,cte1 WHERE cte1.a = t2.a),
+cte3 AS
+(SELECT cte2.a FROM t2,cte1,cte2 WHERE cte1.a = t2.a AND t2.a = cte2.a),
+cte4 AS
+(SELECT cte2.a FROM t2,cte2 WHERE cte2.a = t2.a)
+SELECT * FROM cte4 as r;
+
+--disconnect u1
+--connection default
+
+DROP USER 'u1'@'localhost';
+DROP DATABASE db;
+
+USE test;
+
+--echo # End of 10.2 tests
diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result
index 3f5be4c05a7..62354a619c1 100644
--- a/mysql-test/main/cte_recursive.result
+++ b/mysql-test/main/cte_recursive.result
@@ -818,7 +818,7 @@ where p.id = a.father or p.id = a.mother
 select * from ancestors;
 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 with recursive ancestors as (select `folks`.`id` AS `id`,`folks`.`name` AS `name`,`folks`.`dob` AS `dob`,`folks`.`father` AS `father`,`folks`.`mother` AS `mother` from `folks` where `folks`.`name` = 'Me' and `folks`.`dob` = '2000-01-01' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `a`) where `p`.`id` = `a`.`father` or `p`.`id` = `a`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors`	latin1	latin1_swedish_ci
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with recursive ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' and `test`.`folks`.`dob` = '2000-01-01' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `a`) where `p`.`id` = `a`.`father` or `p`.`id` = `a`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors`	latin1	latin1_swedish_ci
 select * from v1;
 id	name	dob	father	mother
 100	Me	2000-01-01	20	30
@@ -849,7 +849,7 @@ where p.id = ma.mother
 select * from ancestors;
 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 with recursive ancestors as (select `folks`.`id` AS `id`,`folks`.`name` AS `name`,`folks`.`dob` AS `dob`,`folks`.`father` AS `father`,`folks`.`mother` AS `mother` from `folks` where `folks`.`name` = 'Me' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `fa`) where `p`.`id` = `fa`.`father` union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `ma`) where `p`.`id` = `ma`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors`	latin1	latin1_swedish_ci
+v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with recursive ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `fa`) where `p`.`id` = `fa`.`father` union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `ma`) where `p`.`id` = `ma`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors`	latin1	latin1_swedish_ci
 select * from v2;
 id	name	dob	father	mother
 100	Me	2000-01-01	20	30
@@ -4237,6 +4237,269 @@ a	b	c
 deallocate prepare stmt;
 drop table t1;
 #
+# MDEV-24019: query with recursive CTE when no default database is set
+#
+drop database test;
+with recursive a as
+(select 1 from dual union select * from a as r)
+select * from a;
+1
+1
+create database db1;
+create table db1.t1 (a int);
+insert into db1.t1 values (3), (7), (1);
+with recursive cte as
+(select * from db1.t1 union select * from (select * from cte) as t)
+select * from cte;
+a
+3
+7
+1
+explain with recursive cte as
+(select * from db1.t1 union select * from (select * from cte) as t)
+select * from cte;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	
+3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	3	
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+prepare stmt from "with recursive cte as
+(select * from db1.t1 union select * from (select * from cte) as t)
+select * from cte";
+execute stmt;
+a
+3
+7
+1
+execute stmt;
+a
+3
+7
+1
+deallocate prepare stmt;
+drop database db1;
+create database test;
+use test;
+#
+# MDEV-23406: query with mutually recursive CTEs when big_tables=1
+#
+set @save_big_tables=@@big_tables;
+set big_tables=1;
+create table  folks(id int, name char(32), dob date, father int, mother int);
+insert into folks values
+(100, 'Me', '2000-01-01', 20, 30),
+(20, 'Dad', '1970-02-02', 10, 9),
+(30, 'Mom', '1975-03-03', 8, 7),
+(10, 'Grandpa Bill', '1940-04-05', null, null),
+(9, 'Grandma Ann', '1941-10-15', null, null),
+(25, 'Uncle Jim', '1968-11-18', 8, 7),
+(98, 'Sister Amy', '2001-06-20', 20, 30),
+(7, 'Grandma Sally', '1943-08-23', null, 6),
+(8, 'Grandpa Ben', '1940-10-21', null, null),
+(6, 'Grandgrandma Martha', '1923-05-17', null, null),
+(67, 'Cousin Eddie', '1992-02-28', 25, 27),
+(27, 'Auntie Melinda', '1971-03-29', null, null);
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w,  coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+union
+select h.*, w.*
+from folks v, folks h, folks w
+where v.name = 'Me' and
+(v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples;
+h_name	h_dob	w_name	w_dob
+Dad	1970-02-02	Mom	1975-03-03
+Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
+explain with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w,  coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+union
+select h.*, w.*
+from folks v, folks h, folks w
+where v.name = 'Me' and
+(v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	1728	
+4	DERIVED	<derived3>	ALL	NULL	NULL	NULL	NULL	1728	
+5	RECURSIVE UNION	<derived3>	ALL	NULL	NULL	NULL	NULL	1728	
+NULL	UNION RESULT	<union4,5>	ALL	NULL	NULL	NULL	NULL	NULL	
+3	DERIVED	v	ALL	NULL	NULL	NULL	NULL	12	Using where
+3	DERIVED	h	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
+3	DERIVED	w	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (incremental, BNL join)
+2	RECURSIVE UNION	<derived4>	ALL	NULL	NULL	NULL	NULL	2	
+2	RECURSIVE UNION	h	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
+2	RECURSIVE UNION	w	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (incremental, BNL join)
+NULL	UNION RESULT	<union3,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+prepare stmt from "with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w,  coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+union
+select h.*, w.*
+from folks v, folks h, folks w
+where v.name = 'Me' and
+(v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples";
+execute stmt;
+h_name	h_dob	w_name	w_dob
+Dad	1970-02-02	Mom	1975-03-03
+Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
+execute stmt;
+h_name	h_dob	w_name	w_dob
+Dad	1970-02-02	Mom	1975-03-03
+Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
+deallocate prepare stmt;
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w,  coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select *
+from folks
+where name = 'Me'
+  union all
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union all
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples;
+h_name	h_dob	w_name	w_dob
+Dad	1970-02-02	Mom	1975-03-03
+Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
+explain with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w,  coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select *
+from folks
+where name = 'Me'
+  union all
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union all
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+3	DERIVED	folks	ALL	NULL	NULL	NULL	NULL	12	Using where
+4	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+5	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+NULL	UNION RESULT	<union3,4,5>	ALL	NULL	NULL	NULL	NULL	NULL	
+2	DERIVED	h	ALL	NULL	NULL	NULL	NULL	12	
+2	DERIVED	<derived3>	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
+2	DERIVED	w	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (incremental, BNL join)
+prepare stmt from "with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w,  coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select *
+from folks
+where name = 'Me'
+  union all
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union all
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples";
+execute stmt;
+h_name	h_dob	w_name	w_dob
+Dad	1970-02-02	Mom	1975-03-03
+Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
+execute stmt;
+h_name	h_dob	w_name	w_dob
+Dad	1970-02-02	Mom	1975-03-03
+Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
+deallocate prepare stmt;
+drop table folks;
+set big_tables=@save_big_tables;
+#
 # End of 10.2 tests
 #
 #
diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
index 58264baac02..c3537e5bd0c 100644
--- a/mysql-test/main/cte_recursive.test
+++ b/mysql-test/main/cte_recursive.test
@@ -2726,6 +2726,135 @@ deallocate prepare stmt;
 drop table t1;
 
 --echo #
+--echo # MDEV-24019: query with recursive CTE when no default database is set
+--echo #
+
+drop database test;
+
+let $q=
+with recursive a as
+   (select 1 from dual union select * from a as r)
+select * from a;
+
+eval $q;
+
+create database db1;
+create table db1.t1 (a int);
+insert into db1.t1 values (3), (7), (1);
+
+let $q=
+with recursive cte as
+  (select * from db1.t1 union select * from (select * from cte) as t)
+select * from cte;
+
+eval $q;
+eval explain $q;
+
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop database db1;
+
+create database test;
+use test;
+
+--echo #
+--echo # MDEV-23406: query with mutually recursive CTEs when big_tables=1
+--echo #
+
+set @save_big_tables=@@big_tables;
+set big_tables=1;
+
+create table  folks(id int, name char(32), dob date, father int, mother int);
+
+insert into folks values
+(100, 'Me', '2000-01-01', 20, 30),
+(20, 'Dad', '1970-02-02', 10, 9),
+(30, 'Mom', '1975-03-03', 8, 7),
+(10, 'Grandpa Bill', '1940-04-05', null, null),
+(9, 'Grandma Ann', '1941-10-15', null, null),
+(25, 'Uncle Jim', '1968-11-18', 8, 7),
+(98, 'Sister Amy', '2001-06-20', 20, 30),
+(7, 'Grandma Sally', '1943-08-23', null, 6),
+(8, 'Grandpa Ben', '1940-10-21', null, null),
+(6, 'Grandgrandma Martha', '1923-05-17', null, null),
+(67, 'Cousin Eddie', '1992-02-28', 25, 27),
+(27, 'Auntie Melinda', '1971-03-29', null, null);
+
+let q=
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+                 w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+  select h.*, w.*
+    from folks h, folks w,  coupled_ancestors a
+      where a.father = h.id AND a.mother = w.id
+  union
+  select h.*, w.*
+    from folks v, folks h, folks w
+      where v.name = 'Me' and
+            (v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select h_id, h_name, h_dob, h_father, h_mother
+    from ancestor_couples
+  union
+  select w_id, w_name, w_dob, w_father, w_mother
+    from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+  from ancestor_couples;
+
+eval $q;
+eval explain $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q=
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+                 w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+  select h.*, w.*
+    from folks h, folks w,  coupled_ancestors a
+      where a.father = h.id AND a.mother = w.id
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select *
+    from folks
+      where name = 'Me'
+  union all
+  select h_id, h_name, h_dob, h_father, h_mother
+    from ancestor_couples
+  union all
+  select w_id, w_name, w_dob, w_father, w_mother
+    from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+  from ancestor_couples;
+
+eval $q;
+eval explain $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop table folks;
+
+set big_tables=@save_big_tables;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
 
diff --git a/mysql-test/main/ctype_utf16.result b/mysql-test/main/ctype_utf16.result
index 7fbe73bacef..f0350f022c6 100644
--- a/mysql-test/main/ctype_utf16.result
+++ b/mysql-test/main/ctype_utf16.result
@@ -1492,6 +1492,8 @@ ab
 AE
 AE
 SET max_sort_length=8;
+Warnings:
+Warning	1292	Truncated incorrect max_sort_length value: '8'
 SELECT * FROM t1 ORDER BY s1;
 s1
 ab
diff --git a/mysql-test/main/ctype_utf16le.result b/mysql-test/main/ctype_utf16le.result
index d98c113840b..a43ed6ee538 100644
--- a/mysql-test/main/ctype_utf16le.result
+++ b/mysql-test/main/ctype_utf16le.result
@@ -1765,6 +1765,8 @@ ab
 AE
 AE
 SET max_sort_length=8;
+Warnings:
+Warning	1292	Truncated incorrect max_sort_length value: '8'
 SELECT * FROM t1 ORDER BY s1;
 s1
 ab
diff --git a/mysql-test/main/ctype_utf32.result b/mysql-test/main/ctype_utf32.result
index 8f40a90859b..7598474e493 100644
--- a/mysql-test/main/ctype_utf32.result
+++ b/mysql-test/main/ctype_utf32.result
@@ -1505,6 +1505,8 @@ ab
 AE
 AE
 SET max_sort_length=8;
+Warnings:
+Warning	1292	Truncated incorrect max_sort_length value: '8'
 SELECT * FROM t1 ORDER BY s1;
 s1
 ab
diff --git a/mysql-test/main/ctype_utf8.result b/mysql-test/main/ctype_utf8.result
index 102e904d4f0..f454e5e6052 100644
--- a/mysql-test/main/ctype_utf8.result
+++ b/mysql-test/main/ctype_utf8.result
@@ -6755,9 +6755,11 @@ DFFFDFFF9CFF9DFF9EFF
 # Checking strnxfrm() with odd length
 #
 set max_sort_length=9;
+Warnings:
+Warning	1292	Truncated incorrect max_sort_length value: '9'
 select @@max_sort_length;
 @@max_sort_length
-9
+64
 create table t1 (a varchar(128) character set utf8 collate utf8_general_ci);
 insert into t1 values ('a'),('b'),('c');
 select * from t1 order by a;
diff --git a/mysql-test/main/ctype_utf8mb4.result b/mysql-test/main/ctype_utf8mb4.result
index 691ac51e241..bdcc07d590e 100644
--- a/mysql-test/main/ctype_utf8mb4.result
+++ b/mysql-test/main/ctype_utf8mb4.result
@@ -2371,10 +2371,10 @@ drop table t1;
 #
 # Check strnxfrm() with odd length
 #
-set max_sort_length=9;
+set max_sort_length=65;
 select @@max_sort_length;
 @@max_sort_length
-9
+65
 create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci);
 insert into t1 values ('a'),('b'),('c');
 select * from t1 order by a;
diff --git a/mysql-test/main/ctype_utf8mb4.test b/mysql-test/main/ctype_utf8mb4.test
index 532729dafde..8fbbee872ce 100644
--- a/mysql-test/main/ctype_utf8mb4.test
+++ b/mysql-test/main/ctype_utf8mb4.test
@@ -1520,7 +1520,7 @@ drop table t1;
 --echo #
 --echo # Check strnxfrm() with odd length
 --echo #
-set max_sort_length=9;
+set max_sort_length=65;
 select @@max_sort_length;
 create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci);
 insert into t1 values ('a'),('b'),('c');
diff --git a/mysql-test/main/ctype_utf8mb4_heap.result b/mysql-test/main/ctype_utf8mb4_heap.result
index 4aef2d8cb66..9eb7d48370f 100644
--- a/mysql-test/main/ctype_utf8mb4_heap.result
+++ b/mysql-test/main/ctype_utf8mb4_heap.result
@@ -2203,10 +2203,10 @@ drop table t1;
 #
 # Check strnxfrm() with odd length
 #
-set max_sort_length=9;
+set max_sort_length=65;
 select @@max_sort_length;
 @@max_sort_length
-9
+65
 create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci) engine heap;
 insert into t1 values ('a'),('b'),('c');
 select * from t1 order by a;
diff --git a/mysql-test/main/ctype_utf8mb4_innodb.result b/mysql-test/main/ctype_utf8mb4_innodb.result
index 3c7d0ba2fb7..fc2a368f3f0 100644
--- a/mysql-test/main/ctype_utf8mb4_innodb.result
+++ b/mysql-test/main/ctype_utf8mb4_innodb.result
@@ -2329,10 +2329,10 @@ drop table t1;
 #
 # Check strnxfrm() with odd length
 #
-set max_sort_length=9;
+set max_sort_length=65;
 select @@max_sort_length;
 @@max_sort_length
-9
+65
 create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci) engine InnoDB;
 insert into t1 values ('a'),('b'),('c');
 select * from t1 order by a;
diff --git a/mysql-test/main/ctype_utf8mb4_myisam.result b/mysql-test/main/ctype_utf8mb4_myisam.result
index fd8d7adf3a0..8940d10fc78 100644
--- a/mysql-test/main/ctype_utf8mb4_myisam.result
+++ b/mysql-test/main/ctype_utf8mb4_myisam.result
@@ -2336,10 +2336,10 @@ drop table t1;
 #
 # Check strnxfrm() with odd length
 #
-set max_sort_length=9;
+set max_sort_length=65;
 select @@max_sort_length;
 @@max_sort_length
-9
+65
 create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci) engine MyISAM;
 insert into t1 values ('a'),('b'),('c');
 select * from t1 order by a;
diff --git a/mysql-test/main/func_gconcat.result b/mysql-test/main/func_gconcat.result
index 94c24a63bb5..a7517e98134 100644
--- a/mysql-test/main/func_gconcat.result
+++ b/mysql-test/main/func_gconcat.result
@@ -1280,6 +1280,18 @@ Name_exp_1
 DROP VIEW v1;
 DROP TABLE t1;
 #
+# MDEV-4677 GROUP_CONCAT not showing any output with group_concat_max_len >= 4Gb
+#
+set group_concat_max_len=1024*1024*1024*4;
+Warnings:
+Warning	1292	Truncated incorrect group_concat_max_len value: '4294967296'
+create table t1 (i int, j int);
+insert into t1 values (1,1),(1,2);
+select i, group_concat(j) from t1 group by i;
+i	group_concat(j)
+1	1,2
+drop table t1;
+#
 # End of 10.2 tests
 #
 #
diff --git a/mysql-test/main/func_gconcat.test b/mysql-test/main/func_gconcat.test
index 2ab856e0edd..3c21aa04ffc 100644
--- a/mysql-test/main/func_gconcat.test
+++ b/mysql-test/main/func_gconcat.test
@@ -939,6 +939,16 @@ DROP VIEW v1;
 
 DROP TABLE t1;
 
+
+--echo #
+--echo # MDEV-4677 GROUP_CONCAT not showing any output with group_concat_max_len >= 4Gb
+--echo #
+set group_concat_max_len=1024*1024*1024*4;
+create table t1 (i int, j int);
+insert into t1 values (1,1),(1,2);
+select i, group_concat(j) from t1 group by i;
+drop table t1;
+
 --echo #
 --echo # End of 10.2 tests
 --echo #
diff --git a/mysql-test/main/func_like.result b/mysql-test/main/func_like.result
index 06a549e94b2..69387b3bb11 100644
--- a/mysql-test/main/func_like.result
+++ b/mysql-test/main/func_like.result
@@ -289,6 +289,24 @@ a	b	c	d
 3 f_	1	0	1
 3 f\_	0	1	0
 drop table t1;
+create table t1 (f int);
+insert t1 values (1),(2);
+select 1 from (select distinct * from t1) as x where f < (select 1 like 2 escape (3=1));
+1
+drop table t1;
+create table t1(f1 int);
+insert into t1 values(1);
+update (select 1 like 2 escape (1 in (select 1 from t1))) x, t1 as d set d.f1 = 1;
+ERROR HY000: Incorrect arguments to ESCAPE
+select * from (select 1 like 2 escape (1 in (select 1 from t1))) x;
+1 like 2 escape (1 in (select 1 from t1))
+0
+drop table t1;
+create table t1 (f int);
+insert t1 values (1),(2);
+create view v1 as select * from t1 where (1 like 2 escape (3 in (('h', 'b') in (select 'k', 'k' union select 'g', 'j'))) and f >= 0);
+drop view v1;
+drop table t1;
 #
 # MDEV-17359 - Extend expression supported by like (| & << >> || + - * / DIV MOD ^ )
 #
diff --git a/mysql-test/main/func_like.test b/mysql-test/main/func_like.test
index cb50fb91879..ef13d46c268 100644
--- a/mysql-test/main/func_like.test
+++ b/mysql-test/main/func_like.test
@@ -187,7 +187,7 @@ DROP TABLE t1;
 --echo #
 
 #
-# Item_func_line::print()
+# Item_func_like::print()
 #
 create view v1 as select 'foo!' like 'foo!!', 'foo!' like 'foo!!' escape '!';
 show create view v1;
@@ -208,6 +208,33 @@ set sql_mode=default;
 select * from t1;
 drop table t1;
 
+#
+# Item_func_like::fix_fields()
+#
+create table t1 (f int);
+insert t1 values (1),(2);
+select 1 from (select distinct * from t1) as x where f < (select 1 like 2 escape (3=1));
+drop table t1;
+
+#
+# Item_func_like::fix_fields, ESCAPE, const_item()
+#
+create table t1(f1 int);
+insert into t1 values(1);
+--error ER_WRONG_ARGUMENTS
+update (select 1 like 2 escape (1 in (select 1 from t1))) x, t1 as d set d.f1 = 1;
+select * from (select 1 like 2 escape (1 in (select 1 from t1))) x;
+drop table t1;
+
+#
+# Item_func_like::walk
+#
+create table t1 (f int);
+insert t1 values (1),(2);
+create view v1 as select * from t1 where (1 like 2 escape (3 in (('h', 'b') in (select 'k', 'k' union select 'g', 'j'))) and f >= 0);
+drop view v1;
+drop table t1;
+
 --echo #
 --echo # MDEV-17359 - Extend expression supported by like (| & << >> || + - * / DIV MOD ^ )
 --echo #
diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result
index 52bb7601c22..7aa7a075eb0 100644
--- a/mysql-test/main/information_schema.result
+++ b/mysql-test/main/information_schema.result
@@ -858,6 +858,7 @@ where data_type = 'longtext' and table_schema != 'performance_schema'
 order by binary table_name, ordinal_position;
 table_schema	table_name	column_name
 information_schema	ALL_PLUGINS	PLUGIN_DESCRIPTION
+information_schema	CHECK_CONSTRAINTS	CHECK_CLAUSE
 information_schema	COLUMNS	COLUMN_DEFAULT
 information_schema	COLUMNS	COLUMN_TYPE
 information_schema	COLUMNS	GENERATION_EXPRESSION
@@ -2208,7 +2209,7 @@ SCHEMA_NAME
 SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10;
 TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	IS_GENERATED	GENERATION_EXPRESSION
 Warnings:
-Warning	1931	Query execution was interrupted. The query examined at least 671 rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete
+Warning	1931	Query execution was interrupted. The query examined at least ### rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete
 #
 # End of 10.2 Test
 #
diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test
index ba6e64a1c70..97e5fe6b0bd 100644
--- a/mysql-test/main/information_schema.test
+++ b/mysql-test/main/information_schema.test
@@ -1930,6 +1930,7 @@ SELECT SCHEMA_NAME from information_schema.schemata where schema_name=REPEAT('a'
 --echo # Diagnostics_area::sql_errno upon query from I_S with LIMIT ROWS EXAMINED
 --echo #
 
+replace_regex /at least \d+ rows/at least ### rows/;
 SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10;
 
 --echo #
diff --git a/mysql-test/main/lock_view.result b/mysql-test/main/lock_view.result
index 4d375bace42..364c2cddf60 100644
--- a/mysql-test/main/lock_view.result
+++ b/mysql-test/main/lock_view.result
@@ -229,3 +229,37 @@ drop user definer@localhost;
 drop database mysqltest1;
 drop database mysqltest2;
 drop database mysqltest3;
+#
+# MDEV-24331 mysqldump fails with "Got error: 1356" if the database contains a view with a subquery
+#
+create user u1@localhost;
+grant all privileges on test.* to u1@localhost;
+connect con1,localhost,u1;
+use test;
+create table t1 (id int not null);
+create view v1 as select * from (select * from t1) dt;
+lock table v1 read;
+disconnect con1;
+connection default;
+SET @saved_cs_client     = @@character_set_client;
+SET character_set_client = utf8;
+/*!50001 CREATE TABLE `v1` (
+  `id` tinyint NOT NULL
+) ENGINE=MyISAM */;
+SET character_set_client = @saved_cs_client;
+/*!50001 DROP TABLE IF EXISTS `v1`*/;
+/*!50001 SET @saved_cs_client          = @@character_set_client */;
+/*!50001 SET @saved_cs_results         = @@character_set_results */;
+/*!50001 SET @saved_col_connection     = @@collation_connection */;
+/*!50001 SET character_set_client      = latin1 */;
+/*!50001 SET character_set_results     = latin1 */;
+/*!50001 SET collation_connection      = latin1_swedish_ci */;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`u1`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW `v1` AS select `dt`.`id` AS `id` from (select `test`.`t1`.`id` AS `id` from `test`.`t1`) `dt` */;
+/*!50001 SET character_set_client      = @saved_cs_client */;
+/*!50001 SET character_set_results     = @saved_cs_results */;
+/*!50001 SET collation_connection      = @saved_col_connection */;
+drop view v1;
+drop table t1;
+drop user u1@localhost;
diff --git a/mysql-test/main/lock_view.test b/mysql-test/main/lock_view.test
index 4b1adac5be1..abb8d317946 100644
--- a/mysql-test/main/lock_view.test
+++ b/mysql-test/main/lock_view.test
@@ -75,3 +75,20 @@ drop user definer@localhost;
 drop database mysqltest1;
 drop database mysqltest2;
 drop database mysqltest3;
+
+--echo #
+--echo # MDEV-24331 mysqldump fails with "Got error: 1356" if the database contains a view with a subquery
+--echo #
+create user u1@localhost;
+grant all privileges on test.* to u1@localhost;
+connect con1,localhost,u1;
+use test;
+create table t1 (id int not null);
+create view v1 as select * from (select * from t1) dt;
+lock table v1 read;
+disconnect con1;
+connection default;
+exec $MYSQL_DUMP test v1 -uu1 --compact;
+drop view v1;
+drop table t1;
+drop user u1@localhost;
diff --git a/mysql-test/main/mysql_upgrade.result b/mysql-test/main/mysql_upgrade.result
index 41556e21ced..8154a8fc49c 100644
--- a/mysql-test/main/mysql_upgrade.result
+++ b/mysql-test/main/mysql_upgrade.result
@@ -485,8 +485,6 @@ even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@loca
 DROP USER very_long_user_name_number_1, very_long_user_name_number_2, even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost;
 DROP PROCEDURE test.pr;
 use test;
-call mtr.add_suppression('Column last_update in table `mysql`.`innodb_table_stats` is INT NOT NULL but should be');
-alter table mysql.innodb_table_stats modify last_update int not null;
 create table extralongname_extralongname_extralongname_extralongname_ext (
 id int(10) unsigned not null,
 created_date date not null,
diff --git a/mysql-test/main/mysql_upgrade.test b/mysql-test/main/mysql_upgrade.test
index b5a5c173e26..ab4ac9e2b4a 100644
--- a/mysql-test/main/mysql_upgrade.test
+++ b/mysql-test/main/mysql_upgrade.test
@@ -173,8 +173,6 @@ DROP PROCEDURE test.pr;
 # MDEV-13274 mysql_upgrade fails if dbname+tablename+partioname > 64 chars
 #
 use test;
-call mtr.add_suppression('Column last_update in table `mysql`.`innodb_table_stats` is INT NOT NULL but should be');
-alter table mysql.innodb_table_stats modify last_update int not null;
 
 create table extralongname_extralongname_extralongname_extralongname_ext (
   id int(10) unsigned not null,
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index e46ca7377b0..f4e88d6e6e3 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -763,18 +763,20 @@ col2	col	col
 2	2	2
 1	3	3
 drop table t1, t2;
-create table t1 (a char(25));
+create table t1 (a char(70));
 insert into t1 set a = repeat('x', 20);
-insert into t1 set a = concat(repeat('x', 19), 'z');
-insert into t1 set a = concat(repeat('x', 19), 'ab');
-insert into t1 set a = concat(repeat('x', 19), 'aa');
+insert into t1 set a = concat(repeat('x', 63), 'z');
+insert into t1 set a = concat(repeat('x', 63), 'ab');
+insert into t1 set a = concat(repeat('x', 63), 'aa');
 set max_sort_length=20;
+Warnings:
+Warning	1292	Truncated incorrect max_sort_length value: '20'
 select a from t1 order by a;
 a
-xxxxxxxxxxxxxxxxxxxab
-xxxxxxxxxxxxxxxxxxxaa
 xxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxz
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxab
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxaa
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxz
 drop table t1;
 create table t1 (
 `sid` decimal(8,0) default null,
@@ -3241,6 +3243,8 @@ drop table t1;
 SET @save_sort_buffer_size= @@sort_buffer_size;
 SET @save_max_sort_length= @@max_sort_length;
 SET max_sort_length=8;
+Warnings:
+Warning	1292	Truncated incorrect max_sort_length value: '8'
 SET sort_buffer_size=1024;
 CREATE TABLE t1(a INT, b DECIMAL(65), c BLOB);
 INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_25;
@@ -3308,6 +3312,8 @@ SET @save_max_sort_length= @@max_sort_length;
 SET @save_sort_buffer_size= @@sort_buffer_size;
 SET @save_max_length_for_sort_data= @@max_length_for_sort_data;
 SET max_sort_length=8;
+Warnings:
+Warning	1292	Truncated incorrect max_sort_length value: '8'
 SET sort_buffer_size=1024;
 SET max_length_for_sort_data=7000;
 CREATE TABLE t1(a VARCHAR(64), b VARCHAR(2048))DEFAULT CHARSET=utf8;
@@ -3443,6 +3449,8 @@ INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
 SET max_length_for_sort_data= 30;
 SET sql_select_limit = 3;
 SET max_sort_length=8;
+Warnings:
+Warning	1292	Truncated incorrect max_sort_length value: '8'
 SELECT * FROM t1 ORDER BY a+1;
 a	b
 1	1
diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test
index f94ad945d67..74884144a98 100644
--- a/mysql-test/main/order_by.test
+++ b/mysql-test/main/order_by.test
@@ -531,11 +531,11 @@ drop table t1, t2;
 # Bug #5428: a problem with small max_sort_length value
 #
 
-create table t1 (a char(25));
+create table t1 (a char(70));
 insert into t1 set a = repeat('x', 20);
-insert into t1 set a = concat(repeat('x', 19), 'z');
-insert into t1 set a = concat(repeat('x', 19), 'ab');
-insert into t1 set a = concat(repeat('x', 19), 'aa');
+insert into t1 set a = concat(repeat('x', 63), 'z');
+insert into t1 set a = concat(repeat('x', 63), 'ab');
+insert into t1 set a = concat(repeat('x', 63), 'aa');
 set max_sort_length=20;
 select a from t1 order by a;
 drop table t1;
diff --git a/mysql-test/main/precedence.result b/mysql-test/main/precedence.result
index d5d4e662a8e..fc6579651b4 100644
--- a/mysql-test/main/precedence.result
+++ b/mysql-test/main/precedence.result
@@ -8016,4 +8016,8 @@ create or replace view v1 as select 1 IS TRUE IS FALSE, 2 IS FALSE IS UNKNOWN, 3
 Select view_definition from information_schema.views where table_schema='test' and table_name='v1';
 view_definition
 select 1 is true is false AS `1 IS TRUE IS FALSE`,/*always not null*/ 1 is null AS `2 IS FALSE IS UNKNOWN`,/*always not null*/ 1 is null AS `3 IS UNKNOWN IS NULL`,/*always not null*/ 1 is null is true AS `4 IS NULL IS TRUE`
+create or replace view v1 as select 2 IS TRUE = 3, 2 IS FALSE = 3, 2 IS UNKNOWN = 3, 2 IS NULL = 3, ISNULL(2) = 1;
+Select view_definition from information_schema.views where table_schema='test' and table_name='v1';
+view_definition
+select 2 is true = 3 AS `2 IS TRUE = 3`,2 is false = 3 AS `2 IS FALSE = 3`,/*always not null*/ 1 is null = 3 AS `2 IS UNKNOWN = 3`,/*always not null*/ 1 is null = 3 AS `2 IS NULL = 3`,/*always not null*/ 1 is null = 1 AS `ISNULL(2) = 1`
 drop view v1;
diff --git a/mysql-test/main/precedence.test b/mysql-test/main/precedence.test
index ad367c23603..cd7cee4f911 100644
--- a/mysql-test/main/precedence.test
+++ b/mysql-test/main/precedence.test
@@ -4785,4 +4785,7 @@ Select view_definition from information_schema.views where table_schema='test' a
 create or replace view v1 as select 1 IS TRUE IS FALSE, 2 IS FALSE IS UNKNOWN, 3 IS UNKNOWN IS NULL, 4 IS NULL IS TRUE;
 Select view_definition from information_schema.views where table_schema='test' and table_name='v1';
 
+create or replace view v1 as select 2 IS TRUE = 3, 2 IS FALSE = 3, 2 IS UNKNOWN = 3, 2 IS NULL = 3, ISNULL(2) = 1;
+Select view_definition from information_schema.views where table_schema='test' and table_name='v1';
+
 drop view v1;
diff --git a/mysql-test/main/query_cache.result b/mysql-test/main/query_cache.result
index 439d5d24c46..b3ec48ac830 100644
--- a/mysql-test/main/query_cache.result
+++ b/mysql-test/main/query_cache.result
@@ -814,33 +814,33 @@ select @@character_set_results;
 NULL
 set character_set_results=default;
 set GLOBAL query_cache_size=1355776;
-create table t1 (id int auto_increment primary key, c char(25));
+create table t1 (id int auto_increment primary key, c char(65));
 insert into t1 set c = repeat('x',24);
-insert into t1 set c = concat(repeat('x',24),'x');
-insert into t1 set c = concat(repeat('x',24),'w');
-insert into t1 set c = concat(repeat('x',24),'y');
+insert into t1 set c = concat(repeat('x',64),'x');
+insert into t1 set c = concat(repeat('x',64),'w');
+insert into t1 set c = concat(repeat('x',64),'y');
 set max_sort_length=200;
 select c from t1 order by c, id;
 c
 xxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxw
-xxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy
 reset query cache;
-set max_sort_length=20;
+set max_sort_length=64;
 select c from t1 order by c, id;
 c
 xxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxw
-xxxxxxxxxxxxxxxxxxxxxxxxy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy
 set max_sort_length=200;
 select c from t1 order by c, id;
 c
 xxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxw
-xxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy
 set max_sort_length=default;
 select '1' || '3' from t1;
 '1' || '3'
diff --git a/mysql-test/main/query_cache.test b/mysql-test/main/query_cache.test
index 7235c6af3b3..d518d7a3956 100644
--- a/mysql-test/main/query_cache.test
+++ b/mysql-test/main/query_cache.test
@@ -606,15 +606,15 @@ set character_set_results=default;
 #
 # max_sort_length
 set GLOBAL query_cache_size=1355776;
-create table t1 (id int auto_increment primary key, c char(25));
+create table t1 (id int auto_increment primary key, c char(65));
 insert into t1 set c = repeat('x',24);
-insert into t1 set c = concat(repeat('x',24),'x');
-insert into t1 set c = concat(repeat('x',24),'w');
-insert into t1 set c = concat(repeat('x',24),'y');
+insert into t1 set c = concat(repeat('x',64),'x');
+insert into t1 set c = concat(repeat('x',64),'w');
+insert into t1 set c = concat(repeat('x',64),'y');
 set max_sort_length=200;
 select c from t1 order by c, id;
 reset query cache;
-set max_sort_length=20;
+set max_sort_length=64;
 select c from t1 order by c, id;
 set max_sort_length=200;
 select c from t1 order by c, id;
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index a54b76c0b3f..5a956fd1109 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -3137,6 +3137,31 @@ pk	a	b
 1	5	50
 65	5	50
 drop table t1;
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (
+pk int primary key,
+key1 int,
+col1 varchar(255),
+key (key1, pk)
+);
+insert into t2 (pk, key1)
+select A.a+10 *B.a + 100*C.a, A.a+10 *B.a +100*C.a from t1 A, t1 B, t1 C;
+# This must use ALL, not range:
+explain select * from t2 force index (primary) where pk not in (1,2,3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1000	Using where
+drop table t1,t2;
+#
+# MDEV-24444: ASAN use-after-poison in Item_func_in::get_func_mm_tree with NOT IN
+#
+CREATE TABLE t1 (id INT, a CHAR(3), b INT, PRIMARY KEY(id), KEY(b), KEY(a));
+INSERT INTO t1 VALUES (1,'foo',10),(2,'bar',20);
+CREATE TABLE t2 (code CHAR(8), num INT, PRIMARY KEY (code));
+INSERT INTO t2 VALUES ('100',1),('111',2);
+SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR t2.num != 3;
+id	a	b	code	num
+DROP TABLE t1, t2;
 #
 # End of 10.2 tests
 #
diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test
index c850498d7f2..0cb36a6e823 100644
--- a/mysql-test/main/range.test
+++ b/mysql-test/main/range.test
@@ -2155,6 +2155,40 @@ eval $q4;
 
 drop table t1;
 
+#
+# MDEV-21958: Query having many NOT-IN clauses running forever (testcase 2)
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t2 (
+  pk int primary key,
+  key1 int,
+  col1 varchar(255),
+  key (key1, pk)
+);
+
+insert into t2 (pk, key1)
+select A.a+10 *B.a + 100*C.a, A.a+10 *B.a +100*C.a from t1 A, t1 B, t1 C;
+
+--echo # This must use ALL, not range:
+explain select * from t2 force index (primary) where pk not in (1,2,3);
+
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-24444: ASAN use-after-poison in Item_func_in::get_func_mm_tree with NOT IN
+--echo #
+CREATE TABLE t1 (id INT, a CHAR(3), b INT, PRIMARY KEY(id), KEY(b), KEY(a));
+INSERT INTO t1 VALUES (1,'foo',10),(2,'bar',20);
+
+CREATE TABLE t2 (code CHAR(8), num INT, PRIMARY KEY (code));
+INSERT INTO t2 VALUES ('100',1),('111',2);
+
+SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR t2.num != 3;
+
+DROP TABLE t1, t2;
+
 --echo #
 --echo # End of 10.2 tests
 --echo #
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index 98e95e75d37..a462d9d8736 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -3149,6 +3149,31 @@ pk	a	b
 70	4	40
 71	2	20
 drop table t1;
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (
+pk int primary key,
+key1 int,
+col1 varchar(255),
+key (key1, pk)
+);
+insert into t2 (pk, key1)
+select A.a+10 *B.a + 100*C.a, A.a+10 *B.a +100*C.a from t1 A, t1 B, t1 C;
+# This must use ALL, not range:
+explain select * from t2 force index (primary) where pk not in (1,2,3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1000	Using where
+drop table t1,t2;
+#
+# MDEV-24444: ASAN use-after-poison in Item_func_in::get_func_mm_tree with NOT IN
+#
+CREATE TABLE t1 (id INT, a CHAR(3), b INT, PRIMARY KEY(id), KEY(b), KEY(a));
+INSERT INTO t1 VALUES (1,'foo',10),(2,'bar',20);
+CREATE TABLE t2 (code CHAR(8), num INT, PRIMARY KEY (code));
+INSERT INTO t2 VALUES ('100',1),('111',2);
+SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR t2.num != 3;
+id	a	b	code	num
+DROP TABLE t1, t2;
 #
 # End of 10.2 tests
 #
diff --git a/mysql-test/main/type_year.result b/mysql-test/main/type_year.result
index 99c3c50ea8c..75876d2efad 100644
--- a/mysql-test/main/type_year.result
+++ b/mysql-test/main/type_year.result
@@ -1,4 +1,3 @@
-drop table if exists t1;
 create table t1 (y year,y2 year(2));
 Warnings:
 Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
@@ -28,6 +27,9 @@ y	y2
 2001	01
 2069	69
 drop table t1;
+#
+# Bug 2335
+#
 create table t1 (y year);
 insert ignore into t1 values (now());
 Warnings:
@@ -36,6 +38,9 @@ select if(y = now(), 1, 0) from t1;
 if(y = now(), 1, 0)
 1
 drop table t1;
+#
+# Bug #27176: Assigning a string to an year column has unexpected results
+#
 create table t1(a year);
 insert into t1 values (2000.5), ('2000.5'), ('2001a'), ('2.001E3');
 Warnings:
@@ -47,7 +52,9 @@ a
 2001
 2001
 drop table t1;
-End of 5.0 tests
+#
+# End of 5.0 tests
+#
 #
 # Bug #49480: WHERE using YEAR columns returns unexpected results
 #
@@ -373,7 +380,8 @@ Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Pleas
 Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 DROP TABLE t1;
 #
-End of 5.1 tests
+# End of 5.1 tests
+#
 create function y2k() returns int deterministic return 2000;
 create table t1 (a year(2), b int);
 Warnings:
@@ -456,9 +464,6 @@ DROP TABLE t1;
 # End of 10.1 tests
 #
 #
-# Start of 10.2 tests
-#
-#
 # MDEV-9392 Copying from DECIMAL to YEAR is not consistent about warnings
 #
 CREATE TABLE t1 (a YEAR);
@@ -500,5 +505,63 @@ Warnings:
 Warning	1264	Out of range value for column 'a' at row 1
 DROP TABLE t1;
 #
+# Various widths of the YEAR
+#
+create or replace table t1 (a YEAR(0));
+Warnings:
+Note	1287	'YEAR(0)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` year(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t1 (a YEAR(1));
+Warnings:
+Note	1287	'YEAR(1)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` year(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t1 (a YEAR(2));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` year(2) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t1 (a YEAR(3));
+Warnings:
+Note	1287	'YEAR(3)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` year(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t1 (a YEAR(4));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` year(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t1 (a YEAR(5));
+Warnings:
+Note	1287	'YEAR(5)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` year(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t1 (a YEAR(100));
+Warnings:
+Note	1287	'YEAR(100)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` year(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/main/type_year.test b/mysql-test/main/type_year.test
index af26a69c581..34c417d9026 100644
--- a/mysql-test/main/type_year.test
+++ b/mysql-test/main/type_year.test
@@ -1,9 +1,6 @@
 #
 # Test year
 #
---disable_warnings
-drop table if exists t1;
---enable_warnings
 
 create table t1 (y year,y2 year(2));
 insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69);
@@ -12,24 +9,27 @@ select * from t1 order by y;
 select * from t1 order by y2;
 drop table t1;
 
-#
-# Bug 2335
-#
+--echo #
+--echo # Bug 2335
+--echo #
 
 create table t1 (y year);
 insert ignore into t1 values (now());
 select if(y = now(), 1, 0) from t1;
 drop table t1;
 
-#
-# Bug #27176: Assigning a string to an year column has unexpected results
-#
+--echo #
+--echo # Bug #27176: Assigning a string to an year column has unexpected results
+--echo #
+
 create table t1(a year);
 insert into t1 values (2000.5), ('2000.5'), ('2001a'), ('2.001E3');
 select * from t1;
 drop table t1;
 
---echo End of 5.0 tests
+--echo #
+--echo # End of 5.0 tests
+--echo #
 
 --echo #
 --echo # Bug #49480: WHERE using YEAR columns returns unexpected results
@@ -169,8 +169,9 @@ ALTER TABLE t1 MODIFY COLUMN c2 YEAR(2);
 DROP TABLE t1;
 
 --echo #
+--echo # End of 5.1 tests
+--echo #
 
---echo End of 5.1 tests
 #
 # fun with convert_const_to_int
 # in some cases 00 is equal to 2000, in others it is not.
@@ -230,10 +231,6 @@ DROP TABLE t1;
 --echo #
 
 --echo #
---echo # Start of 10.2 tests
---echo #
-
---echo #
 --echo # MDEV-9392 Copying from DECIMAL to YEAR is not consistent about warnings
 --echo #
 CREATE TABLE t1 (a YEAR);
@@ -268,6 +265,17 @@ SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 MODIFY a YEAR;
 DROP TABLE t1;
 
+--echo #
+--echo # Various widths of the YEAR
+--echo #
+create or replace table t1 (a YEAR(0)); SHOW CREATE TABLE t1;
+create or replace table t1 (a YEAR(1)); SHOW CREATE TABLE t1;
+create or replace table t1 (a YEAR(2)); SHOW CREATE TABLE t1;
+create or replace table t1 (a YEAR(3)); SHOW CREATE TABLE t1;
+create or replace table t1 (a YEAR(4)); SHOW CREATE TABLE t1;
+create or replace table t1 (a YEAR(5)); SHOW CREATE TABLE t1;
+create or replace table t1 (a YEAR(100)); SHOW CREATE TABLE t1;
+drop table t1;
 
 --echo #
 --echo # End of 10.2 tests
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
index b6ad828e43a..71f8e75c930 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -6711,6 +6711,41 @@ DROP PROCEDURE sp;
 DROP VIEW v1;
 DROP TABLE t1;
 #
+# MDEV-24314: create view with derived table without default database
+#
+drop database test;
+create database db1;
+create table db1.t1 (a int);
+insert into db1.t1 values (3),(7),(1);
+create view db1.v1 as select * from (select * from db1.t1) t;
+show create view db1.v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS select `t`.`a` AS `a` from (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`) `t`	latin1	latin1_swedish_ci
+select * from db1.v1;
+a
+3
+7
+1
+drop view db1.v1;
+prepare stmt from "
+create view db1.v1 as select * from (select * from db1.t1) t;
+";
+execute stmt;
+deallocate prepare stmt;
+show create view db1.v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS select `t`.`a` AS `a` from (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`) `t`	latin1	latin1_swedish_ci
+select * from db1.v1;
+a
+3
+7
+1
+drop view db1.v1;
+drop table db1.t1;
+drop database db1;
+create database test;
+use test;
+#
 # End of 10.2 tests
 #
 #
diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
index 6e7b0adfbc3..b3650503e08 100644
--- a/mysql-test/main/view.test
+++ b/mysql-test/main/view.test
@@ -6432,6 +6432,36 @@ DROP VIEW v1;
 DROP TABLE t1;
 
 --echo #
+--echo # MDEV-24314: create view with derived table without default database
+--echo #
+
+drop database test;
+
+create database db1;
+create table db1.t1 (a int);
+insert into db1.t1 values (3),(7),(1);
+
+create view db1.v1 as select * from (select * from db1.t1) t;
+show create view db1.v1;
+select * from db1.v1;
+drop view db1.v1;
+
+prepare stmt from "
+create view db1.v1 as select * from (select * from db1.t1) t;
+";
+execute stmt;
+deallocate prepare stmt;
+show create view db1.v1;
+select * from db1.v1;
+drop view db1.v1;
+
+drop table db1.t1;
+drop database db1;
+
+create database test;
+use test;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #