On 25-4-2017 17:43, Lester Caine lester@... [firebird-support]
wrote:

> On 25/04/17 12:51, Maxi maxirobaina@... [firebird-support] wrote:
>> I expect the value assigned by DEFAULT when I add the new field is
>> still there.
>
> There has been various discussions on this area ...
>
> If you add 'NOT NULL' to an existing field, then you need to populate
> the existing fields with a value in the absence of a default. In my book
> the existing records start the update for adding a new field with simple
> new null records, and FROM A GOOD PRACTICE VIEW I would expect to
> populate these empty fields under my control, so the idea that adding
> the 'DEFAULT' to a field THEN populates existing fields 'magically' is
> wrong, and the way the 'magic' bit would work depends on the order you
> process things anyway.
>
> It's not a 'bug' but rather preference and I'm with SET that the process
> should include a managed UPDATE rather than a magic one ...

This is not about adding a NOT NULL or a default to an existing field,
it is about adding a whole new column (not null) with a default to an
existing table, and then dropping that default.

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

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.

Mark
--
Mark Rotteveel