Commented by: Sean Leyne (seanleyne)

The functionality is entirely consistent with SQL standard. Default values are only assigned when rows are inserted. Default is not assigned to existing rows via alter. If that is desired, then developer must use appropriate UPDATE statement.

Commented by: Nick (nick)

>Default is not assigned to existing rows via alter.
Please see my example more accurately.

Commented by: @dyemanov

What about backward compatibility? ALTER TABLE for nullable fields (regardless with or without DEFAULT) now works the same way as it did prior to FB 3.0.

Commented by: Nick (nick)

But for not nullable fields it now works different way. What about backward compatibility and common sense? :)
Maybe add something like
"add field F1 integer default 1 with apply"
?

Commented by: Sean Leyne (seanleyne)

common sense <> SQL conformance

Commented by: @dyemanov

Priorly it was impossible to add a NOT NULL field and have a database in a valid state. So we don't have much reasons to cry about backward compatibility in this case.

Anyway, your ovservation has some sense. Quick look at the SQL spec reveals:

<add column definition>
General Rules
1) The column defined by the <column definition> is added to T.
2) Let C be the column added to T.
Case:
a) If C is a generated column, ...
b) Otherwise, C is a base column.
Case:
i) If C is an identity column, ...
ii) Otherwise, every value in C is the default value for C.

So Sean seems to be wrong regarding the SQL standard.

Commented by: Sean Leyne (seanleyne)

Dmitry,

Is this a recent SQL change?

{Trying to keep up with or understand the SQL standard is a moving target}

Doesn't your last comment invalidate your previous comment about backward compatibility and/or same as previous FB versions?

Commented by: @asfernandes

Previous versions allowed that syntax but was problems. The value assigned was not stored, so a default applied may have its value changed when read.

The new behavior stores the default value within the table formats so not all records need to be updated.

Oracle uses the same syntax a behavior.

I feel no need for any change now, and don't think this is a bug.

Commented by: Nick (nick)

>I feel no need for any change now, and don't think this is a bug
alter table T add field F integer default 1; -- leaves F = NULL
alter table T add field F integer default 1 not null; -- sets F = 1
Field was updated by "not null" statement? :)

Commented by: @dyemanov

This is surely not a bug, maybe an improvement request. From the Oracle docs:

"If you are adding a new column to the table and specify the default value, then the database inserts the default column value into all rows of the table." Of course, populating the entire table is overkill, but perhaps we could use the same approach as we do for NOT NULL fields.

The question is whether we should. On the one side, we have backward compatibility, on the other side is SQL standard compliance.

Commented by: Sean Leyne (seanleyne)

Reopened issue, changed Type to "New Feature" and edited Summary to reflect the actual problem.

Commented by: @dyemanov

Sean, I don't have older standard versions at hands to check. My comments don't invalidate each other, they're just an indication that both options are valid and we cannot easily reject this ticket as wrong / non-standard.

BTW, MS SQL supports both modes and resolves this issue using the optional WITH VALUES clause.

Commented by: @mrotteveel

I looked it up in SQL:1992 and it says (11.11 <add column definition>):

"
1) The column defined by the <column definition> is added to T.
2) Let C be the column added to T. Every value in C is the default value for C.
"

In other words the current behavior in 2.5 and earlier is incorrect: it should apply the default when adding the column, which I also think this is the logical approach.

We certainly can't have a situation in Firebird 2.5 (and 3.0) where having a DEFAULT combined with a NOT NULL behaves differently from a plain DEFAULT, in that case I would suggest to fix the existing behavior to be in line with the SQL standard.

I don't think we should keep the old behavior just for backwards compatibility sake (a new major version should be allowed to break certain expectations, especially as I think that people actually expect that this will populate the column!).

BTW: Note that the behavior described in the ticket for DEFAULT with NOT NULL already exists in Firebird 2.5, see CORE4351