Bug #24356 alter table unsigned -> signed

• Submitted: 16 Nov 2006 9:43
• Modified: 16 Nov 2006 15:08
• Reporter: Hans Ginzel
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.22, 5.0.29, 5.1.13
• OS: Linux (Linux/MS Windows XP)
• CPU Architecture: Any
• Tags: ALTER TABLE, change column, modify column, signed, UNSIGNED

[16 Nov 2006 9:43] Hans Ginzel

Description:
ALTER TABLE does not change unsigned column to signed one.

How to repeat:
mysql> create table a (n tinyint(2) unsigned zerofill);
mysql> desc a;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| n     | tinyint(2) unsigned zerofill | YES  |     | NULL    |       |
+-------+------------------------------+------+-----+---------+-------+

mysql> alter table a modify n tinyint(2) signed 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> alter table a modify n tinyint(2) zerofill;
Query OK, 0 rows affected (0.05 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.02 sec)

[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