MDEV-23351 Rounding functions return wrong data types for DATE input

Fixing ROUND(date,0), TRUNCATE(date,x), FLOOR(date), CEILING(date)
to return the `int(8) unsigned` data type.

Details:
1. Cleanup: moving virtual implementations
   - Type_handler_temporal_result::Item_func_int_val_fix_length_and_dec()
   - Type_handler_temporal_result::Item_func_round_fix_length_and_dec()
   to Type_handler_date_common. Other temporal data type handlers
   override these methods anyway. So they were only DATE specific.
   This change makes the code clearer.
2. Backporting DTCollation_numeric from 10.5, to reuse the code easier.
3. Adding the `preferred_attrs` argument to Item_func_round::fix_arg_int(). Now
   Type_handler_xxx::Item_func_round_val_fix_length_and_dec() work as follows:
   - The INT-alike and YEAR handlers copy preferred_attrs from args[0].
   - The DATE handler passes explicit attributes, to get `int(8) unsigned`.
   - The hex hybrid handler passes NULL, so fix_arg_int() calculates attributes.
4. Type_handler_date_common::Item_func_int_val_fix_length_and_dec()
   now sets the type handler and attributes to get `int(8) unsigned`.

6 files changed, 61 insertions, 13 deletions

diff --git a/mysql-test/main/type_date.result b/mysql-test/main/type_date.result
index 616ba0a4529..f40a27e5f5c 100644
--- a/mysql-test/main/type_date.result
+++ b/mysql-test/main/type_date.result
@@ -1089,5 +1089,19 @@ CAST(1012.5 AS DATE) * 1.0
 Warnings:
 Note	1292	Truncated incorrect date value: '1012.5'
 #
+# MDEV-23351 Rounding functions return wrong data types for DATE input
+#
+CREATE TABLE t1 (a date);
+CREATE TABLE t2 AS SELECT FLOOR(a), CEIL(a),ROUND(a),TRUNCATE(a,0) FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `FLOOR(a)` int(8) unsigned DEFAULT NULL,
+  `CEIL(a)` int(8) unsigned DEFAULT NULL,
+  `ROUND(a)` int(8) unsigned DEFAULT NULL,
+  `TRUNCATE(a,0)` int(8) unsigned DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2,t1;
+#
 # End of 10.4 tests
 #
diff --git a/mysql-test/main/type_date.test b/mysql-test/main/type_date.test
index c041295f845..33598846504 100644
--- a/mysql-test/main/type_date.test
+++ b/mysql-test/main/type_date.test
@@ -740,6 +740,14 @@ SET sql_mode=DEFAULT;
 SELECT NULLIF(CAST(1012.5 AS DATE), 1);
 SELECT CAST(1012.5 AS DATE) * 1.0;
 
+--echo #
+--echo # MDEV-23351 Rounding functions return wrong data types for DATE input
+--echo #
+
+CREATE TABLE t1 (a date);
+CREATE TABLE t2 AS SELECT FLOOR(a), CEIL(a),ROUND(a),TRUNCATE(a,0) FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2,t1;
 
 --echo #
 --echo # End of 10.4 tests
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 8e36804888b..6b19933fd48 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -2455,13 +2455,16 @@ void Item_func_round::fix_arg_datetime()
                           (without extending to DECIMAL).
                           - If `preferred` is not NULL, then the code tries
                             to preserve the given data type handler and
-                            data type attributes of the argument.
+                            the data type attributes `preferred_attrs`.
                           - If `preferred` is NULL, then the code fully
                             calculates attributes using
                             args[0]->decimal_precision() and chooses between
                             INT and BIGINT, depending on attributes.
+  @param [IN] preferred_attrs - The preferred data type attributes for
+                                simple cases.
 */
