On 2017-03-24 07:26, Vlad Khorsun wrote: > 24.03.2017 7:53, Vlad Khorsun wrote: >> 24.03.2017 1:29, Mark Rotteveel wrote: > ... >>> The column was created with a default, which means that existing rows >>> will get that value, >> >> Engine doesn't assing values to a new field, i.e. there is no >> implicit UPDATE of >> the existing records. This is strong point of the engine, btw. >> >>> afaik it shouldn't change if the default >>> later is changed. Is there a requirement in the SQL standard that >>> supports your expectation? >> >> I doubt SQL standard describes Firebird multiversion metadata >> handling > > > -------------------------------- > 11.11 <add column definition> > > Function > Add a column to a table. > > Format > <add column definition> ::= > ADD [ COLUMN ] <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, then let TN be the <table > name> immediately contained in the containing > <alter table statement>, let CN be the <column name> > immediately contained in <column definition>, > and let GE be the generation expression included in the > column descriptor of C. The following <update > statement: searched> is executed without further Syntax > Rule or Access Rule checking: > > UPDATE TN SET CN = GE > > b) Otherwise, C is a base column. > Case: > i) If C is an identity column, then for each row in > T let CS be the site corresponding to C and let > NV be the result of applying the General Rules of > Subclause 9.23, “Generation of the next value > of a sequence generator”, with the sequence > generator descriptor included in the column > descriptor of C as SEQUENCE. > > Case: > 1) If the declared type of C is a distinct type > DIST, then let CNV be DIST(NV). > 2) Otherwise, let CNV be NV. > > The General Rules of Subclause 9.2, “Store > assignment”, are applied with CS as TARGET and > CNV as VALUE. > ... > ii) Otherwise, every value in C is the default value > for C. > -------------------------------- > > I see that table is updated if generated or identity column is > added. > For other cases there is no such requirement.
To me 2ii) means that the behaviour should be that the newly added column **has** the value of the default clause, this can also be inferred from 11.5 <default clause>. And I think clause 4 is relevant: """ 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. """ If the column had been added with create table, but rows had been inserted without values for that column, then those rows would have had the default as the actual value, and those would not be changed by subsequent alteration of the default. And 11.13 <set column default clause> does not state that the value of existing columns should be changed. In other words: the current behaviour in Firebird 3 is the correct behaviour as expected by the SQL standard. I do not think we should revert that for reasons of preserving legacy bugs. I will take a more in depth look this weekend. Mark ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
