Merge mariadb-10.4.14

19 files changed, 518 insertions, 16 deletions

diff --git a/mysql-test/main/disabled.def b/mysql-test/main/disabled.def
index 0faaed4bef9..8ba5b271c2b 100644
--- a/mysql-test/main/disabled.def
+++ b/mysql-test/main/disabled.def
@@ -17,4 +17,3 @@ mysql_embedded           : Bug#12561297 2011-05-14 Anitha Dependent on PB2 chang
 file_contents            : MDEV-6526 these files are not installed anymore
 max_statement_time       : cannot possibly work, depends on timing
 partition_open_files_limit : open_files_limit check broken by MDEV-18360
-main.upgrade_MDEV-19650 : MDEV-23358 fails with result difference
diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result
index 9311a556191..57f5744373e 100644
--- a/mysql-test/main/func_group.result
+++ b/mysql-test/main/func_group.result
@@ -1186,13 +1186,13 @@ i	count(*)	std(e1/e2)
 3	4	0.00000000
 select std(s1/s2) from bug22555;
 std(s1/s2)
-0.21325764
+0.21328517
 select std(o1/o2) from bug22555;
 std(o1/o2)
 0.2132576358664934
 select std(e1/e2) from bug22555;
 std(e1/e2)
-0.21325764
+0.21328517
 set @saved_div_precision_increment=@@div_precision_increment;
 set div_precision_increment=19;
 select i, count(*), std(s1/s2) from bug22555 group by i order by i;
diff --git a/mysql-test/main/grant5.result b/mysql-test/main/grant5.result
index 2cc1c11f7d8..972131a24b7 100644
--- a/mysql-test/main/grant5.result
+++ b/mysql-test/main/grant5.result
@@ -25,6 +25,19 @@ ERROR HY000: Table 'procs_priv' was not locked with LOCK TABLES
 REVOKE PROCESS ON *.* FROM u;
 ERROR HY000: Table 'db' was not locked with LOCK TABLES
 DROP TABLE t1;
+create database mysqltest1;
+use mysqltest1;
+create table t1(id int);
+insert t1 values(2);
+create user u1@localhost;
+grant select on mysqltest1.t1 to u1@localhost;
+grant update on mysqltest1.* to u1@localhost;
+connect u1, localhost, u1;
+update mysqltest1.t1 set id=1 where id=2;
+connection default;
+disconnect u1;
+drop user u1@localhost;
+drop database mysqltest1;
 #
 # MDEV-20076: SHOW GRANTS does not quote role names properly
 #
diff --git a/mysql-test/main/grant5.test b/mysql-test/main/grant5.test
index 054b16c0a6e..e133108516e 100644
--- a/mysql-test/main/grant5.test
+++ b/mysql-test/main/grant5.test
@@ -34,6 +34,27 @@ REVOKE EXECUTE ON PROCEDURE sp FROM u;
 REVOKE PROCESS ON *.* FROM u;
 DROP TABLE t1;
 
+#
+# MDEV-23010 UPDATE privilege at Database and Table level fail to update with SELECT command denied to user
+#
+create database mysqltest1;
+use mysqltest1;
+create table t1(id int);
+insert t1 values(2);
+create user u1@localhost;
+grant select on mysqltest1.t1 to u1@localhost;
+grant update on mysqltest1.* to u1@localhost;
+connect u1, localhost, u1;
+update mysqltest1.t1 set id=1 where id=2;
+connection default;
+disconnect u1;
+drop user u1@localhost;
+drop database mysqltest1;
+
+#
+# End of 10.1 tests
+#
+
 --echo #
 --echo # MDEV-20076: SHOW GRANTS does not quote role names properly
 --echo #
diff --git a/mysql-test/main/lowercase_fs_off.result b/mysql-test/main/lowercase_fs_off.result
index 6ff8c1b7f93..3e9aa7cc239 100644
--- a/mysql-test/main/lowercase_fs_off.result
+++ b/mysql-test/main/lowercase_fs_off.result
@@ -158,3 +158,13 @@ show triggers like '%T1%';
 Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
 drop table t1;
 set GLOBAL sql_mode=default;
