Merge branch '10.4' into 10.5

24 files changed, 678 insertions, 12 deletions

diff --git a/mysql-test/main/default.result b/mysql-test/main/default.result
index ca65c66ea41..0d2c2e6acbc 100644
--- a/mysql-test/main/default.result
+++ b/mysql-test/main/default.result
@@ -3388,3 +3388,18 @@ ALTER TABLE t1 ADD b CHAR(255) DEFAULT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 ERROR 42S22: Unknown column 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' in 'DEFAULT'
 DROP TABLE t1;
 # end of 10.2 test
+#
+# MDEV-22703 DEFAULT() on a BLOB column can overwrite the default
+# record, which can cause crashes when accessing already released
+# memory.
+#
+CREATE TEMPORARY TABLE t1 (h POINT DEFAULT ST_GEOMFROMTEXT('Point(1 1)')) ENGINE=InnoDB;
+INSERT INTO t1 () VALUES (),();
+ALTER TABLE t1 FORCE;
+SELECT DEFAULT(h) FROM t1;
+SELECT length(DEFAULT(h)) FROM t1;
+length(DEFAULT(h))
+25
+25
+INSERT INTO t1 () VALUES ();
+drop table t1;
diff --git a/mysql-test/main/default.test b/mysql-test/main/default.test
index 27e38eeeb49..c0561deac67 100644
--- a/mysql-test/main/default.test
+++ b/mysql-test/main/default.test
@@ -1,3 +1,5 @@
+--source include/have_innodb.inc
+
 #
 # test of already fixed bugs
 #
