MySQL Bugs: #8433: Overflow must be an error

Bug #8433	Overflow must be an error
Submitted:	11 Feb 2005 2:05	Modified:	25 Mar 2010 0:37
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:
MySQL is not treating numeric overflow as an error,
as required by standard SQL.

How to repeat:
mysql> select 1E300 * 1E300;
+---------------+
| 1E300 * 1E300 |
+---------------+
|           inf |
+---------------+
1 row in set (0.00 sec)
-- This is the incorrect response. The operation should fail,
with SQLSTATE 22003 numeric value out of range

mysql> select 18446744073709551615 + 1;
+--------------------------+
| 18446744073709551615 + 1 |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)
-- As above, this is the incorrect response. The operation should 
fail, with SQLSTATE 22003 numeric value out of range

Thank you for the bug report.

No reason to keep this unfixed. We can issue errors from SELECT now.

Results of some microbenchmarks on an optimized (non-debug) binary:

+-----------------------------------+---------+--------+----------+
|               Test                | W/o fix | W/fix  | Slowdown |
+-----------------------------------+---------+--------+----------+
| SELECT BENCHMARK(1e9, 1+1)        | 17.75s  | 19.09s |  7.57%   |
+-----------------------------------+---------+--------+----------+
| SELECT BENCHMARK(1e9, 1-1)        | 17.41s  | 19.27s | 10.68%   |
+-----------------------------------+---------+--------+----------+
| SELECT BENCHMARK(1e9, 1*1)        | 17.67s  | 19.87s | 12.45%   |
+-----------------------------------+---------+--------+----------+
| SELECT BENCHMARK(1e9, 1*(-1))     | 24.52s  | 31.21s | 27.28%   |
+-----------------------------------+---------+--------+----------+
| SELECT BENCHMARK(1e9, (-1)*(-1))  | 32.94s  | 42.87s | 30.15%   |
+-----------------------------------+---------+--------+----------+
| SELECT BENCHMARK(1e9, 1 DIV 1)    | 29.24s  | 31.20s | 3.65%    |
+-----------------------------------+---------+--------+----------+
| SELECT BENCHMARK(1e9, 1 MOD 1)    | 27.97s  | 28.58s | 2.18%    |
+-----------------------------------+---------+--------+----------+

For more tests look at WL#5176. See alo bug#49253, bug#49523, bug#49524.

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/103677

2938 Alexey Kopytov	2010-03-18
      Bug #8433: Overflow must be an error 
       
      All numeric operators and functions on integer, floating point 
      and DECIMAL values now throw an 'out of range' error rather 
      than returning an incorrect value or NULL,  when the result is 
      out of supported range for the corresponding data type. 
       
      Some test cases in the test suite had to be updated 
      accordingly either because the test case itself relied on a 
      value returned in case of a numeric overflow, or because a 
      numeric overflow was the root cause of the corresponding bugs. 
      The latter tests are no longer relevant, since the expressions 
      used to trigger the corresponding bugs are not valid anymore. 
      However, such test cases have been adjusted and kept "for the 
      record". 
     @ mysql-test/r/func_math.result
        Added test cases for bug #8433. 
        Updated results of the test case for bug #31236.
     @ mysql-test/r/func_misc.result
        Streamlined test cases.
     @ mysql-test/r/func_test.result
        Streamlined test cases.
     @ mysql-test/r/select.result
        Streamlined test cases.
     @ mysql-test/r/sp.result
        Streamlined test cases.
     @ mysql-test/r/strict.result
        Streamlined test cases.
     @ mysql-test/r/type_newdecimal.result
        Streamlined test cases.
     @ mysql-test/suite/sys_vars/r/sql_slave_skip_counter_basic.result
        Streamlined test cases.
     @ mysql-test/suite/sys_vars/t/sql_slave_skip_counter_basic.test
        Streamlined test cases.
     @ mysql-test/t/func_math.test
        Added test cases for bug #8433. 
        Updated results of the test case for bug #31236.
     @ mysql-test/t/func_misc.test
        Streamlined test cases.
     @ mysql-test/t/func_test.test
        Streamlined test cases.
     @ mysql-test/t/select.test
        Streamlined test cases.
     @ mysql-test/t/sp.test
        Streamlined test cases.
     @ mysql-test/t/strict.test
        Streamlined test cases.
     @ mysql-test/t/type_newdecimal.test
        Streamlined test cases.
     @ sql/item_create.cc
        Changed Item_func_cot() to be defined as a standalone Item
        rather than a combination of "1 / TAN(x)".
     @ sql/item_func.cc
        Throw an 'out of range' error rather than returning an  
        incorrect value or NULL,  when the result of a numeric 
        operator or a function is out of supported range for  
        the corresponding data type.
     @ sql/item_func.h
        Added validation helpers as inline methods of Item_func.
     @ sql/share/errmsg-utf8.txt
        New ER_DATA_OUT_OF_RANGE error.

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/103898

