Adding new column via ALTER TABLE should assign default value (not just to new NOT NULL columns) [CORE4525]

[firebird-automations]

Submitted by: Nick (nick)

Votes: 1

create table T (Id integer);
insert into T (Id) values (1);
insert into T (Id) values (2);
alter table T add field F1 integer default 1 not null;
select F1 from T
--
1
1

alter table T add field F2 integer default 1;
select F2 from T
--
null
null

snapshot 31273

[firebird-automations]

Modified by: Nick (nick)

summary: add field with default value to table with data has not intuitive behavior => add field with default value to table with data has non-intuitive behavior

[firebird-automations]

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.

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

[firebird-automations]

Commented by: Nick (nick)

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

[firebird-automations]

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.

[firebird-automations]

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"
?

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

common sense <> SQL conformance

[firebird-automations]

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.

[firebird-automations]

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?

[firebird-automations]

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.

[firebird-automations]

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? :)

[firebird-automations]

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.

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Won't Fix [ 2 ] =>

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

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

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

issuetype: Bug [ 1 ] => New Feature [ 2 ]

summary: add field with default value to table with data has non-intuitive behavior => Adding new column via ALTER TABLE should assign default value (not just to new NOT NULL columns)