@@ -2107,5 +2109,20 @@ CREATE OR REPLACE TABLE t1(i int);
 ALTER TABLE t1 ADD b CHAR(255) DEFAULT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa`;
 DROP TABLE t1;
 
-
 --echo # end of 10.2 test
+
+--echo #
+--echo # MDEV-22703 DEFAULT() on a BLOB column can overwrite the default
+--echo # record, which can cause crashes when accessing already released
+--echo # memory.
+--echo #
+
+CREATE TEMPORARY TABLE t1 (h POINT DEFAULT ST_GEOMFROMTEXT('Point(1 1)')) ENGINE=InnoDB;
+INSERT INTO t1 () VALUES (),();
+ALTER TABLE t1 FORCE;
+--disable_result_log
+SELECT DEFAULT(h) FROM t1;
+--enable_result_log
+SELECT length(DEFAULT(h)) FROM t1;
+INSERT INTO t1 () VALUES ();
+drop table t1;
diff --git a/mysql-test/main/flush_and_binlog.result b/mysql-test/main/flush_and_binlog.result
new file mode 100644
index 00000000000..b9560964046
--- /dev/null
+++ b/mysql-test/main/flush_and_binlog.result
@@ -0,0 +1,22 @@
+#
+# MDEV-23843 Assertions in Diagnostics_area upon table operations under
+# FTWRL
+#
+CREATE TABLE t1 (a INT);
+FLUSH TABLES WITH READ LOCK;
+connect  con1,localhost,root,,;
+SET lock_wait_timeout= 1;
+OPTIMIZE TABLE t1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+disconnect con1;
+connection default;
+UNLOCK TABLES;
+DROP TABLE t1;
+FLUSH TABLES WITH READ LOCK;
+connect  con1,localhost,root,,test;
+SET lock_wait_timeout= 1;
+FLUSH TABLES;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+connection default;
+disconnect con1;
+unlock tables;
diff --git a/mysql-test/main/flush_and_binlog.test b/mysql-test/main/flush_and_binlog.test
new file mode 100644
index 00000000000..373b900b451
--- /dev/null
+++ b/mysql-test/main/flush_and_binlog.test
@@ -0,0 +1,29 @@
+--source include/have_log_bin.inc
+
+--echo #
+--echo # MDEV-23843 Assertions in Diagnostics_area upon table operations under
+--echo # FTWRL
+--echo #
+
+CREATE TABLE t1 (a INT);
+FLUSH TABLES WITH READ LOCK;
+--connect (con1,localhost,root,,)
+SET lock_wait_timeout= 1;
+--error ER_LOCK_WAIT_TIMEOUT
+OPTIMIZE TABLE t1;
+# Cleanup
+--disconnect con1
+--connection default
+UNLOCK TABLES;
+DROP TABLE t1;
+#
+# Second test case from MDEV_23843
+#
+FLUSH TABLES WITH READ LOCK;
+--connect (con1,localhost,root,,test)
+SET lock_wait_timeout= 1;
+--error ER_LOCK_WAIT_TIMEOUT
+FLUSH TABLES;
+--connection default
+--disconnect con1
+unlock tables;
diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result
index 4b34fbed02c..fcaa0a921c9 100644
--- a/mysql-test/main/information_schema.result
+++ b/mysql-test/main/information_schema.result
@@ -2325,5 +2325,18 @@ group by f;
 f
 drop table t1;
 #
+# MDEV-24929 Server crash in thr_multi_unlock or in
+# get_schema_tables_result upon select from I_S with joins
+#
+CREATE TABLE t1 (a TIMESTAMP, KEY (a));
+INSERT INTO t1 VALUES ('2012-12-12'),('2021-11-11');
+SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.ROUTINES) ON (t1b.a IS NULL);
+count(*)
+2
+SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.PROFILING) ON (t1b.a IS NULL);
+count(*)
+2
+DROP TABLE t1;
+#
 # End of 10.3 tests
 #
diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test
index 59704803175..5879f910e4d 100644
--- a/mysql-test/main/information_schema.test
+++ b/mysql-test/main/information_schema.test
@@ -2033,5 +2033,15 @@ group by f;
 drop table t1;
 
 --echo #
+--echo # MDEV-24929 Server crash in thr_multi_unlock or in
+--echo # get_schema_tables_result upon select from I_S with joins
+--echo #
+
+CREATE TABLE t1 (a TIMESTAMP, KEY (a));
+INSERT INTO t1 VALUES ('2012-12-12'),('2021-11-11');
+SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.ROUTINES) ON (t1b.a IS NULL);
+SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.PROFILING) ON (t1b.a IS NULL);
+DROP TABLE t1;
+--echo #
 --echo # End of 10.3 tests
 --echo #
diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result
index 5708cf88934..4c502a46981 100644
--- a/mysql-test/main/innodb_ext_key.result
+++ b/mysql-test/main/innodb_ext_key.result
@@ -799,6 +799,21 @@ EXPLAIN
 }
 drop table t1;
 SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t2 (
+pk VARCHAR(50),
+a VARCHAR(20),
+KEY k1(a),
+PRIMARY KEY(pk)
+)ENGINE=INNODB;
+INSERT INTO t2 SELECT a,a FROM t1;
+EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	NULL	k1	23	NULL	10	Using index
+DROP TABLE t1,t2;
 set global innodb_stats_persistent= @innodb_stats_persistent_save;
-set global innodb_stats_persistent_sample_pages=
-@innodb_stats_persistent_sample_pages_save;
+set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save;
diff --git a/mysql-test/main/innodb_ext_key.test b/mysql-test/main/innodb_ext_key.test
index 5fcf6a309b9..70d0009af27 100644
--- a/mysql-test/main/innodb_ext_key.test
+++ b/mysql-test/main/innodb_ext_key.test
@@ -624,6 +624,25 @@ select * from t1 force index(k1)  where f2 <= 5 and pk2 <=5 and pk1 = 'abc' and
 drop table t1;
 
 SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+--echo #
+--echo # MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index
+--echo #
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+CREATE TABLE t2 (
+  pk VARCHAR(50),
+  a VARCHAR(20),
+  KEY k1(a),
+  PRIMARY KEY(pk)
+)ENGINE=INNODB;
+
+INSERT INTO t2 SELECT a,a FROM t1;
+EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1);
+
+DROP TABLE t1,t2;
+
 set global innodb_stats_persistent= @innodb_stats_persistent_save;
-set global innodb_stats_persistent_sample_pages=
-             @innodb_stats_persistent_sample_pages_save;
+set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save;
diff --git a/mysql-test/main/lock_user.result b/mysql-test/main/lock_user.result
index 3ee8bbf2296..24fff2105ab 100644
--- a/mysql-test/main/lock_user.result
+++ b/mysql-test/main/lock_user.result
@@ -156,6 +156,7 @@ alter user user1@localhost  PASSWORD EXPIRE NEVER ACCOUNT UNLOCK ;
 show create user user1@localhost;
 CREATE USER for user1@localhost
 CREATE USER `user1`@`localhost` PASSWORD EXPIRE
+ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER
 alter user user1@localhost  ACCOUNT LOCK PASSWORD EXPIRE DEFAULT;
 show create user user1@localhost;
 CREATE USER for user1@localhost
@@ -167,5 +168,6 @@ localhost	user1	{"access":0,"version_id":XXX,"plugin":"mysql_native_password","a
 show create user user1@localhost;
 CREATE USER for user1@localhost
 CREATE USER `user1`@`localhost` PASSWORD EXPIRE
+ALTER USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 60 DAY
 drop user user1@localhost;
 drop user user2@localhost;
diff --git a/mysql-test/main/password_expiration.result b/mysql-test/main/password_expiration.result
index d05f6b3b5d0..897811bb4ad 100644
--- a/mysql-test/main/password_expiration.result
+++ b/mysql-test/main/password_expiration.result
@@ -125,6 +125,7 @@ alter user user1@localhost password expire;
 show create user user1@localhost;
 CREATE USER for user1@localhost
 CREATE USER `user1`@`localhost` PASSWORD EXPIRE
+ALTER USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY
 set password for user1@localhost= password('');
 show create user user1@localhost;
 CREATE USER for user1@localhost
@@ -151,10 +152,12 @@ alter user user1@localhost password expire;
 show create user user1@localhost;
 CREATE USER for user1@localhost
 CREATE USER `user1`@`localhost` PASSWORD EXPIRE
+ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER
 flush privileges;
 show create user user1@localhost;
 CREATE USER for user1@localhost
 CREATE USER `user1`@`localhost` PASSWORD EXPIRE
+ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER
 set password for user1@localhost= password('');
 alter user user1@localhost password expire default;
 show create user user1@localhost;
@@ -184,10 +187,12 @@ alter user user1@localhost password expire;
 show create user user1@localhost;
 CREATE USER for user1@localhost
 CREATE USER `user1`@`localhost` PASSWORD EXPIRE
+ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER
 flush privileges;
 show create user user1@localhost;
 CREATE USER for user1@localhost
 CREATE USER `user1`@`localhost` PASSWORD EXPIRE
+ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER
 set global disconnect_on_expired_password=ON;
 connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
 connect con1,localhost,user1;
diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result
index d97720c2d41..b7e126e5061 100644
--- a/mysql-test/main/ps.result
+++ b/mysql-test/main/ps.result
@@ -5485,6 +5485,18 @@ ERROR HY000: Default/ignore value is not supported for such parameter usage
 EXECUTE IMMEDIATE 'SHOW DATABASES WHERE ?' USING 0;
 Database
 #
+# MDEV-24779: main.subselect fails in buildbot with --ps-protocol
+#
+CREATE TABLE t1(a INT);
+PREPARE stmt FROM "SELECT EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1))";
+EXECUTE stmt;
+EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1))
+0
+EXECUTE stmt;
+EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1))
+0
+DROP TABLE t1;
+#
 # End of 10.2 tests
 #
 #
diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test
index e702cb76bbb..6a109951d0e 100644
--- a/mysql-test/main/ps.test
+++ b/mysql-test/main/ps.test
@@ -4932,6 +4932,16 @@ EXECUTE IMMEDIATE 'SHOW DATABASES WHERE ?' USING DEFAULT;
 EXECUTE IMMEDIATE 'SHOW DATABASES WHERE ?' USING 0;
 
 --echo #
+--echo # MDEV-24779: main.subselect fails in buildbot with --ps-protocol
+--echo #
+
+CREATE TABLE t1(a INT);
+PREPARE stmt FROM "SELECT EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1))";
+EXECUTE stmt;
+EXECUTE stmt;
+DROP TABLE t1;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
 
diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result
index 591294aff10..40ab309fffd 100644
--- a/mysql-test/main/selectivity.result
+++ b/mysql-test/main/selectivity.result
@@ -1882,4 +1882,62 @@ a	b
 set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
 drop table t1;
 # End of 10.1 tests
+#
+# MDEV-22583: Selectivity for BIT columns in filtered column for EXPLAIN is incorrect
+#
+SET optimizer_use_condition_selectivity=4;
+SET histogram_size=255;
+CREATE TABLE t1 (a BIT(32), b INT);
+INSERT INTO t1 VALUES (80, 80), (81, 81), (82, 82);
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+EXPLAIN EXTENDED SELECT * from t1 where t1.a >= 81;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	66.41	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` >= 81
+SELECT HEX(a), b from t1 where t1.a >= 81;
+HEX(a)	b
+51	81
+52	82
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+DROP TABLE t1;
+#
+# MDEV-19474: Histogram statistics are used even with optimizer_use_condition_selectivity=3
+#
+CREATE TABLE t1(a int);
+INSERT INTO t1 values (1),(2),(2),(3),(4);
+SET optimizer_use_condition_selectivity=4;
+SET histogram_size= 255;
+set use_stat_tables='preferably';
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	39.84	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2
+SET optimizer_use_condition_selectivity=3;
+# filtered should show 25 %
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	25.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	25.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+set use_stat_tables= @save_use_stat_tables;
+DROP TABLE t1;
+# End of 10.2 tests
 set @@global.histogram_size=@save_histogram_size;
diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test
index 2a0cc823eb4..1d96dc0bf80 100644
--- a/mysql-test/main/selectivity.test
+++ b/mysql-test/main/selectivity.test
@@ -1281,6 +1281,51 @@ drop table t1;
 
 --echo # End of 10.1 tests
 
+--echo #
+--echo # MDEV-22583: Selectivity for BIT columns in filtered column for EXPLAIN is incorrect
+--echo #
+
+SET optimizer_use_condition_selectivity=4;
+SET histogram_size=255;
+CREATE TABLE t1 (a BIT(32), b INT);
+INSERT INTO t1 VALUES (80, 80), (81, 81), (82, 82);
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+EXPLAIN EXTENDED SELECT * from t1 where t1.a >= 81;
+SELECT HEX(a), b from t1 where t1.a >= 81;
+
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-19474: Histogram statistics are used even with optimizer_use_condition_selectivity=3
+--echo #
+
+CREATE TABLE t1(a int);
+INSERT INTO t1 values (1),(2),(2),(3),(4);
+SET optimizer_use_condition_selectivity=4;
+SET histogram_size= 255;
+
+set use_stat_tables='preferably';
+
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+SET optimizer_use_condition_selectivity=3;
+
+--echo # filtered should show 25 %
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+FLUSH TABLES;
+
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+set use_stat_tables= @save_use_stat_tables;
+
+DROP TABLE t1;
+
+--echo # End of 10.2 tests
+
 #
 # Clean up
 #
diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result
index 7a8f837d4e2..46723410b75 100644
--- a/mysql-test/main/selectivity_innodb.result
+++ b/mysql-test/main/selectivity_innodb.result
@@ -1892,6 +1892,64 @@ a	b
 set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
 drop table t1;
 # End of 10.1 tests
+#
+# MDEV-22583: Selectivity for BIT columns in filtered column for EXPLAIN is incorrect
+#
+SET optimizer_use_condition_selectivity=4;
+SET histogram_size=255;
+CREATE TABLE t1 (a BIT(32), b INT);
+INSERT INTO t1 VALUES (80, 80), (81, 81), (82, 82);
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+EXPLAIN EXTENDED SELECT * from t1 where t1.a >= 81;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	66.41	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` >= 81
+SELECT HEX(a), b from t1 where t1.a >= 81;
+HEX(a)	b
+51	81
+52	82
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+DROP TABLE t1;
+#
+# MDEV-19474: Histogram statistics are used even with optimizer_use_condition_selectivity=3
+#
+CREATE TABLE t1(a int);
+INSERT INTO t1 values (1),(2),(2),(3),(4);
+SET optimizer_use_condition_selectivity=4;
+SET histogram_size= 255;
+set use_stat_tables='preferably';
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	39.84	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2
+SET optimizer_use_condition_selectivity=3;
+# filtered should show 25 %
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	25.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	25.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+set use_stat_tables= @save_use_stat_tables;
+DROP TABLE t1;
+# End of 10.2 tests
 set @@global.histogram_size=@save_histogram_size;
 set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
 set @tmp_ust= @@use_stat_tables;
diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result
index 44b879ae33b..90550058dd9 100644
--- a/mysql-test/main/subselect4.result
+++ b/mysql-test/main/subselect4.result
@@ -2687,6 +2687,15 @@ f
 bar
 DROP TABLE t1, t2;
 #
+# MDEV-23449: alias do not exist and a query do not report an error
+#
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5);
+SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id);
+ERROR 42S22: Unknown column 's.id' in 'group statement'
+DROP TABLE t1;
+# End of 10.2 tests
+#
 # MDEV-18335: Assertion `!error || error == 137' failed in subselect_rowid_merge_engine::init
 #
 CREATE TABLE t1 (i1 int,v1 varchar(1),KEY (v1,i1));