+#
+# MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode
+#
+# Compatibility schema names respect the filesystem case sensitivity
+CREATE TABLE t1 (a MARIADB_SCHEMA.date);
+ERROR HY000: Unknown data type: 'MARIADB_SCHEMA.date'
+CREATE TABLE t1 (a Mariadb_schema.date);
+ERROR HY000: Unknown data type: 'Mariadb_schema.date'
+CREATE TABLE t1 (a mariadb_schema.date);
+DROP TABLE t1;
diff --git a/mysql-test/main/lowercase_fs_off.test b/mysql-test/main/lowercase_fs_off.test
index f828773ed06..e8e8dafe584 100644
--- a/mysql-test/main/lowercase_fs_off.test
+++ b/mysql-test/main/lowercase_fs_off.test
@@ -130,3 +130,18 @@ let $datadir= `select @@datadir`;
 remove_file $datadir/mysql_upgrade_info;
 
 set GLOBAL sql_mode=default;
+
+
+--echo #
+--echo # MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode
+--echo #
+
+--echo # Compatibility schema names respect the filesystem case sensitivity
+
+--error ER_UNKNOWN_ERROR
+CREATE TABLE t1 (a MARIADB_SCHEMA.date);
+--error ER_UNKNOWN_ERROR
+CREATE TABLE t1 (a Mariadb_schema.date);
+
+CREATE TABLE t1 (a mariadb_schema.date);
+DROP TABLE t1;
diff --git a/mysql-test/main/parser_precedence.result b/mysql-test/main/parser_precedence.result
index 4330c8a2045..f23295bd61b 100644
--- a/mysql-test/main/parser_precedence.result
+++ b/mysql-test/main/parser_precedence.result
@@ -619,7 +619,7 @@ select 4 - 3 * 2, (4 - 3) * 2, 4 - (3 * 2);
 Testing that / is left associative
 select 15 / 5 / 3, (15 / 5) / 3, 15 / (5 / 3);
 15 / 5 / 3	(15 / 5) / 3	15 / (5 / 3)
-1.00000000	1.00000000	9.0000
+1.00000000	1.00000000	8.9998
 Testing that / has precedence over |
 select 105 / 5 | 2, (105 / 5) | 2, 105 / (5 | 2);
 105 / 5 | 2	(105 / 5) | 2	105 / (5 | 2)
diff --git a/mysql-test/main/partition.result b/mysql-test/main/partition.result
index ea12158862e..0fe60807308 100644
--- a/mysql-test/main/partition.result
+++ b/mysql-test/main/partition.result
@@ -2763,5 +2763,45 @@ SELECT 1 FROM t1 WHERE a XOR 'a';
 1
 DROP TABLE t1;
 #
+# Bug #25207522: INCORRECT ORDER-BY BEHAVIOR ON A PARTITIONED TABLE
+# WITH A COMPOSITE PREFIX INDEX
+#
+create table t1(id int unsigned not null,
+data varchar(2) default null,
+key data_idx (data(1),id)
+) default charset=utf8
+partition by range (id) (
+partition p10 values less than (10),
+partition p20 values less than (20)
+);
+insert t1 values (6, 'ab'), (4, 'ab'), (5, 'ab'), (16, 'ab'), (14, 'ab'), (15, 'ab'), (5, 'ac'), (15, 'aa') ;
+select id from t1 where data = 'ab' order by id;
+id
+4
+5
+6
+14
+15
+16
+drop table t1;
+create table t1(id int unsigned not null,
+data text default null,
+key data_idx (data(1),id)
+) default charset=utf8
+partition by range (id) (
+partition p10 values less than (10),
+partition p20 values less than (20)
+);
+insert t1 values (6, 'ab'), (4, 'ab'), (5, 'ab'), (16, 'ab'), (14, 'ab'), (15, 'ab'), (5, 'ac'), (15, 'aa') ;
+select id from t1 where data = 'ab' order by id;
+id
+4
+5
+6
+14
+15
+16
+drop table t1;
+#
 # End of 10.1 tests
 #
diff --git a/mysql-test/main/partition.test b/mysql-test/main/partition.test
index 89db3e92162..0ac91611e43 100644
--- a/mysql-test/main/partition.test
+++ b/mysql-test/main/partition.test
@@ -2974,5 +2974,33 @@ SELECT 1 FROM t1 WHERE a XOR 'a';
 DROP TABLE t1;
 
 --echo #
