Bug #27416 Out of range value is accepted for TINYINT when used with zerofill option
Submitted: 24 Mar 2007 7:43 Modified: 14 Aug 2007 18:18
Reporter: Nadeem Chaudhry Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.37-community-nt, 5.1, 5.2 OS:Linux (Linux, Microsoft Windows 2000)
Assigned to: Timothy Smith CPU Architecture:Any
Tags: data types, Tinyint, ZEROFILL

[24 Mar 2007 7:43] Nadeem Chaudhry
I create a table with a tinyint(5) zerofill  column.
I insert 255 in it and it accepts. This should be allowed if I had declared it as unsigned but not as a signed tinyint.
There is no error, even though sql_mode=traditional.

How to repeat:
set sql_mode='TRADITIONAL';
drop table test;
create table test ( f1 tinyint(5) zerofill);
insert into test values ( 255 );
select * from test;

Suggested fix:
It should give an error message and not allow more then 127 to be inserted.
[26 Mar 2007 9:09] Sveta Smirnova
Thank you for the report.

Verified as described.
[14 Aug 2007 18:18] Timothy Smith
Thanks to senpablo on freenode #mysql-dev for analyzing this report.  I'm marking this as Not a bug, because of how ZEROFILL is designed.  ZEROFILL automatically implies UNSIGNED.


mysql> create table t1 (f1 int zerofill); show create table t1\G
Query OK, 0 rows affected (0.00 sec)

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `f1` int(10) unsigned zerofill DEFAULT NULL

I agree that this is unexpected.  However, it is how ZEROFILL is defined to behave, and as such it doesn't make sense to warn about it, or to give an error in strict mode.