@@ -2717,7 +2726,6 @@ Warnings:
 Warning	1931	Query execution was interrupted. The query examined at least 3020 rows, which exceeds LIMIT ROWS EXAMINED (500). The query result may be incomplete
 SET join_cache_level= @save_join_cache_level;
 DROP TABLE t1,t2,t3,t4;
-# End of 10.2 tests
 #
 # MDEV-21265: IN predicate conversion to IN subquery should be allowed for a broader set of datatype comparison
 #
diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test
index d46bb846a9e..423353fe677 100644
--- a/mysql-test/main/subselect4.test
+++ b/mysql-test/main/subselect4.test
@@ -2202,6 +2202,19 @@ SELECT * FROM t2;
 DROP TABLE t1, t2;
 
 --echo #
+--echo # MDEV-23449: alias do not exist and a query do not report an error
+--echo #
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5);
+
+--error ER_BAD_FIELD_ERROR
+SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id);
+DROP TABLE t1;
+
+--echo # End of 10.2 tests
+
+--echo #
 --echo # MDEV-18335: Assertion `!error || error == 137' failed in subselect_rowid_merge_engine::init
 --echo #
 
@@ -2236,9 +2249,6 @@ from t2 join t1 on
 SET join_cache_level= @save_join_cache_level;
 
 DROP TABLE t1,t2,t3,t4;
-
---echo # End of 10.2 tests
-
 --echo #
 --echo # MDEV-21265: IN predicate conversion to IN subquery should be allowed for a broader set of datatype comparison
 --echo #
diff --git a/mysql-test/main/system_mysql_db_507.result b/mysql-test/main/system_mysql_db_507.result
index 2d68dc82529..8069405aa3a 100644
--- a/mysql-test/main/system_mysql_db_507.result
+++ b/mysql-test/main/system_mysql_db_507.result
@@ -214,6 +214,7 @@ alter user user@localhost password expire;
 show create user user@localhost;
 CREATE USER for user@localhost
 CREATE USER `user`@`localhost` PASSWORD EXPIRE
+ALTER USER `user`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY
 set password for user@localhost= password('');
 show create user user@localhost;
 CREATE USER for user@localhost
diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index c2269f0862b..7ab9666c373 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -2675,6 +2675,107 @@ values ((select min(a), max(b) from t1));
 ERROR 21000: Operand should contain 1 column(s)
 drop table t1;
 #