+--echo # Bug #25207522: INCORRECT ORDER-BY BEHAVIOR ON A PARTITIONED TABLE
+--echo # WITH A COMPOSITE PREFIX INDEX
+--echo #
+create table t1(id int unsigned not null,
+  data varchar(2) default null,
+  key data_idx (data(1),id)
+) default charset=utf8
+partition by range (id) (
+  partition p10 values less than (10),
+  partition p20 values less than (20)
+);
+insert t1 values (6, 'ab'), (4, 'ab'), (5, 'ab'), (16, 'ab'), (14, 'ab'), (15, 'ab'), (5, 'ac'), (15, 'aa') ;
+select id from t1 where data = 'ab' order by id;
+drop table t1;
+
+create table t1(id int unsigned not null,
+  data text default null,
+  key data_idx (data(1),id)
+) default charset=utf8
+partition by range (id) (
+  partition p10 values less than (10),
+  partition p20 values less than (20)
+);
+insert t1 values (6, 'ab'), (4, 'ab'), (5, 'ab'), (16, 'ab'), (14, 'ab'), (15, 'ab'), (5, 'ac'), (15, 'aa') ;
+select id from t1 where data = 'ab' order by id;
+drop table t1;
+
+--echo #
 --echo # End of 10.1 tests
 --echo #
diff --git a/mysql-test/main/stat_tables_partition.result b/mysql-test/main/stat_tables_partition.result
index 12ae2570272..2619026b231 100644
--- a/mysql-test/main/stat_tables_partition.result
+++ b/mysql-test/main/stat_tables_partition.result
@@ -9,5 +9,38 @@ ANALYZE TABLE t1;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
-SET use_stat_tables = DEFAULT;
 DROP TABLE t1;
+#
+# MDEV-21472: ALTER TABLE ... ANALYZE PARTITION ... with EITS reads and locks all rows
+#
+CREATE TABLE t1 (
+id int(11) auto_increment primary key,
+c1 int(11) DEFAULT NULL
+) PARTITION BY RANGE (id) (
+PARTITION p0 VALUES LESS THAN (4),
+PARTITION p1 VALUES LESS THAN MAXVALUE
+);
+insert into t1(c1) values (1),(1),(1),(1),  (1),(1),(1),(1);
+insert into t1(c1) select c1 from t1;
+insert into t1(c1) select c1 from t1;
+select count(*) from t1;
+count(*)
+32
+select count(*) from t1 where id <4;
+count(*)
+3
+flush status;
+set session use_stat_tables='preferably';
+# Must NOT show "Engine-independent statistics collected":
+alter table t1 analyze partition p0;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+# Should not have Handler_read_rnd_next=34
+show session status like 'Handler_read_rnd%';
+Variable_name	Value
+Handler_read_rnd	0
+Handler_read_rnd_deleted	0
+Handler_read_rnd_next	34
+drop table t1;
+SET use_stat_tables = DEFAULT;
diff --git a/mysql-test/main/stat_tables_partition.test b/mysql-test/main/stat_tables_partition.test
index 1316e5cca11..11b74818d82 100644
--- a/mysql-test/main/stat_tables_partition.test
+++ b/mysql-test/main/stat_tables_partition.test
@@ -11,7 +11,33 @@ CREATE TABLE t1 (pk int PRIMARY KEY, a bit(1), INDEX idx(a)
 INSERT INTO t1 VALUES (1,1),(2,0),(3,0),(4,1);
 
 ANALYZE TABLE t1;
+DROP TABLE t1;
 
-SET use_stat_tables = DEFAULT;
+--echo #
+--echo # MDEV-21472: ALTER TABLE ... ANALYZE PARTITION ... with EITS reads and locks all rows
+--echo #
+CREATE TABLE t1 (
+  id int(11) auto_increment primary key,
+  c1 int(11) DEFAULT NULL
+) PARTITION BY RANGE (id) (
+  PARTITION p0 VALUES LESS THAN (4),
+  PARTITION p1 VALUES LESS THAN MAXVALUE
+);
 
-DROP TABLE t1;
+insert into t1(c1) values (1),(1),(1),(1),  (1),(1),(1),(1);
+insert into t1(c1) select c1 from t1;
+insert into t1(c1) select c1 from t1;
+
+select count(*) from t1;
+select count(*) from t1 where id <4;
+flush status;
+set session use_stat_tables='preferably';
+
+--echo # Must NOT show "Engine-independent statistics collected":
+alter table t1 analyze partition p0;
+
+--echo # Should not have Handler_read_rnd_next=34
+show session status like 'Handler_read_rnd%';
+drop table t1;
+
+SET use_stat_tables = DEFAULT;
diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result
index 34a484f3b18..8c8471512cf 100644
--- a/mysql-test/main/subselect4.result
+++ b/mysql-test/main/subselect4.result
@@ -2585,6 +2585,30 @@ e	2
 o	6
 DROP TABLE t1, t2;
 #
+# MDEV-19232: Floating point precision / value comparison problem
+#
+CREATE TABLE t1 (region varchar(60), area decimal(10,0), population decimal(11,0));
+INSERT INTO t1 VALUES ('Central America and the Caribbean',91,11797);
+INSERT INTO t1 VALUES ('Central America and the Caribbean',442,66422);
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='subquery_cache=on';
+SELECT
+population, area, population/area,
+cast(population/area as DECIMAL(20,9)) FROM t1 LIMIT 1;
+population	area	population/area	cast(population/area as DECIMAL(20,9))
+11797	91	129.6374	129.637400000
+SELECT * FROM t1 A
+WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region);
+region	area	population
+Central America and the Caribbean	442	66422
+SET optimizer_switch='subquery_cache=off';
+SELECT * FROM t1 A
+WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region);
+region	area	population
+Central America and the Caribbean	442	66422
+SET @@optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1;
+#
 # MDEV-22852: SIGSEGV in sortlength (optimized builds)
 #
 SET @save_optimizer_switch=@@optimizer_switch;
diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test
index 36490c340e1..1ed79de4598 100644
--- a/mysql-test/main/subselect4.test
+++ b/mysql-test/main/subselect4.test
@@ -2116,6 +2116,32 @@ SELECT * FROM t1 where  ( t1.l1 < ANY (SELECT MAX(t2.v1) FROM t2));
 DROP TABLE t1, t2;
 
 --echo #
+--echo # MDEV-19232: Floating point precision / value comparison problem
+--echo #
+
+CREATE TABLE t1 (region varchar(60), area decimal(10,0), population decimal(11,0));
+INSERT INTO t1 VALUES ('Central America and the Caribbean',91,11797);
+INSERT INTO t1 VALUES ('Central America and the Caribbean',442,66422);
+
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='subquery_cache=on';
+
+SELECT
+population, area, population/area,
+cast(population/area as DECIMAL(20,9)) FROM t1 LIMIT 1;
+
+SELECT * FROM t1 A
+WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region);
+
+SET optimizer_switch='subquery_cache=off';
+SELECT * FROM t1 A
+WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region);
+
+SET @@optimizer_switch= @save_optimizer_switch;
+
+DROP TABLE t1;
+
+--echo #
 --echo # MDEV-22852: SIGSEGV in sortlength (optimized builds)
 --echo #
 
diff --git a/mysql-test/main/subselect_exists2in.result b/mysql-test/main/subselect_exists2in.result
index 576559c2e86..e8ef7081b09 100644
--- a/mysql-test/main/subselect_exists2in.result
+++ b/mysql-test/main/subselect_exists2in.result
@@ -975,4 +975,131 @@ id
 DROP PROCEDURE p1;
 DROP TABLE t1;
 # End of 10.0 tests
