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

[11 Feb 2005 2:05] Trudy Pelzer
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
[11 Feb 2005 2:16] MySQL Verification Team
Thank you for the bug report.
[29 Sep 2008 21:26] Konstantin Osipov
No reason to keep this unfixed. We can issue errors from SELECT now.
[26 Nov 2009 14:18] Alexey Kopytov
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%    |
+-----------------------------------+---------+--------+----------+
[8 Dec 2009 7:59] Horst Hunger
For more tests look at WL#5176. See alo bug#49253, bug#49523, bug#49524.
[18 Mar 2010 10:39] 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/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.
[20 Mar 2010 18:35] 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/103898

3002 Alexey Kopytov	2010-03-20
      Streamlined funcs_1.storedproc broken by the patch for bug #8433.
[24 Mar 2010 8:14] Bugs System
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)
[24 Mar 2010 8:16] Bugs System
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)
[24 Mar 2010 8:17] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100324081159-5b8juv8ldiqwce8v) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (pib:16)
[25 Mar 2010 0:37] Paul Dubois
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.
[31 Mar 2010 15:58] Paul Dubois
5.5.4 changelog entry was moved to 5.5.5.
[9 Apr 2010 11:07] Sveta Smirnova
Bug #52715 was marked as duplicate of this one.
[8 May 2010 19:16] MySQL Verification Team
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?
[10 May 2010 3:33] James Day
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.
[10 May 2010 7:56] Alexey Kopytov
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.
[10 May 2010 8:08] Alexey Kopytov
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.
[10 May 2010 8:23] James Day
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?
[14 Oct 2010 13:48] MySQL Verification Team
http://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html