> Apr 25, 2017, at 12:21 PM, Lester Caine lester@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

>
> On 25/04/17 16:50, Mark Rotteveel mark@...
> [firebird-support] wrote:
>> The SQL standard is quite clear at what needs to happen: adding a new
>> column with a default should behave as if that column has existed from
>> the original create table (btw: irrespective of the NOT NULL constraint,
>> something that if I'm not mistaken also Firebird doesn't do correctly).

Right. When a record is read Firebird converts it from its declared format to the current format ignoring intervening formats. ALTER TABLE does not cause changes to existing rows in the table (in general, there may be exceptions). The result is the appearing and disappearing default values.

>
> Exactly the problem in my book. If you add 'DEFAULT' without a 'NOT
> NULL', then in my book the unpopulated fields should remain NULL unless
> I populate them. When you now add creating the field later then things
> should be consistent but there is no consistent way of handling things
> based on the SQL standard inconsistencies.
>
>> This has the effect that all existing records should get the default
>> value as if they had that value from the start (either stored or
>> virtual). It also means that a subsequent alteration of the default (new
>> value or dropping the default), should not lead to changes to the value
>> of those rows that existed before the column was added with; the columns
>> need to retain the original default.

If Firebird were to convert a record from its stored format through each intervening format, the result would be more logical but it would be a change to a behavior that's over 30 years old. And there are advantages to the current behavior. If you alter a table in a way that invalidates existing values, another alter table can undo the damage.

>
> The argument that other engines put forward is this idea that a record
> does not need to store a full set of fields, some can be 'virtual' and
> only exist when something is stored in them. I HOPE that this is not
> something that Firebird plans to adopt? In my book the 'original value'
> is always 'NULL' unless other rules require something replaces it, and
> an empty field magically showing some default value is not a safe way of
> working.

Firebird doesn't store null fields, instead it stores an array of bits that indicate whether or not a field is null. Between that, compression, and computed fields, there's a lot of magic going on.

Good luck,

Ann