MDEV-23368 ROUND(18446744073709551615,-11) returns a wrong result

Item_func_round::fix_arg_int() did not take into account cases
when the result of ROUND(bigint_subject,negative_precision)
could go outside of the BIGINT range. The old code only incremented
max_length, but did not extend change the data type.
Fixing to extend the data type (together with max_length increment).

7 files changed, 73 insertions, 8 deletions

diff --git a/mysql-test/main/type_hex_hybrid.result b/mysql-test/main/type_hex_hybrid.result
index 92a7432ae56..91490375e7e 100644
--- a/mysql-test/main/type_hex_hybrid.result
+++ b/mysql-test/main/type_hex_hybrid.result
@@ -178,5 +178,22 @@ c3	18446744073709551615
 c4	18446744073709551615
 c5	18446744073709551615
 #
+# MDEV-23368 ROUND(18446744073709551615,-11) returns a wrong result
+#
+SELECT ROUND(0xFFFFFFFFFFFFFFFF,-10), ROUND(0xFFFFFFFFFFFFFFFF,-11);
+ROUND(0xFFFFFFFFFFFFFFFF,-10)	ROUND(0xFFFFFFFFFFFFFFFF,-11)
+18446744070000000000	18446744100000000000
+CREATE TABLE t1 AS SELECT ROUND(0xFFFFFFFFFFFFFFFF,-10), ROUND(0xFFFFFFFFFFFFFFFF,-11);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `ROUND(0xFFFFFFFFFFFFFFFF,-10)` decimal(21,0) unsigned NOT NULL,
+  `ROUND(0xFFFFFFFFFFFFFFFF,-11)` decimal(21,0) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+ROUND(0xFFFFFFFFFFFFFFFF,-10)	ROUND(0xFFFFFFFFFFFFFFFF,-11)
+18446744070000000000	18446744100000000000
+DROP TABLE t1;
+#
 # End of 10.4 tests
 #
diff --git a/mysql-test/main/type_hex_hybrid.test b/mysql-test/main/type_hex_hybrid.test
index 77afb45cfbf..62d1a8ace91 100644
--- a/mysql-test/main/type_hex_hybrid.test
+++ b/mysql-test/main/type_hex_hybrid.test
@@ -47,5 +47,16 @@ DELIMITER ;$$
 
 
 --echo #
+--echo # MDEV-23368 ROUND(18446744073709551615,-11) returns a wrong result
+--echo #
+
+SELECT ROUND(0xFFFFFFFFFFFFFFFF,-10), ROUND(0xFFFFFFFFFFFFFFFF,-11);
+CREATE TABLE t1 AS SELECT ROUND(0xFFFFFFFFFFFFFFFF,-10), ROUND(0xFFFFFFFFFFFFFFFF,-11);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+--echo #
 --echo # End of 10.4 tests
 --echo #
diff --git a/mysql-test/main/type_int.result b/mysql-test/main/type_int.result
index 95c27c0b8d8..c52ad826f3c 100644
--- a/mysql-test/main/type_int.result
+++ b/mysql-test/main/type_int.result
@@ -949,5 +949,22 @@ FLOOR(a)	18446744073709551615
 CEILING(a)	18446744073709551615
 DROP PROCEDURE p1;
 #
+# MDEV-23368 ROUND(18446744073709551615,-11) returns a wrong result
+#
+SELECT ROUND(18446744073709551615,-10), ROUND(18446744073709551615,-11);
+ROUND(18446744073709551615,-10)	ROUND(18446744073709551615,-11)
+18446744070000000000	18446744100000000000
+CREATE TABLE t1 AS SELECT ROUND(18446744073709551615,-10), ROUND(18446744073709551615,-11);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `ROUND(18446744073709551615,-10)` decimal(21,0) unsigned NOT NULL,
+  `ROUND(18446744073709551615,-11)` decimal(21,0) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+ROUND(18446744073709551615,-10)	ROUND(18446744073709551615,-11)
+18446744070000000000	18446744100000000000
+DROP TABLE t1;
+#
 # End of 10.4 tests
 #
diff --git a/mysql-test/main/type_int.test b/mysql-test/main/type_int.test
index e54726b8720..6191a72fc25 100644
--- a/mysql-test/main/type_int.test
+++ b/mysql-test/main/type_int.test
@@ -340,5 +340,16 @@ CALL p1('bigint(30) unsigned');
 DROP PROCEDURE p1;
 
 --echo #
