MySQL Bugs: #62353: alter table modify column_definition doesn't change former default value

Bug #62353	alter table modify column_definition doesn't change former default value
Submitted:	5 Sep 2011 15:24	Modified:	16 Jan 2012 20:47
Reporter:	Alberto Massidda	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.1.3	OS:	Linux (Ubuntu 11.04)
Assigned to:		CPU Architecture:	Any
Tags:	change, column_definition

Description:
the command 

"alter table [table name] modify [column name] column_definition [new column definition] default [new default]" 

doesn't change retroactively rows who have the field (belonging to the changing column) set to the former default type.
E.g.:
when a column of type 

INT default NULL 

is changed to 

FLOAT default '1.0' 

all the rows which had that INT field set to NULL do not change automatically to '1.0', but remains at NULL.

This is a arguably wrong behaviour, since it may lead to inconsistencies to homogeneity of the dataset (for example we loose the information about how many rows were set to the default value, but it may lead to more serious putfalls since from now on the default value is expected to be the new one, not the old one)

How to repeat:
1)create a column of any type with default NULL; 
2)populate column with some values;
3)change column definition and default value

Suggested fix:
the command should retroactively change all the rows that had that field set to the former default value to the new value

What if column was explicitly set to the same value as default, say, to NULL in your example? Should its value be also changed by your ALTER?

>What if column was explicitly set to the same value as default, say, 
>to NULL in your example? Should its value be also changed by your ALTER?

It makes sense, indeed: if you explicitly set by hand that field to a value which, coincidence, is the same as the default then it means that you wanted it to be... the default value.
Now, if for some business reason you decide to change the default value of that column you also should make sure that all the rows that had that default value converge to the new default.

Otherwise there's the risk that your application may encounter the... "wrong" default value, now knowing how to deal with it or, worse, dealing with it in the wrong way

Can you name any popular RDBMS that implements semantics you described? 

I do not see anything like that in any published SQL standard I can find (see http://kb.askmonty.org/en/default-clause, http://kb.askmonty.org/en/alter-table-statement#alter-column-set-default-clause etc).

According to http://www.dba-oracle.com/t_oracle_column_default_value.htm , it seems that Oracle did it until 11g:

"This optimized behavior differs from earlier releases, when as part of the ALTER TABLE operation Oracle Database updated each row in the newly created column with the default value, and then fired any AFTER UPDATE triggers defined on the table."

Even though I admin that I'm not completely sure about what he really means.

Apart from that, does it make any difference? I mean, it's a matter of internal data coherence and it's not tied to the stardard or to other available products

Thank you for the feedback.

I think this would be dangerous addition.

Anyway if you need such behavior you can manually run query UPDATE table SET field=NEW_DEFAULT WHERE field=OLD_DEFAULT

This can be verified as feature request though.

But I'd prefer to have different kind of ALTER, say, ALTER ... UPDATE DEFAULT