Merge 10.4 into 10.5

24 files changed, 269 insertions, 43 deletions

diff --git a/mysql-test/main/derived_opt.result b/mysql-test/main/derived_opt.result
index 9da9e6e4413..cf0c1cb617f 100644
--- a/mysql-test/main/derived_opt.result
+++ b/mysql-test/main/derived_opt.result
@@ -540,4 +540,31 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 set join_cache_level=default;
 set optimizer_switch= @save_optimizer_switch;
 DROP TABLE t1,t2;
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch="derived_merge=on";
+CREATE TABLE t1 (id int, d2 datetime, id1 int) ;
+insert into t1 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',3);
+CREATE TABLE t2 (id int, d1 datetime, id1 int) ;
+insert into t2 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',2);
+prepare stmt from "
+SELECT * from
+  (SELECT min(d2) AS d2, min(d1) AS d1 FROM
+     (SELECT  t1.d2 AS d2, (SELECT t2.d1
+                              FROM t2 WHERE t1.id1 = t2.id1
+                              ORDER BY t2.id DESC LIMIT 1) AS d1
+        FROM t1
+     ) dt2
+  ) ca
+  ORDER BY ca.d2;";
+execute stmt;
+d2	d1
+2020-01-01 10:10:10	2020-01-01 10:10:10
+execute stmt;
+d2	d1
+2020-01-01 10:10:10	2020-01-01 10:10:10
+set optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1, t2;
+#
+# End of 10.3 tests
+#
 set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/main/derived_opt.test b/mysql-test/main/derived_opt.test
index eccf4c13020..dee424559ee 100644
--- a/mysql-test/main/derived_opt.test
+++ b/mysql-test/main/derived_opt.test
@@ -406,5 +406,38 @@ set optimizer_switch= @save_optimizer_switch;
 
 DROP TABLE t1,t2;
 
+#
+# MDEV-25182: Complex query in Store procedure corrupts results
+#
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch="derived_merge=on";
+
+CREATE TABLE t1 (id int, d2 datetime, id1 int) ;
+insert into t1 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',3);
+
+CREATE TABLE t2 (id int, d1 datetime, id1 int) ;
+insert into t2 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',2);
+
+prepare stmt from "
+SELECT * from
+  (SELECT min(d2) AS d2, min(d1) AS d1 FROM
+     (SELECT  t1.d2 AS d2, (SELECT t2.d1
+                              FROM t2 WHERE t1.id1 = t2.id1
+                              ORDER BY t2.id DESC LIMIT 1) AS d1
+        FROM t1
+     ) dt2
+  ) ca
+  ORDER BY ca.d2;";
+
+execute stmt;
+execute stmt;
+
+set optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1, t2;
+
+--echo #
+--echo # End of 10.3 tests
+--echo #
+
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result
index 0f09aedc22a..177f0950a77 100644
--- a/mysql-test/main/func_group.result
+++ b/mysql-test/main/func_group.result
@@ -606,7 +606,7 @@ select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	0	NULL	15	Using where; Using index
 explain 
-select min(a1) from t1 where a1 != 'KKK';
+select min(a1) from t1 where (a1 < 'KKK' or a1 > 'KKK');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	3	NULL	14	Using where; Using index
 explain
diff --git a/mysql-test/main/func_group.test b/mysql-test/main/func_group.test
index 10b92cbadca..a28b39c28f6 100644
--- a/mysql-test/main/func_group.test
+++ b/mysql-test/main/func_group.test
@@ -359,7 +359,7 @@ select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK';
 explain 
 select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX';
 explain 
-select min(a1) from t1 where a1 != 'KKK';
+select min(a1) from t1 where (a1 < 'KKK' or a1 > 'KKK');
 explain
 select max(a3) from t1 where a2 < 2 and a3 < 'SEA';
 explain
diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result
index 4f32db780fd..a17f3f09c3b 100644
--- a/mysql-test/main/group_min_max.result
+++ b/mysql-test/main/group_min_max.result
@@ -2942,7 +2942,7 @@ NULL
 EXPLAIN
 SELECT MIN( a ) FROM t1 WHERE a <> NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
+x	x	x	x	x	x	x	x	x	Using where; Using index
 SELECT MIN( a ) FROM t1 WHERE a <> NULL;
 MIN( a )
 NULL
diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result
index 1a4906801a0..053239b7fb1 100644
--- a/mysql-test/main/index_merge_myisam.result
+++ b/mysql-test/main/index_merge_myisam.result
@@ -1687,7 +1687,8 @@ INSERT INTO t1 VALUES
 ALTER TABLE t1 ENABLE KEYS;
 EXPLAIN
 SELECT  * FROM t1 FORCE KEY (PRIMARY , i , c1 , c2) 
-WHERE pk = 255 OR i = 22 OR (pk  IN (1 , 136) AND c2  IN ('c' , 'w') AND (c1 NOT BETWEEN 'e' AND 'i' OR  c2 > 'g'))  OR pk != 1  ;
+WHERE pk = 255 OR i = 22 OR (pk  IN (1 , 136) AND c2  IN ('c' , 'w') AND (c1
+NOT BETWEEN 'e' AND 'i' OR  c2 > 'g'))  OR (pk is not null and (pk <1 or pk>1))  ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	PRIMARY,c1,i,c2	PRIMARY,i	0,5	NULL	69	Using sort_union(PRIMARY,i); Using where
 DROP TABLE t1;
diff --git a/mysql-test/main/index_merge_myisam.test b/mysql-test/main/index_merge_myisam.test
index e35ce567573..10ddbee36b2 100644
--- a/mysql-test/main/index_merge_myisam.test
+++ b/mysql-test/main/index_merge_myisam.test
@@ -236,9 +236,11 @@ INSERT INTO t1 VALUES
 
 ALTER TABLE t1 ENABLE KEYS;
 
+# note: (pk is not null and (pk <1 or pk>1)) below is a sargable form of pk!=1
 EXPLAIN
 SELECT  * FROM t1 FORCE KEY (PRIMARY , i , c1 , c2) 
-WHERE pk = 255 OR i = 22 OR (pk  IN (1 , 136) AND c2  IN ('c' , 'w') AND (c1 NOT BETWEEN 'e' AND 'i' OR  c2 > 'g'))  OR pk != 1  ;
+WHERE pk = 255 OR i = 22 OR (pk  IN (1 , 136) AND c2  IN ('c' , 'w') AND (c1
+NOT BETWEEN 'e' AND 'i' OR  c2 > 'g'))  OR (pk is not null and (pk <1 or pk>1))  ;
 
 DROP TABLE t1;
 
diff --git a/mysql-test/main/innodb_icp.result b/mysql-test/main/innodb_icp.result
index adf65780d21..c89d49cad0c 100644
--- a/mysql-test/main/innodb_icp.result
+++ b/mysql-test/main/innodb_icp.result
@@ -455,11 +455,11 @@ c1 INT NOT NULL,
 PRIMARY KEY (pk)
 );
 INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
-EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
+EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where
 SET SESSION optimizer_switch='index_condition_pushdown=off';
-SELECT pk, c1 FROM t1 WHERE pk <> 3;
+SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3);
 pk	c1
 1	9
 2	7
@@ -689,23 +689,23 @@ INSERT INTO t2 VALUES
 insert into t2 select seq from seq_1_to_100;
 SET SESSION optimizer_switch='index_condition_pushdown=off';
 EXPLAIN
-SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 
+SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0)
 HAVING t1.c != 5 ORDER BY t1.c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	#	Using where; Using filesort
 1	SIMPLE	t2	ref	a	a	515	test.t1.a	#	Using where
-SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
+SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0)
 HAVING t1.c != 5 ORDER BY t1.c;
 b	c
 1	4
 SET SESSION optimizer_switch='index_condition_pushdown=on';
 EXPLAIN
-SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
+SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0)
 HAVING t1.c != 5 ORDER BY t1.c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	#	Using where; Using filesort
 1	SIMPLE	t2	ref	a	a	515	test.t1.a	#	Using where
-SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
+SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0)
 HAVING t1.c != 5 ORDER BY t1.c;
 b	c
 1	4
diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result
index f059c3f95e5..d0614913480 100644
--- a/mysql-test/main/myisam_icp.result
+++ b/mysql-test/main/myisam_icp.result
@@ -448,11 +448,11 @@ c1 INT NOT NULL,
 PRIMARY KEY (pk)
 );
 INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
-EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
+EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	5	Using where
 SET SESSION optimizer_switch='index_condition_pushdown=off';
-SELECT pk, c1 FROM t1 WHERE pk <> 3;
+SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3);
 pk	c1
 1	9
 2	7