+--echo # MDEV-23368 ROUND(18446744073709551615,-11) returns a wrong result
+--echo #
+
+SELECT ROUND(18446744073709551615,-10), ROUND(18446744073709551615,-11);
+CREATE TABLE t1 AS SELECT ROUND(18446744073709551615,-10), ROUND(18446744073709551615,-11);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+--echo #
 --echo # End of 10.4 tests
 --echo #
diff --git a/sql/item_func.cc b/sql/item_func.cc
index dc30bad6fe3..a98d2db921c 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -2464,7 +2464,8 @@ void Item_func_round::fix_arg_datetime()
                                 simple cases.
 */
 void Item_func_round::fix_arg_int(const Type_handler *preferred,
-                                  const Type_std_attributes *preferred_attrs)
+                                  const Type_std_attributes *preferred_attrs,
+                                  bool use_decimal_on_length_increase)
 {
   DBUG_ASSERT(args[0]->decimals == 0);
   if (args[1]->const_item())
@@ -2490,7 +2491,10 @@ void Item_func_round::fix_arg_int(const Type_handler *preferred,
         else
         {
           max_length++;
-          set_handler(type_handler_long_or_longlong());
+          if (use_decimal_on_length_increase)
+            set_handler(&type_handler_newdecimal);
+          else
+            set_handler(type_handler_long_or_longlong());
         }
       }
       else
@@ -2503,7 +2507,10 @@ void Item_func_round::fix_arg_int(const Type_handler *preferred,
         max_length= args[0]->decimal_precision() + length_can_increase;
         unsigned_flag= true;
         decimals= 0;
-        set_handler(type_handler_long_or_longlong());
+        if (length_can_increase && use_decimal_on_length_increase)
+          set_handler(&type_handler_newdecimal);
+        else
+          set_handler(type_handler_long_or_longlong());
       }
     }
     else
diff --git a/sql/item_func.h b/sql/item_func.h
index ecee1a1b83a..ea00a0cb033 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -1775,7 +1775,8 @@ public:
   }
   void fix_arg_decimal();
   void fix_arg_int(const Type_handler *preferred,
-                   const Type_std_attributes *preferred_attributes);
+                   const Type_std_attributes *preferred_attributes,
+                   bool use_decimal_on_length_increase);
   void fix_arg_double();
   void fix_arg_time();
   void fix_arg_datetime();
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index 3e762affe2f..e9d30295406 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -5673,7 +5673,8 @@ bool Type_handler_row::
 bool Type_handler_int_result::
        Item_func_round_fix_length_and_dec(Item_func_round *item) const
 {
-  item->fix_arg_int(this, item->arguments()[0]);
+  item->fix_arg_int(this, item->arguments()[0],
+                    field_type() == MYSQL_TYPE_LONGLONG);
   return false;
 }
 
@@ -5681,7 +5682,7 @@ bool Type_handler_int_result::
 bool Type_handler_year::
        Item_func_round_fix_length_and_dec(Item_func_round *item) const
 {
-  item->fix_arg_int(&type_handler_long, item->arguments()[0]); // 10.5 merge: fix to type_handler_ulong
+  item->fix_arg_int(&type_handler_long, item->arguments()[0], false); // 10.5 merge: fix to type_handler_ulong
   return false;
 }
 
@@ -5689,7 +5690,7 @@ bool Type_handler_year::
 bool Type_handler_hex_hybrid::
        Item_func_round_fix_length_and_dec(Item_func_round *item) const
 {
-  item->fix_arg_int(NULL, NULL);
+  item->fix_arg_int(NULL, NULL, item->arguments()[0]->max_length >= 8);
   return false;
 }
 
@@ -5732,7 +5733,7 @@ bool Type_handler_date_common::
 {
   static const Type_std_attributes attr(8, 0/*dec*/, true/*unsigned*/,
                                         DTCollation_numeric::singleton());
-  item->fix_arg_int(&type_handler_long, &attr); // 10.5 merge: fix to *_ulong
+  item->fix_arg_int(&type_handler_long, &attr, false); // 10.5 merge: fix to *_ulong
   return false;
 }