Bug #5903 Traditional mode: invalid DEFAULT value accepted rather than rejected

• Submitted: 5 Oct 2004 17:38
• Modified: 31 Mar 2005 16:20
• Reporter: Trudy Pelzer
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.2-alpha-debug
• OS: Linux (SuSE 9.1)
• Assigned to: Michael Widenius
• CPU Architecture: Any

[5 Oct 2004 17:38] Trudy Pelzer

Description:
When sql_mode='traditional', numbers which are out of range 
for a numeric data type must be rejected rather than being 
changed to an in-range value. But if the value is coming from 
a DEFAULT, the invalid value is still being silently changed and 
accepted. That is: 
CREATE TABLE t1 (col1 TINYINT DEFAULT 1000, col2 INT); 
INSERT INTO t1 (col2) VALUES (5); 
should be rejected with SQLSTATE 22003: Out of range value ... 
UPDATE t1 SET col1=DEFAULT; 
should be rejected  with SQLSTATE 22003: Out of range value ... 
 
 

How to repeat:
mysql> set sql_mode='traditional'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create table t1 (col1 tinyint default 1000, col2 int) engine=innodb; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> insert into t1 values (1000,5); 
ERROR 1264 (22003): Out of range value adjusted for column 'col1' at row 1 
-- This is the correct response. 
 
mysql> select * from t1; 
Empty set (0.00 sec) 
 
mysql> insert into t1 (col2) values (5); 
Query OK, 1 row affected (0.00 sec) 
-- This is the incorrect response; the  INSERT should be rejected. 
 
mysql> select * from t1; 
+------+------+ 
| col1 | col2 | 
+------+------+ 
|  127 |    5 | 
+------+------+ 
1 row in set (0.00 sec) 
 
mysql> insert into t1 values(50,10); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> select * from t1; 
+------+------+ 
| col1 | col2 | 
+------+------+ 
|  127 |    5 | 
|   50 |   10 | 
+------+------+ 
2 rows in set (0.00 sec) 
 
mysql> update t1 set col1=default where col2=10; 
Query OK, 1 row affected (0.01 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 
-- This is the incorrect response; the UPDATE should be rejected. 
 
mysql> select * from t1; 
+------+------+ 
| col1 | col2 | 
+------+------+ 
|  127 |    5 | 
|  127 |   10 | 
+------+------+ 
2 rows in set (0.00 sec)

[6 Oct 2004 1:57] MySQL Verification Team

Verified against latest BK source tree.

[31 Mar 2005 16:20] Michael Widenius

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fixed in 5.0.4
Now one gets an error if one uses an invalid DEFAULT value for CREATE TABLE