Merge branch '10.2' into 10.3

13 files changed, 401 insertions, 7 deletions

diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result
index a6bf31d1f35..686038521f8 100644
--- a/mysql-test/main/innodb_ext_key.result
+++ b/mysql-test/main/innodb_ext_key.result
@@ -790,3 +790,19 @@ 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;
diff --git a/mysql-test/main/innodb_ext_key.test b/mysql-test/main/innodb_ext_key.test
index f5edd736490..de9ca10ff1b 100644
--- a/mysql-test/main/innodb_ext_key.test
+++ b/mysql-test/main/innodb_ext_key.test
@@ -606,3 +606,22 @@ explain format= json
 select * from t1 force index(k1)  where f2 <= 5 and pk2 <=5 and pk1 = 'abc' and f1 <= '3';
 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;
diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result
index 696a8d23d94..d51414d8f54 100644
--- a/mysql-test/main/ps.result
+++ b/mysql-test/main/ps.result
@@ -5467,5 +5467,17 @@ 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 1d7f990f81c..3228181d922 100644
--- a/mysql-test/main/ps.test
+++ b/mysql-test/main/ps.test
@@ -4940,5 +4940,15 @@ 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 b446c3591b9..4a39726fb91 100644
--- a/mysql-test/main/selectivity.result
+++ b/mysql-test/main/selectivity.result
@@ -1887,4 +1887,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 a31573edb8e..4d7dcdc9781 100644
--- a/mysql-test/main/selectivity.test
+++ b/mysql-test/main/selectivity.test
@@ -1286,6 +1286,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 9cf65d1f950..34951c858c8 100644
--- a/mysql-test/main/selectivity_innodb.result
+++ b/mysql-test/main/selectivity_innodb.result
@@ -1897,6 +1897,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 23b6e268d91..b89ed3d5ddd 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 f264b3857ff..cdea43a1aa1 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/type_decimal.result b/mysql-test/main/type_decimal.result
index d87ab0b38a3..03d6a6fa331 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 71c7c7bcd5b..05c60a48eec 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 6b964d28ad0..4e4403dc8ed 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -6762,6 +6762,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 948ceca7561..f8fac507775 100644
--- a/mysql-test/main/view.test
+++ b/mysql-test/main/view.test
@@ -6484,6 +6484,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 #