MySQL Bugs: #97197: The decimal type result calculated by division gets truncated in temporary table

Bug #97197	The decimal type result calculated by division gets truncated in temporary table
Submitted:	11 Oct 2019 7:49	Modified:	11 Oct 2019 12:26
Reporter:	Hope Lee (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.16	OS:	CentOS
Assigned to:		CPU Architecture:	Any
Tags:	decimal, division, precision, scale

Description:
When a temporary table is used to store a decimal type result calculated by division, the result may get truncated.

How to repeat:
CREATE TABLE select_four (tinyint_test tinyint(4), smallint_test smallint(6), decimal_test decimal(10,0));
INSERT INTO select_four VALUES (31, 24, 1000000000);

root@localhost:test 8.0.16-rds-dev-debug> SELECT tinyint_test / (smallint_test / decimal_test) FROM select_four;
+-----------------------------------------------+
| tinyint_test / (smallint_test / decimal_test) |
+-----------------------------------------------+
|                               1291666666.6667 |
+-----------------------------------------------+
1 row in set (0.00 sec)

root@localhost:test 8.0.16-rds-dev-debug> SELECT DISTINCT tinyint_test / (smallint_test / decimal_test) FROM select_four;
+-----------------------------------------------+
| tinyint_test / (smallint_test / decimal_test) |
+-----------------------------------------------+
|                                  9999999.9999 |
+-----------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
The precision and scale of the result calculated by division are not set correctly (see in Item_func_div::result_precision()). It causes that when a temporary table is needed, the precision and scale of the corresponding field is not accurate. So if we write the result to the field, the actual result may get truncated (see in fill_record()). 

Adjust the precision and scale calculating algorithm for the decimal type (Item_func_div::result_precision()).

Hello Mr. Lee,

Thank you for your bug report.

However, this is not a bug.

If you read our Reference Manual, you will find it documented that the final result is constrained by the number of digits in the fixed point domain.

Your definition can not accommodate 14 (fourteen) digits and hence the truncation.

Not a bug.

Hi Sinisa!

So it is by design that SELECT and SELECT DISTINCT return different result?

Hi Oystein,

Thank you for your comment.

You know as well as I do, that it is not so by design. Hence, that could be a design flaw, which could make a feature request.