+#
+# MDEV-23221: A subquery causes crash
+#
+create table t1 (
+location_code varchar(10),
+country_id varchar(10)
+);
+insert into t1 values ('HKG', 'HK');
+insert into t1 values ('NYC', 'US');
+insert into t1 values ('LAX', 'US');
+create table t2 (
+container_id varchar(10),
+cntr_activity_type varchar(10),
+cntr_dest varchar(10)
+);
+insert into t2 values ('AAAA1111', 'VSL', 'NYC');
+insert into t2 values ('AAAA1111', 'CUV', 'NYC');
+insert into t2 values ('BBBB2222', 'VSL', 'LAX');
+insert into t2 values ('BBBB2222', 'XYZ', 'LAX');
+# Must not crash or return an error:
+select
+(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry,
+(select
+max(container_id)
+from t2 as cl2
+where
+cl2.container_id = cl1.container_id and
+cl2.cntr_activity_type = 'CUV' and
+exists (select location_code
+from t1
+where
+location_code = cl2.cntr_dest and
+country_id = dest_cntry)
+) as CUV
+from
+t2 cl1;
+dest_cntry	CUV
+US	AAAA1111
+US	AAAA1111
+US	NULL
+US	NULL
+prepare s from "select
+(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry,
+(select
+max(container_id)
+from t2 as cl2
+where
+cl2.container_id = cl1.container_id and
+cl2.cntr_activity_type = 'CUV' and
+exists (select location_code
+from t1
+where
+location_code = cl2.cntr_dest and
+country_id = dest_cntry)
+) as CUV
+from
+t2 cl1";
+execute s;
+dest_cntry	CUV
+US	AAAA1111
+US	AAAA1111
+US	NULL
+US	NULL
+execute s;
+dest_cntry	CUV
+US	AAAA1111
+US	AAAA1111
+US	NULL
+US	NULL
+drop table t1,t2;
+# 
+# MDEV-20557: SQL query with duplicate table aliases consistently crashes server
+# (Just a testcase)
+#
+create table t1 (id int, id2 int);
+create table t2 (id int, id2 int, a int);
+create table t3 (id int);
+create table t4 (id int);
+select (select 1 from t1 where (exists 
+(select 1 from t2  
+where t2.a = (select t4.id from t4 where t4.id = t3.id) and t2.id2 = t1.id2))) dt
+from t3;
+ERROR 42000: This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY'
+drop table t1,t2,t3,t4;
+# 
+# MDEV-21649: Crash when using nested EXISTS
+# (Just a testcase)
+# 
+CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id));
+CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT, ip_id INT, PRIMARY KEY(id));
+CREATE TABLE t3 (id INT NOT NULL AUTO_INCREMENT, storage_method_id INT, storage_target_id INT, PRIMARY KEY(id));
+SELECT
+W0.`id`
+FROM
+`t1` W0
+WHERE (
+EXISTS(
+SELECT
+V0.`id`
+        FROM
+`t2` V0
+WHERE (
+EXISTS(
+SELECT
+U0.`id`
+                FROM
+`t2` U0
+INNER JOIN `t3` U4 ON (U0.`id` = U4.`storage_target_id`)
+WHERE (
+U0.`ip_id` = V0.`ip_id`
+                        AND U4.`storage_method_id` = (
+SELECT
+U5.`storage_method_id`
+                            FROM
+`t3` U5
+WHERE
+U5.`storage_target_id` = V0.`id`
+                            LIMIT
+1
+)
+)
+)
+)
+)
+);
+id
+drop table t1,t2,t3;
 set optimizer_switch=default;
diff --git a/mysql-test/main/subselect_exists2in.test b/mysql-test/main/subselect_exists2in.test
index 2a9947123d4..e27ce57038b 100644
--- a/mysql-test/main/subselect_exists2in.test
+++ b/mysql-test/main/subselect_exists2in.test
@@ -829,5 +829,117 @@ DROP TABLE t1;
 
 --echo # End of 10.0 tests
 
