Merge branch '10.7' into 10.8

20 files changed, 1461 insertions, 117 deletions

diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result
index 8320c8d3c4d..de9a979dd94 100644
--- a/mysql-test/main/cte_recursive.result
+++ b/mysql-test/main/cte_recursive.result
@@ -5703,6 +5703,178 @@ r
 3
 drop table t1,t2,t3,x;
 #
+# MDEV-30248: Embedded non-recursive CTE referring to base table 'x'
+#             within a CTE with name 'x' used in a subquery from
+#             select list of another CTE
+#
+CREATE TABLE x (a int) ENGINE=MyISAM;
+INSERT INTO x VALUES (3),(7),(1);
+CREATE TABLE t1 (b int) ENGINE=MYISAM;
+INSERT INTO t1 VALUES (1);
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+c
+1
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+c
+1
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+c
+1
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+c
+3
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+c
+3
+WITH x AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT x.c from x;
+c
+1
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b)
+SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b
+) AS c
+)
+SELECT cte.c from cte;
+c
+2
+DROP TABLE x;
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+ERROR 42S02: Table 'test.x' doesn't exist
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+ERROR 42S02: Table 'test.x' doesn't exist
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+ERROR 42S02: Table 'test.x' doesn't exist
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+ERROR 42S02: Table 'test.x' doesn't exist
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+ERROR 42S02: Table 'test.x' doesn't exist
+WITH x AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT x.c from x;
+ERROR 42S02: Table 'test.x' doesn't exist
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b)
+SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b
+) AS c
+)
+SELECT cte.c from cte;
+ERROR 42S02: Table 'test.x' doesn't exist
+DROP TABLE t1;
+#
 # End of 10.3 tests
 #
 #
diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
index 8efbdbf9677..ebea3b96754 100644
--- a/mysql-test/main/cte_recursive.test
+++ b/mysql-test/main/cte_recursive.test
@@ -3875,6 +3875,129 @@ select * from cte;
 drop table t1,t2,t3,x;
 
 --echo #
+--echo # MDEV-30248: Embedded non-recursive CTE referring to base table 'x'
+--echo #             within a CTE with name 'x' used in a subquery from
+--echo #             select list of another CTE
+--echo #
+
+CREATE TABLE x (a int) ENGINE=MyISAM;
+INSERT INTO x VALUES (3),(7),(1);
+CREATE TABLE t1 (b int) ENGINE=MYISAM;
+INSERT INTO t1 VALUES (1);
+
+let $q1=
+WITH cte AS
+(
+  SELECT
+    (
+      WITH x AS
+      (WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b)
+      SELECT b FROM x AS r
+    ) AS c
+)
+SELECT cte.c FROM cte;
+eval $q1;
+
+let $q2=
+WITH cte AS
+(
+  SELECT
+    (
+      WITH x AS
+      (WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1)
+      SELECT b FROM x AS r
+    ) AS c
+)
+SELECT cte.c FROM cte;
+eval $q2;
+
+let $q3=
+WITH cte AS
+(
+  SELECT
+    (
+      WITH x AS
+      (WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1)
+      SELECT b FROM x AS r
+    ) AS c
+)
+SELECT cte.c FROM cte;
+eval $q3;
+
+
+let $q4=
+WITH cte AS
+(
+  SELECT
+    (
+      WITH x AS
+      (WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y)
+      SELECT b FROM x AS r
+    ) AS c
+)
+SELECT cte.c FROM cte;
+eval $q4;
+
+let $q5=
+WITH cte AS
+(
+  SELECT
+    (
+      WITH x AS
+      (WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x)
+      SELECT b FROM x AS r
+    ) AS c
+)
+SELECT cte.c FROM cte;
+eval $q5;
+
+let $q6=
+WITH x AS
+(
+  SELECT
+    (
+      WITH x AS
+      (WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b)
+      SELECT b FROM x AS r
+    ) AS c
+)
+SELECT x.c from x;
+eval $q6;
+
+let $q7=
+WITH cte AS
+(
+  SELECT
+    (
+      WITH x AS
+      (WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b)
+      SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b
+    ) AS c
+)
+SELECT cte.c from cte;
+eval $q7;
+
+
+DROP TABLE x;
+
+--ERROR ER_NO_SUCH_TABLE
+eval $q1;
+--ERROR ER_NO_SUCH_TABLE
+eval $q2;
+--ERROR ER_NO_SUCH_TABLE
+eval $q3;
+--ERROR ER_NO_SUCH_TABLE
+eval $q4;
+--ERROR ER_NO_SUCH_TABLE
+eval $q5;
+--ERROR ER_NO_SUCH_TABLE
+eval $q6;
+--ERROR ER_NO_SUCH_TABLE
+eval $q7;
+
+DROP TABLE t1;
+
+--echo #
 --echo # End of 10.3 tests
 --echo #
 
diff --git a/mysql-test/main/ctype_utf8.result b/mysql-test/main/ctype_utf8.result
index 53d3be0b125..a10dbfea179 100644
--- a/mysql-test/main/ctype_utf8.result
+++ b/mysql-test/main/ctype_utf8.result
@@ -11383,6 +11383,181 @@ a
 # End of 10.3 tests
 #
 #
+# Start of 10.4 tests
+#
+#
+# MDEV-27653 long uniques don't work with unicode collations
+#
+SET NAMES utf8mb3;
+CREATE TABLE t1 (
+a CHAR(30) COLLATE utf8mb3_general_ci,
+UNIQUE KEY(a) USING HASH
+);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` char(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  UNIQUE KEY `a` (`a`) USING HASH
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+INSERT INTO t1 VALUES ('a');
+INSERT INTO t1 VALUES ('ä');
+ERROR 23000: Duplicate entry 'ä' for key 'a'
+SELECT * FROM t1;
+a
+a
+DROP TABLE t1;
+CREATE TABLE t1 (
+a CHAR(30) COLLATE utf8mb3_general_ci,
+UNIQUE KEY(a(10)) USING HASH
+);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` char(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(10)) USING HASH
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+INSERT INTO t1 VALUES ('a');
+INSERT INTO t1 VALUES ('ä');
+ERROR 23000: Duplicate entry 'ä' for key 'a'
+SELECT * FROM t1;
+a
+a
+DROP TABLE t1;
+CREATE TABLE t1 (
+a VARCHAR(30) COLLATE utf8mb3_general_ci,
+UNIQUE KEY(a) USING HASH
+);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  UNIQUE KEY `a` (`a`) USING HASH
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+INSERT INTO t1 VALUES ('a');
+INSERT INTO t1 VALUES ('ä');
+ERROR 23000: Duplicate entry 'ä' for key 'a'
+SELECT * FROM t1;
+a
+a
+DROP TABLE t1;
+CREATE TABLE t1 (
+a VARCHAR(30) COLLATE utf8mb3_general_ci,
+UNIQUE KEY(a(10)) USING HASH
+);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(10)) USING HASH
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+INSERT INTO t1 VALUES ('a');
+INSERT INTO t1 VALUES ('ä');
+ERROR 23000: Duplicate entry 'ä' for key 'a'
+SELECT * FROM t1;
+a
+a
+DROP TABLE t1;
+CREATE TABLE t1 (a TEXT COLLATE utf8mb3_general_ci UNIQUE);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  UNIQUE KEY `a` (`a`) USING HASH
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+INSERT INTO t1 VALUES ('a');
+INSERT INTO t1 VALUES ('ä');
+ERROR 23000: Duplicate entry 'ä' for key 'a'
+SELECT * FROM t1;
+a
+a
+DROP TABLE t1;
+CREATE TABLE t1 (
+a LONGTEXT COLLATE utf8mb3_general_ci,
+UNIQUE KEY(a(10)) USING HASH
+);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(10)) USING HASH
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+INSERT INTO t1 VALUES ('a');
+INSERT INTO t1 VALUES ('ä');
+ERROR 23000: Duplicate entry 'ä' for key 'a'
+SELECT * FROM t1;
+a
+a
+DROP TABLE t1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  UNIQUE KEY `a` (`a`) USING HASH
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a;
+a	OCTET_LENGTH(a)
+a	1
+ä	2
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	error	Upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it!
+INSERT INTO t1 VALUES ('A');
+ERROR 23000: Duplicate entry 'A' for key 'a'
+INSERT INTO t1 VALUES ('Ä');
+ERROR 23000: Duplicate entry 'Ä' for key 'a'
+INSERT INTO t1 VALUES ('Ấ');
+SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a;
+a	OCTET_LENGTH(a)
+a	1
+ä	2
+Ấ	3
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	error	Upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it!
+ALTER TABLE t1 FORCE;
+ERROR 23000: Duplicate entry 'ä' for key 'a'
+DELETE FROM t1 WHERE OCTET_LENGTH(a)>1;
+ALTER TABLE t1 FORCE;
+INSERT INTO t1 VALUES ('ä');
+ERROR 23000: Duplicate entry 'ä' for key 'a'
+DROP TABLE t1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  UNIQUE KEY `a` (`a`) USING HASH
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a;
+a	OCTET_LENGTH(a)
+a	1
+ä	2
+ALTER IGNORE TABLE t1 FORCE;
+SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a;
+a	OCTET_LENGTH(a)
+a	1
+DROP TABLE t1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  UNIQUE KEY `a` (`a`) USING HASH
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a;
+a	OCTET_LENGTH(a)
+a	1
+ä	2
+REPAIR TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	Warning	Number of rows changed from 2 to 1
+test.t1	repair	status	OK
+SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a;
+a	OCTET_LENGTH(a)
+a	1
+DROP TABLE t1;
+#
+# End of 10.4 tests
+#
+#
 # Start of 10.5 tests
 #
 #
