MySQL Bugs: #6104: ALTER TABLE MODIFY COLUMN ignores default

Bug #6104	ALTER TABLE MODIFY COLUMN ignores default
Submitted:	14 Oct 2004 18:04	Modified:	11 Nov 2004 21:24
Reporter:	Matt Solnit	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.21	OS:	Windows (Windows XP Professional SP1)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
If you add a column to a table with existing rows, and that column has a default, it will be applied to any NULL value.

If you modify a column to make it NOT NULL and include a default, it will not be applied to NULL values.

See "How to repeat"

How to repeat:
mysql> select * from a;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> ALTER TABLE `a` MODIFY `a` DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00
Z';
Query OK, 1 row affected (0.11 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> select * from a;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.02 sec)

mysql> ALTER TABLE `a` ADD `b` DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00Z';

Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from a;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 0000-00-00 00:00:00 | 1900-01-01 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Suggested fix:
MODIFY COLUMN should behave as ADD COLUMN

I verified this on windows 2000 SMP and RH 9 linux (Linux booty 2.4.21 #12 SMP Thu Aug 14 00:
49:40 EDT 2003 i686 i686 i386 GNU/Linux) with MySQL 4.0.21 and 4.1.5.  I also verified that it 
happens with myisam and innodb tables.

Here's a create table to use:

create table `a` (a DATETIME NULL);
insert into `a` values (now()), (NULL);

I understand that any bug fix for this issue is under debate.  I am suprised.  Certainly this is the way SQL Server behaves:

> create table foo( a  int )
> go
> insert into foo ( a ) values ( 1 )
> go
> alter table foo add b datetime default '1900-01-01' not null
> go
> select * from foo
>
> a           b
> ----------- ------------------------------------------------------
> 1           1900-01-01 00:00:00.000
> (1 row(s) affected)
>

I am not sure if there is a SQL standard on this, but SQL Server's behavior certainly seems correct to me.

If the default MySQL behavior is not modified, we will at least need some way to allow it using a command-line option or special ALTER syntax.  We cannot go on using custom builds forever.

Hi!

A bug state "In review" does not mean whether or not bug should be fixed.

When it is in that stage, then it has been agreed already to fix it.

"In review" means that developer's code for the fix awaits the approval.

There are always several way of doing server code and many ways to code, and we 
must choose the best possible for our product.

We expect that a patch (corrected or not) will be applied in the near future.

Thanks Sinisa!

4.0 and 4.1 behave as expected. 
5.0 has a bug in strict mode which needs fixing.

The bug as reported here on MySQL 4.0 is the expected and designed behaviour. 
 
MySQL 5.0 should prevent the ALTER operation providing that the server is in  
strict 'traditional' mode, which adheres to the SQL standard. (Bug#6562)