MySQL Bugs: #5913: Traditional mode: BIGINT range not correctly delimited

Bug #5913	Traditional mode: BIGINT range not correctly delimited
Submitted:	6 Oct 2004 1:43	Modified:	21 May 2005 11:18
Reporter:	Trudy Pelzer
Status:	Verified
Category:	Server: Errors	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Alexey Botchkov	Target Version:
Triage:	Triaged: D2 (Serious)

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.

Verified against latest BK source tree.

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)

See also BUG#5083

See also BUG#7670

See also Bug#9809; probably related.

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

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.