MySQL Bugs: #43149: Default value dropped when modifying a columns type

Bug #43149	Default value dropped when modifying a columns type
Submitted:	24 Feb 2009 14:21	Modified:	6 Mar 2009 18:42
Reporter:	Gustaf Thorslund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.31	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	ALTER TABLE, DEFAULT, modify

Description:
When modifying the type for a column the default value is lost.

How to repeat:
mysql> CREATE TABLE t1 (c1 float DEFAULT 3.14 NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` float NOT NULL DEFAULT '3.14'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 MODIFY c1 double;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Suggested fix:
Keep the default value (and NOT NULL option) when modifying a columns type.

This is indeed design behavior. 

Quoting from
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

| MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]

There is no room in the syntax to allow you to "keep" any part of the previous column definition.  As it is specified, the entire definition is replaced, not just parts of it. 

That means that when you modify a column defined as:

  int(12) NOT NULL default=0

and replace it with 
  bigint NULL

The "default" portion of the definition is also replaced.

As Shawn has demonstrated, it's expected and documented behaviour

At http://dev.mysql.com/doc/refman/5.0/en/create-table.html column_definition is defined as follows:

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string'] [reference_definition]

So, DEFAULT clause is optional there. So, we are not forced to set it in ALTER TABLE ... MODIFY as well as in CREATE TABLE, and the the question remains: is it removed or not. This is semantics, not syntax, and it should be explained properly in the documentation of ALTER TABLE.

Here is an example of proper documentation of this feature, http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqls.doc/sqls124.htm#si...:

"When you modify a column, all attributes previously associated with the column (that is, default value, single-column check constraint, or referential constraint) are dropped. When you want certain attributes of the column to remain, such as PRIMARY KEY, you must re-specify those attributes."

We need similar text in our manual.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Unspecified attributes are not carried forward by intent. Otherwise, how would you remove them?

I've added the following to the ALTER TABLE statement to clarify the intended behavior:

When you use CHANGE or MODIFY, column_definition must include the
data type and all attributes that should apply to the new column,
other than index attributes such as PRIMARY KEY or UNIQUE. Attributes
present in the original definition but not specified for the new
definition are not carried forward. Suppose a column col1 is defined
as INT UNSIGNED DEFAULT 1 COMMENT 'my column' and you modify the
column as follows:

ALTER TABLE t1 MODIFY col1 BIGINT;

The resulting column will be defined as BIGINT, but will not include
the attributes UNSIGNED DEFAULT 1 COMMENT 'my column'. To retain
them, the statement should be: 

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';