diff --git a/mysql-test/main/ctype_utf8.test b/mysql-test/main/ctype_utf8.test
index 5ea4b17f259..99b952cdfb8 100644
--- a/mysql-test/main/ctype_utf8.test
+++ b/mysql-test/main/ctype_utf8.test
@@ -2314,6 +2314,164 @@ VALUES (_latin1 0xDF) UNION VALUES(_utf8'a' COLLATE utf8_bin);
 
 
 --echo #
+--echo # Start of 10.4 tests
+--echo #
+
+--echo #
+--echo # MDEV-27653 long uniques don't work with unicode collations
+--echo #
+
+SET NAMES utf8mb3;
+
+# CHAR
+
+CREATE TABLE t1 (
+   a CHAR(30) COLLATE utf8mb3_general_ci,
+   UNIQUE KEY(a) USING HASH
+);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES ('a');
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES ('ä');
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+   a CHAR(30) COLLATE utf8mb3_general_ci,
+   UNIQUE KEY(a(10)) USING HASH
+);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES ('a');
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES ('ä');
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+# VARCHAR
+
+CREATE TABLE t1 (
+   a VARCHAR(30) COLLATE utf8mb3_general_ci,
+   UNIQUE KEY(a) USING HASH
+);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES ('a');
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES ('ä');
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+   a VARCHAR(30) COLLATE utf8mb3_general_ci,
+   UNIQUE KEY(a(10)) USING HASH
+);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES ('a');
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES ('ä');
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+# TEXT
+
+CREATE TABLE t1 (a TEXT COLLATE utf8mb3_general_ci UNIQUE);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES ('a');
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES ('ä');
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+  a LONGTEXT COLLATE utf8mb3_general_ci,
+  UNIQUE KEY(a(10)) USING HASH
+);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES ('a');
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES ('ä');
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+# Testing upgrade:
+# Prior to MDEV-27653, the UNIQUE HASH function errorneously
+# took into account string octet length.
+# Old tables should still open and work, but with wrong results.
+
+copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm $MYSQLD_DATADIR/test/t1.frm;
+copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD $MYSQLD_DATADIR/test/t1.MYD;
+copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI $MYSQLD_DATADIR/test/t1.MYI;
+SHOW CREATE TABLE t1;
+SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a;
+CHECK TABLE t1;
+
+# There is already a one byte value 'a' in the table
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES ('A');
+
+# There is already a two-byte value 'ä' in the table
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES ('Ä');
+
+# There were no three-byte values in the table so far.
+# The below value violates UNIQUE, but it gets inserted.
+# This is wrong but expected for a pre-MDEV-27653 table.
+INSERT INTO t1 VALUES ('Ấ');
+SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a;
+CHECK TABLE t1;
+
+# ALTER FORCE fails: it tries to rebuild the table
+# with a correct UNIQUE HASH function, but there are duplicates!
+--error ER_DUP_ENTRY
+ALTER TABLE t1 FORCE;
+
+# Let's remove all duplicate values, so only the one-byte 'a' stays.
+# ALTER..FORCE should work after that.
+DELETE FROM t1 WHERE OCTET_LENGTH(a)>1;
+ALTER TABLE t1 FORCE;
+
+# Make sure that 'a' and 'ä' cannot co-exists any more,
+# because the table was recreated with a correct UNIQUE HASH function.
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES ('ä');
+DROP TABLE t1;
+
+#
+# Testing an old table with ALTER IGNORE.
+# The table is expected to rebuild with a new hash function,
+# duplicates go away.
+#
+copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm $MYSQLD_DATADIR/test/t1.frm;
+copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD $MYSQLD_DATADIR/test/t1.MYD;
+copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI $MYSQLD_DATADIR/test/t1.MYI;
+SHOW CREATE TABLE t1;
+SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a;
+ALTER IGNORE TABLE t1 FORCE;
+SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a;
+DROP TABLE t1;
+
+#
+# Testing an old table with REPAIR.
+# The table is expected to rebuild with a new hash function,
+# duplicates go away.
+#
+copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm $MYSQLD_DATADIR/test/t1.frm;
+copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD $MYSQLD_DATADIR/test/t1.MYD;
+copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI $MYSQLD_DATADIR/test/t1.MYI;
+SHOW CREATE TABLE t1;
+SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a;
+REPAIR TABLE t1;
+SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
+
+
+--echo #
 --echo # Start of 10.5 tests
 --echo #
 
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 9bbd32a9c84..8c936f3374a 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -20611,6 +20611,69 @@ DROP TABLE transaction_items;
 DROP TABLE transactions;
 DROP TABLE charges;
 DROP TABLE ledgers;
+#
+# MDEV-30081: Splitting from a constant mergeable derived table
+#             used in inner part of an outer join.
+#
+CREATE TABLE t1 ( id int PRIMARY KEY ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3),(4),(7);
+CREATE TABLE t2 (
+id int,  id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,4,6),(7,7,7);
+CREATE TABLE t3 (
+wid int, wtid int, otid int, oid int,
+PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid)
+) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (6,30,6,6),(7,17,7,7);
+CREATE TABLE t4 ( id int, a int, PRIMARY KEY (id), KEY (a) )  ENGINE=MyISAM;
+INSERT INTO t4 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+CREATE TABLE t5 (
+id int, id1 int, PRIMARY KEY (id), KEY id1 (id1)
+) ENGINE=MyISAM ;
+INSERT INTO t5 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+ANALYZE TABLE t1,t2,t3,t4,t5;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+test.t3	analyze	status	Engine-independent statistics collected
+test.t3	analyze	status	OK
+test.t4	analyze	status	Engine-independent statistics collected
+test.t4	analyze	status	OK
+test.t5	analyze	status	Engine-independent statistics collected
+test.t5	analyze	status	OK
+CREATE VIEW v1 AS (SELECT id1 FROM t5 GROUP BY id1);
+SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.*
+FROM
+t1, t2, t3
+LEFT JOIN
+(SELECT t4.* FROM t4 WHERE t4.a=3) dt
+ON t3.oid = dt.id AND t3.otid = 14
+LEFT JOIN v1
+ON (v1.id1 = dt.a)
+WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7;
+wid	wtid	otid	oid	t1_id	t2_id	id	a	id1
+7	17	7	7	7	7	NULL	NULL	NULL
+EXPLAIN SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.*
+FROM
+t1, t2, t3
+LEFT JOIN
+(SELECT t4.* FROM t4 WHERE t4.a=3) dt
+ON t3.oid = dt.id AND t3.otid = 14
+LEFT JOIN v1
+ON (v1.id1 = dt.a)
+WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	const	PRIMARY,oid	PRIMARY	4	const	1	
+1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	PRIMARY	t2	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	PRIMARY	t4	const	PRIMARY,a	NULL	NULL	NULL	1	Impossible ON condition
+1	PRIMARY	<derived3>	ref	key0	key0	5	const	0	Using where
+3	LATERAL DERIVED	t5	ref	id1	id1	5	const	0	Using index
+DROP VIEW v1;
+DROP TABLE t1,t2,t3,t4,t5;
 # End of 10.3 tests
 #
 # MDEV-18679: materialized view with SELECT S containing materialized
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index 39e82210e4c..17115c143ac 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -3871,6 +3871,55 @@ DROP TABLE transactions;
 DROP TABLE charges;
 DROP TABLE ledgers;
 
+
+--echo #
+--echo # MDEV-30081: Splitting from a constant mergeable derived table
+--echo #             used in inner part of an outer join.
+--echo #
+
+  CREATE TABLE t1 ( id int PRIMARY KEY ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3),(4),(7);
+
+CREATE TABLE t2 (
+  id int,  id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,4,6),(7,7,7);
+
+CREATE TABLE t3 (
+  wid int, wtid int, otid int, oid int,
+  PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid)
+) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (6,30,6,6),(7,17,7,7);
+
+CREATE TABLE t4 ( id int, a int, PRIMARY KEY (id), KEY (a) )  ENGINE=MyISAM;
+INSERT INTO t4 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+CREATE TABLE t5 (
+  id int, id1 int, PRIMARY KEY (id), KEY id1 (id1)
+) ENGINE=MyISAM ;
+INSERT INTO t5 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+ANALYZE TABLE t1,t2,t3,t4,t5;
+
+CREATE VIEW v1 AS (SELECT id1 FROM t5 GROUP BY id1);
+
+let $q=
+SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.*
+FROM
+  t1, t2, t3
+  LEFT JOIN
+  (SELECT t4.* FROM t4 WHERE t4.a=3) dt
+  ON t3.oid = dt.id AND t3.otid = 14
+  LEFT JOIN v1
+  ON (v1.id1 = dt.a)
+WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7;
+
+eval $q;
+eval EXPLAIN $q;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3,t4,t5;
+
 --echo # End of 10.3 tests
 
 --echo #
diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result
index 4c2fbcf1dd6..283ea870a5f 100644
--- a/mysql-test/main/long_unique_bugs.result
+++ b/mysql-test/main/long_unique_bugs.result
@@ -1,3 +1,6 @@
+#
+# MDEV-18707 Server crash in my_hash_sort_bin, ASAN heap-use-after-free in Field::is_null, server hang, corrupted double-linked list
+#
 create table t1 (a int, b int, c int, d int, e int);
 insert into t1 () values
 (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
@@ -11,6 +14,9 @@ load data infile 'load.data' into table tmp;
 delete from tmp;
 drop table t1;
 drop table tmp;
+#
+# MDEV-18712 InnoDB indexes are inconsistent with what defined in .frm for table after rebuilding table with index on blob
+#
 create table t1 (b blob) engine=innodb;
 alter table t1 add unique (b);
 alter table t1 force;
@@ -21,12 +27,18 @@ t1	CREATE TABLE `t1` (
   UNIQUE KEY `b` (`b`) USING HASH
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 drop table t1;
+#
+# MDEV-18713 Assertion `strcmp(share->unique_file_name,filename) || share->last_version' failed in test_if_reopen upon REPLACE into table with key on blob
+#
 create table t1 (pk int, b blob, primary key(pk), unique(b)) engine=myisam;
 insert into t1 values (1,'foo');
 replace into t1 (pk) values (1);
 alter table t1 force;
 replace into t1 (pk) values (1);
 drop table t1;
+#
+# MDEV-18722 Assertion `templ->mysql_null_bit_mask' failed in row_sel_store_mysql_rec upon modifying indexed column into blob
+#
 create table t1 (t time, unique(t)) engine=innodb;
 insert into t1 values (null),(null);
 alter ignore table t1 modify t text not null default '';
@@ -34,6 +46,9 @@ Warnings:
 Warning	1265	Data truncated for column 't' at row 1
 Warning	1265	Data truncated for column 't' at row 2
 drop table t1;
+#
+# MDEV-18720 Assertion `inited==NONE' failed in ha_index_init upon update on versioned table with key on blob
+#
 create table t1 ( pk int, f text, primary key (pk), unique(f)) with system versioning;
 insert into t1 values (1,'foo');
 update t1 set f = 'bar';
@@ -50,20 +65,32 @@ pk	f	row_end > DATE'2030-01-01'
 1	foo	0
 1	bar	0
 drop table t1;
+#
+# MDEV-18747 InnoDB: Failing assertion: table->get_ref_count() == 0 upon dropping temporary table with unique blob
+#
 create temporary table t1 (f blob, unique(f)) engine=innodb;
 insert into t1 values (1);
 replace into t1 values (1);
 drop table t1;
+#
+# MDEV-18748 REPLACE doesn't work with unique blobs on MyISAM table
+#
 create table t (b blob, unique(b)) engine=myisam;
 insert into t values ('foo');
 replace into t values ('foo');
 drop table t;
+#
+# MDEV-18790 Server crash in fields_in_hash_keyinfo after unsuccessful attempt to drop BLOB with long index
+#
 CREATE TABLE t1 (f INT, x BLOB, UNIQUE (x));
 INSERT INTO t1 VALUES (1,'foo');
 ALTER TABLE t1 DROP x, ALGORITHM=INPLACE;
 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 UPDATE t1 SET x = 'bar';
 DROP TABLE t1;
+#
+# MDEV-18799 Long unique does not work after failed alter table
+#
 create table t1(a blob unique , b blob);
 insert into t1 values(1,1),(2,1);
 alter table t1 add unique(b);
@@ -86,16 +113,26 @@ Ignored	NO
 insert into t1 values(1,1);
 ERROR 23000: Duplicate entry '1' for key 'a'
 DROP TABLE t1;
+#
+# MDEV-18792 ASAN unknown-crash in _mi_pack_key upon UPDATE after failed ALTER on a table with long BLOB key
+#
 CREATE TABLE t1 (a TEXT, b INT, UNIQUE(a)) ENGINE=MyISAM;
 ALTER TABLE t1 DROP x;
 ERROR 42000: Can't DROP COLUMN `x`; check that it exists
 UPDATE t1 SET b = 0 WHERE a = 'foo';
 DROP TABLE t1;
+#
+# MDEV-18793 Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, ASAN unknown-crash in
+#    row_mysql_store_col_in_innobase_format, warning " InnoDB: Using a partial-field key prefix in search"
+#
 CREATE TABLE t1 (a TEXT, b INT, UNIQUE(a)) ENGINE=InnoDB;
 ALTER TABLE t1 DROP x;
 ERROR 42000: Can't DROP COLUMN `x`; check that it exists
 UPDATE t1 SET b = 0 WHERE a = 'foo';
 DROP TABLE t1;
+#
+# MDEV-18795 InnoDB: Failing assertion: field->prefix_len > 0 upon DML on table with BLOB index
+#
 CREATE TEMPORARY TABLE t1 (f BLOB, UNIQUE(f)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
 ALTER TABLE t1 ADD KEY (f);
 ERROR HY000: Index column size too large. The maximum column size is 767 bytes
@@ -103,17 +140,29 @@ TRUNCATE TABLE t1;
 SELECT * FROM t1 WHERE f LIKE 'foo';
 f
 DROP TABLE t1;
+#
+# MDEV-18798 InnoDB: No matching column for `DB_ROW_HASH_1`and server crash in
+#   ha_innobase::commit_inplace_alter_table upon ALTER on table with UNIQUE key
+#
 CREATE TABLE t1 (a INT, UNIQUE ind USING HASH (a)) ENGINE=InnoDB;
 ALTER TABLE t1 CHANGE COLUMN IF EXISTS b a INT;
 Warnings:
 Note	1054	Unknown column 'b' in 't1'
 DROP TABLE t1;
+#
+# MDEV-18801 InnoDB: Failing assertion: field->col->mtype == type or ASAN heap-buffer-overflow
+#   in row_sel_convert_mysql_key_to_innobase upon SELECT on table with long index
+#
 CREATE TABLE t1 (f VARCHAR(4096), UNIQUE(f)) ENGINE=InnoDB;
 ALTER TABLE t1 DROP x;
 ERROR 42000: Can't DROP COLUMN `x`; check that it exists
 SELECT * FROM t1 WHERE f LIKE 'foo';
 f
 DROP TABLE t1;
+#
+# MDEV-18800 Server crash in instant_alter_column_possible or
+#   Assertion `!pk->has_virtual()' failed in instant_alter_column_possible upon adding key
+#
 CREATE TABLE t1 (pk INT, PRIMARY KEY USING HASH (pk)) ENGINE=InnoDB;
 show keys from t1;;
 Table	t1
@@ -132,6 +181,9 @@ Index_comment
 Ignored	NO
 ALTER TABLE t1 ADD INDEX (pk);
 DROP TABLE t1;
+#
+# MDEV-18922 Alter on long unique varchar column makes result null
+#
 CREATE TABLE t1 (b int, a  varchar(4000));
 INSERT INTO t1 VALUES (1, 2),(2,3),(3,4);
 ALTER TABLE t1 ADD UNIQUE INDEX (a);
@@ -146,6 +198,10 @@ a
 3
 4
 drop table t1;
+#
+# MDEV-18809 Server crash in fields_in_hash_keyinfo or Assertion `key_info->key_part->field->flags
+# & (1<< 30)' failed in setup_keyinfo_hash
+#
 CREATE TABLE t1 (f VARCHAR(4096), UNIQUE(f)) ENGINE=InnoDB;
 ALTER TABLE t1 DROP KEY f, ADD INDEX idx1(f), ALGORITHM=INSTANT;
 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY
@@ -161,6 +217,9 @@ insert into t1 values(1,1);
 ERROR 23000: Duplicate entry '1-1' for key 'a'
 alter table t1 add column c int;
 drop table t1;
+#
+# MDEV-18889 Long unique on virtual fields crashes server
+#
 create table t1(a blob , b blob as (a) unique);
 insert into t1 values(1, default);
 insert into t1 values(1, default);
@@ -174,6 +233,9 @@ insert into t1(a,b) values(2,2);
 insert into t1(a,b) values(2,3);
 insert into t1(a,b) values(3,2);
 drop table t1;
+#
+# MDEV-18888 Server crashes in Item_field::register_field_in_read_map upon MODIFY COLUMN
+#
 CREATE TABLE t1 ( 
 a CHAR(128),
 b CHAR(128) AS (a),
@@ -189,6 +251,9 @@ c varchar(5000),
 UNIQUE(c,b(64))
 ) ENGINE=InnoDB;
 drop table t1;
+#
+# MDEV-18967 Load data in system version with long unique does not work
+#
 CREATE TABLE t1 (data VARCHAR(4), unique(data) using hash) with system versioning;
 INSERT INTO t1 VALUES ('A');
 SELECT * INTO OUTFILE 'load.data' from t1;
@@ -198,6 +263,9 @@ select * from t1;
 data
 A
 DROP TABLE t1;
+#
+# MDEV-18901 Wrong results after ADD UNIQUE INDEX(blob_column)
+#
 CREATE TABLE t1 (data VARCHAR(7961)) ENGINE=InnoDB;
 INSERT INTO t1 VALUES ('f'), ('o'), ('o');
 SELECT * INTO OUTFILE 'load.data' from t1;
@@ -216,12 +284,16 @@ SELECT * FROM t1;
 data
 f
 o
+# This should be equivalent to the REPLACE above
 LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1;
 SELECT * FROM t1;
 data
 f
 o
 DROP TABLE t1;
+#
+# MDEV-18953 Hash index on partial char field not working
+#
 create table t1 (
 c char(10) character set utf8mb4,
 unique key a using hash (c(1))
@@ -238,10 +310,16 @@ ERROR 23000: Duplicate entry 'Ð' for key 'a'
 insert into t1 values ('ббб');
 ERROR 23000: Duplicate entry 'Ð' for key 'a'
 drop table t1;
+#
+# MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map
+#
 CREATE TABLE t1 (a INT, UNIQUE USING HASH (a)) PARTITION BY HASH (a) PARTITIONS 2;
 INSERT INTO t1 VALUES (2);
 REPLACE INTO t1 VALUES (2);
 DROP TABLE t1;
+#
+# MDEV-18820 Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock upon INSERT into table with blob key'
+#
 set innodb_lock_wait_timeout= 10;
 CREATE TABLE t1 (
 id int primary key,
@@ -268,11 +346,20 @@ ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
 disconnect con1;
 connection default;
 DROP TABLE t1, t2;
+#
+# MDEV-18791 Wrong error upon creating Aria table with long index on BLOB
+#
 CREATE TABLE t1 (a TEXT, UNIQUE(a)) ENGINE=Aria;
 ERROR 42000: Specified key was too long; max key length is 2300 bytes
+#
+# MDEV-20001 Potential dangerous regression: INSERT INTO >=100 rows fail for myisam table with HASH indexes
+#
 create table t1(a int, unique(a) using hash);
 #BULK insert > 100 rows (MI_MIN_ROWS_TO_DISABLE_INDEXES)
 drop table t1;
+#
+# MDEV-21804 Assertion `marked_for_read()' failed upon INSERT into table with long unique blob under binlog_row_image=NOBLOB
+#
 SET binlog_row_image= NOBLOB;
 CREATE TABLE t1 (pk INT PRIMARY KEY, a text ,UNIQUE(a) using hash);
 INSERT INTO t1 VALUES (1,'foo');
@@ -280,6 +367,9 @@ create table t2(id int primary key, a blob, b varchar(20) as (LEFT(a,2)));
 INSERT INTO t2 VALUES (1, 'foo', default);
 DROP TABLE t1, t2;
 SET binlog_row_image= FULL;
+#
+# MDEV-22719 Long unique keys are not created when individual key_part->length < max_key_length but SUM(key_parts->length) > max_key_length
+#
 CREATE TABLE t1 (a int, b VARCHAR(1000), UNIQUE (a,b)) ENGINE=MyISAM;
 show index from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
@@ -317,6 +407,9 @@ ERROR 23000: Duplicate entry '1' for key 'v2'
 update t1,t2 set v1 = v2 , v5 = 0;
 ERROR 23000: Duplicate entry '-128' for key 'v1'
 drop table t1, t2;
+#
+# MDEV-23264 Unique blobs allow duplicate values upon UPDATE
+#
 CREATE TABLE t1 (f TEXT UNIQUE);
 INSERT INTO t1 VALUES (NULL),(NULL);
 UPDATE t1 SET f = '';
@@ -346,6 +439,18 @@ partition n0 values less than (10),
 partition n1 values less than (50));
 drop table t1;
 #
+# MDEV-29199 Unique hash key is ignored upon INSERT ... SELECT into non-empty MyISAM table
+#
+create table t1 (a int, b text, unique(b)) engine=MyISAM;
+insert into t1 values (0,'aa');
+insert into t1 (a,b) select 1,'xxx' from seq_1_to_5;
+ERROR 23000: Duplicate entry 'xxx' for key 'b'
+select * from t1;
+a	b
+0	aa
+1	xxx
+drop table t1;
+#
 # End of 10.4 tests
 #
 #
diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test
index 8d1accd6cd3..27bb402bd8b 100644
--- a/mysql-test/main/long_unique_bugs.test
+++ b/mysql-test/main/long_unique_bugs.test
@@ -1,9 +1,10 @@
 --source include/have_innodb.inc
 --source include/have_partition.inc
+--source include/have_sequence.inc
 
-#
-# MDEV-18707 Server crash in my_hash_sort_bin, ASAN heap-use-after-free in Field::is_null, server hang, corrupted double-linked list
-#
+--echo #
+--echo # MDEV-18707 Server crash in my_hash_sort_bin, ASAN heap-use-after-free in Field::is_null, server hang, corrupted double-linked list
+--echo #
 create table t1 (a int, b int, c int, d int, e int);
 insert into t1 () values
 (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
@@ -20,18 +21,18 @@ drop table t1;
 --remove_file $datadir/test/load.data
 drop table tmp;
 
-#
-# MDEV-18712 InnoDB indexes are inconsistent with what defined in .frm for table after rebuilding table with index on blob
-#
+--echo #
+--echo # MDEV-18712 InnoDB indexes are inconsistent with what defined in .frm for table after rebuilding table with index on blob
+--echo #
 create table t1 (b blob) engine=innodb;
 alter table t1 add unique (b);
 alter table t1 force;
 show create table t1;
 drop table t1;
 
-#
-# MDEV-18713 Assertion `strcmp(share->unique_file_name,filename) || share->last_version' failed in test_if_reopen upon REPLACE into table with key on blob
-#
+--echo #
+--echo # MDEV-18713 Assertion `strcmp(share->unique_file_name,filename) || share->last_version' failed in test_if_reopen upon REPLACE into table with key on blob
+--echo #
 create table t1 (pk int, b blob, primary key(pk), unique(b)) engine=myisam;
 insert into t1 values (1,'foo');
 replace into t1 (pk) values (1);
@@ -39,17 +40,17 @@ alter table t1 force;
 replace into t1 (pk) values (1);
 drop table t1;
 
-#
-# MDEV-18722 Assertion `templ->mysql_null_bit_mask' failed in row_sel_store_mysql_rec upon modifying indexed column into blob
-#
+--echo #
+--echo # MDEV-18722 Assertion `templ->mysql_null_bit_mask' failed in row_sel_store_mysql_rec upon modifying indexed column into blob
+--echo #
 create table t1 (t time, unique(t)) engine=innodb;
 insert into t1 values (null),(null);
 alter ignore table t1 modify t text not null default '';
 drop table t1;
 
-#
-# MDEV-18720 Assertion `inited==NONE' failed in ha_index_init upon update on versioned table with key on blob
-#
+--echo #
+--echo # MDEV-18720 Assertion `inited==NONE' failed in ha_index_init upon update on versioned table with key on blob
+--echo #
 create table t1 ( pk int, f text, primary key (pk), unique(f)) with system versioning;
 insert into t1 values (1,'foo');
 update t1 set f = 'bar';
@@ -59,25 +60,25 @@ select * from t1;
 select pk, f, row_end > DATE'2030-01-01' from t1 for system_time all;
 drop table t1;
 
-#
-# MDEV-18747 InnoDB: Failing assertion: table->get_ref_count() == 0 upon dropping temporary table with unique blob
-#
+--echo #
+--echo # MDEV-18747 InnoDB: Failing assertion: table->get_ref_count() == 0 upon dropping temporary table with unique blob
+--echo #
 create temporary table t1 (f blob, unique(f)) engine=innodb;
 insert into t1 values (1);
 replace into t1 values (1);
 drop table t1;
 
-#
-# MDEV-18748 REPLACE doesn't work with unique blobs on MyISAM table
-#
+--echo #
+--echo # MDEV-18748 REPLACE doesn't work with unique blobs on MyISAM table
+--echo #
 create table t (b blob, unique(b)) engine=myisam;
 insert into t values ('foo');
 replace into t values ('foo');
 drop table t;
 
-#
-# MDEV-18790 Server crash in fields_in_hash_keyinfo after unsuccessful attempt to drop BLOB with long index
-#
+--echo #
+--echo # MDEV-18790 Server crash in fields_in_hash_keyinfo after unsuccessful attempt to drop BLOB with long index
+--echo #
 CREATE TABLE t1 (f INT, x BLOB, UNIQUE (x));
 INSERT INTO t1 VALUES (1,'foo');
 --error ER_ALTER_OPERATION_NOT_SUPPORTED
@@ -85,9 +86,9 @@ ALTER TABLE t1 DROP x, ALGORITHM=INPLACE;
 UPDATE t1 SET x = 'bar';
 DROP TABLE t1;
 
-#
-# MDEV-18799 Long unique does not work after failed alter table
-#
+--echo #
+--echo # MDEV-18799 Long unique does not work after failed alter table
+--echo #
 create table t1(a blob unique , b blob);
 insert into t1 values(1,1),(2,1);
 --error ER_DUP_ENTRY
@@ -97,28 +98,28 @@ alter table t1 add unique(b);
 insert into t1 values(1,1);
 DROP TABLE t1;
 
-#
-# MDEV-18792 ASAN unknown-crash in _mi_pack_key upon UPDATE after failed ALTER on a table with long BLOB key
-#
+--echo #
+--echo # MDEV-18792 ASAN unknown-crash in _mi_pack_key upon UPDATE after failed ALTER on a table with long BLOB key
+--echo #
 CREATE TABLE t1 (a TEXT, b INT, UNIQUE(a)) ENGINE=MyISAM;
 --error ER_CANT_DROP_FIELD_OR_KEY
 ALTER TABLE t1 DROP x;
 UPDATE t1 SET b = 0 WHERE a = 'foo';
 DROP TABLE t1;
 
-#
-# MDEV-18793 Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, ASAN unknown-crash in
-#    row_mysql_store_col_in_innobase_format, warning " InnoDB: Using a partial-field key prefix in search"
-#
+--echo #
+--echo # MDEV-18793 Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, ASAN unknown-crash in
+--echo #    row_mysql_store_col_in_innobase_format, warning " InnoDB: Using a partial-field key prefix in search"
+--echo #
 CREATE TABLE t1 (a TEXT, b INT, UNIQUE(a)) ENGINE=InnoDB;
 --error ER_CANT_DROP_FIELD_OR_KEY
 ALTER TABLE t1 DROP x;
 UPDATE t1 SET b = 0 WHERE a = 'foo';
 DROP TABLE t1;
 
-#
-# MDEV-18795 InnoDB: Failing assertion: field->prefix_len > 0 upon DML on table with BLOB index
-#
+--echo #
+--echo # MDEV-18795 InnoDB: Failing assertion: field->prefix_len > 0 upon DML on table with BLOB index
+--echo #
 CREATE TEMPORARY TABLE t1 (f BLOB, UNIQUE(f)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
 --error ER_INDEX_COLUMN_TOO_LONG
 ALTER TABLE t1 ADD KEY (f);
@@ -126,36 +127,36 @@ TRUNCATE TABLE t1;
 SELECT * FROM t1 WHERE f LIKE 'foo';
 DROP TABLE t1;
 
-#
-# MDEV-18798 InnoDB: No matching column for `DB_ROW_HASH_1`and server crash in
-#   ha_innobase::commit_inplace_alter_table upon ALTER on table with UNIQUE key
-#
+--echo #
+--echo # MDEV-18798 InnoDB: No matching column for `DB_ROW_HASH_1`and server crash in
+--echo #   ha_innobase::commit_inplace_alter_table upon ALTER on table with UNIQUE key
+--echo #
 CREATE TABLE t1 (a INT, UNIQUE ind USING HASH (a)) ENGINE=InnoDB;
 ALTER TABLE t1 CHANGE COLUMN IF EXISTS b a INT;
 DROP TABLE t1;
 
-#
-# MDEV-18801 InnoDB: Failing assertion: field->col->mtype == type or ASAN heap-buffer-overflow
-#   in row_sel_convert_mysql_key_to_innobase upon SELECT on table with long index
-#
+--echo #
+--echo # MDEV-18801 InnoDB: Failing assertion: field->col->mtype == type or ASAN heap-buffer-overflow
+--echo #   in row_sel_convert_mysql_key_to_innobase upon SELECT on table with long index
+--echo #
 CREATE TABLE t1 (f VARCHAR(4096), UNIQUE(f)) ENGINE=InnoDB;
 --error ER_CANT_DROP_FIELD_OR_KEY
 ALTER TABLE t1 DROP x;
 SELECT * FROM t1 WHERE f LIKE 'foo';
 DROP TABLE t1;
 
-#
-# MDEV-18800 Server crash in instant_alter_column_possible or
-#   Assertion `!pk->has_virtual()' failed in instant_alter_column_possible upon adding key
-#
+--echo #
+--echo # MDEV-18800 Server crash in instant_alter_column_possible or
+--echo #   Assertion `!pk->has_virtual()' failed in instant_alter_column_possible upon adding key
+--echo #
 CREATE TABLE t1 (pk INT, PRIMARY KEY USING HASH (pk)) ENGINE=InnoDB;
 --query_vertical show keys from t1;
 ALTER TABLE t1 ADD INDEX (pk);
 DROP TABLE t1;
 
-#
-# MDEV-18922 Alter on long unique varchar column makes result null
-#
+--echo #
+--echo # MDEV-18922 Alter on long unique varchar column makes result null
+--echo #
 CREATE TABLE t1 (b int, a  varchar(4000));
 INSERT INTO t1 VALUES (1, 2),(2,3),(3,4);
 ALTER TABLE t1 ADD UNIQUE INDEX (a);
@@ -163,10 +164,10 @@ SELECT * FROM t1;
 SELECT a FROM t1;
 drop table t1;
 
-#
-# MDEV-18809 Server crash in fields_in_hash_keyinfo or Assertion `key_info->key_part->field->flags
-# & (1<< 30)' failed in setup_keyinfo_hash
-#
+--echo #
+--echo # MDEV-18809 Server crash in fields_in_hash_keyinfo or Assertion `key_info->key_part->field->flags
+--echo # & (1<< 30)' failed in setup_keyinfo_hash
+--echo #
 CREATE TABLE t1 (f VARCHAR(4096), UNIQUE(f)) ENGINE=InnoDB;
 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 ALTER TABLE t1 DROP KEY f, ADD INDEX idx1(f), ALGORITHM=INSTANT;
@@ -181,9 +182,9 @@ insert into t1 values(1,1);
 alter table t1 add column c int;
 drop table t1;
 
-#
-# MDEV-18889 Long unique on virtual fields crashes server
-#
+--echo #
+--echo # MDEV-18889 Long unique on virtual fields crashes server
+--echo #
 create table t1(a blob , b blob as (a) unique);
 insert into t1 values(1, default);
 --error ER_DUP_ENTRY
@@ -199,9 +200,9 @@ insert into t1(a,b) values(2,3);
 insert into t1(a,b) values(3,2);
 drop table t1;
 
-#
-# MDEV-18888 Server crashes in Item_field::register_field_in_read_map upon MODIFY COLUMN
-#
+--echo #
+--echo # MDEV-18888 Server crashes in Item_field::register_field_in_read_map upon MODIFY COLUMN
+--echo #
 CREATE TABLE t1 ( 
   a CHAR(128),
   b CHAR(128) AS (a),
@@ -218,9 +219,9 @@ CREATE TABLE t1 (
 ) ENGINE=InnoDB;
 drop table t1;
 
-#
-# MDEV-18967 Load data in system version with long unique does not work
-#
+--echo #
+--echo # MDEV-18967 Load data in system version with long unique does not work
+--echo #
 CREATE TABLE t1 (data VARCHAR(4), unique(data) using hash) with system versioning;
 INSERT INTO t1 VALUES ('A');
 SELECT * INTO OUTFILE 'load.data' from t1;
@@ -231,9 +232,9 @@ DROP TABLE t1;
 --let $datadir= `select @@datadir`
 --remove_file $datadir/test/load.data
 
-#
-# MDEV-18901 Wrong results after ADD UNIQUE INDEX(blob_column)
-#
+--echo #
+--echo # MDEV-18901 Wrong results after ADD UNIQUE INDEX(blob_column)
+--echo #
 CREATE TABLE t1 (data VARCHAR(7961)) ENGINE=InnoDB;
 
 INSERT INTO t1 VALUES ('f'), ('o'), ('o');
@@ -245,16 +246,16 @@ ALTER TABLE t1 ADD SYSTEM VERSIONING ;
 SELECT * FROM t1;
 REPLACE INTO t1 VALUES ('f'), ('o'), ('o');
 SELECT * FROM t1;
-# This should be equivalent to the REPLACE above
+--echo # This should be equivalent to the REPLACE above
 LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1;
 SELECT * FROM t1;
 DROP TABLE t1;
 --let $datadir= `select @@datadir`
 --remove_file $datadir/test/load.data
 
-#
-# MDEV-18953 Hash index on partial char field not working
-#
+--echo #
+--echo # MDEV-18953 Hash index on partial char field not working
+--echo #
 create table t1 (
  c char(10) character set utf8mb4,
  unique key a using hash (c(1))
@@ -267,17 +268,17 @@ insert into t1 values ('бб');
 insert into t1 values ('ббб');
 drop table t1;
 
-#
-# MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map
-#
+--echo #
+--echo # MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map
+--echo #
 CREATE TABLE t1 (a INT, UNIQUE USING HASH (a)) PARTITION BY HASH (a) PARTITIONS 2;
 INSERT INTO t1 VALUES (2);
 REPLACE INTO t1 VALUES (2);
 DROP TABLE t1;
 
-#
-# MDEV-18820 Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock upon INSERT into table with blob key'
-#
+--echo #
+--echo # MDEV-18820 Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock upon INSERT into table with blob key'
+--echo #
 
 set innodb_lock_wait_timeout= 10;
 
@@ -317,15 +318,15 @@ INSERT IGNORE INTO t1 VALUES (4, 1)/*4*/;
 --connection default
 DROP TABLE t1, t2;
 
-#
-# MDEV-18791 Wrong error upon creating Aria table with long index on BLOB
-#
+--echo #
+--echo # MDEV-18791 Wrong error upon creating Aria table with long index on BLOB
+--echo #
 --error ER_TOO_LONG_KEY
 CREATE TABLE t1 (a TEXT, UNIQUE(a)) ENGINE=Aria;
 
-#
-# MDEV-20001 Potential dangerous regression: INSERT INTO >=100 rows fail for myisam table with HASH indexes
-#
+--echo #
+--echo # MDEV-20001 Potential dangerous regression: INSERT INTO >=100 rows fail for myisam table with HASH indexes
+--echo #
 create table t1(a int, unique(a) using hash);
 --let $count=150
 --let insert_stmt= insert into t1 values(200)
@@ -340,9 +341,9 @@ while ($count)
 --enable_query_log
 drop table t1;
 
-#
-# MDEV-21804 Assertion `marked_for_read()' failed upon INSERT into table with long unique blob under binlog_row_image=NOBLOB
-#
+--echo #
+--echo # MDEV-21804 Assertion `marked_for_read()' failed upon INSERT into table with long unique blob under binlog_row_image=NOBLOB
+--echo #
 
 --source include/have_binlog_format_row.inc
 SET binlog_row_image= NOBLOB;
@@ -352,20 +353,17 @@ INSERT INTO t1 VALUES (1,'foo');
 create table t2(id int primary key, a blob, b varchar(20) as (LEFT(a,2)));
 INSERT INTO t2 VALUES (1, 'foo', default);
 
-# Cleanup
 DROP TABLE t1, t2;
 SET binlog_row_image= FULL;
 
-#
-# MDEV-22719 Long unique keys are not created when individual key_part->length < max_key_length but SUM(key_parts->length) > max_key_length
-#
+--echo #
+--echo # MDEV-22719 Long unique keys are not created when individual key_part->length < max_key_length but SUM(key_parts->length) > max_key_length
+--echo #
 
 CREATE TABLE t1 (a int, b VARCHAR(1000), UNIQUE (a,b)) ENGINE=MyISAM;
 show index from t1;
 CREATE TABLE t2 (a varchar(900), b VARCHAR(900), UNIQUE (a,b)) ENGINE=MyISAM;
 show index from t2;
-
-# Cleanup
 DROP TABLE t1,t2;
 
 --echo #
@@ -397,9 +395,9 @@ update t1 set v2 = 1, v3 = -128;
 update t1,t2 set v1 = v2 , v5 = 0;
 drop table t1, t2;
 
-#
-# MDEV-23264 Unique blobs allow duplicate values upon UPDATE
-#
+--echo #
+--echo # MDEV-23264 Unique blobs allow duplicate values upon UPDATE
+--echo #
 
 CREATE TABLE t1 (f TEXT UNIQUE);
 INSERT INTO t1 VALUES (NULL),(NULL);
@@ -436,6 +434,16 @@ alter table t1 reorganize partition p1 into (
 drop table t1;
 
 --echo #
+--echo # MDEV-29199 Unique hash key is ignored upon INSERT ... SELECT into non-empty MyISAM table
+--echo #
+create table t1 (a int, b text, unique(b)) engine=MyISAM;
+insert into t1 values (0,'aa');
+--error ER_DUP_ENTRY
+insert into t1 (a,b) select 1,'xxx' from seq_1_to_5;
+select * from t1;
+drop table t1;
+
+--echo #
 --echo # End of 10.4 tests
 --echo #
 
diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result
index 2a546f126d1..5f1a4a1db76 100644
--- a/mysql-test/main/rowid_filter.result
+++ b/mysql-test/main/rowid_filter.result
@@ -354,7 +354,7 @@ WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
 o_totalprice between 200000 and 230000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	lineitem	range	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	NULL	98	Using index condition
-1	SIMPLE	orders	eq_ref	PRIMARY,i_o_totalprice	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	Using where
+1	SIMPLE	orders	eq_ref|filter	PRIMARY,i_o_totalprice	PRIMARY|i_o_totalprice	4|9	dbt3_s001.lineitem.l_orderkey	1 (5%)	Using where; Using rowid filter
 set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
 FROM orders JOIN lineitem ON o_orderkey=l_orderkey
 WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
@@ -391,6 +391,14 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_o_totalprice",
+              "used_key_parts": ["o_totalprice"]
+            },
+            "rows": 69,
+            "selectivity_pct": 4.6
+          },
           "rows": 1,
           "filtered": 4.599999905,
           "attached_condition": "orders.o_totalprice between 200000 and 230000"
@@ -405,7 +413,7 @@ WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
 o_totalprice between 200000 and 230000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	SIMPLE	lineitem	range	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	NULL	98	98.00	100.00	100.00	Using index condition
-1	SIMPLE	orders	eq_ref	PRIMARY,i_o_totalprice	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	1.00	4.60	11.22	Using where
+1	SIMPLE	orders	eq_ref|filter	PRIMARY,i_o_totalprice	PRIMARY|i_o_totalprice	4|9	dbt3_s001.lineitem.l_orderkey	1 (5%)	0.11 (10%)	4.60	100.00	Using where; Using rowid filter
 set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
 FROM orders JOIN lineitem ON o_orderkey=l_orderkey
 WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
@@ -449,13 +457,26 @@ ANALYZE
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_o_totalprice",
+              "used_key_parts": ["o_totalprice"]
+            },
+            "rows": 69,
+            "selectivity_pct": 4.6,
+            "r_rows": 71,
+            "r_lookups": 96,
+            "r_selectivity_pct": 10.41666667,
+            "r_buffer_size": "REPLACED",
+            "r_filling_time_ms": "REPLACED"
+          },
           "r_loops": 98,
           "rows": 1,
-          "r_rows": 1,
+          "r_rows": 0.112244898,
           "r_table_time_ms": "REPLACED",
           "r_other_time_ms": "REPLACED",
           "filtered": 4.599999905,
-          "r_filtered": 11.2244898,
+          "r_filtered": 100,
           "attached_condition": "orders.o_totalprice between 200000 and 230000"
         }
       }
@@ -615,7 +636,7 @@ l_quantity > 45 AND
 o_totalprice between 180000 and 230000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	lineitem	range|filter	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity	i_l_shipdate|i_l_quantity	4|9	NULL	509 (12%)	Using index condition; Using where; Using rowid filter
-1	SIMPLE	orders	eq_ref	PRIMARY,i_o_totalprice	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	Using where
+1	SIMPLE	orders	eq_ref|filter	PRIMARY,i_o_totalprice	PRIMARY|i_o_totalprice	4|9	dbt3_s001.lineitem.l_orderkey	1 (9%)	Using where; Using rowid filter
 set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
 FROM orders JOIN lineitem ON o_orderkey=l_orderkey
 WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
@@ -663,6 +684,14 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_o_totalprice",
+              "used_key_parts": ["o_totalprice"]
+            },
+            "rows": 139,
+            "selectivity_pct": 9.266666667
+          },
           "rows": 1,
           "filtered": 9.266666412,
           "attached_condition": "orders.o_totalprice between 180000 and 230000"
@@ -678,7 +707,7 @@ l_quantity > 45 AND
 o_totalprice between 180000 and 230000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	SIMPLE	lineitem	range|filter	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity	i_l_shipdate|i_l_quantity	4|9	NULL	509 (12%)	60.00 (11%)	11.69	100.00	Using index condition; Using where; Using rowid filter
-1	SIMPLE	orders	eq_ref	PRIMARY,i_o_totalprice	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	1.00	9.27	26.67	Using where
+1	SIMPLE	orders	eq_ref|filter	PRIMARY,i_o_totalprice	PRIMARY|i_o_totalprice	4|9	dbt3_s001.lineitem.l_orderkey	1 (9%)	0.27 (25%)	9.27	100.00	Using where; Using rowid filter
 set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
 FROM orders JOIN lineitem ON o_orderkey=l_orderkey
 WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
@@ -738,13 +767,26 @@ ANALYZE
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
+          "rowid_filter": {
+            "range": {
+              "key": "i_o_totalprice",
+              "used_key_parts": ["o_totalprice"]
+            },
+            "rows": 139,
+            "selectivity_pct": 9.266666667,
+            "r_rows": 144,
+            "r_lookups": 59,
+            "r_selectivity_pct": 25.42372881,
+            "r_buffer_size": "REPLACED",
+            "r_filling_time_ms": "REPLACED"
+          },
           "r_loops": 60,
           "rows": 1,
-          "r_rows": 1,
+          "r_rows": 0.266666667,
           "r_table_time_ms": "REPLACED",
           "r_other_time_ms": "REPLACED",
           "filtered": 9.266666412,
-          "r_filtered": 26.66666667,
+          "r_filtered": 100,
           "attached_condition": "orders.o_totalprice between 180000 and 230000"
         }
       }
diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result
index 93687056c91..99844d05f2a 100644
--- a/mysql-test/main/select.result
+++ b/mysql-test/main/select.result
@@ -3616,7 +3616,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
 1	SIMPLE	t2	range	si	si	5	NULL	4	Using index condition; Using where
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+1	SIMPLE	t3	eq_ref|filter	PRIMARY,ci	PRIMARY|ci	4|5	test.t2.a	1 (30%)	Using where; Using rowid filter
 EXPLAIN
 SELECT t3.a FROM t1,t2,t3
 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
@@ -3624,7 +3624,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
 1	SIMPLE	t2	range	si,ai	si	5	NULL	4	Using index condition; Using where
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+1	SIMPLE	t3	eq_ref|filter	PRIMARY,ci	PRIMARY|ci	4|5	test.t2.a	1 (30%)	Using where; Using rowid filter
 EXPLAIN 
 SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
@@ -3632,7 +3632,7 @@ t3.c IN ('bb','ee');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
 1	SIMPLE	t2	range	si	si	5	NULL	2	Using index condition; Using where
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+1	SIMPLE	t3	eq_ref|filter	PRIMARY,ci	PRIMARY|ci	4|5	test.t2.a	1 (30%)	Using where; Using rowid filter
 EXPLAIN 
 SELECT t3.a FROM t1,t2,t3
 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
