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.

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

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk