Bug #5913 Traditional mode: BIGINT range not correctly delimited
Submitted: 5 Oct 2004 23:43 Modified: 9 Jun 2014 16:59
Reporter: Trudy Pelzer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.0.2-alpha-debug OS:Linux (SuSE 9.1)
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[5 Oct 2004 23:43] Trudy Pelzer
Description:
When sql_mode='traditional', out-of-range values for 
a numeric data type must be rejected with SQLSTATE 
22003. But for BIGINT, this is not happening. When I 
attempt to INSERT a number that is less than the lowest 
allowed number for a BIGINT, the server silently changes 
my number to the end-point of BIGINT's range. It should 
reject the operation instead. 
 
Worse, when I attempt to INSERT a number that is greater 
than the largest allowed number for a BIGINT, the server  
silently changes my number to the *negative* end-point  
of BIGINT's range. It should reject the operation instead. 

How to repeat:
mysql> set sql_mode='traditional'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create table t1 (col1 bigint) engine=innodb; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into t1 values(-9223372036854775808); 
Query OK, 1 row affected (0.00 sec) 
-- This is OK, it's the outer limit of BIGINT on the negative side. 
 
mysql> insert into t1 values(-9223372036854775809); 
Query OK, 1 row affected (0.00 sec) 
-- This is an incorrect result. The INSERT should be rejected 
with SQLSTATE 22003: Out of range value ... 
 
mysql> select * from t1; 
+----------------------+ 
| col1                 | 
+----------------------+ 
| -9223372036854775808 | 
| -9223372036854775808 | 
+----------------------+ 
2 rows in set (0.00 sec) 
-- This result shows that the second value, which should 
have been rejected, was silently changed and inserted. 
 
mysql> insert into t1 values(9223372036854775807); 
Query OK, 1 row affected (0.00 sec) 
-- This is OK, it's the outer limit of BIGINT on the positive side. 
 
mysql> insert into t1 values(9223372036854775808); 
Query OK, 1 row affected (0.00 sec) 
-- This is an incorrect result. The INSERT should be rejected 
with SQLSTATE 22003: Out of range value ... 
 
mysql> select * from t1; 
+----------------------+ 
| col1                 | 
+----------------------+ 
| -9223372036854775808 | 
| -9223372036854775808 | 
|  9223372036854775807 | 
| -9223372036854775808 | 
+----------------------+ 
4 rows in set (0.00 sec) 
-- The final value is of most concern. The INSERT was for 
the *positive* out-of-range number 9223372036854775808, 
but the result set shows that was silently changed to the 
*negative* end-range number -9223372036854775808.
[6 Oct 2004 0:02] Miguel Solorzano
Verified against latest BK source tree.
[3 Dec 2004 22:19] Trudy Pelzer
Here's another example, using BIGINT UNSIGNED, 
failing with 5.0.3-alpha-debug: 
 
mysql> set sql_mode='traditional'; 
mysql> create table t1 (col1 bigint unsigned); 
 
mysql> insert into t1 values(-1); 
Query OK, 1 row affected (0.00 sec) 
-- This is the incorrect response. INSERT should fail with 
SQLSTATE 22003 Out of range value 
 
mysql> select * from t1; 
+----------------------+ 
| col1                 | 
+----------------------+ 
| 18446744073709551615 | 
+----------------------+ 
1 row in set (0.00 sec)
[11 Jan 2005 4:20] Sergey Petrunya
See also BUG#5083
[12 Jan 2005 22:07] Sergey Petrunya
See also BUG#7670
[11 Apr 2005 16:37] Trudy Pelzer
See also Bug#9809; probably related.
[21 May 2005 9:17] Sergey Petrunya
Same as in BUG#5083:
Delaying the fix as we currently don't have a set of well-defined rules about
how and when are BIGINTs/strings/decimal numbers are converted.
(btw this probem is documented in "Open bugs and design deficiencies" section of
user manual).
[29 Jun 2005 10:09] Matthias Leich
Even when it may sound banal and obviously, please do not forget
that the function
CAST( <value too big for target data type> 
                AS SIGNED/UNSIGNED INTEGER)
shows the same wrong behaviour.
[9 Jun 2014 16:59] Paul Dubois
This bug was due to faulty arithmetic that was done in 5.0.
It has been fixed and are not reproducible in 5.1+ versions.
No changelog entry because it is unknown in which version the issue was corrected.