+# MDEV-24840: union of TVCs in IN subquery
+#
+create table t1 (a int) engine=myisam;
+insert into t1 values (3), (7), (1);
+select a from t1 where a in (values (7) union values (8));
+a
+7
+explain extended select a from t1 where a in (values (7) union values (8));
+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
+4	DEPENDENT SUBQUERY	<derived2>	ref	key0	key0	4	func	2	100.00	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+5	DEPENDENT UNION	<derived3>	ref	key0	key0	4	func	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union4,5>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`7` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`8`)))
+prepare stmt from "select a from t1 where a in (values (7) union values (8))";
+execute stmt;
+a
+7
+execute stmt;
+a
+7
+deallocate prepare stmt;
+select a from t1 where a not in (values (7) union values (8));
+a
+3
+1
+explain extended select a from t1 where a not in (values (7) union values (8));
+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
+4	DEPENDENT SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+5	DEPENDENT UNION	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union4,5>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`7`) union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`8`))))
+select a from t1 where a < all(values (7) union values (8));
+a
+3
+1
+explain extended select a from t1 where a < all(values (7) union values (8));
+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
+4	SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+5	UNION	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union4,5>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`)))
+select a from t1 where a >= any(values (7) union values (8));
+a
+7
+explain extended select a from t1 where a >= any(values (7) union values (8));
+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
+4	SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+5	UNION	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union4,5>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`)))
+drop table t1;
+#
+# MDEV-24934:EXPLAIN for queries based on TVC using subqueries
+#
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+values (8), ((select * from t1 where a between 2 and 4));
+8
+8
+3
+explain values (8), ((select * from t1 where a between 2 and 4));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+values  ((select * from t1 where a between 2 and 4)),
+((select * from t1 where a > 10));
+(select * from t1 where a between 2 and 4)
+3
+NULL
+explain values  ((select * from t1 where a between 2 and 4)),
+((select * from t1 where a > 10));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+values (10,11), ((select * from t1 where a = 7) + 1, 21);
+10	11
+10	11
+8	21
+explain values (10,11), ((select * from t1 where a = 7) + 1, 21);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+drop table t1;
+#
 # End of 10.3 tests
 #
 #
diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test
index fc0288d3fa0..66463cb1f1a 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1402,6 +1402,64 @@ values ((select min(a), max(b) from t1));
 drop table t1;
 
 --echo #
+--echo # MDEV-24840: union of TVCs in IN subquery
+--echo #
+
+create table t1 (a int) engine=myisam;
+insert into t1 values (3), (7), (1);
+
+let $q=
+select a from t1 where a in (values (7) union values (8));
+eval $q;
+eval explain extended $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q=
+select a from t1 where a not in (values (7) union values (8));
+eval $q;
+eval explain extended $q;
+
+let $q=
+select a from t1 where a < all(values (7) union values (8));
+eval $q;
+eval explain extended $q;
+
+let $q=
+select a from t1 where a >= any(values (7) union values (8));
+eval $q;
+eval explain extended $q;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-24934:EXPLAIN for queries based on TVC using subqueries
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+
+let $q1=
+values (8), ((select * from t1 where a between 2 and 4));
+eval $q1;
+eval explain $q1;
+
+let $q2=
+values  ((select * from t1 where a between 2 and 4)),
+         ((select * from t1 where a > 10));
+eval $q2;
+eval explain $q2;
+
+let $q3=
+values (10,11), ((select * from t1 where a = 7) + 1, 21);
+eval $q3;
+eval explain $q3;
+
+drop table t1;
+
+--echo #
 --echo # End of 10.3 tests
 --echo #
 
diff --git a/mysql-test/main/type_decimal.result b/mysql-test/main/type_decimal.result
index 6e7d7c8b6fe..0228652f74c 100644
--- a/mysql-test/main/type_decimal.result
+++ b/mysql-test/main/type_decimal.result
@@ -176,9 +176,8 @@ Note	1265	Data truncated for column 'a' at row 2
 insert ignore into t1 values ("1e+18446744073709551615"),("1e+18446744073709551616"),("1e-9223372036854775807"),("1e-9223372036854775809");
 Warnings:
 Warning	1264	Out of range value for column 'a' at row 1
-Warning	1366	Incorrect decimal value: '1e+18446744073709551616' for column `test`.`t1`.`a` at row 2
+Warning	1264	Out of range value for column 'a' at row 2
 Note	1265	Data truncated for column 'a' at row 3
