Bug #31236 Inconsistent division by zero behavior for floating point numbers

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

Thank you for a problem report. Please, check with a newer 5.1.x versions, 5.1.21, for example. It looks more consistent.

I verified the behavior with version 5.1.23.

You got exactly the same results as reportered initially with 5.1.23? Strange, 5.1.21 gave me different results.

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.

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;
}

Thank you for the report.

Verified as described.

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".

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.

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.

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.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

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.

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.

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.

Fix also noted in the 5.1.23-ndb-6.3.11 changelog.