@@ -682,23 +682,23 @@ INSERT INTO t2 VALUES
 insert into t2 select seq from seq_1_to_100;
 SET SESSION optimizer_switch='index_condition_pushdown=off';
 EXPLAIN
-SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 
+SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0)
 HAVING t1.c != 5 ORDER BY t1.c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	#	Using where; Using filesort
 1	SIMPLE	t2	ref	a	a	515	test.t1.a	#	Using where
-SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
+SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0)
 HAVING t1.c != 5 ORDER BY t1.c;
 b	c
 1	4
 SET SESSION optimizer_switch='index_condition_pushdown=on';
 EXPLAIN
-SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
+SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0)
 HAVING t1.c != 5 ORDER BY t1.c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	#	Using where; Using filesort
 1	SIMPLE	t2	ref	a	a	515	test.t1.a	#	Using where
-SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
+SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0)
 HAVING t1.c != 5 ORDER BY t1.c;
 b	c
 1	4
diff --git a/mysql-test/main/mysqldump-nl.test b/mysql-test/main/mysqldump-nl.test
index 863c846b9a6..4451b0605c2 100644
--- a/mysql-test/main/mysqldump-nl.test
+++ b/mysql-test/main/mysqldump-nl.test
@@ -26,10 +26,10 @@ create procedure sp() select * from `v1
 flush tables;
 use test;
 
-exec $MYSQL_DUMP --compact --comment --routines --add-drop-database --databases 'mysqltest1
+exec $MYSQL_DUMP --compact --comments --routines --add-drop-database --databases 'mysqltest1
 1tsetlqsym';
 
-exec $MYSQL_DUMP --compact --comment --routines --add-drop-database --databases 'mysqltest1
+exec $MYSQL_DUMP --compact --comments --routines --add-drop-database --databases 'mysqltest1
 1tsetlqsym' | $MYSQL;
 
 show tables from `mysqltest1
@@ -45,11 +45,11 @@ create database `test\``
 
 show databases like 'test%';
 
-exec $MYSQL_DUMP --compact --comment --add-drop-database --databases 'test`' 'test\`
+exec $MYSQL_DUMP --compact --comments --add-drop-database --databases 'test`' 'test\`
 \! ls
 #';
 
-exec $MYSQL_DUMP --compact --comment --add-drop-database --databases 'test`' 'test\`
+exec $MYSQL_DUMP --compact --comments --add-drop-database --databases 'test`' 'test\`
 \! ls
 #' | $MYSQL;
 
diff --git a/mysql-test/main/parser_not_embedded.test b/mysql-test/main/parser_not_embedded.test
index 3ebd23e888e..3af1260f4ad 100644
--- a/mysql-test/main/parser_not_embedded.test
+++ b/mysql-test/main/parser_not_embedded.test
@@ -21,7 +21,7 @@ select 7 as expected, /*!01000 1 + /*!01000 8 + /*!01000 error */ 16 + */ 2 + */
 select 4 as expected, /* 1 + /*!01000 8 + */ 2 + */ 4;
 EOF
 
---exec $MYSQL --comment --force --table test <$MYSQLTEST_VARDIR/tmp/bug39559.sql
+--exec $MYSQL --comments --force --table test <$MYSQLTEST_VARDIR/tmp/bug39559.sql
 --remove_file $MYSQLTEST_VARDIR/tmp/bug39559.sql
 
 --echo # Bug#46527 "COMMIT AND CHAIN RELEASE does not make sense"
diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result
index 1c13b0a0b84..6b2586e9be6 100644
--- a/mysql-test/main/ps.result
+++ b/mysql-test/main/ps.result
@@ -5514,6 +5514,43 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
 #