+--echo #
+--echo # MDEV-23221: A subquery causes crash
+--echo #
+create table t1 (
+location_code varchar(10),
+country_id varchar(10)
+);
+insert into t1 values ('HKG', 'HK');
+insert into t1 values ('NYC', 'US');
+insert into t1 values ('LAX', 'US');
+
+create table t2 (
+container_id varchar(10),
+cntr_activity_type varchar(10),
+cntr_dest varchar(10)
+);
+insert into t2 values ('AAAA1111', 'VSL', 'NYC');
+insert into t2 values ('AAAA1111', 'CUV', 'NYC');
+insert into t2 values ('BBBB2222', 'VSL', 'LAX');
+insert into t2 values ('BBBB2222', 'XYZ', 'LAX');
+
+let $query=
+select
+  (select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry,
+  (select
+     max(container_id)
+   from t2 as cl2
+   where
+    cl2.container_id = cl1.container_id and
+    cl2.cntr_activity_type = 'CUV' and
+    exists (select location_code
+            from t1
+            where
+               location_code = cl2.cntr_dest and
+               country_id = dest_cntry)
+   ) as CUV
+from
+  t2 cl1;
+
+--echo # Must not crash or return an error:
+eval $query;
+
+eval prepare s from "$query";
+execute s;
+execute s;
+
+drop table t1,t2;
+
+--echo # 
+--echo # MDEV-20557: SQL query with duplicate table aliases consistently crashes server
+--echo # (Just a testcase)
+--echo #
+
+create table t1 (id int, id2 int);
+create table t2 (id int, id2 int, a int);
+create table t3 (id int);
+create table t4 (id int);
+
+--error ER_NOT_SUPPORTED_YET
+select (select 1 from t1 where (exists 
+	(select 1 from t2  
+	where t2.a = (select t4.id from t4 where t4.id = t3.id) and t2.id2 = t1.id2))) dt
+from t3;
+ 
+drop table t1,t2,t3,t4;
+
+
+--echo # 
+--echo # MDEV-21649: Crash when using nested EXISTS
+--echo # (Just a testcase)
+--echo # 
+CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id));
+CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT, ip_id INT, PRIMARY KEY(id));
+CREATE TABLE t3 (id INT NOT NULL AUTO_INCREMENT, storage_method_id INT, storage_target_id INT, PRIMARY KEY(id));
+ 
+SELECT
+    W0.`id`
+FROM
+    `t1` W0
+WHERE (
+    EXISTS(
+        SELECT
+            V0.`id`
+        FROM
+            `t2` V0
+        WHERE (
+            EXISTS(
+                SELECT
+                    U0.`id`
+                FROM
+                    `t2` U0
+                    INNER JOIN `t3` U4 ON (U0.`id` = U4.`storage_target_id`)
+                WHERE (
+                        U0.`ip_id` = V0.`ip_id`
+                        AND U4.`storage_method_id` = (
+                            SELECT
+                                U5.`storage_method_id`
+                            FROM
+                                `t3` U5
+                            WHERE
+                                U5.`storage_target_id` = V0.`id`
+                            LIMIT
+                                1
+                        )
+                )
+            )
+        )
+    )
+);
+
+drop table t1,t2,t3;
+
 #restore defaults
 set optimizer_switch=default;
diff --git a/mysql-test/main/type_float.result b/mysql-test/main/type_float.result
index 57fba0e8d40..5137a8229b6 100644
--- a/mysql-test/main/type_float.result
+++ b/mysql-test/main/type_float.result
@@ -676,6 +676,27 @@ Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2010e0
 DROP TABLE t1;
 #
+# MDEV-23282 FLOAT(53,0) badly handles out-of-range values
+#
+CREATE OR REPLACE TABLE t1 (c1 FLOAT NOT NULL, c2 FLOAT NOT NULL);
+INSERT IGNORE INTO t1 VALUES (1e+40, -1e+40);
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+Warning	1264	Out of range value for column 'c2' at row 1
+SELECT c1, c2 FROM t1;
+c1	c2
+3.40282e38	-3.40282e38
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (c1 FLOAT(53,0) NOT NULL, c2 FLOAT(53,0) NOT NULL);
+INSERT IGNORE INTO t1 VALUES (1e+40, -1e+40);
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+Warning	1264	Out of range value for column 'c2' at row 1
+SELECT c1, c2 FROM t1;
+c1	c2
+340282346638528860000000000000000000000	-340282346638528860000000000000000000000
+DROP TABLE t1;
+#
 # End of 10.1 tests
 #
 #
diff --git a/mysql-test/main/type_float.test b/mysql-test/main/type_float.test
index 1c17b6345f3..a12bf67a426 100644
--- a/mysql-test/main/type_float.test
+++ b/mysql-test/main/type_float.test
@@ -486,6 +486,20 @@ DROP TABLE t1;
 
 
 --echo #
+--echo # MDEV-23282 FLOAT(53,0) badly handles out-of-range values
+--echo #
+
+CREATE OR REPLACE TABLE t1 (c1 FLOAT NOT NULL, c2 FLOAT NOT NULL);
+INSERT IGNORE INTO t1 VALUES (1e+40, -1e+40);
+SELECT c1, c2 FROM t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (c1 FLOAT(53,0) NOT NULL, c2 FLOAT(53,0) NOT NULL);
+INSERT IGNORE INTO t1 VALUES (1e+40, -1e+40);
+SELECT c1, c2 FROM t1;
+DROP TABLE t1;
+
+--echo #
 --echo # End of 10.1 tests
 --echo #
 
