Bug #24356 | alter table unsigned -> signed | ||
---|---|---|---|
Submitted: | 16 Nov 2006 9:43 | Modified: | 16 Nov 2006 15:08 |
Reporter: | Hans Ginzel | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 4.1.22, 5.0.29, 5.1.13 | OS: | Linux (Linux/MS Windows XP) |
Assigned to: | CPU Architecture: | Any | |
Tags: | ALTER TABLE, change column, modify column, signed, UNSIGNED |
[16 Nov 2006 9:43]
Hans Ginzel
[16 Nov 2006 10:06]
Andrey Hristov
mysql> select version(); +-------------------------------+ | version() | +-------------------------------+ | 4.1.22-valgrind-max-debug-log | +-------------------------------+ 1 row in set (0.07 sec) mysql> create table a (n tinyint(2) unsigned zerofill); Query OK, 0 rows affected (0.08 sec) mysql> desc a; +-------+------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------------------+------+-----+---------+-------+ | n | tinyint(2) unsigned zerofill | YES | | NULL | | +-------+------------------------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> alter table a modify n tinyint(2) signed zerofill; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc a; +-------+------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------------------+------+-----+---------+-------+ | n | tinyint(2) unsigned zerofill | YES | | NULL | | +-------+------------------------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> show create table a; +-------+-----------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------+ | a | CREATE TABLE `a` ( `n` tinyint(2) unsigned zerofill default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table a modify n tinyint(2) zerofill; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table a; +-------+-----------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------+ | a | CREATE TABLE `a` ( `n` tinyint(2) unsigned zerofill default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> drop table a; Query OK, 0 rows affected (0.00 sec) --------------------------------------- mysql> select version(); +-------------------------------+ | version() | +-------------------------------+ | 5.0.29-valgrind-max-debug-log | +-------------------------------+ 1 row in set (0.09 sec) mysql> create table a (n tinyint(2) unsigned zerofill); Query OK, 0 rows affected (0.07 sec) mysql> desc a; +-------+------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------------------+------+-----+---------+-------+ | n | tinyint(2) unsigned zerofill | YES | | NULL | | +-------+------------------------------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> alter table a modify n tinyint(2) zerofill; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc a; +-------+------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------------------+------+-----+---------+-------+ | n | tinyint(2) unsigned zerofill | YES | | NULL | | +-------+------------------------------+------+-----+---------+-------+ 1 row in set (0.00 sec) ------------------------------------------------------------------ mysql> select version(); +------------------------------------+ | version() | +------------------------------------+ | 5.1.13-beta-valgrind-max-debug-log | +------------------------------------+ 1 row in set (0.01 sec) mysql> create table a (n tinyint(2) unsigned zerofill); Query OK, 0 rows affected (0.13 sec) mysql> desc a; +-------+------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------------------+------+-----+---------+-------+ | n | tinyint(2) unsigned zerofill | YES | | NULL | | +-------+------------------------------+------+-----+---------+-------+ 1 row in set (0.09 sec) mysql> alter table a modify n tinyint(2) zerofill; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc a; +-------+------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------------------+------+-----+---------+-------+ | n | tinyint(2) unsigned zerofill | YES | | NULL | | +-------+------------------------------+------+-----+---------+-------+ 1 row in set (0.22 sec)
[16 Nov 2006 10:08]
Andrey Hristov
Thank you for your bug report!
[16 Nov 2006 15:08]
Andrey Hristov
From the documentation: "If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column." http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html
[25 May 2011 15:32]
Jason Edelman
FWIW, this bug still exists. Try this on MySQL Workbench 5.2.26 CE and you get the same behavior