+# MDEV-25108: Running of the EXPLAIN EXTENDED statement produces extra warning
+#             in case it is executed in PS (prepared statement) mode
+#
+CREATE TABLE t1 (c int);
+CREATE TABLE t2 (d int);
+# EXPLAIN EXTENDED in regular way (not PS mode)
+EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	0.00	Const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1276	Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
+Note	1003	/* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1`
+SHOW WARNINGS;
+Level	Code	Message
+Note	1276	Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
+Note	1003	/* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1`
+# Now run the same EXPLAIN EXTENDED in PS mode. Number of warnings
+# and their content must be the same as in case running the statement
+# in regular way
+PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1";
+Warnings:
+Note	1276	Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	0.00	Const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1276	Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
+Note	1003	/* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1`
+SHOW WARNINGS;
+Level	Code	Message
+Note	1276	Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
+Note	1003	/* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1`
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2;
+#
 # End of 10.2 tests
 #
 #
@@ -5532,5 +5569,37 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW v1;
 DROP TABLE t1;
 #
+# MDEV-25197: The statement set password=password('') executed in PS mode
+#             fails in case it is run by a user with expired password
+#
+CREATE USER user1@localhost PASSWORD EXPIRE;
+SET @disconnect_on_expired_password_save=@@global.disconnect_on_expired_password;
+SET GLOBAL disconnect_on_expired_password=OFF;
+connect con1,localhost,user1;
+connection con1;
+# Check that no regular statement like SELECT can be prepared
+# by a user with an expired password
+PREPARE stmt FROM "SELECT 1";
+ERROR HY000: You must SET PASSWORD before executing this statement
+# Check that the DEALLOCATE PREPARE statement can be run by a user
+# with an expired password
+PREPARE stmt FROM "SET password=password('')";
+DEALLOCATE PREPARE stmt;
+# Check that the SET PASSWORD statement can be executed in PS mode by
+# a user with an expired password
+PREPARE stmt FROM "SET password=password('')";
+EXECUTE stmt;
+PREPARE stmt FROM "SELECT 1";
+# Check that user's password is not expired anymore
+EXECUTE stmt;
+1
+1
+DEALLOCATE PREPARE stmt;
+# Clean up
+disconnect con1;
+connection default;
+SET GLOBAL disconnect_on_expired_password=@disconnect_on_expired_password_save;
+DROP USER user1@localhost;
+#
 # End of 10.4 tests
 #
diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test
index 2ce78b78e90..2a468d33ace 100644
--- a/mysql-test/main/ps.test
+++ b/mysql-test/main/ps.test
@@ -4956,6 +4956,26 @@ DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
 
 --echo #
+--echo # MDEV-25108: Running of the EXPLAIN EXTENDED statement produces extra warning
+--echo #             in case it is executed in PS (prepared statement) mode
+--echo #
+CREATE TABLE t1 (c int);
+CREATE TABLE t2 (d int);
+
+--echo # EXPLAIN EXTENDED in regular way (not PS mode)
+EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
+SHOW WARNINGS;
+
+--echo # Now run the same EXPLAIN EXTENDED in PS mode. Number of warnings
+--echo # and their content must be the same as in case running the statement
+--echo # in regular way
+PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1";
+EXECUTE stmt;
+SHOW WARNINGS;
+
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2;
+--echo #
 --echo # End of 10.2 tests
 --echo #
 
@@ -4980,5 +5000,42 @@ DROP VIEW v1;
 DROP TABLE t1;
 
 --echo #
+--echo # MDEV-25197: The statement set password=password('') executed in PS mode
+--echo #             fails in case it is run by a user with expired password
+--echo #
+CREATE USER user1@localhost PASSWORD EXPIRE;
+
+SET @disconnect_on_expired_password_save=@@global.disconnect_on_expired_password;
+SET GLOBAL disconnect_on_expired_password=OFF;
+
+connect(con1,localhost,user1);
+connection con1;
+--echo # Check that no regular statement like SELECT can be prepared
+--echo # by a user with an expired password
+--error ER_MUST_CHANGE_PASSWORD
+PREPARE stmt FROM "SELECT 1";
+
+--echo # Check that the DEALLOCATE PREPARE statement can be run by a user
+--echo # with an expired password
+PREPARE stmt FROM "SET password=password('')";
+DEALLOCATE PREPARE stmt;
+
+--echo # Check that the SET PASSWORD statement can be executed in PS mode by
+--echo # a user with an expired password
+PREPARE stmt FROM "SET password=password('')";
+EXECUTE stmt;
+PREPARE stmt FROM "SELECT 1";
+--echo # Check that user's password is not expired anymore
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+--echo # Clean up
+disconnect con1;
+connection default;
+
+SET GLOBAL disconnect_on_expired_password=@disconnect_on_expired_password_save;
+DROP USER user1@localhost;
+
+--echo #
 --echo # End of 10.4 tests
 --echo #
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index 8e9b9596e1e..d97cfb2b587 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -3252,6 +3252,17 @@ SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR
 id	a	b	code	num
 DROP TABLE t1, t2;
 #
+# MDEV-23634: Select query hanged the server and leads to OOM ...
+# (The fix is to add the same handling for "col!=const" as MDEV-21958 did for NOT IN)
+#
+create table t1 (pk int primary key, a int);
+insert into t1 (pk) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+# must not use range:
+explain select * from t1 force index (primary) where pk != 1 and pk!=2 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	10	Using where
+drop table t1;
+#
 # MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value
 #
 create table t1 (pk int, i int, v int, primary key (pk), key(v));
diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test
index 8e8161cde25..6d43ad9090d 100644
--- a/mysql-test/main/range.test
+++ b/mysql-test/main/range.test
@@ -2216,6 +2216,17 @@ DROP TABLE t1, t2;
 
 
 --echo #
+--echo # MDEV-23634: Select query hanged the server and leads to OOM ...
+--echo # (The fix is to add the same handling for "col!=const" as MDEV-21958 did for NOT IN)
+--echo #
+create table t1 (pk int primary key, a int);
+insert into t1 (pk) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+--echo # must not use range:
+explain select * from t1 force index (primary) where pk != 1 and pk!=2 ;
+drop table t1;
+
+--echo #
 --echo # MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value
 --echo #
 
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index e24e3bc3903..9fa7bd29851 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -3241,6 +3241,17 @@ SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR
 id	a	b	code	num
 DROP TABLE t1, t2;
 #
+# MDEV-23634: Select query hanged the server and leads to OOM ...
+# (The fix is to add the same handling for "col!=const" as MDEV-21958 did for NOT IN)
+#
+create table t1 (pk int primary key, a int);
+insert into t1 (pk) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+# must not use range:
+explain select * from t1 force index (primary) where pk != 1 and pk!=2 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	10	Using where
+drop table t1;
+#
 # MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value
 #
 create table t1 (pk int, i int, v int, primary key (pk), key(v));
diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result
index 6b2c9a2f0bc..207e012b825 100644
--- a/mysql-test/main/range_vs_index_merge.result
+++ b/mysql-test/main/range_vs_index_merge.result
@@ -1810,11 +1810,11 @@ CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX idx(b));
 INSERT INTO t1 VALUES (167,9999), (168,10000);
 EXPLAIN
 SELECT * FROM t1
-WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2;
+WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	PRIMARY,idx	PRIMARY	0	NULL	2	Using index condition; Using where
 SELECT * FROM t1
-WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2;
+WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2);
 a	b
 167	9999
 168	10000
@@ -1846,12 +1846,12 @@ test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
 EXPLAIN
 SELECT * FROM t1 FORCE KEY (state,capital) 
-WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 
+WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) 
 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	state,capital	state	71	NULL	8	Using index condition; Using where
 SELECT * FROM t1 FORCE KEY (state,capital) 
-WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 
+WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9)
 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
 id	state	capital
 4	Florida	Tallahassee
diff --git a/mysql-test/main/range_vs_index_merge.test b/mysql-test/main/range_vs_index_merge.test
index 5ed5f621ab6..94210ce5dd3 100644
--- a/mysql-test/main/range_vs_index_merge.test
+++ b/mysql-test/main/range_vs_index_merge.test
@@ -1231,9 +1231,9 @@ INSERT INTO t1 VALUES (167,9999), (168,10000);
 
 EXPLAIN
 SELECT * FROM t1
-  WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2;
+  WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2);
 SELECT * FROM t1
-  WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2;
+  WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2);
 
 DROP TABLE t1;
 
@@ -1266,10 +1266,10 @@ ANALYZE TABLE t1;
  
 EXPLAIN
 SELECT * FROM t1 FORCE KEY (state,capital) 
-WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 
+WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) 
    OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
 SELECT * FROM t1 FORCE KEY (state,capital) 
-WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 
+WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9)
    OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
 
 DROP TABLE t1;
diff --git a/mysql-test/main/range_vs_index_merge_innodb.result b/mysql-test/main/range_vs_index_merge_innodb.result
index 497aa3d94dc..4ac62e24940 100644
--- a/mysql-test/main/range_vs_index_merge_innodb.result
+++ b/mysql-test/main/range_vs_index_merge_innodb.result
@@ -1816,11 +1816,11 @@ CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX idx(b));
 INSERT INTO t1 VALUES (167,9999), (168,10000);
 EXPLAIN
 SELECT * FROM t1
-WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2;
+WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	PRIMARY,idx	idx	5	NULL	2	Using where; Using index
 SELECT * FROM t1
-WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2;
+WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2);
 a	b
 167	9999
 168	10000
@@ -1852,12 +1852,12 @@ test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
 EXPLAIN
 SELECT * FROM t1 FORCE KEY (state,capital) 
-WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 
+WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) 
 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	state,capital	state	71	NULL	8	Using index condition; Using where
 SELECT * FROM t1 FORCE KEY (state,capital) 
-WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 
+WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9)
 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
 id	state	capital
 4	Florida	Tallahassee
diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result
index 46723410b75..dc377aa5b6c 100644
--- a/mysql-test/main/selectivity_innodb.result
+++ b/mysql-test/main/selectivity_innodb.result
@@ -1443,7 +1443,7 @@ EXPLAIN EXTENDED
 SELECT * FROM t1, t2
 WHERE  a <> 'USARussian' AND b IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	range	PRIMARY,b	b	23	NULL	2	100.00	Using where; Using index
+1	SIMPLE	t1	ref	PRIMARY,b	b	5	const	1	100.00	Using where; Using index
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	14	100.00	Using join buffer (flat, BNL join)
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`i` AS `i` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` <> 'USARussian' and `test`.`t1`.`b` is null
diff --git a/mysql-test/main/subselect_mat_cost_bugs.result b/mysql-test/main/subselect_mat_cost_bugs.result
index 34c0ccab620..6a218ed5e04 100644
--- a/mysql-test/main/subselect_mat_cost_bugs.result
+++ b/mysql-test/main/subselect_mat_cost_bugs.result
@@ -189,8 +189,8 @@ SELECT alias2.f2 AS field1
 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1
 WHERE (
 SELECT t2.f2
-FROM t2 JOIN t1 ON t1.f1
-WHERE t1.f1 AND alias2.f10
+FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0)
+WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10
 )
 ORDER BY field1 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -203,8 +203,8 @@ SELECT alias2.f2 AS field1
 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1
 WHERE (
 SELECT t2.f2
-FROM t2 JOIN t1 ON t1.f1
-WHERE t1.f1 AND alias2.f10
+FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0)
+WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10
 )
 ORDER BY field1 ;
 field1
diff --git a/mysql-test/main/subselect_mat_cost_bugs.test b/mysql-test/main/subselect_mat_cost_bugs.test
index 028cdced560..ba1aad06a15 100644
--- a/mysql-test/main/subselect_mat_cost_bugs.test
+++ b/mysql-test/main/subselect_mat_cost_bugs.test
@@ -217,8 +217,8 @@ SELECT alias2.f2 AS field1
 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1
 WHERE (
         SELECT t2.f2
-        FROM t2 JOIN t1 ON t1.f1
-        WHERE t1.f1 AND alias2.f10
+        FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0)
+        WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10
 )
 ORDER BY field1 ;
 
@@ -226,8 +226,8 @@ SELECT alias2.f2 AS field1
 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1
 WHERE (
         SELECT t2.f2
-        FROM t2 JOIN t1 ON t1.f1
-        WHERE t1.f1 AND alias2.f10
+        FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0)
+        WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10
 )
 ORDER BY field1 ;
 
diff --git a/mysql-test/main/userstat.result b/mysql-test/main/userstat.result
index 6d9cd491de6..9152f602304 100644
--- a/mysql-test/main/userstat.result
+++ b/mysql-test/main/userstat.result
@@ -243,6 +243,8 @@ create function f() returns int return (select 1 from performance_schema.threads
 set global userstat= 1;
 select f() from information_schema.table_statistics;
 ERROR 21000: Subquery returns more than 1 row
+select f() from information_schema.index_statistics;
+ERROR 21000: Subquery returns more than 1 row
 set global userstat= 0;
 drop function f;
 #
diff --git a/mysql-test/main/userstat.test b/mysql-test/main/userstat.test
index cc7ddd58e11..6d486810db1 100644
--- a/mysql-test/main/userstat.test
+++ b/mysql-test/main/userstat.test
@@ -121,6 +121,8 @@ create function f() returns int return (select 1 from performance_schema.threads
 set global userstat= 1;
 --error ER_SUBQUERY_NO_1_ROW
 select f() from information_schema.table_statistics;
+--error ER_SUBQUERY_NO_1_ROW
+select f() from information_schema.index_statistics;
 set global userstat= 0;
 drop function f;