On 25-4-2017 18:21, Lester Caine lester@... [firebird-support]
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).
>
> 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.

I find the opposite a lot more logical. I create a new field with a
default, the existing records should get the value with that default. If
you want the value of a field to be null for existing rows, then first
add it without a default (which implies default null), and then alter
the default.

This is what the SQL:2011 standard says:
"""
2) Let C be the column added to T.
Case:
a) [about generated columns]
[..]
b) Otherwise, C is a base column.
Case:
i) [about identity columns]
[..]
ii) Otherwise, every value in C is the default value
for C.
[..]
4) In all other respects, the specification of a <column definition> in
an <alter table statement> has the same effect as specification of the
<column definition> in the <table definition> for T would have had.
"""

>> 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.
>
> 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.

You are misinterpreting my words. With virtual I mean that the value
might not yet be actually persisted with the record itself, but that for
all intents and purposes the system behaves as if it **is**. This is BTW
what Firebird currently does with format versions.

Mark
--
Mark Rotteveel