Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Ann W. H. <aha...@ib...>]

Adriano dos Santos Fernandes wrote:
> Ann W. Harrison wrote:
>> Adriano dos Santos Fernandes wrote:
>>> So I think on an alternate way. At field creation time, its default 
>>> value may be written in RDB$FORMATS. When reading a NOT NULL field
>>> with a DEFAULT, the value written in the format version of the
>>> record is returned. ...
>>>
>> How would you handle the case of a default of USER or CURRENT_TIME?
>>
> For DML, DEFAULT applies in the moment of INSERT.
> 
> My feeling is that for DDL, the new field of existing records should have the DEFAULT value evaluated in the moment of the DDL command is executed. Storing it value in the format allows that.
> 

Here's the problem I see, and it may exist already.

Create a table, which creates format 1 for that table.
Store a bunch of records.

Add a NOT NULL field with a default value of 'ABC',
which creates format 2 for the table.  The new format
includes the default value.

Now if you read records for that table, the new field's
value is 'ABC'.

Now alter the table, changing the default value to '123',
creating a new format 3 which includes the new default
for that field.

Read the same records again and the value is probably going
to be '123'.


Cheers,

Ann