3002 Alexey Kopytov	2010-03-20
      Streamlined funcs_1.storedproc broken by the patch for bug #8433.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100324081249-yfwol7qtcek6dh7w) (version source revid:alik@sun.com-20100324081113-kc7x1iytnplww91u) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.4-m3 (revid:alik@sun.com-20100324081056-6bndv6f0nrvbblhp) (version source revid:alik@sun.com-20100324081056-6bndv6f0nrvbblhp) (merge vers: 5.5.4-m3) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100324081159-5b8juv8ldiqwce8v) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (pib:16)

Noted in 5.5.4, 6.0.14 changelogs.

All numeric operators and functions on integer, floating point and
DECIMAL values now throw an "out of range" error
(ER_DATA_OUT_OF_RANGE) rather than returning an incorrect value or
NULL, when the result is out of the supported range for the
corresponding data type.

5.5.4 changelog entry was moved to 5.5.5.

Bug #52715 was marked as duplicate of this one.

I think that our performance losses:

| SELECT BENCHMARK(1e9, 1*1)        | 17.67s  | 19.87s | 12.45%   |
+-----------------------------------+---------+--------+----------+
| SELECT BENCHMARK(1e9, 1*(-1))     | 24.52s  | 31.21s | 27.28%   |
+-----------------------------------+---------+--------+----------+
| SELECT BENCHMARK(1e9, (-1)*(-1))  | 32.94s  | 42.87s | 30.15%   |

especially when it comes to multiplication are too significant to allow this patch to move forward. I don't think this patch is ready for production purposes with this kind of performance hit. 

Please revise/rewrite to improve these benchmark numbers. 

Also... Why are we not simply catching and forwarding the exceptions directly from the math libraries themselves? Why are we trying to precompute and anticipate problems?

How does the performance change when a billion different select statements are done instead of one statement with a billion fake operations? Is the difference even measurable after all of the other overhead for parsing and execution?

It seems to take fake workloads to make the performance change look interesting.

Shawn,

There is no way to significantly reduce the overhead to "improve these benchmark numbers". We either validate the arithmetic results or we don't. And validation is a _relatively_ expensive operation (especially in the multiplication case) as compared to a single CPU instruction it would normally take to just add/multiply/etc. two numbers.

OTOH the validation checks could be made optional via an SQL mode so that those who don't care about arithmetic overflows or even rely on them would not suffer.

James,

>How does the performance change when a billion different select statements are done
instead of one statement with a billion fake operations? Is the difference even
measurable after all of the other overhead for parsing and execution?

Yes, it would not be measurable. Any workload spending most of its execution time in parsing, network I/O or storage engine will make any change in server runtime performance look insignificant or non-existing, right?

> It seems to take fake workloads to make the performance change look interesting.

The purpose was to measure the pure impact on arithmetic operations. BENCHMARK(1e9,...) is surely not a realistic workload, but running individual selects for a billion arithmetic operations does not look realistic to me either. Is that really that common in computationally intensive workloads? What about selects with arithmetic expressions on large tables? Stored routines?

Ranger is doing some benchmarks for the last two cases and will be posting the results soon.

Compute-intensive workloads in the server seem uncommon, since none of the teams asked seems to remember encountering one that would be affected by this. 

A billion selects is a more normal workload. So are selects with a little computation, like date or timestamp work or simple comparisons. But if those are only done once per row, the time to get the row is surely going to be far more significant than the effect of this change.

Ranger's work should be interesting, good to read about it. Would be particularly interesting to see how the time for some simple calculations done once per row for a range of storage engines was affected, in case my belief that it would make this insignificant is wrong.

How would the work of adding an SQL mode later compare to adding one now? I assume no difference, so if nobody can come up with any users who have a problem today, it could be done later once someone finds a need for it?

http://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html