| Bug #96470 | Negating operator refines to DECIMAL_RESULT for constant negative integers. | ||
|---|---|---|---|
| Submitted: | 8 Aug 2019 12:34 | Modified: | 15 Aug 2019 9:07 |
| Reporter: | Kaiwang CHen (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S4 (Feature request) |
| Version: | 8.0.16 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Aug 2019 12:40]
Kaiwang CHen
Add version.
[8 Aug 2019 12:41]
Kaiwang CHen
Fix typo in title.
[8 Aug 2019 12:42]
MySQL Verification Team
Hi Mr. Chen, Thank you for your bug report. What you ask is a very, very rare feature, that we did not anticipate anybody using a vast number of negations in the expression. However, since you took trouble to provide a test case, I think that this is a proper feature request.
[8 Aug 2019 12:42]
MySQL Verification Team
Verified as a feature request.
[15 Aug 2019 9:07]
Kaiwang CHen
A refined fix: diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index 4a1722f4045..a2cab6cf04d 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -659,6 +659,27 @@ ERROR 22003: BIGINT value is out of range in '-(`test`.`t1`.`a`)' SELECT -b FROM t1; ERROR 22003: BIGINT value is out of range in '-(`test`.`t1`.`b`)' DROP TABLE t1; +# +# Bug #96470 Negating operator refines to DECIMAL_RESULT for constant negative integers +# Notice the display width of integer is not part of data type, +# should any fix to display width changes the outcomes, feel free to fix these tests as well. +# +CREATE TABLE t1 SELECT +- - 5, +- - 9223372036854775808; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `- - 5` int(3) NOT NULL DEFAULT '0', + `- - 9223372036854775808` decimal(20,0) NOT NULL DEFAULT '0' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci +DROP TABLE t1; +CREATE TABLE t1 (col2 int(11) DEFAULT NULL); +INSERT INTO t1 VALUES (1); +SELECT - - 5 + 96 DIV + col2 FROM t1; +- - 5 + 96 DIV + col2 +101 +DROP TABLE t1; SET @a:=999999999999999999999999999999999999999999999999999999999999999999999999999999999; SELECT @a + @a; ERROR 22003: DECIMAL value is out of range in '((@`a`) + (@`a`))' diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 5d03c5f9066..262fb09e565 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -488,6 +488,23 @@ SELECT -b FROM t1; DROP TABLE t1; +-- echo # +-- echo # Bug #96470 Negating operator refines to DECIMAL_RESULT for constant negative integers +-- echo # Notice the display width of integer is not part of data type, +-- echo # should any fix to display width changes the outcomes, feel free to fix these tests as well. +-- echo # +CREATE TABLE t1 SELECT + - - 5, + - - 9223372036854775808; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# Aone #21114225 [rqg]DIV + const expression return wrong data +CREATE TABLE t1 (col2 int(11) DEFAULT NULL); +INSERT INTO t1 VALUES (1); +SELECT - - 5 + 96 DIV + col2 FROM t1; +DROP TABLE t1; + # Decimal overflows # ================= diff --git a/sql/item_func.cc b/sql/item_func.cc index 06785220a39..72c1a2fb3f1 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -2076,9 +2076,43 @@ bool Item_func_neg::resolve_type(THD *thd) { */ if (hybrid_type == INT_RESULT && args[0]->const_item()) { longlong val = args[0]->val_int(); - if ((ulonglong)val >= (ulonglong)LLONG_MIN && - ((ulonglong)val != (ulonglong)LLONG_MIN || - args[0]->type() != INT_ITEM)) { + /* + Bug#96470 + + Since arg0 can be either signed or unsigned, val should be treated + as a 64-bit value instead of a signed value, and the interpretation + of bit pattern depends on the unsigned_flag. + + Here uses 8-bit as an example; the same rule applies to 64-bit. + + bit pattern 00000000 ... 01111111 10000000 ... 11111111 + unsigned 0 127 128 255 + signed 0 127 -128(INT8_MIN) -1 + + See https://en.wikipedia.org/wiki/Signed_number_representations + + Intuitively, for unsigned numbers, negating any value to the right + of 128 gets out-of-range; for signed numbers, only negating -128 + itself gets out-of-range. + + Since both 128 and -128 use the same bit pattern (INT8_MIN), for the + right half of the bit patterns (1), we have the following truth table: + + arg0 is bitpat is outcome of + unsigned INT8_MIN negating operator + true true ok + true false out-of-range + false true out-of-range + false false ok + + So exclusive-or the left two columns gets out-of-range. (2) + + With respect to out-of-range test, only the type and the value of + args[0] are significant, while the node representation is ignored. + */ + if ((ulonglong)val >= (ulonglong)LLONG_MIN && // (1) + args[0]->unsigned_flag != // (2) + ((ulonglong)val == (ulonglong)LLONG_MIN)) { /* Ensure that result is converted to DECIMAL, as longlong can't hold the negated number
[20 Aug 2019 12:55]
Kaiwang CHen
Fix 96470. However, the max_length is not fixed yet since it needs many other mtr fixes as well. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 96470.patch (application/octet-stream, text), 4.17 KiB.
[22 Aug 2019 15:16]
MySQL Verification Team
Thank you, Mr. Chen, Your contribution will be forwarded to our developers in charge with this matter.
Description: Negating operator (-) refines to DECIMAL_RSULT for constant negative integers. Nested negating operator produces greater precision than expected, with one level nesting increases one in precision. How to repeat: mysql> DROP TABLE IF EXISTS t2; mysql> CREATE TABLE t2 SELECT - - 5; mysql> SHOW CREATE TABLE t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `- - 5` decimal(2,0) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> DROP TABLE IF EXISTS t3; mysql> CREATE TABLE t3 SELECT - - - - 5; mysql> SHOW CREATE TABLE t3\G *************************** 1. row *************************** Table: t3 Create Table: CREATE TABLE `t3` ( `- - - - 5` decimal(4,0) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) Suggested fix: diff --git a/sql/item_func.cc b/sql/item_func.cc index 6eefb5f..4da4a87 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -2195,8 +2195,10 @@ my_decimal *Item_func_neg::decimal_op(my_decimal *decimal_value) { void Item_func_neg::fix_num_length_and_dec() { decimals = args[0]->decimals; + unsigned_flag = false; /* 1 add because sign can appear */ - max_length = args[0]->max_length + 1; + max_length = my_decimal_precision_to_length_no_truncation( + decimal_precision(), decimals, unsigned_flag) + 1; } bool Item_func_neg::resolve_type(THD *thd) { @@ -2210,9 +2212,37 @@ bool Item_func_neg::resolve_type(THD *thd) { */ if (hybrid_type == INT_RESULT && args[0]->const_item()) { longlong val = args[0]->val_int(); - if ((ulonglong)val >= (ulonglong)LLONG_MIN && - ((ulonglong)val != (ulonglong)LLONG_MIN || - args[0]->type() != INT_ITEM)) { + /* + Since val can be either signed or unsigned, the interpretation of + bit pattern depends on unsigned_flag. + + Here uses 8-bit as an example; the same rule applies to 64-bit. + + bit pattern 00000000 ... 01111111 10000000 ... 11111111 + unsigned 0 127 128 255 + signed 0 127 -128(INT8_MIN) -1 + + Intuitively, for unsigned numbers, negating any value to the right + of 128 gets out-of-range; for signed numbers, only negating -128 + gets out-of-range. + + Since 128 and -128 use the same bit pattern INT8_MIN, for the right + half of the bit patterns (1), we have the following truth table: + + unsigned INT8_MIN negating + true true ok + true false out-of-range + false true out-of-range + false false ok + + So exclusive-or the two variables gets out-of-range. (2) + + With respect to out-of-range test, only the type and the value of + args[0] are significant, while the node representation is ignored. + */ + if ((ulonglong)val >= (ulonglong)LLONG_MIN && // (1) + args[0]->unsigned_flag != // (2) + ((ulonglong)val == (ulonglong)LLONG_MIN)) { /* Ensure that result is converted to DECIMAL, as longlong can't hold the negated number @@ -2222,7 +2252,6 @@ bool Item_func_neg::resolve_type(THD *thd) { DBUG_PRINT("info", ("Type changed: DECIMAL_RESULT")); } } - unsigned_flag = false; DBUG_RETURN(false); }