Bug #14693 Cannot create a new column default after column created

Description:
Hi,

In earlier versions of MySQL I've been able to create a table and then set default values for columns e.g.

create table mytable (mycol int(10) not null);
alter table mytable alter column mycol set default 0;

In MySQL 5.0, the alter table returns 'Query OK, 0 rows affected (0.05 sec)' and if I desc the table it shows the default as not set. I can set defaults at the time I create tables, but my scripts (ported from MSSQL and Oracle) do it as a second stage.

This appears to be a fault.

How to repeat:
Key the following...

create table mytable (mycol int(10) not null);
alter table mytable alter column mycol set default 0;

Thank you for a bug report. I was able to repeat it with both 5.0.15 on Windows and with 5.0.16-BK (ChangeSet@1.1957.1.18, 2005-11-03 20:29:21+02:00, jani@ua141d10.elisa.omakaista.fi) on Linux:

mysql> use test;
Database changed
mysql> create table mytable (mycol int(10) not null);
Query OK, 0 rows affected (0.25 sec)

mysql> alter table mytable alter column mycol set default 0;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytable;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| mycol | int(10) | NO   |     |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.15 sec)

mysql> alter table mytable alter column mycol set default 1;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytable;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| mycol | int(10) | NO   |     |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into mytable values();
ERROR 1364 (HY000): Field 'mycol' doesn't have a default value

As a workaround, I recommend you to use more standard way (same as in Oracle, for example) to modify column properties:

mysql> alter table mytable modify mycol int(10) not null default 1;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytable;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| mycol | int(10) | NO   |     | 1       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

It just works, as in any database.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32685

fixed in 5.0.17

Noted in 5.0.17 changelog.