MDEV-23337 Rounding functions create a wrong data type for integer input

1. Fixing ROUND(x) and TRUNCATE(x,0) with TINYINT, SMALLINT, MEDIUMINT, BIGINT
   input to preserve the exact data type of the argument when it's possible.

2. Fixing FLOOR(x) and CEILING(x) with TINYINT, SMALLINT, MEDIUMINT, BIGINT
  to preserve the exact data type of the argument.

3. Adding dedicated Type_handler_year::Item_func_round_fix_length_and_dec()
  to easier handle ROUND(x) and TRUNCATE(x,y) for the YEAR(2) and YEAR(4)
  input. They still return INT(2) UNSIGNED and INT(4) UNSIGNED correspondingly,
  as before.

1 files changed, 35 insertions, 0 deletions

diff --git a/mysql-test/main/type_int.test b/mysql-test/main/type_int.test
index 662be57abd3..4822f22521f 100644
--- a/mysql-test/main/type_int.test
+++ b/mysql-test/main/type_int.test
@@ -294,5 +294,40 @@ SELECT COUNT(*) FROM t1 WHERE a XOR a;
 DROP TABLE t1;
 
 --echo #
+--echo # MDEV-23337 Rounding functions create a wrong data type for numeric input
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1(t VARCHAR(64))
+BEGIN
+  SELECT t AS ``;
+  EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (a TYPE)', 'TYPE', t);
+  INSERT IGNORE INTO t1 VALUES (-100000000000000000000000000000);
+  INSERT IGNORE INTO t1 VALUES (100000000000000000000000000000);
+  CREATE  TABLE t2 AS SELECT a, ROUND(a), TRUNCATE(a,0), FLOOR(a), CEILING(a) FROM t1;
+  SHOW CREATE TABLE t2;
+  SELECT * FROM t2;
+  DROP TABLE t1, t2;
+END;
+$$
+DELIMITER ;$$
+
+--vertical_results
+CALL p1('tinyint');
+CALL p1('smallint');
+CALL p1('mediumint');
+CALL p1('int');
+CALL p1('bigint');
+
+CALL p1('tinyint unsigned');
+CALL p1('smallint unsigned');
+CALL p1('mediumint unsigned');
+CALL p1('int unsigned');
+CALL p1('bigint unsigned');
+--horizontal_results
+
+DROP PROCEDURE p1;
+
+--echo #
 --echo # End of 10.4 tests
 --echo #