2 files changed, 188 insertions, 0 deletions
diff --git a/mysql-test/main/type_hex_hybrid.result b/mysql-test/main/type_hex_hybrid.result
index eec24f6e89e..92a7432ae56 100644
--- a/mysql-test/main/type_hex_hybrid.result
+++ b/mysql-test/main/type_hex_hybrid.result
@@ -20,5 +20,163 @@ Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where <cache>(octet_length(charset('a'))) + `test`.`t1`.`a` <=> <cache>(octet_length(charset(0x61))) + `test`.`t1`.`a`
 DROP TABLE t1;
 #
+# MDEV-23320 Hex hybrid constants 0xHHHH work badly in rounding functions
+#
+BEGIN NOT ATOMIC
+DECLARE arg TEXT DEFAULT '';
+DECLARE query TEXT DEFAULT
+'CREATE TABLE t1 AS SELECT '
+    '0xFFFFFFFFFFFFFFFF+0 AS c1,'
+    'FLOOR(0xFFFFFFFFFFFFFFFF) AS c2,'
+    'CEILING(0xFFFFFFFFFFFFFFFF) AS c3,'
+    'ROUND(0xFFFFFFFFFFFFFFFF) AS c4,'
+    'TRUNCATE(0xFFFFFFFFFFFFFFFF,0) AS c5';
+FOR i IN 1..9
+DO
+SET arg= CONCAT('0x',REPEAT('FF',i));
+SELECT i, arg;
+EXECUTE IMMEDIATE REPLACE(query,'0xFFFFFFFFFFFFFFFF', arg);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+END FOR;
+END;
+$$
+i	1
+arg	0xFF
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `c1` int(4) unsigned NOT NULL,
+  `c2` int(3) unsigned NOT NULL,
+  `c3` int(3) unsigned NOT NULL,
+  `c4` int(3) unsigned NOT NULL,
+  `c5` int(3) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+c1	255
+c2	255
+c3	255
+c4	255
+c5	255
+i	2
+arg	0xFFFF
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `c1` int(6) unsigned NOT NULL,
+  `c2` int(5) unsigned NOT NULL,
+  `c3` int(5) unsigned NOT NULL,
+  `c4` int(5) unsigned NOT NULL,
+  `c5` int(5) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+c1	65535
+c2	65535
+c3	65535
+c4	65535
+c5	65535
+i	3
+arg	0xFFFFFF
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `c1` int(9) unsigned NOT NULL,
+  `c2` int(8) unsigned NOT NULL,
+  `c3` int(8) unsigned NOT NULL,
+  `c4` int(8) unsigned NOT NULL,
+  `c5` int(8) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+c1	16777215
+c2	16777215
+c3	16777215
+c4	16777215
+c5	16777215
+i	4
+arg	0xFFFFFFFF
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `c1` bigint(11) unsigned NOT NULL,
+  `c2` bigint(10) unsigned NOT NULL,
+  `c3` bigint(10) unsigned NOT NULL,
+  `c4` bigint(10) unsigned NOT NULL,
+  `c5` bigint(10) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+c1	4294967295
+c2	4294967295
+c3	4294967295
+c4	4294967295
+c5	4294967295
+i	5
+arg	0xFFFFFFFFFF
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `c1` bigint(14) unsigned NOT NULL,
+  `c2` bigint(13) unsigned NOT NULL,
+  `c3` bigint(13) unsigned NOT NULL,
+  `c4` bigint(13) unsigned NOT NULL,
+  `c5` bigint(13) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+c1	1099511627775
+c2	1099511627775
+c3	1099511627775
+c4	1099511627775
+c5	1099511627775
+i	6
+arg	0xFFFFFFFFFFFF
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `c1` bigint(16) unsigned NOT NULL,
+  `c2` bigint(15) unsigned NOT NULL,
+  `c3` bigint(15) unsigned NOT NULL,
+  `c4` bigint(15) unsigned NOT NULL,
+  `c5` bigint(15) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+c1	281474976710655
+c2	281474976710655
+c3	281474976710655
+c4	281474976710655
+c5	281474976710655
+i	7
+arg	0xFFFFFFFFFFFFFF
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `c1` bigint(18) unsigned NOT NULL,
+  `c2` bigint(17) unsigned NOT NULL,
+  `c3` bigint(17) unsigned NOT NULL,
+  `c4` bigint(17) unsigned NOT NULL,
+  `c5` bigint(17) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+c1	72057594037927935
+c2	72057594037927935
+c3	72057594037927935
+c4	72057594037927935
+c5	72057594037927935
+i	8
+arg	0xFFFFFFFFFFFFFFFF
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `c1` bigint(21) unsigned NOT NULL,
+  `c2` bigint(20) unsigned NOT NULL,
+  `c3` bigint(20) unsigned NOT NULL,
+  `c4` bigint(20) unsigned NOT NULL,
+  `c5` bigint(20) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+c1	18446744073709551615
+c2	18446744073709551615
+c3	18446744073709551615
+c4	18446744073709551615
+c5	18446744073709551615
+i	9
+arg	0xFFFFFFFFFFFFFFFFFF
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `c1` bigint(21) unsigned NOT NULL,
+  `c2` bigint(20) unsigned NOT NULL,
+  `c3` bigint(20) unsigned NOT NULL,
+  `c4` bigint(20) unsigned NOT NULL,
+  `c5` bigint(20) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+c1	18446744073709551615
+c2	18446744073709551615
+c3	18446744073709551615
+c4	18446744073709551615
+c5	18446744073709551615
+#
 # End of 10.4 tests
 #
diff --git a/mysql-test/main/type_hex_hybrid.test b/mysql-test/main/type_hex_hybrid.test
index a39750e2635..77afb45cfbf 100644
--- a/mysql-test/main/type_hex_hybrid.test
+++ b/mysql-test/main/type_hex_hybrid.test
@@ -17,5 +17,35 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(CHARSET('a'))+a<=>LENGTH(CHARSET(
 DROP TABLE t1;
 
 --echo #
+--echo # MDEV-23320 Hex hybrid constants 0xHHHH work badly in rounding functions
+--echo #
+
+--vertical_results
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE arg TEXT DEFAULT '';
+  DECLARE query TEXT DEFAULT
+    'CREATE TABLE t1 AS SELECT '
+    '0xFFFFFFFFFFFFFFFF+0 AS c1,'
+    'FLOOR(0xFFFFFFFFFFFFFFFF) AS c2,'
+    'CEILING(0xFFFFFFFFFFFFFFFF) AS c3,'
+    'ROUND(0xFFFFFFFFFFFFFFFF) AS c4,'
+    'TRUNCATE(0xFFFFFFFFFFFFFFFF,0) AS c5';
+  FOR i IN 1..9
+  DO
+    SET arg= CONCAT('0x',REPEAT('FF',i));
+    SELECT i, arg;
+    EXECUTE IMMEDIATE REPLACE(query,'0xFFFFFFFFFFFFFFFF', arg);
+    SHOW CREATE TABLE t1;
+    SELECT * FROM t1;
+    DROP TABLE t1;
+  END FOR;
+END;
+$$
+DELIMITER ;$$
+--horizontal_results
+
+
+--echo #
 --echo # End of 10.4 tests
 --echo #