-void Item_func_round::fix_arg_int(const Type_handler *preferred)
+void Item_func_round::fix_arg_int(const Type_handler *preferred,
+                                  const Type_std_attributes *preferred_attrs)
 {
   DBUG_ASSERT(args[0]->decimals == 0);
   if (args[1]->const_item())
@@ -2477,7 +2480,7 @@ void Item_func_round::fix_arg_int(const Type_handler *preferred)
       int length_can_increase= MY_TEST(!truncate && val1.neg());
       if (preferred)
       {
-        Type_std_attributes::set(args[0]);
+        Type_std_attributes::set(preferred_attrs);
         if (!length_can_increase)
         {
           // Preserve the exact data type and attributes
diff --git a/sql/item_func.h b/sql/item_func.h
index 549e576dbd3..ecee1a1b83a 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -1774,7 +1774,8 @@ public:
     return NULL;
   }
   void fix_arg_decimal();
-  void fix_arg_int(const Type_handler *preferred);
+  void fix_arg_int(const Type_handler *preferred,
+                   const Type_std_attributes *preferred_attributes);
   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 ee25a6c4fc5..8757d2fef59 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -23,6 +23,12 @@
 #include "log.h"
 #include "tztime.h"
 
+const DTCollation &DTCollation_numeric::singleton()
+{
+  static const DTCollation_numeric tmp;
+  return tmp;
+}
+
 Type_handler_row         type_handler_row;
 
 Type_handler_null        type_handler_null;
@@ -5659,7 +5665,7 @@ 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->fix_arg_int(this, item->arguments()[0]);
   return false;
 }
 
@@ -5667,7 +5673,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); // 10.5 merge: fix to type_handler_ulong
+  item->fix_arg_int(&type_handler_long, item->arguments()[0]); // 10.5 merge: fix to type_handler_ulong
   return false;
 }
 
@@ -5675,7 +5681,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);
+  item->fix_arg_int(NULL, NULL);
   return false;
 }
 
@@ -5713,10 +5719,12 @@ bool Type_handler_decimal_result::
 }
 
 
-bool Type_handler_temporal_result::
+bool Type_handler_date_common::
        Item_func_round_fix_length_and_dec(Item_func_round *item) const
 {
-  item->fix_arg_double();
+  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
   return false;
 }
 
@@ -5831,10 +5839,13 @@ bool Type_handler_decimal_result::
 }
 
 
-bool Type_handler_temporal_result::
+bool Type_handler_date_common::
        Item_func_int_val_fix_length_and_dec(Item_func_int_val *item) const
 {
-  item->fix_length_and_dec_int_or_decimal();
+  static const Type_std_attributes attr(8, 0/*dec*/, true/*unsigned*/,
+                                        DTCollation_numeric::singleton());
+  item->Type_std_attributes::set(attr);
+  item->set_handler(&type_handler_long); // 10.5 merge: fix to *_ulong
   return false;
 }
 
diff --git a/sql/sql_type.h b/sql/sql_type.h
index dd3319dce5c..65935c1bd66 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -2742,6 +2742,17 @@ public:
 };
 
 
+class DTCollation_numeric: public DTCollation
+{
+public:
+  DTCollation_numeric()
+   :DTCollation(charset_info(), DERIVATION_NUMERIC, MY_REPERTOIRE_NUMERIC)
+  { }
+  static const CHARSET_INFO *charset_info() { return &my_charset_numeric; }
+  static const DTCollation & singleton();
+};
+
+
 static inline uint32
 char_to_byte_length_safe(size_t char_length_arg, uint32 mbmaxlen_arg)
 {
@@ -4658,8 +4669,6 @@ public:
   bool Item_func_between_fix_length_and_dec(Item_func_between *func) const;
   bool Item_func_in_fix_comparator_compatible_types(THD *thd,
                                                     Item_func_in *) 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;
   bool Item_func_abs_fix_length_and_dec(Item_func_abs *) const;
   bool Item_func_neg_fix_length_and_dec(Item_func_neg *) const;
   bool Item_func_plus_fix_length_and_dec(Item_func_plus *) const;
@@ -5486,6 +5495,8 @@ public:
   longlong Item_func_min_max_val_int(Item_func_min_max *) const;
   my_decimal *Item_func_min_max_val_decimal(Item_func_min_max *,
                                             my_decimal *) 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;
   bool Item_hybrid_func_fix_attributes(THD *thd,
                                        const char *name,
                                        Type_handler_hybrid_field_type *,