MySQL Bugs: #8457: Precision math: DIV returns incorrect result with large decimal value

Bug #8457	Precision math: DIV returns incorrect result with large decimal value
Submitted:	11 Feb 2005 20:41	Modified:	6 Mar 2010 18:33
Reporter:	Trudy Pelzer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
The MySQL Reference Manual says that DIV is for:
"Integer division. Similar to FLOOR() but safe with
BIGINT values."

I take this to mean that, if an operand is too big
to fit into BIGINT's range (or is not an integer), the 
operation should be rejected. Instead, DIV returns 
an incorrect result. I would prefer that the operation 
be rejected in such cases, but if this doesn't happen, 
the documentation should be amended to warn users
that they may get weird results from DIV if the
operands are too big to be BIGINTs.

How to repeat:
mysql> create table t1 (col1 decimal(60,30));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(123456789012345678901234567890.123456789012345678901234567890);
Query OK, 1 row affected (0.00 sec)

mysql> select col1 div 1 from t1;
+--------------+
| col1 div 1   |
+--------------+
| 123456789012 |
+--------------+
1 row in set, 1 warning (0.00 sec)
-- This is not the correct result, because the col1 operand
is too big to be a BIGINT value. Since the warning may be
missed or interpreted, the ideal solution is to return an
error rather than returning an invalid result.

mysql> show warnings;
+-------+------+-----------------------------------------------------+
| Level | Code | Message                                             |
+-------+------+-----------------------------------------------------+
| Error | 1264 | Out of range value adjusted for column '' at row -1 |
+-------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select col1/1 from t1;
+---------------------------------------------------------------------+
| col1/1                                                              |
+---------------------------------------------------------------------+
| 123456789012345678901234567890.123456789012345678901234567890000000 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
-- The result of the division operation is correct, so
the problem lies with DIV itself.

Thank you for the bug report.

We can issue errors from SELECT now.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61378

2922 Alexey Kopytov	2008-12-11
      Fix for bug #8457: Precision math: DIV returns incorrect result with large decimal
                         value
                  
      For the DIV operator, neither operands nor result were checked for integer
      overflows.
      
      This patch changes the DIV behavior for non-integer operands as follows:
      if either of the operands has a non-integer type, convert both operands to the
      DECIMAL type, then calculate the division using DECIMAL arithmetics. Convert the 
      resulting DECIMAL value into BIGINT [UNSIGNED] if it fits into the corresponding 
      range, or throw an 'out of range' error otherwise.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/62210

2714 Alexey Kopytov	2008-12-22
      After-merge fix for bug #8457: correctly merged the results file.

Pushed into 6.0.10-alpha (revid:sergefp@mysql.com-20081229085854-ui755edl9x4xomen) (version source revid:sergefp@mysql.com-20081227125220-feifj44ao5kapr93) (merge vers: 6.0.10-alpha) (pib:6)

Noted in 6.0.10 changelog.

For the DIV operator, incorrect results could occur for non-integer
operands that exceed BIGINT range. Now, if either operand has a
non-integer type, the operands are converted to DECIMAL and divided
using DECIMAL arithmetic before converting the result to BIGINT. If
the result exceeds BIGINT range, an error occurs.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86655

2896 Alexey Kopytov	2009-10-13
      Backport of the patch for bug #8457 "Precision math: DIV 
      returns incorrect result with large decimal value" 
       
      For the DIV operator, neither operands nor result were checked 
      for integer overflows. 
       
      This patch changes the DIV behavior for non-integer operands as 
      follows: if either of the operands has a non-integer type, 
      convert both operands to the DECIMAL type, then calculate the 
      division using DECIMAL arithmetics. Convert the resulting 
      DECIMAL value into BIGINT [UNSIGNED] if it fits into the 
      corresponding range, or throw an 'out of range' error 
      otherwise. 
     @ mysql-test/r/func_math.result
        Added a test case for bug #8457.
        Fixed results for a test case depending on the wrong behavior.
     @ mysql-test/r/type_varchar.result
        Fixed results for a test case depending on the wrong behavior.
     @ mysql-test/t/func_math.test
        Added a test case for bug #8457.
     @ sql/item_func.cc
        If either of the operands has a non-integer type, convert both 
        operands to the DECIMAL type, then calculate the division using 
        DECIMAL arithmetics. Convert the resulting DECIMAL value into 
        BIGINT [UNSIGNED] if it fits into the corresponding range, or 
        throw an 'out of range' error otherwise.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)

Already fixed in 6.0.x.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)

Noted in 5.5.3 changelog.

The fix for this issue has introduced Bug#59241.