Bug #96459 Type resolution of DIV operator produces one less precision.
Submitted: 8 Aug 2019 6:03 Modified: 20 Aug 2019 12:22
Reporter: Kaiwang CHen (OCA) Email Updates:
Status: Verified Impact on me:
None 
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:03] Kaiwang CHen
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);
 }
[8 Aug 2019 6:34] Umesh Shastry
Hello Kaiwang CHen,

Thank you for the report and test case.

regards,
Umesh
[8 Aug 2019 6:35] Umesh Shastry
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] Umesh Shastry
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] Umesh Shastry
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] Umesh Shastry
Thank you, Kaiwang CHen.

regards,
Umesh