diff options
author | Alexander Barkov <bar@mariadb.com> | 2020-08-03 10:53:06 +0400 |
---|---|---|
committer | Alexander Barkov <bar@mariadb.com> | 2020-08-03 10:53:06 +0400 |
commit | 9840bb21ef683afd59a30a79ebc5bd5ad33a7c1a (patch) | |
tree | d3a79d95550a05c8381855adaa45bc5f2941619d | |
parent | 97f7bfcebcc17df337bd97c0a9535a906547016d (diff) | |
download | mariadb-git-9840bb21ef683afd59a30a79ebc5bd5ad33a7c1a.tar.gz |
MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result
Changing that in case of *INT and hex hybrid input:
- ROUND(x,NULL) creates a column with the same type as x.
The old code created a DOUBLE column, which was not relevant at all.
This change simplifies the code a lot.
- ROUND(x,non_constant) creates a column of the INT, BIGINT or DECIMAL
data type (depending on the exact type of x).
The old code created a column of the DOUBLE data type,
which lead to precision loss. Hence MDEV-23366.
- ROUND(bigint_30,negative_constant) creates a column of the DECIMAL(30,0)
data type. The old code created DECIMAL(29,0), which looked strange:
the data type promoted to a higher one, but max length reduced.
Now the length attribute is preserved.
-rw-r--r-- | mysql-test/main/func_math.result | 3 | ||||
-rw-r--r-- | mysql-test/main/type_hex_hybrid.result | 37 | ||||
-rw-r--r-- | mysql-test/main/type_hex_hybrid.test | 25 | ||||
-rw-r--r-- | mysql-test/main/type_int.result | 165 | ||||
-rw-r--r-- | mysql-test/main/type_int.test | 34 | ||||
-rw-r--r-- | sql/item_func.cc | 87 | ||||
-rw-r--r-- | sql/item_func.h | 2 | ||||
-rw-r--r-- | sql/sql_type.cc | 2 |
8 files changed, 300 insertions, 55 deletions
diff --git a/mysql-test/main/func_math.result b/mysql-test/main/func_math.result index 68922b09bf7..163478f330c 100644 --- a/mysql-test/main/func_math.result +++ b/mysql-test/main/func_math.result @@ -348,7 +348,6 @@ truncate(4, cast(-2 as unsigned)) truncate(4, 18446744073709551614) truncate(4, 4 4 0 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement -Note 1105 Cast to unsigned converted negative integer to it's positive complement select round(10000000000000000000, -19), truncate(10000000000000000000, -19); round(10000000000000000000, -19) truncate(10000000000000000000, -19) 10000000000000000000 10000000000000000000 @@ -1784,7 +1783,7 @@ ROUND(10e0,NULL) AS c3; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` double DEFAULT NULL, + `c1` int(2) DEFAULT NULL, `c2` double DEFAULT NULL, `c3` double DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 diff --git a/mysql-test/main/type_hex_hybrid.result b/mysql-test/main/type_hex_hybrid.result index 91490375e7e..26ff385a709 100644 --- a/mysql-test/main/type_hex_hybrid.result +++ b/mysql-test/main/type_hex_hybrid.result @@ -195,5 +195,42 @@ ROUND(0xFFFFFFFFFFFFFFFF,-10) ROUND(0xFFFFFFFFFFFFFFFF,-11) 18446744070000000000 18446744100000000000 DROP TABLE t1; # +# MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result +# +SELECT +ROUND(0xFFFFFFFFFFFFFFFF,NULL) AS c1, +ROUND(0xFFFFFFFFFFFFFFFF,rand()*0) AS c2, +ROUND(0xFFFFFFFFFFFFFFFF,-1) AS c3, +ROUND(0xFFFFFFFFFFFFFFFF,-19) AS c4, +ROUND(0xFFFFFFFFFFFFFFFF,rand()*0-19) AS c5; +c1 NULL +c2 18446744073709551615 +c3 18446744073709551620 +c4 20000000000000000000 +c5 20000000000000000000 +CREATE OR REPLACE TABLE t1 AS +SELECT +ROUND(0xFFFFFFFFFFFFFFFF,NULL) AS c1, +ROUND(0xFFFFFFFFFFFFFFFF,rand()*0) AS c2, +ROUND(0xFFFFFFFFFFFFFFFF,-1) AS c3, +ROUND(0xFFFFFFFFFFFFFFFF,-19) AS c4, +ROUND(0xFFFFFFFFFFFFFFFF,rand()*0-19) AS c5; +SELECT * FROM t1; +c1 NULL +c2 18446744073709551615 +c3 18446744073709551620 +c4 20000000000000000000 +c5 20000000000000000000 +SHOW CREATE TABLE t1; +Table t1 +Create Table CREATE TABLE `t1` ( + `c1` bigint(20) unsigned DEFAULT NULL, + `c2` decimal(21,0) unsigned NOT NULL, + `c3` decimal(21,0) unsigned NOT NULL, + `c4` decimal(21,0) unsigned NOT NULL, + `c5` decimal(21,0) unsigned NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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 62d1a8ace91..175169fbda4 100644 --- a/mysql-test/main/type_hex_hybrid.test +++ b/mysql-test/main/type_hex_hybrid.test @@ -58,5 +58,30 @@ DROP TABLE t1; --echo # +--echo # MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result +--echo # + +--vertical_results +SELECT + ROUND(0xFFFFFFFFFFFFFFFF,NULL) AS c1, + ROUND(0xFFFFFFFFFFFFFFFF,rand()*0) AS c2, + ROUND(0xFFFFFFFFFFFFFFFF,-1) AS c3, + ROUND(0xFFFFFFFFFFFFFFFF,-19) AS c4, + ROUND(0xFFFFFFFFFFFFFFFF,rand()*0-19) AS c5; + +CREATE OR REPLACE TABLE t1 AS +SELECT + ROUND(0xFFFFFFFFFFFFFFFF,NULL) AS c1, + ROUND(0xFFFFFFFFFFFFFFFF,rand()*0) AS c2, + ROUND(0xFFFFFFFFFFFFFFFF,-1) AS c3, + ROUND(0xFFFFFFFFFFFFFFFF,-19) AS c4, + ROUND(0xFFFFFFFFFFFFFFFF,rand()*0-19) AS c5; + +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +--horizontal_results + +--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 840def4184d..08abf2db916 100644 --- a/mysql-test/main/type_int.result +++ b/mysql-test/main/type_int.result @@ -1163,6 +1163,75 @@ ROUND(a,-2) 9223372036854775800 ROUND(a,-19) 10000000000000000000 ROUND(a,-20) 0 ROUND(a,-30) 0 +CALL p1('bigint(22)'); + bigint(22) +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(22) DEFAULT NULL, + `ROUND(a,-1)` decimal(22,0) DEFAULT NULL, + `ROUND(a,-2)` decimal(22,0) DEFAULT NULL, + `ROUND(a,-19)` decimal(22,0) DEFAULT NULL, + `ROUND(a,-20)` decimal(22,0) DEFAULT NULL, + `ROUND(a,-30)` decimal(22,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -9223372036854775808 +ROUND(a,-1) -9223372036854775810 +ROUND(a,-2) -9223372036854775800 +ROUND(a,-19) -10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 9223372036854775807 +ROUND(a,-1) 9223372036854775810 +ROUND(a,-2) 9223372036854775800 +ROUND(a,-19) 10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint(23)'); + bigint(23) +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(23) DEFAULT NULL, + `ROUND(a,-1)` decimal(23,0) DEFAULT NULL, + `ROUND(a,-2)` decimal(23,0) DEFAULT NULL, + `ROUND(a,-19)` decimal(23,0) DEFAULT NULL, + `ROUND(a,-20)` decimal(23,0) DEFAULT NULL, + `ROUND(a,-30)` decimal(23,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -9223372036854775808 +ROUND(a,-1) -9223372036854775810 +ROUND(a,-2) -9223372036854775800 +ROUND(a,-19) -10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 9223372036854775807 +ROUND(a,-1) 9223372036854775810 +ROUND(a,-2) 9223372036854775800 +ROUND(a,-19) 10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint(30)'); + bigint(30) +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(30) DEFAULT NULL, + `ROUND(a,-1)` decimal(30,0) DEFAULT NULL, + `ROUND(a,-2)` decimal(30,0) DEFAULT NULL, + `ROUND(a,-19)` decimal(30,0) DEFAULT NULL, + `ROUND(a,-20)` decimal(30,0) DEFAULT NULL, + `ROUND(a,-30)` decimal(30,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -9223372036854775808 +ROUND(a,-1) -9223372036854775810 +ROUND(a,-2) -9223372036854775800 +ROUND(a,-19) -10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 9223372036854775807 +ROUND(a,-1) 9223372036854775810 +ROUND(a,-2) 9223372036854775800 +ROUND(a,-19) 10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 CALL p1('tinyint unsigned'); tinyint unsigned Table t2 @@ -1324,7 +1393,103 @@ ROUND(a,-2) 18446744073709551600 ROUND(a,-19) 20000000000000000000 ROUND(a,-20) 0 ROUND(a,-30) 0 +CALL p1('bigint(22) unsigned'); + bigint(22) unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(22) unsigned DEFAULT NULL, + `ROUND(a,-1)` decimal(23,0) unsigned DEFAULT NULL, + `ROUND(a,-2)` decimal(23,0) unsigned DEFAULT NULL, + `ROUND(a,-19)` decimal(23,0) unsigned DEFAULT NULL, + `ROUND(a,-20)` decimal(23,0) unsigned DEFAULT NULL, + `ROUND(a,-30)` decimal(23,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a,-1) 0 +ROUND(a,-2) 0 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 18446744073709551615 +ROUND(a,-1) 18446744073709551620 +ROUND(a,-2) 18446744073709551600 +ROUND(a,-19) 20000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint(23) unsigned'); + bigint(23) unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(23) unsigned DEFAULT NULL, + `ROUND(a,-1)` decimal(24,0) unsigned DEFAULT NULL, + `ROUND(a,-2)` decimal(24,0) unsigned DEFAULT NULL, + `ROUND(a,-19)` decimal(24,0) unsigned DEFAULT NULL, + `ROUND(a,-20)` decimal(24,0) unsigned DEFAULT NULL, + `ROUND(a,-30)` decimal(24,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a,-1) 0 +ROUND(a,-2) 0 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 18446744073709551615 +ROUND(a,-1) 18446744073709551620 +ROUND(a,-2) 18446744073709551600 +ROUND(a,-19) 20000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint(30) unsigned'); + bigint(30) unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(30) unsigned DEFAULT NULL, + `ROUND(a,-1)` decimal(31,0) unsigned DEFAULT NULL, + `ROUND(a,-2)` decimal(31,0) unsigned DEFAULT NULL, + `ROUND(a,-19)` decimal(31,0) unsigned DEFAULT NULL, + `ROUND(a,-20)` decimal(31,0) unsigned DEFAULT NULL, + `ROUND(a,-30)` decimal(31,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a,-1) 0 +ROUND(a,-2) 0 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 18446744073709551615 +ROUND(a,-1) 18446744073709551620 +ROUND(a,-2) 18446744073709551600 +ROUND(a,-19) 20000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 DROP PROCEDURE p1; # +# MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result +# +SELECT +ROUND(18446744073709551615,NULL) AS c1, +ROUND(18446744073709551615,rand()*0) AS c2, +ROUND(18446744073709551615,rand()*0-19) AS c3; +c1 NULL +c2 18446744073709551615 +c3 20000000000000000000 +CREATE OR REPLACE TABLE t1 AS +SELECT +ROUND(18446744073709551615,NULL) AS c1, +ROUND(18446744073709551615,rand()*0) AS c2, +ROUND(18446744073709551615,rand()*0-19) AS c3; +SELECT * FROM t1; +c1 NULL +c2 18446744073709551615 +c3 20000000000000000000 +SHOW CREATE TABLE t1; +Table t1 +Create Table CREATE TABLE `t1` ( + `c1` bigint(20) unsigned DEFAULT NULL, + `c2` decimal(21,0) unsigned NOT NULL, + `c3` decimal(21,0) unsigned NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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 e94fc4d3298..0505020374d 100644 --- a/mysql-test/main/type_int.test +++ b/mysql-test/main/type_int.test @@ -394,9 +394,9 @@ CALL p1('int'); CALL p1('bigint'); CALL p1('bigint(20)'); CALL p1('bigint(21)'); -#CALL p1('bigint(22)'); -#CALL p1('bigint(23)'); -#CALL p1('bigint(30)'); +CALL p1('bigint(22)'); +CALL p1('bigint(23)'); +CALL p1('bigint(30)'); CALL p1('tinyint unsigned'); CALL p1('smallint unsigned'); @@ -405,14 +405,36 @@ CALL p1('int unsigned'); CALL p1('bigint unsigned'); CALL p1('bigint(20) unsigned'); CALL p1('bigint(21) unsigned'); -#CALL p1('bigint(22) unsigned'); -#CALL p1('bigint(23) unsigned'); -#CALL p1('bigint(30) unsigned'); +CALL p1('bigint(22) unsigned'); +CALL p1('bigint(23) unsigned'); +CALL p1('bigint(30) unsigned'); --horizontal_results DROP PROCEDURE p1; --echo # +--echo # MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result +--echo # + +--vertical_results +SELECT + ROUND(18446744073709551615,NULL) AS c1, + ROUND(18446744073709551615,rand()*0) AS c2, + ROUND(18446744073709551615,rand()*0-19) AS c3; + +CREATE OR REPLACE TABLE t1 AS +SELECT + ROUND(18446744073709551615,NULL) AS c1, + ROUND(18446744073709551615,rand()*0) AS c2, + ROUND(18446744073709551615,rand()*0-19) AS c3; + +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +--horizontal_results + + +--echo # --echo # End of 10.4 tests --echo # diff --git a/sql/item_func.cc b/sql/item_func.cc index a98d2db921c..8000d7f6a1d 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -2446,6 +2446,20 @@ void Item_func_round::fix_arg_datetime() } +bool Item_func_round::test_if_length_can_increase() +{ + if (truncate) + return false; + if (args[1]->const_item() && !args[1]->is_expensive()) + { + // Length can increase in some cases: e.g. ROUND(9,-1) -> 10. + Longlong_hybrid val1= args[1]->to_longlong_hybrid(); + return !args[1]->null_value && val1.neg(); + } + return true; // ROUND(x,n), where n is not a constant. +} + + /** Calculate data type and attributes for INT-alike input. @@ -2468,56 +2482,37 @@ void Item_func_round::fix_arg_int(const Type_handler *preferred, bool use_decimal_on_length_increase) { DBUG_ASSERT(args[0]->decimals == 0); - if (args[1]->const_item()) + + Type_std_attributes::set(preferred_attrs); + if (!test_if_length_can_increase()) { - Longlong_hybrid val1= args[1]->to_longlong_hybrid(); - if (args[1]->null_value) - fix_length_and_dec_double(NOT_FIXED_DEC); - else if (truncate || - !val1.neg() /* ROUND(x, n>=0) */ || - args[0]->decimal_precision() < DECIMAL_LONGLONG_DIGITS) - { - // Here we can keep INT_RESULT - // Length can increase in some cases: ROUND(9,-1) -> 10 - int length_can_increase= MY_TEST(!truncate && val1.neg()); - if (preferred) - { - Type_std_attributes::set(preferred_attrs); - if (!length_can_increase) - { - // Preserve the exact data type and attributes - set_handler(preferred); - } - else - { - max_length++; - if (use_decimal_on_length_increase) - set_handler(&type_handler_newdecimal); - else - set_handler(type_handler_long_or_longlong()); - } - } - else - { - /* - This branch is currently used for hex hybrid only. - It's known to be unsigned. So sign length is 0. - */ - DBUG_ASSERT(args[0]->unsigned_flag); // no needs to add sign length - max_length= args[0]->decimal_precision() + length_can_increase; - unsigned_flag= true; - decimals= 0; - if (length_can_increase && use_decimal_on_length_increase) - set_handler(&type_handler_newdecimal); - else - set_handler(type_handler_long_or_longlong()); - } - } + // Preserve the exact data type and attributes + set_handler(preferred); + } + else + { + max_length++; + if (use_decimal_on_length_increase) + set_handler(&type_handler_newdecimal); else - fix_length_and_dec_decimal(val1.to_uint(DECIMAL_MAX_SCALE)); + set_handler(type_handler_long_or_longlong()); } +} + + +void Item_func_round::fix_arg_hex_hybrid() +{ + DBUG_ASSERT(args[0]->decimals == 0); + DBUG_ASSERT(args[0]->decimal_precision() < DECIMAL_LONGLONG_DIGITS); + DBUG_ASSERT(args[0]->unsigned_flag); // no needs to add sign length + bool length_can_increase= test_if_length_can_increase(); + max_length= args[0]->decimal_precision() + MY_TEST(length_can_increase); + unsigned_flag= true; + decimals= 0; + if (length_can_increase && args[0]->max_length >= 8) + set_handler(&type_handler_newdecimal); else - fix_length_and_dec_double(args[0]->decimals); + set_handler(type_handler_long_or_longlong()); } diff --git a/sql/item_func.h b/sql/item_func.h index ea00a0cb033..24601983f86 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -1754,6 +1754,7 @@ class Item_func_round :public Item_func_hybrid_field_type bool truncate; void fix_length_and_dec_decimal(uint decimals_to_set); void fix_length_and_dec_double(uint decimals_to_set); + bool test_if_length_can_increase(); public: Item_func_round(THD *thd, Item *a, Item *b, bool trunc_arg) :Item_func_hybrid_field_type(thd, a, b), truncate(trunc_arg) {} @@ -1777,6 +1778,7 @@ public: void fix_arg_int(const Type_handler *preferred, const Type_std_attributes *preferred_attributes, bool use_decimal_on_length_increase); + void fix_arg_hex_hybrid(); 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 e9d30295406..0b333d44b77 100644 --- a/sql/sql_type.cc +++ b/sql/sql_type.cc @@ -5690,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->arguments()[0]->max_length >= 8); + item->fix_arg_hex_hybrid(); return false; } |