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
Triage: Triaged: D2 (Serious)

[11 Feb 2005 20:41] Trudy Pelzer
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.
[11 Feb 2005 20:45] Miguel Solorzano
Thank you for the bug report.
[29 Sep 2008 21:27] Konstantin Osipov
We can issue errors from SELECT now.
[11 Dec 2008 15:27] Bugs System
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.
[22 Dec 2008 12:45] Bugs System
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.
[29 Dec 2008 15:39] Bugs System
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)
[12 Jan 2009 19:43] Paul Dubois
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.
[13 Oct 2009 8:32] Bugs System
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.
[11 Dec 2009 6:01] Bugs System
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)
[11 Dec 2009 19:30] Paul Dubois
Already fixed in 6.0.x.
[6 Mar 2010 11:06] Bugs System
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)
[6 Mar 2010 18:33] Paul Dubois
Noted in 5.5.3 changelog.
[5 Jan 2011 14:51] Øystein Grøvlen
The fix for this issue has introduced Bug#59241.