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

Bug #5903	Traditional mode: invalid DEFAULT value accepted rather than rejected
Submitted:	5 Oct 2004 19:38	Modified:	31 Mar 2005 18:20
Reporter:	Trudy Pelzer
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Bugs System	Target Version:

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)

Verified against latest BK source tree.

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