@@ -3640,7 +3640,7 @@ t3.c IN ('bb','ee');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
 1	SIMPLE	t2	range	si,ai	si	5	NULL	2	Using index condition; Using where
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+1	SIMPLE	t3	eq_ref|filter	PRIMARY,ci	PRIMARY|ci	4|5	test.t2.a	1 (30%)	Using where; Using rowid filter
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
 CREATE TABLE t2 ( f11 int PRIMARY KEY );
@@ -5639,4 +5639,60 @@ EXECUTE stmt;
 COUNT(DISTINCT a)
 3
 DROP TABLE t1;
+#
+# MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()'
+#             failed in Item_cond::remove_eq_conds on SELECT
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+# Test for nested OR conditions:
+SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+a
+1
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using temporary
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+Note	1249	Select 2 was reduced during optimization
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL  where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3)
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND
+  (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+a
+1
+EXECUTE stmt;
+a
+1
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v1;
+a
+1
+# Test for nested AND conditions:
+SELECT * FROM t1 WHERE a = 1 OR
+(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+a
+1
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR
+  (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+a
+1
+EXECUTE stmt;
+a
+1
+CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR
+(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v2;
+a
+1
+DROP TABLE t1;
+DROP VIEW v1, v2;
 End of 10.0 tests
diff --git a/mysql-test/main/select.test b/mysql-test/main/select.test
index 823aa0d54c9..5c18c6547e7 100644
--- a/mysql-test/main/select.test
+++ b/mysql-test/main/select.test
@@ -4747,4 +4747,44 @@ EXECUTE stmt;
 --enable_warnings
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()'
+--echo #             failed in Item_cond::remove_eq_conds on SELECT
+--echo #
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+
+--echo # Test for nested OR conditions:
+SELECT * FROM t1 WHERE a = 1 AND
+  (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a = 1 AND
+  (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND
+  (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+EXECUTE stmt;
+
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND
+  (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v1;
+
+--echo # Test for nested AND conditions:
+SELECT * FROM t1 WHERE a = 1 OR
+  (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR
+  (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+EXECUTE stmt;
+
+CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR
+  (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v2;
+
+DROP TABLE t1;
+DROP VIEW v1, v2;
+
 --echo End of 10.0 tests
diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result
index c1098e660cd..a19e98d49a3 100644
--- a/mysql-test/main/select_jcl6.result
+++ b/mysql-test/main/select_jcl6.result
@@ -3627,7 +3627,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
 1	SIMPLE	t2	range	si	si	5	NULL	4	Using index condition; Using where; Rowid-ordered scan
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	t3	eq_ref|filter	PRIMARY,ci	PRIMARY|ci	4|5	test.t2.a	1 (30%)	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
 EXPLAIN
 SELECT t3.a FROM t1,t2,t3
 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
@@ -3635,7 +3635,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
 1	SIMPLE	t2	range	si,ai	si	5	NULL	4	Using index condition; Using where; Rowid-ordered scan
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	t3	eq_ref|filter	PRIMARY,ci	PRIMARY|ci	4|5	test.t2.a	1 (30%)	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
 EXPLAIN 
 SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
@@ -3643,7 +3643,7 @@ t3.c IN ('bb','ee');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
 1	SIMPLE	t2	range	si	si	5	NULL	2	Using index condition; Using where; Rowid-ordered scan
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	t3	eq_ref|filter	PRIMARY,ci	PRIMARY|ci	4|5	test.t2.a	1 (30%)	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
 EXPLAIN 
 SELECT t3.a FROM t1,t2,t3
 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
@@ -3651,7 +3651,7 @@ t3.c IN ('bb','ee');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
 1	SIMPLE	t2	range	si,ai	si	5	NULL	2	Using index condition; Using where; Rowid-ordered scan
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	t3	eq_ref|filter	PRIMARY,ci	PRIMARY|ci	4|5	test.t2.a	1 (30%)	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
 CREATE TABLE t2 ( f11 int PRIMARY KEY );
@@ -5650,6 +5650,62 @@ EXECUTE stmt;
 COUNT(DISTINCT a)
 3
 DROP TABLE t1;
+#
+# MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()'
+#             failed in Item_cond::remove_eq_conds on SELECT
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+# Test for nested OR conditions:
+SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+a
+1
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using temporary
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+Note	1249	Select 2 was reduced during optimization
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL  where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3)
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND
+  (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+a
+1
+EXECUTE stmt;
+a
+1
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v1;
+a
+1
+# Test for nested AND conditions:
+SELECT * FROM t1 WHERE a = 1 OR
+(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+a
+1
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR
+  (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+a
+1
+EXECUTE stmt;
+a
+1
+CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR
+(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v2;
+a
+1
+DROP TABLE t1;
+DROP VIEW v1, v2;
 End of 10.0 tests
 set join_cache_level=default;
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result
index 93687056c91..99844d05f2a 100644
--- a/mysql-test/main/select_pkeycache.result
+++ b/mysql-test/main/select_pkeycache.result
@@ -3616,7 +3616,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
 1	SIMPLE	t2	range	si	si	5	NULL	4	Using index condition; Using where
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+1	SIMPLE	t3	eq_ref|filter	PRIMARY,ci	PRIMARY|ci	4|5	test.t2.a	1 (30%)	Using where; Using rowid filter
 EXPLAIN
 SELECT t3.a FROM t1,t2,t3
 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
@@ -3624,7 +3624,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
 1	SIMPLE	t2	range	si,ai	si	5	NULL	4	Using index condition; Using where
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+1	SIMPLE	t3	eq_ref|filter	PRIMARY,ci	PRIMARY|ci	4|5	test.t2.a	1 (30%)	Using where; Using rowid filter
 EXPLAIN 
 SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
@@ -3632,7 +3632,7 @@ t3.c IN ('bb','ee');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
 1	SIMPLE	t2	range	si	si	5	NULL	2	Using index condition; Using where
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+1	SIMPLE	t3	eq_ref|filter	PRIMARY,ci	PRIMARY|ci	4|5	test.t2.a	1 (30%)	Using where; Using rowid filter
 EXPLAIN 
 SELECT t3.a FROM t1,t2,t3
 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
@@ -3640,7 +3640,7 @@ t3.c IN ('bb','ee');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
 1	SIMPLE	t2	range	si,ai	si	5	NULL	2	Using index condition; Using where
-1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+1	SIMPLE	t3	eq_ref|filter	PRIMARY,ci	PRIMARY|ci	4|5	test.t2.a	1 (30%)	Using where; Using rowid filter
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
 CREATE TABLE t2 ( f11 int PRIMARY KEY );
@@ -5639,4 +5639,60 @@ EXECUTE stmt;
 COUNT(DISTINCT a)
 3
 DROP TABLE t1;
+#
+# MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()'
+#             failed in Item_cond::remove_eq_conds on SELECT
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+# Test for nested OR conditions:
+SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+a
+1
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using temporary
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+Note	1249	Select 2 was reduced during optimization
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL  where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3)
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND
+  (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+a
+1
+EXECUTE stmt;
+a
+1
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v1;
+a
+1
+# Test for nested AND conditions:
+SELECT * FROM t1 WHERE a = 1 OR
+(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+a
+1
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR
+  (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+a
+1
+EXECUTE stmt;
+a
+1
+CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR
+(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v2;
+a
+1
+DROP TABLE t1;
+DROP VIEW v1, v2;
 End of 10.0 tests
diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result
index 7dc3c2f463c..e6363610301 100644
--- a/mysql-test/main/subselect2.result
+++ b/mysql-test/main/subselect2.result
@@ -132,7 +132,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where
 1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where
 1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where
-1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where
+1	PRIMARY	t3	ref|filter	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	FFOLDERID_IDX|CMFLDRPARNT_IDX	34|35	test.t3.PARENTID	1 (29%)	Using where; Using rowid filter
 drop table t1, t2, t3, t4;
 CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
 INSERT INTO t1 VALUES (1),(2);
diff --git a/mysql-test/main/type_timestamp.result b/mysql-test/main/type_timestamp.result
index 508dd56f694..275d35ac476 100644
--- a/mysql-test/main/type_timestamp.result
+++ b/mysql-test/main/type_timestamp.result
@@ -1320,6 +1320,28 @@ CASE WHEN a THEN DEFAULT(a) END
 DROP TABLE t1;
 SET timestamp=DEFAULT;
 #
+# MDEV-27653 long uniques don't work with unicode collations
+#
+CREATE TABLE t1 (a timestamp, UNIQUE KEY(a) USING HASH);
+SET time_zone='+00:00';
+INSERT INTO t1 VALUES ('2001-01-01 10:20:30');
+SET time_zone='+01:00';
+INSERT INTO t1 SELECT MAX(a) FROM t1;
+ERROR 23000: Duplicate entry '2001-01-01 11:20:30' for key 'a'
+SELECT * FROM t1;
+a
+2001-01-01 11:20:30
+DROP TABLE t1;
+CREATE TABLE t1 (a timestamp, UNIQUE KEY(a) USING HASH);
+SET time_zone='+00:00';
+INSERT INTO t1 VALUES ('2001-01-01 10:20:30');
+SET time_zone='+01:00';
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+DROP TABLE t1;
+SET time_zone=DEFAULT;
+#
 # End of 10.4 tests
 #
 #
diff --git a/mysql-test/main/type_timestamp.test b/mysql-test/main/type_timestamp.test
index 3f4346bb17a..bff33c030b5 100644
--- a/mysql-test/main/type_timestamp.test
+++ b/mysql-test/main/type_timestamp.test
@@ -878,6 +878,27 @@ DROP TABLE t1;
 SET timestamp=DEFAULT;
 
 --echo #
+--echo # MDEV-27653 long uniques don't work with unicode collations
+--echo #
+
+CREATE TABLE t1 (a timestamp, UNIQUE KEY(a) USING HASH);
+SET time_zone='+00:00';
+INSERT INTO t1 VALUES ('2001-01-01 10:20:30');
+SET time_zone='+01:00';
+--error ER_DUP_ENTRY
+INSERT INTO t1 SELECT MAX(a) FROM t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a timestamp, UNIQUE KEY(a) USING HASH);
+SET time_zone='+00:00';
+INSERT INTO t1 VALUES ('2001-01-01 10:20:30');
+SET time_zone='+01:00';
+CHECK TABLE t1;
+DROP TABLE t1;
+SET time_zone=DEFAULT;
+
+--echo #
 --echo # End of 10.4 tests
 --echo #
 
diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result
index a60995ea836..109296ef2a1 100644
--- a/mysql-test/main/win.result
+++ b/mysql-test/main/win.result
@@ -4417,6 +4417,46 @@ pk	a	bit_or
 DROP TABLE t2;
 DROP TABLE t1;
 #
+# MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null |
+#
+CREATE TABLE t1 (i1 int, a int);
+INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3);
+CREATE TABLE t2 (i2 int);
+INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3);
+SELECT
+a,
+RANK() OVER (ORDER BY SUM(DISTINCT i1))
+FROM
+t1, t2 WHERE t2.i2 = t1.i1
+GROUP BY
+a;
+a	RANK() OVER (ORDER BY SUM(DISTINCT i1))
+1	1
+2	2
+3	3
+DROP TABLE t1, t2;
+#
+# MDEV-17014: Crash server using ROW_NUMBER() OVER (PARTITION ..)
+#
+CREATE TABLE t1 (UID BIGINT);
+CREATE TABLE t2 (UID BIGINT);
+CREATE TABLE t3 (UID BIGINT);
+insert into t1 VALUES (1),(2);
+insert into t2 VALUES (1),(2);
+insert into t3 VALUES (1),(2);
+SELECT
+ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID))
+FROM t1 TT1,
+t2 TT2,
+t3 TT3
+WHERE TT3.UID = TT1.UID AND TT2.UID = TT3.UID
+GROUP BY TT1.UID
+;
+ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID))
+1
+1
+DROP TABLE t1, t2, t3;
+#
 # End of 10.3 tests
 #
 #
diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test
index d7f52ec32e9..0cc929ce46b 100644
--- a/mysql-test/main/win.test
+++ b/mysql-test/main/win.test
@@ -2816,6 +2816,46 @@ DROP TABLE t2;
 
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null |
+--echo #
+
+CREATE TABLE t1 (i1 int, a int);
+INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3);
+
+CREATE TABLE t2 (i2 int);
+INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3);
+
+SELECT
+  a,
+  RANK() OVER (ORDER BY SUM(DISTINCT i1))
+FROM
+  t1, t2 WHERE t2.i2 = t1.i1
+GROUP BY
+  a;
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # MDEV-17014: Crash server using ROW_NUMBER() OVER (PARTITION ..)
+--echo #
+CREATE TABLE t1 (UID BIGINT);
+CREATE TABLE t2 (UID BIGINT);
+CREATE TABLE t3 (UID BIGINT);
+
+insert into t1 VALUES (1),(2);
+insert into t2 VALUES (1),(2);
+insert into t3 VALUES (1),(2);
+SELECT
+ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID))
+FROM t1 TT1,
+  t2 TT2,
+  t3 TT3
+WHERE TT3.UID = TT1.UID AND TT2.UID = TT3.UID
+GROUP BY TT1.UID
+;
+
+DROP TABLE t1, t2, t3;
 
 --echo #
 --echo # End of 10.3 tests
diff --git a/mysql-test/main/win_orderby.result b/mysql-test/main/win_orderby.result
index bf4a40a4db3..1a9860c1c76 100644
--- a/mysql-test/main/win_orderby.result
+++ b/mysql-test/main/win_orderby.result
@@ -24,3 +24,66 @@ pk	count(a) over (order by pk rows between 2 preceding and 2 following)
 28	5
 27	5
 drop table t0,t1;
+#
+# MDEV-30052: Crash with a query containing nested WINDOW clauses
+#
+CREATE TABLE t1 (c INT);
+insert into t1 values (1),(2);
+UPDATE t1 SET c=1
+WHERE c=2
+ORDER BY
+(1 IN ((
+SELECT *
+FROM (SELECT * FROM t1) AS v1
+GROUP BY c
+WINDOW v2 AS (ORDER BY
+(SELECT *
+FROM t1
+GROUP BY c
+WINDOW v3 AS (PARTITION BY c)
+)
+)
+))
+);
+drop table t1;
+#
+# MDEV-29359: Server crashed with heap-use-after-free in
+# Field::is_null(long long) const (Just testcase)
+#
+CREATE TABLE t1 (id int);
+INSERT INTO t1 VALUES (-1),(0),(84);
+SELECT
+id IN (SELECT id
+FROM t1
+WINDOW w AS (ORDER BY (SELECT  1
+FROM t1
+WHERE
+EXISTS ( SELECT id
+FROM t1
+GROUP BY id
+WINDOW w2 AS (ORDER BY id)
+)
+)
+)
+)
+FROM t1;
+id IN (SELECT id
+FROM t1
+WINDOW w AS (ORDER BY (SELECT  1
+FROM t1
+WHERE
+EXISTS ( SELECT id
+FROM t1
+GROUP BY id
+WINDOW w2 AS (ORDER BY id)
+)
+)
+)
+)
+1
+1
+1
+DROP TABLE t1;
+#
+# End of 10.3 tests
+#
diff --git a/mysql-test/main/win_orderby.test b/mysql-test/main/win_orderby.test
index 7f02a582ea0..65421fc095c 100644
--- a/mysql-test/main/win_orderby.test
+++ b/mysql-test/main/win_orderby.test
@@ -33,3 +33,58 @@ limit 4;
 --disable_view_protocol
 
 drop table t0,t1;
+
+
+--echo #
+--echo # MDEV-30052: Crash with a query containing nested WINDOW clauses
+--echo #
+
+CREATE TABLE t1 (c INT);
+insert into t1 values (1),(2);
+UPDATE t1 SET c=1
+WHERE c=2
+ORDER BY
+  (1 IN ((
+      SELECT *
+      FROM (SELECT * FROM t1) AS v1
+      GROUP BY c
+      WINDOW v2 AS (ORDER BY
+                      (SELECT *
+                       FROM t1
+                       GROUP BY c
+                       WINDOW v3 AS (PARTITION BY c)
+                      )
+                    )
+    ))
+  );
+drop table t1;
+
+--echo #
+--echo # MDEV-29359: Server crashed with heap-use-after-free in
+--echo # Field::is_null(long long) const (Just testcase)
+--echo #
+
+CREATE TABLE t1 (id int);
+INSERT INTO t1 VALUES (-1),(0),(84);
+
+SELECT
+  id IN (SELECT id
+         FROM t1
+         WINDOW w AS (ORDER BY (SELECT  1
+                                FROM t1
+                                WHERE
+                                  EXISTS ( SELECT id
+                                           FROM t1
+                                           GROUP BY id
+                                           WINDOW w2 AS (ORDER BY id)
+                                          )
+                                )
+                      )
+         )
+FROM t1;
+
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.3 tests
+--echo #