| Bug #96459 | Type resolution of DIV operator produces one less precision. | ||
|---|---|---|---|
| Submitted: | 8 Aug 2019 6:03 | Modified: | 11 Jan 2021 18:33 |
| Reporter: | Kaiwang CHen (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.16, 5.7.27, 8.0.17 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Aug 2019 6:34]
MySQL Verification Team
Hello Kaiwang CHen, Thank you for the report and test case. regards, Umesh
[8 Aug 2019 6:35]
MySQL Verification Team
Please note that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html. If you have any questions, please contact the MySQL community team.
[15 Aug 2019 9:09]
Kaiwang CHen
A refined fix:
diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result
index 4a1722f4045..991afcbdef0 100644
--- a/mysql-test/r/func_math.result
+++ b/mysql-test/r/func_math.result
@@ -525,6 +525,27 @@ Level Code Message
Warning 1292 Truncated incorrect DECIMAL value: '123456789012345678901234567890'
Error 1690 BIGINT value is out of range in '('123456789012345678901234567890.123456789012345678901234567890' DIV 1)'
#
+# Bug #96459 Type resolution of DIV operator produces one less precision
+# Notice here uses decimal plus to reveal the precision problem,
+# so should any fix to plus changes the outcomes, feel free to fix these tests as well.
+#
+CREATE TABLE t1 SELECT
+5.0 + 96 DIV 1, # Dividend is integer
+5.0 + 96.1234 DIV 1, # Dividend is decimal
+5.0 + "96" DIV 1, # Dividend is string
+5.0 + CAST("96" AS SIGNED) DIV 1, # Dividend is function
+5.0 + CAST("96" AS UNSIGNED) DIV 1;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `5.0 + 96 DIV 1` decimal(4,1) DEFAULT NULL,
+ `5.0 + 96.1234 DIV 1` decimal(4,1) DEFAULT NULL,
+ `5.0 + "96" DIV 1` decimal(4,1) DEFAULT NULL,
+ `5.0 + CAST("96" AS SIGNED) DIV 1` decimal(4,1) DEFAULT NULL,
+ `5.0 + CAST("96" AS UNSIGNED) DIV 1` decimal(4,1) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+DROP TABLE t1;
+#
# Bug#57810 case/when/then : Assertion failed: length || !scale
#
SELECT CASE(('')) WHEN (CONVERT(1, CHAR(1))) THEN (('' / 1)) END;
diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test
index 5d03c5f9066..44c83e862e7 100644
--- a/mysql-test/t/func_math.test
+++ b/mysql-test/t/func_math.test
@@ -339,6 +339,20 @@ select 123456789012345678901234567890.123456789012345678901234567890 div 1 as x;
select "123456789012345678901234567890.123456789012345678901234567890" div 1 as x;
SHOW WARNINGS;
+-- echo #
+-- echo # Bug #96459 Type resolution of DIV operator produces one less precision
+-- echo # Notice here uses decimal plus to reveal the precision problem,
+-- echo # so should any fix to plus changes the outcomes, feel free to fix these tests as well.
+-- echo #
+CREATE TABLE t1 SELECT
+ 5.0 + 96 DIV 1, # Dividend is integer
+ 5.0 + 96.1234 DIV 1, # Dividend is decimal
+ 5.0 + "96" DIV 1, # Dividend is string
+ 5.0 + CAST("96" AS SIGNED) DIV 1, # Dividend is function
+ 5.0 + CAST("96" AS UNSIGNED) DIV 1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
--echo #
--echo # Bug#57810 case/when/then : Assertion failed: length || !scale
--echo #
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 06785220a39..ffccf4399fe 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -1947,6 +1947,15 @@ bool Item_func_int_div::resolve_type(THD *) {
return reject_geometry_args(arg_count, args, this);
}
+uint Item_func_int_div::decimal_precision() const {
+ Item_result argtype = args[0]->result_type();
+ uint precision =
+ args[0]->decimal_precision() -
+ (argtype == DECIMAL_RESULT || argtype == INT_RESULT ? args[0]->decimals
+ : 0);
+ return precision;
+}
+
longlong Item_func_mod::int_op() {
DBUG_ASSERT(fixed == 1);
longlong val0 = args[0]->val_int();
diff --git a/sql/item_func.h b/sql/item_func.h
index ffe42560db4..0f59a3bf109 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -1044,6 +1044,7 @@ class Item_func_int_div final : public Item_int_func {
longlong val_int() override;
const char *func_name() const override { return "DIV"; }
bool resolve_type(THD *thd) override;
+ uint decimal_precision() const override;
void print(const THD *thd, String *str,
enum_query_type query_type) const override {
[15 Aug 2019 9:33]
MySQL Verification Team
Hello Kaiwang , Please note that in order to accept your contribution you should either have your own personal OCA signed (you can follow the procedure outlined in the "Contributions" tab of this bug report) or if you want us to include you in Alibaba’s employees covered by Alibaba company OCA then please get in touch with Roger Shang. Roger needs to formally request our community manager for including you in Alibaba’s employees covered OCA. Thank you! regards, Umesh
[20 Aug 2019 12:45]
MySQL Verification Team
Thank you, all looks good. Please ensure to re-send the patch via "contribution" tab. Otherwise we would not be able to accept it. regards, Umesh
[20 Aug 2019 12:52]
Kaiwang CHen
Fix #96459 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 96459.patch (application/octet-stream, text), 3.72 KiB.
[20 Aug 2019 12:54]
MySQL Verification Team
Thank you, Kaiwang CHen. regards, Umesh
[11 Jan 2021 18:33]
Jon Stephens
Documented fix as follows in the MySQL 8.0.24 changelog:
Type resolution performed by the integer division operator (DIV)
yielded a precision of one less than expected in the result.
Our thanks to Kaiwang Chen for the contribution.
Closed.
Description: Type resolution of integer division operator (DIV) produces precision one less than expected. The problem is hidden in regular select queries, however, create table .. select complains out-of-range. How to repeat: mysql> SELECT 5.0 + 96 DIV 1; +----------------+ | 5.0 + 96 DIV 1 | +----------------+ | 101.0 | +----------------+ 1 row in set (0.00 sec) mysql> DROP TABLE IF EXISTS t1; mysql> CREATE TABLE t1 SELECT 5.0 + 96 DIV 1; ERROR 1264 (22003): Out of range value for column '5.0 + 96 DIV 1' at row 1 Suggested fix: diff --git a/sql/item_func.cc b/sql/item_func.cc index 6eefb5f..0adbe5a 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -2048,17 +2048,15 @@ longlong Item_func_int_div::val_int() { } bool Item_func_int_div::resolve_type(THD *) { - Item_result argtype = args[0]->result_type(); - /* use precision ony for the data type it is applicable for and valid */ - uint32 char_length = - args[0]->max_char_length() - - (argtype == DECIMAL_RESULT || argtype == INT_RESULT ? args[0]->decimals - : 0); + /* Item::decimal_precision() already supports any type. */ + int precision = args[0]->decimal_precision(); + unsigned_flag = args[0]->unsigned_flag | args[1]->unsigned_flag; + uint32 char_length = my_decimal_precision_to_length_no_truncation( + precision, 0, unsigned_flag); fix_char_length(char_length > MY_INT64_NUM_DECIMAL_DIGITS ? MY_INT64_NUM_DECIMAL_DIGITS : char_length); maybe_null = true; - unsigned_flag = args[0]->unsigned_flag | args[1]->unsigned_flag; return reject_geometry_args(arg_count, args, this); }