diff --git a/mysql-test/main/type_newdecimal.result b/mysql-test/main/type_newdecimal.result
index dd55d230450..cbe7b84277d 100644
--- a/mysql-test/main/type_newdecimal.result
+++ b/mysql-test/main/type_newdecimal.result
@@ -1532,11 +1532,8 @@ select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 *
 1.01500000 * 1.01500000 * 0.99500000)
 0.81298807395367312459230693948000000000
 create table t1 as select 5.05 / 0.014;
-Warnings:
-Note	1265	Data truncated for column '5.05 / 0.014' at row 1
 show warnings;
 Level	Code	Message
-Note	1265	Data truncated for column '5.05 / 0.014' at row 1
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -1651,8 +1648,6 @@ my_col
 0.12345678912345678912345678912345678912
 DROP TABLE t1;
 CREATE TABLE t1 SELECT 1 / .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col;
-Warnings:
-Note	1265	Data truncated for column 'my_col' at row 1
 DESCRIBE t1;
 Field	Type	Null	Key	Default	Extra
 my_col	decimal(65,4)	YES		NULL	
diff --git a/mysql-test/main/type_ranges.result b/mysql-test/main/type_ranges.result
index 784a394d8b5..199c8a45c97 100644
--- a/mysql-test/main/type_ranges.result
+++ b/mysql-test/main/type_ranges.result
@@ -91,8 +91,6 @@ DROP INDEX test ON t1;
 insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,0,0,0,1,1,1,1,'one','one');
 insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,NULL,2,2,'two','two,one');
 insert ignore into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','10:10:10','19970303101010','','','','3',3,3);
-Warnings:
-Warning	1265	Data truncated for column 'string' at row 1
 insert ignore into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,080706,19970403090807,-1,-1,-1,'-1',-1,-1);
 Warnings:
 Warning	1264	Out of range value for column 'utiny' at row 1
@@ -130,7 +128,7 @@ select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,ut
 auto	string	tiny	short	medium	long_int	longlong	real_float	real_double	utiny	ushort	umedium	ulong	ulonglong	mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000)	date_field	time_field	date_time	blob_col	tinyblob_col	mediumblob_col	longblob_col
 10	1	1	1	1	1	1	1.0	1.0000	1	00001	1	1	1	0	0000-00-00	00:00:00	0000-00-00 00:00:00	1	1	1	1
 11	2	2	2	2	2	2	2.0	2.0000	2	00002	2	2	2	0	NULL	NULL	NULL	NULL	NULL	2	2
-12	0.33333333	3	3	3	3	3	3.0	3.0000	3	00003	3	3	3	0	1997-03-03	10:10:10	1997-03-03 10:10:10				3
+12	0.3333	3	3	3	3	3	3.0	3.0000	3	00003	3	3	3	0	1997-03-03	10:10:10	1997-03-03 10:10:10				3
 13	-1	-1	-1	-1	-1	-1	-1.0	-1.0000	0	00000	0	0	0	0	1997-08-07	08:07:06	1997-04-03 09:08:07	-1	-1	-1	-1
 14	-429496729	-128	-32768	-8388608	-2147483648	-4294967295	-4294967296.0	-4294967295.0000	0	00000	0	0	0	0	0000-00-00	00:00:00	0000-00-00 00:00:00	-4294967295	-4294967295	-4294967295	-4294967295
 15	4294967295	127	32767	8388607	2147483647	4294967295	4294967296.0	4294967295.0000	255	65535	16777215	4294967295	4294967295	0	0000-00-00	00:00:00	0000-00-00 00:00:00	4294967295	4294967295	4294967295	4294967295
@@ -182,7 +180,7 @@ Warning	1265	Data truncated for column 'new_field' at row 7
 select * from t2;
 auto	string	mediumblob_col	new_field
 1	2	2	ne
-2	0.33333333		ne
+2	0.3333		ne
 3	-1	-1	ne
 4	-429496729	-4294967295	ne
 5	4294967295	4294967295	ne