MySQL Bugs: #27416: Out of range value is accepted for TINYINT when used with zerofill option

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:	None
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

Description:
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.

Thank you for the report.

Verified as described.

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.

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

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
) ENGINE=MyISAM DEFAULT CHARSET=utf8

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.

Regards,

Timothy