Bug #31236 Inconsistent division by zero behavior for floating point numbers
Submitted: 27 Sep 2007 12:49 Modified: 15 Mar 2008 22:37
Reporter: Sven Sandberg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: Alexey Kopytov CPU Architecture:Any
Tags: division by zero
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[27 Sep 2007 12:49] Sven Sandberg
Description:
Our code handles division by 0 instead of overflow. Division by zero is only one case where overflow may occur, and hence we get an inconsistent behavior when overflow happens. In particular, we have three non-numbers: +inf, -inf, nan, and NULL, where NULL appears unpredictably instead of any of the other three.

As far as I know, it affects floating point division, pow(), and exp().
See, e.g., Item_func_div::real_op() in item_func.cc

Test runs:

mysql> select 1.0/0;
+-------+
| 1.0/0 |
+-------+
|  NULL | 
+-------+
1 row in set (0.16 sec)

mysql> select 1e300/1e-300;
+--------------+
| 1e300/1e-300 |
+--------------+
|          inf | 
+--------------+
1 row in set (0.09 sec)

mysql> select (1e300/1e-300)*0;
+------------------+
| (1e300/1e-300)*0 |
+------------------+
|              nan | 
+------------------+
1 row in set (0.00 sec)

mysql> select (1e300/0)*0;
+-------------+
| (1e300/0)*0 |
+-------------+
|        NULL | 
+-------------+
1 row in set (0.00 sec)

mysql> select exp(750);
+----------+
| exp(750) |
+----------+
|      inf | 
+----------+
1 row in set (0.07 sec)

Related bugs: BUG#6105

How to repeat:
See the test-runs above
[27 Sep 2007 18:05] Valeriy Kravchuk
Thank you for a problem report. Please, check with a newer 5.1.x versions, 5.1.21, for example. It looks more consistent.
[4 Oct 2007 9:34] Sven Sandberg
I verified the behavior with version 5.1.23.
[28 Oct 2007 14:04] Valeriy Kravchuk
You got exactly the same results as reportered initially with 5.1.23? Strange, 5.1.21 gave me different results.
[28 Oct 2007 15:29] Sven Sandberg
What results did you get? On what platform?

Our code does not handle overflow correctly (see, e.g., Item_func_div::real_op() in sql/item_func.cc, where it only checks if the divisor is equal to 0.0). When you divide a large number by a small number such that the result overflows, the result is platform-dependent. So, it is plausible that you got other results because you are on another platform.

I ran under Linux on i686.
[28 Oct 2007 17:17] Rayson Ho
One possible fix is to do the division, and then verify the result.

double Item_func_div::real_op()
{
  ...
  double result = value/val2;

  /* the division did not cause any problem, just return the result */
  if (isnormal(result))
    return result;

  /* the result can be infinite, NaN, etc */
  /* handle error cases here, check result with isfinite(3), isnan(3), etc */
  ...
  ...
  return 0.0;
}
[30 Oct 2007 13:04] Sveta Smirnova
Thank you for the report.

Verified as described.
[10 Feb 2008 15:56] Alexey Kopytov
According to http://dev.mysql.com/doc/refman/5.1/en/arithmetic-functions.html "Division by zero produces a NULL result". 

Note that the type of the result of division depends on the types of the arguments, e.g. the result of SELECT 1.0/0 is DECIMAL, not DOUBLE. Since no numeric data type except FLOAT/DOUBLE has special "non-number" values, NULL is the only possible result of division by zero. Changing Item_func_div::real_op() to return "Inf" in this case would make the behavior inconsistent with all other numeric types.

Changing the status to "Not a bug".
[11 Feb 2008 18:02] Alexey Kopytov
Sorry, I misread the problem description. The inconsistency is that some operations with floating point numbers return NULL in case of overflow, while the other ones return inf/nan.
[19 Feb 2008 21:34] 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/42598

ChangeSet@1.2659, 2008-02-20 00:33:43+03:00, kaa@kaamos.(none) +11 -0
  Fix for bug #31236: Inconsistent division by zero behavior for 
                      floating point numbers
  
  Some math functions did not check if the result is a valid number
  (i.e. neither of +-inf or nan).
  
  Fixed by validating the result where necessary and returning NULL in
  case of invalid result.
[20 Feb 2008 9:28] 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/42622

ChangeSet@1.2582, 2008-02-20 12:28:44+03:00, kaa@kaamos.(none) +2 -0
  6.0-specific fixes for bug#31236. Fixed a test case that relied on
  the old math functions behavior.
[13 Mar 2008 19:29] Bugs System
Pushed into 6.0.5-alpha
[13 Mar 2008 19:36] Bugs System
Pushed into 5.1.24-rc
[14 Mar 2008 22:04] Jon Stephens
Documented in the 5.1.24 and 6.0.5 changelogs as follows:

        Division-by-zero errors involving floating-point values were not handled
        consistently by all math functions. Now such functions return
        NULL whenever a division by zero is attempted.
[15 Mar 2008 18:54] Alexey Kopytov
The changelog entry is inaccurate. The patch for this bug affects not just division, but also the following operators and functions when floating point numbers are involved: "-", "*", "/", POW(), EXP(). Previously they could return "+-inf" or "nan" in case of numeric overflow or invalid arguments. Now NULL is returned in such cases.
[15 Mar 2008 22:37] Jon Stephens
Updated changelog entry to read:

        The -, *, and / operators and the functions POW() and EXP() could
        misbehave when used with floating-point numbers. Previously they might
        return +INF, -INF, or NaN in cases of numeric overflow (including that
        caused by division by zero) or when invalid arguments were used. Now
        NULL is returned in all such cases.
[30 Mar 2008 20:02] Jon Stephens
Fix also noted in the 5.1.23-ndb-6.3.11 changelog.