On 26/03/17 22:43, Vlad Khorsun wrote:
>> That is what I would expect to see ...
>> As I stated in SET's reply I would expect to manage the change of a
>> default as appropriate, but the fall back should always be the stored
>> values? You would only see a 'new' default value if you added it, not if
>> you simply changed the metadata?
>If i understand you correctly - fb3 works the way you expect. You may look
> at my sample at CORE-5507 to see how it works.
I'm specifically saying that this bug report is wrong! But perhaps for
different reasons? When the F1 field was added, the first record needed
an additional field entry adding to comply with the 'F1 NOT NULL'
constraint. So *I* would have expected to add an update to provide a
value to all of the 'new' 'null' F1 fields on already existing records.
That may be 'XYZ' as per the created default when the NOT NULL
constraint was added, or something like 'No Default' to retain the
pre-default history. I've changed the logic of how this works at this
point so both cases now need handling!
When
ALTER TABLE T ADD F1 VARCHAR(16) DEFAULT 'XYZ' NOT NULL;
COMMIT;
was actioned, the unpopulated F1 fields have to comply with the new 'NOT
NULL' constraint ... although the alternative of leaving out the 'NOT
NULL' leaves the option to add the value later and removes the blocking
constraint. At this point the table is compliant because F1 exists in
all records.
Compliance with the constraint is required at this point, so why would
you expect when changing the default later that the values of the
earlier records then magically change? Without the NOT NULL they would
stay as 'null', but with the NOT NULL they already have to have had the
conflict resolved ... and have a fixed value of some sort, either the
'XYZ' or an alternate that fits the business logic. So I am quite happy
that the 'actual' result from CORE-5507 is correct but I am thinking
that at some point we lost the specific requirement to use
ALTER TABLE T ADD F1 VARCHAR(16) DEFAULT 'XYZ' NOT NULL;
UPDATE TABLE SET F1 = 'No Default' WHERE F1 IS NULL;
COMMIT;
Ignoring the adding of the new default, the old records have to comply
with the new constraint, but it sounds as if there is some 'magic' that
is messing up the handling of the stored records to carry out this
specific UPDATE? This is perhaps the mention of 'format', where as far
as I am concerned there is only one format, the current record layout.
I am more than happy that the SQL standard is written on the basis that
when a change is made to a record it is physically implemented so that
the 'F1' field will be created for every old record and would default to
a new default ... but there is probably some discussion as to if that
needs the explicate UPDATE or is magically implied by the use of the
COMMIT without any other action. If you have some reason to distinguish
between the older records and the 'XYZ' default records, then this
should be managed specifically, and leaving these as 'null' values
without the 'NOT NULL' constraint would perhaps be appropriate for this
business logic? Rather than some hidden mechanism that distinguishes
different versions of the same record?
--
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
--
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