-Warning	1366	Incorrect decimal value: '1e-9223372036854775809' for column `test`.`t1`.`a` at row 4
 insert ignore into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
 Warnings:
 Warning	1265	Data truncated for column 'a' at row 1
@@ -209,7 +208,7 @@ a
 99999999.99
 0.00
 99999999.99
-0.00
+99999999.99
 0.00
 0.00
 123.40
@@ -1078,6 +1077,90 @@ t1	CREATE TABLE `t1` (
 DROP TABLE t1;
 DROP TABLE t1dec102;
 #
+# MDEV-24790 CAST('0e1111111111' AS DECIMAL(38,0)) returns a wrong result
+#
+SELECT CAST('0e111111111' AS DECIMAL(38,0)) AS a;
+a
+0
+SELECT CAST('0e1111111111' AS DECIMAL(38,0)) AS a;
+a
+0
+SELECT CAST('.00000000000000000000000000000000000001e111111111111111111111' AS DECIMAL(38,0)) AS a;
+a
+99999999999999999999999999999999999999
+Warnings:
+Warning	1916	Got overflow when converting '' to DECIMAL. Value truncated
+Warning	1292	Truncated incorrect DECIMAL value: '.00000000000000000000000000000000000001e111111111111111111111'
+Warning	1264	Out of range value for column 'a' at row 1
+CREATE TABLE t1 (str VARCHAR(128), comment VARCHAR(128));
+INSERT INTO t1 VALUES
+('0e111111111111111111111', 'Zero mantissa and a huge positive exponent'),
+('1e111111111111111111111', 'Non-zero mantissa, huge positive exponent'),
+('0e-111111111111111111111', 'Zero mantissa and a huge negative exponent'),
+('1e-111111111111111111111', 'Non-zero mantissa and a huge negative exponent');
+BEGIN NOT ATOMIC
+DECLARE done INT DEFAULT FALSE;
+DECLARE vstr, vcomment VARCHAR(128);
+DECLARE cur1 CURSOR FOR SELECT str, comment FROM t1 ORDER BY str;
+DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+OPEN cur1;
+read_loop:
+LOOP
+FETCH cur1 INTO vstr, vcomment;
+IF done THEN
+LEAVE read_loop;
+END IF;
+SELECT vstr AS `--------`, vcomment AS `--------`;
+SELECT CAST(str AS DECIMAL(38,0)) FROM t1 WHERE str=vstr;
+SHOW WARNINGS;
+SELECT CAST(CONCAT(str,'garbage') AS DECIMAL(38,0)) FROM t1 WHERE str=vstr;
+SHOW WARNINGS;
+END LOOP;
+END;
+$$
+--------	--------
+0e-111111111111111111111	Zero mantissa and a huge negative exponent
+CAST(str AS DECIMAL(38,0))
+0
+Level	Code	Message
+CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))
+0
+Level	Code	Message
+Warning	1292	Truncated incorrect DECIMAL value: '0e-111111111111111111111garbage'
+--------	--------
+0e111111111111111111111	Zero mantissa and a huge positive exponent
+CAST(str AS DECIMAL(38,0))
+0
+Level	Code	Message
+CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))
+0
+Level	Code	Message
+Warning	1292	Truncated incorrect DECIMAL value: '0e111111111111111111111garbage'
+--------	--------
+1e-111111111111111111111	Non-zero mantissa and a huge negative exponent
+CAST(str AS DECIMAL(38,0))
+0
+Level	Code	Message
+CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))
+0
+Level	Code	Message
+Warning	1292	Truncated incorrect DECIMAL value: '1e-111111111111111111111garbage'
+--------	--------
+1e111111111111111111111	Non-zero mantissa, huge positive exponent
+CAST(str AS DECIMAL(38,0))
+99999999999999999999999999999999999999
+Level	Code	Message
+Warning	1916	Got overflow when converting '' to DECIMAL. Value truncated
+Warning	1292	Truncated incorrect DECIMAL value: '1e111111111111111111111'
+Warning	1264	Out of range value for column 'CAST(str AS DECIMAL(38,0))' at row 1
+CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))
+99999999999999999999999999999999999999
+Level	Code	Message
+Warning	1916	Got overflow when converting '' to DECIMAL. Value truncated
+Warning	1292	Truncated incorrect DECIMAL value: '1e111111111111111111111garbage'
+Warning	1264	Out of range value for column 'CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))' at row 1
+DROP TABLE t1;
+#
 # End of 10.2 tests
 #
 #
