From 3b87a68169bbe1ae8d23ead2f657a292fe29d14a Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Sun, 2 Aug 2020 22:48:53 +0400 Subject: 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). --- mysql-test/main/type_hex_hybrid.result | 17 +++++++++++++++++ mysql-test/main/type_hex_hybrid.test | 11 +++++++++++ mysql-test/main/type_int.result | 17 +++++++++++++++++ mysql-test/main/type_int.test | 11 +++++++++++ sql/item_func.cc | 13 ++++++++++--- sql/item_func.h | 3 ++- sql/sql_type.cc | 9 +++++---- 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 @@ -46,6 +46,17 @@ DELIMITER ;$$ --horizontal_results +--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 @@ -339,6 +339,17 @@ 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; } -- cgit v1.2.1