MDEV-23320 Hex hybrid constants 0xHHHH work badly in rounding functions

- Type_handler_hex_hybrid did not override
  Type_handler_string_result::Item_func_round_fix_length_and_dec(),
  so the result type of ROUND(0xFFFFFFFFFFFFFFFF) was erroneously
  calculated ad DOUBLE with a wrong length.
  Overriding Item_func_round_fix_length_and_dec(), to calculated
  the result type as INT/BIGINT.

  Also, fixing Item_func_round::fix_arg_int() to use
  args[0]->decimal_precision() instead of args[0]->max_length
  when calculating this->max_length, to get a correct result
  for hex hybrids.

- Type_handler_hex_hybrid::Item_func_int_val_fix_length_and_dec()
  called item->fix_length_and_dec_int_or_decimal(), which did not
  produce a correct result data type for hex hybrid.
  Implementing a dedicated code instead, to return INT UNSIGNED or
  BIGINT UNSIGNED depending in the number of digits in the arguments.

5 files changed, 208 insertions, 2 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 #
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 424437ead07..cf01934806c 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -2228,6 +2228,7 @@ bool Item_func_int_val::fix_length_and_dec()
 longlong Item_func_ceiling::int_op()
 {
   switch (args[0]->result_type()) {
+  case STRING_RESULT: // hex hybrid
   case INT_RESULT:
     return val_int_from_item(args[0]);
   case DECIMAL_RESULT:
@@ -2286,6 +2287,7 @@ bool Item_func_ceiling::time_op(THD *thd, MYSQL_TIME *to)
 longlong Item_func_floor::int_op()
 {
   switch (args[0]->result_type()) {
+  case STRING_RESULT: // hex hybrid
   case INT_RESULT:
     return val_int_from_item(args[0]);
   case DECIMAL_RESULT:
@@ -2452,7 +2454,7 @@ void Item_func_round::fix_arg_int()
     {
       // Length can increase in some cases: ROUND(9,-1) -> 10
       int length_can_increase= MY_TEST(!truncate && val1.neg());
-      max_length= args[0]->max_length + length_can_increase;
+      max_length= args[0]->decimal_precision() + length_can_increase;
       // Here we can keep INT_RESULT
       unsigned_flag= args[0]->unsigned_flag;
       decimals= 0;
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index 8b6d99de621..43b742604bc 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -5650,6 +5650,14 @@ bool Type_handler_int_result::
 }
 
 
+bool Type_handler_hex_hybrid::
+       Item_func_round_fix_length_and_dec(Item_func_round *item) const
+{
+  item->fix_arg_int();
+  return false;
+}
+
+
 bool Type_handler_real_result::
        Item_func_round_fix_length_and_dec(Item_func_round *item) const
 {
@@ -5743,7 +5751,14 @@ bool Type_handler_typelib::
 bool Type_handler_hex_hybrid::
        Item_func_int_val_fix_length_and_dec(Item_func_int_val *item) const
 {
-  item->fix_length_and_dec_int_or_decimal();
+  item->collation.set_numeric();
+  item->unsigned_flag= true;
+  item->max_length= item->arguments()[0]->decimal_precision();
+#if MARIADB_VERSION_ID < 100500
+  item->set_handler(type_handler_long_or_longlong(item->max_length));
+#else
+  item->set_handler(type_handler_long_or_longlong(item->max_length, true));
+#endif
   return false;
 }
 
diff --git a/sql/sql_type.h b/sql/sql_type.h
index efc8e94e454..f98f2349f78 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -6034,6 +6034,7 @@ public:
   const Name name() const { return m_name_hex_hybrid; }
   const Type_handler *cast_to_int_type_handler() const;
   const Type_handler *type_handler_for_system_time() const;
+  bool Item_func_round_fix_length_and_dec(Item_func_round *) const;
   bool Item_func_int_val_fix_length_and_dec(Item_func_int_val *) const;
 };