diff --git a/mysql-test/main/type_decimal.test b/mysql-test/main/type_decimal.test
index 4ffbcbd3288..1076592fe62 100644
--- a/mysql-test/main/type_decimal.test
+++ b/mysql-test/main/type_decimal.test
@@ -670,6 +670,50 @@ DROP TABLE t1;
 DROP TABLE t1dec102;
 
 --echo #
+--echo # MDEV-24790 CAST('0e1111111111' AS DECIMAL(38,0)) returns a wrong result
+--echo #
+
+SELECT CAST('0e111111111' AS DECIMAL(38,0)) AS a;
+SELECT CAST('0e1111111111' AS DECIMAL(38,0)) AS a;
+SELECT CAST('.00000000000000000000000000000000000001e111111111111111111111' AS DECIMAL(38,0)) AS a;
+
+CREATE TABLE t1 (str VARCHAR(128), comment VARCHAR(128));
+INSERT INTO t1 VALUES
+('0e111111111111111111111', 'Zero mantissa and a huge positive exponent'),
+('1e111111111111111111111', 'Non-zero mantissa, huge positive exponent'),
+('0e-111111111111111111111', 'Zero mantissa and a huge negative exponent'),
+('1e-111111111111111111111', 'Non-zero mantissa and a huge negative exponent');
+
+# The loop below issues SHOW WARNINGS manually, disable automatic warnings
+--disable_warnings
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE done INT DEFAULT FALSE;
+  DECLARE vstr, vcomment VARCHAR(128);
+  DECLARE cur1 CURSOR FOR SELECT str, comment FROM t1 ORDER BY str;
+  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+  OPEN cur1;
+read_loop:
+  LOOP
+    FETCH cur1 INTO vstr, vcomment;
+    IF done THEN
+      LEAVE read_loop;
+    END IF;
+    SELECT vstr AS `--------`, vcomment AS `--------`;
+    SELECT CAST(str AS DECIMAL(38,0)) FROM t1 WHERE str=vstr;
+    SHOW WARNINGS;
+    SELECT CAST(CONCAT(str,'garbage') AS DECIMAL(38,0)) FROM t1 WHERE str=vstr;
+    SHOW WARNINGS;
+  END LOOP;
+END;
+$$
+DELIMITER ;$$
+--enable_warnings
+
+
+DROP TABLE t1;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
 
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
index e9be123baa6..4585f370c1d 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -6777,6 +6777,21 @@ DROP PROCEDURE sp1;
 DROP VIEW v1;
 DROP TABLE t1, t2;
 #
+# MDEV-23291: SUM column from a derived table returns invalid values
+#
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (2,2);
+CREATE view v1 AS
+SELECT a as x, (select x) as y, (select y) as z FROM t1;
+SELECT sum(z) FROM (SELECT a as x, (select x) as y, (select y) as z FROM t1) q;
+sum(z)
+3
+SELECT sum(z) FROM v1;
+sum(z)
+3
+DROP TABLE t1;
+DROP VIEW v1;
+#
 # End of 10.2 tests
 #
 #
diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
index b1074264bef..78ab6a98ebb 100644
--- a/mysql-test/main/view.test
+++ b/mysql-test/main/view.test
@@ -6485,6 +6485,22 @@ DROP VIEW v1;
 DROP TABLE t1, t2;
 
 --echo #
+--echo # MDEV-23291: SUM column from a derived table returns invalid values
+--echo #
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (2,2);
+
+CREATE view v1 AS
+SELECT a as x, (select x) as y, (select y) as z FROM t1;
+
+SELECT sum(z) FROM (SELECT a as x, (select x) as y, (select y) as z FROM t1) q;
+SELECT sum(z) FROM v1;
+
+DROP TABLE t1;
+DROP VIEW v1;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #