Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Lester Caine]

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

25.03.2017 0:16, Lester Caine wrote:
> On 24/03/17 21:42, Vlad Khorsun wrote:
>> 24.03.2017 21:36, Lester Caine wrote:
>>> On 24/03/17 17:57, Vlad Khorsun wrote:
> What must be done is specified exactly. And we should suppose that in
> all other aspects data stored in database should not be changed -
What data is *stored* ?
>>> For any field, the data that is loaded initially or later modified. And
>>> I REPEAT ... if the field was originally NULL it should remain NULL
>>> until a specific record update modifies it. It should not be returning
>>> some DEFAULT value that was added later UNLESS the constraints are
>>> changed to require the NULL value to be populated, but that should not
>>> be populated 'magically' and the SQL spec does not override that basic
>>> function of a value in an existing record? The DEFAULT value should only
>>> be used to replace a NULL value when a record is added ... full stop.
>
>>Engine substitute default value if and only if NOT NULL field was added
>> and DEFAULT value was specified. Engine not allow to add NOT NULL field
>> without DEFAULT. If NULLable field was added engine will return NULL's
>> for that field at existing old records despite of precence of DEFAULT value.
>>
>>Is it OK ?
>
> 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.

Regards,
Vlad

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Alex Peshkoff]

On 03/24/17 20:57, Vlad Khorsun wrote:
> 24.03.2017 19:39, Alex Peshkoff wrote:
>> On 03/24/17 20:26, Vlad Khorsun wrote:
>>> 24.03.2017 17:57, Alex Peshkoff wrote:
 On 03/24/17 17:53, Vlad Khorsun wrote:

>   So far we have no agreement on what is "correct result". Current 
> implementation
> changed well known old behaviour not claiming it as a bug. I'd say it 
> looks like
> a bug itself.
 Vlad, looking at sql2008 (part2, foundation, 11.10 - alter table
 statement) one can read in  that when new column
 is added, and this is base (not generated) column, and this is not
 identity column then every value in this column is it's default value.
 And it's mentioned in  >>> clause> that new default value is stored in column descriptor, but
 nobody mentions that it should be changed for existing records.

 I.e. according to standard old (interbase origined) behavior was a bug
 and current one is correct.
>   >>
>>>  I already show that info from standard but can't make same conclusion.
>>> I see it as not specified.
>> Well, it's also not specified that rand() output should not be placed
>> into a column for which default value was changed :-)
> I consider standard as document which we should read curefully and follow
> strictly. Such documents should be created in a such way to not allow 
> ambiguity
> and describe every possible and not possible conditions. Unfortunately, this 
> is
> not always true.

Yes, it's written by people. When we look at our favorite p.11.11 it's 
clear that action for different types of columns is expressed 
differently. I agree that 'update statement is executed ' and 'every 
value in column is the default value' are different, thay may mean same 
or may not. Regarding correct treatment of them it's better to trust our 
native english-speaking people - minor language differences may be 
important here.



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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Lester Caine]

On 24/03/17 21:42, Vlad Khorsun wrote:
> 24.03.2017 21:36, Lester Caine wrote:
>> On 24/03/17 17:57, Vlad Khorsun wrote:
 What must be done is specified exactly. And we should suppose that in
 all other aspects data stored in database should not be changed -
>>>What data is *stored* ?
>> For any field, the data that is loaded initially or later modified. And
>> I REPEAT ... if the field was originally NULL it should remain NULL
>> until a specific record update modifies it. It should not be returning
>> some DEFAULT value that was added later UNLESS the constraints are
>> changed to require the NULL value to be populated, but that should not
>> be populated 'magically' and the SQL spec does not override that basic
>> function of a value in an existing record? The DEFAULT value should only
>> be used to replace a NULL value when a record is added ... full stop.

>Engine substitute default value if and only if NOT NULL field was added
> and DEFAULT value was specified. Engine not allow to add NOT NULL field
> without DEFAULT. If NULLable field was added engine will return NULL's
> for that field at existing old records despite of precence of DEFAULT value.
> 
>Is it OK ?

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?

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

24.03.2017 21:36, Lester Caine wrote:
> On 24/03/17 17:57, Vlad Khorsun wrote:
>>> What must be done is specified exactly. And we should suppose that in
>>> all other aspects data stored in database should not be changed -
>>What data is *stored* ?
>
> For any field, the data that is loaded initially or later modified. And
> I REPEAT ... if the field was originally NULL it should remain NULL
> until a specific record update modifies it. It should not be returning
> some DEFAULT value that was added later UNLESS the constraints are
> changed to require the NULL value to be populated, but that should not
> be populated 'magically' and the SQL spec does not override that basic
> function of a value in an existing record? The DEFAULT value should only
> be used to replace a NULL value when a record is added ... full stop.

   Engine substitute default value if and only if NOT NULL field was added
and DEFAULT value was specified. Engine not allow to add NOT NULL field
without DEFAULT. If NULLable field was added engine will return NULL's
for that field at existing old records despite of precence of DEFAULT value.

   Is it OK ?

Regards,
Vlad


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Mark Rotteveel]

On 2017-03-24 21:14, Mark Rotteveel wrote:
> 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 .
> 
> And I think clause 4 is relevant:
> 
> """
> 4) In all other respects, the specification of a  in
> an  has the same
> effect as specification of the  in the  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  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.

Another reason I just came up with:

Consider scenario:

1. create table
2. add rows
3. add column with default
4. backup + restore database
5. alter default of column added in step 3

After step 5 the values should be identical if the step 4 had been 
executed or not.

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Mark Rotteveel]

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 
> 
> Function
> Add a column to a table.
> 
> Format
>  ::=
>   ADD [ COLUMN ] 
> ...
> General Rules
> 1) The column defined by the  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  name> immediately contained in the containing
>  , let CN be the 
> immediately contained in ,
>  and let GE be the generation expression included in the
> column descriptor of C. The following   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 .

And I think clause 4 is relevant:

"""
4) In all other respects, the specification of a  in 
an  has the same
effect as specification of the  in the  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  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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Lester Caine]

On 24/03/17 17:57, Vlad Khorsun wrote:
>> What must be done is specified exactly. And we should suppose that in
>> all other aspects data stored in database should not be changed -
>What data is *stored* ?

For any field, the data that is loaded initially or later modified. And
I REPEAT ... if the field was originally NULL it should remain NULL
until a specific record update modifies it. It should not be returning
some DEFAULT value that was added later UNLESS the constraints are
changed to require the NULL value to be populated, but that should not
be populated 'magically' and the SQL spec does not override that basic
function of a value in an existing record? The DEFAULT value should only
be used to replace a NULL value when a record is added ... full stop.

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

24.03.2017 19:39, Alex Peshkoff wrote:
> On 03/24/17 20:26, Vlad Khorsun wrote:
>> 24.03.2017 17:57, Alex Peshkoff wrote:
>>> On 03/24/17 17:53, Vlad Khorsun wrote:
>>>
  So far we have no agreement on what is "correct result". Current 
 implementation
 changed well known old behaviour not claiming it as a bug. I'd say it 
 looks like
 a bug itself.
>>> Vlad, looking at sql2008 (part2, foundation, 11.10 - alter table
>>> statement) one can read in  that when new column
>>> is added, and this is base (not generated) column, and this is not
>>> identity column then every value in this column is it's default value.
>>> And it's mentioned in  >> clause> that new default value is stored in column descriptor, but
>>> nobody mentions that it should be changed for existing records.
>>>
>>> I.e. according to standard old (interbase origined) behavior was a bug
>>> and current one is correct.
 >>
>> I already show that info from standard but can't make same conclusion.
>> I see it as not specified.
>
> Well, it's also not specified that rand() output should not be placed
> into a column for which default value was changed :-)

   I consider standard as document which we should read curefully and follow
strictly. Such documents should be created in a such way to not allow ambiguity
and describe every possible and not possible conditions. Unfortunately, this is
not always true.

   As for rand() output - i already show example when value returned by known 
empty
field with default can't be found at system dictionary.

> What must be done is specified exactly. And we should suppose that in
> all other aspects data stored in database should not be changed -

   What data is *stored* ?

> without that addition any operation on database can randomly modify it
> in unpredictable way.

   Sure. But not in our case ;)

Regards,
Vlad


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

24.03.2017 14:02, Ann Harrison wrote:
> It's been a long time, but I think that's an ancient behavior that Jim and I 
> argued about many years ago.
 > Maybe even in Rdb$ELN, InterBase's ancestor.
>
> Unless my memory fails me (again) the internal format rectifier doesn't go 
> through all intermediate formats,
 > it just converts from the stored format to the format requested.

   Yes, it works this way when convert fields that present in both on-disk and 
current formats.
And a bit different for default values.

> I came up with some places where not considering
 > the intermediate formats produced different results, but most were errors. 
 > Jim thought it was idiotic to go through
 > a lot of extra work to discover errors that had been corrected.  My use case 
 > may have involved changing a field
 > from varchar to double and back when the field contains alphabetic 
 > characters.  The non-errors might be that changing
 > a field from double (format 1) to float (format 2) and back (format 3) had 
 > the result that format 1 records had
 > truncated values (at the low end) when seen as format 2, and went back to 
 > full precision when viewed as format 3.
>
> The case at hand includes a relatively new feature - new fields that are not 
> null and include a default.
>
>  Create a table with no field called "NewField".  (Format 1)
>  Store a record with the primary key of 1.
> Alter the table adding "NewField", not null, default "Ann".  (Format 2)
> Store a record with the primary key 2 and no value for "New Field"
> Alter the record again, changing the default to "Jim". (Format 3)

   Currently engine doesn't create new format if changes are at default values 
only.
And this was one of my point to consider.

> Store a record with the primary key 3 and no value for "New Field"
> Read all the records.
>
>  1 Jim
> 2 Ann
> 3 Jim
>
> The situation is that Firebird converts record 1 from format 1 to format 3 
> without going through format 2.
 > If it had gone through format 2, the initial default value would be applied 
 > and you'd see
>
> 1 Ann
> 2 Ann
> 3 Jim
>
> Should the behavior be changed?  It's ancient.  It has benefits (e.g. 
> Changing a column from double to float and back).
 > The benefits are in dumb cases.  The new behavior might be more standard 
 > conformant, if the standard allows addind Not
 > Null columns with defaults and the Standards Committee assumed that default 
 > values were added in the most crude way possible.

   I failed to see how standard defines such cases, sorry.

Thanks for the story,
Vlad

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

24.03.2017 17:08, Adriano dos Santos Fernandes wrote:
> Em 24/03/2017 11:58, Vlad Khorsun escreveu:
>>> Well, actually seems we do not known why you started this topic mixing
>>> implementation details with users behaviour.
>>
>>Because you left blr filter for rdb$format broken and i going to fix it
>> and start palying with default values.
>>
>
> Where is the message that you reported it to me and I ignored?

   Now i must report to you ?

   But.. ok, look at CORE-5395, for example. There was more cases, don't force 
me
to remember it all.

> PS: Sorry, my code is not perfect like you one, that never has a single bug.

   You asked i answered

Vlad

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Alex Peshkoff]

On 03/24/17 20:26, Vlad Khorsun wrote:
> 24.03.2017 17:57, Alex Peshkoff wrote:
>> On 03/24/17 17:53, Vlad Khorsun wrote:
>>
>>>  So far we have no agreement on what is "correct result". Current 
>>> implementation
>>> changed well known old behaviour not claiming it as a bug. I'd say it looks 
>>> like
>>> a bug itself.
>> Vlad, looking at sql2008 (part2, foundation, 11.10 - alter table
>> statement) one can read in  that when new column
>> is added, and this is base (not generated) column, and this is not
>> identity column then every value in this column is it's default value.
>> And it's mentioned in  > clause> that new default value is stored in column descriptor, but
>> nobody mentions that it should be changed for existing records.
>>
>> I.e. according to standard old (interbase origined) behavior was a bug
>> and current one is correct.
> I already show that info from standard but can't make same conclusion.
> I see it as not specified.

Well, it's also not specified that rand() output should not be placed 
into a column for which default value was changed :-)
What must be done is specified exactly. And we should suppose that in 
all other aspects data stored in database should not be changed - 
without that addition any operation on database can randomly modify it 
in unpredictable way.



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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

24.03.2017 17:12, Adriano dos Santos Fernandes wrote:

...

> Looks like you do not understand how it works. Re-read thread "Adding
> NOT NULL fields with DEFAULT" from 2009.

   That thread contains more details, thanks for recall it.

>>> Correct result is priority, and current implementation detail does it
>>> very well
>>
>>So far we have no agreement on what is "correct result".
>
> And have no one agreeing with you so far.

   You would be wondered but i discussed it with Dmitry before create a ticket.

>> Current implementation
>> changed well known old behaviour not claiming it as a bug. I'd say it looks 
>> like
>> a bug itself.
>>
>
> Changed a bugged design/implementation since 2009. Looks a bit late to
> say this.

   It is never late to fix something if it is wrong. And to add some docs to 
avoid
such conversations in the future.

Vlad

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

24.03.2017 17:57, Alex Peshkoff wrote:
> On 03/24/17 17:53, Vlad Khorsun wrote:
>
>> So far we have no agreement on what is "correct result". Current 
>> implementation
>> changed well known old behaviour not claiming it as a bug. I'd say it looks 
>> like
>> a bug itself.
>
> Vlad, looking at sql2008 (part2, foundation, 11.10 - alter table
> statement) one can read in  that when new column
> is added, and this is base (not generated) column, and this is not
> identity column then every value in this column is it's default value.
> And it's mentioned in   clause> that new default value is stored in column descriptor, but
> nobody mentions that it should be changed for existing records.
>
> I.e. according to standard old (interbase origined) behavior was a bug
> and current one is correct.

   I already show that info from standard but can't make same conclusion.
I see it as not specified.

Regards,
Vlad


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Alex Peshkoff]

On 03/24/17 17:53, Vlad Khorsun wrote:

> So far we have no agreement on what is "correct result". Current 
> implementation
> changed well known old behaviour not claiming it as a bug. I'd say it looks 
> like
> a bug itself.

Vlad, looking at sql2008 (part2, foundation, 11.10 - alter table 
statement) one can read in  that when new column 
is added, and this is base (not generated) column, and this is not 
identity column then every value in this column is it's default value. 
And it's mentioned in   that new default value is stored in column descriptor, but 
nobody mentions that it should be changed for existing records.

I.e. according to standard old (interbase origined) behavior was a bug 
and current one is correct.


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Lester Caine]

On 24/03/17 15:12, Adriano dos Santos Fernandes wrote:
>> Current implementation
>> changed well known old behaviour not claiming it as a bug. I'd say it looks 
>> like
>> a bug itself.
>>
> Changed a bugged design/implementation since 2009. Looks a bit late to
> say this.

I'm still running FB1.5 on some legacy sites, so keeping up with all
this new stuff is a problem. My FB3 installs simply did not work and I
don't have time to find out why, but the ONE thing I expect to be
consistent is that the data returned in a record is actually what is
stored IN that record. The IDEA that a field that can store NULL but not
actually return that in the result set is most definitely a bug. If you
want to ensure a real value is returned, then the field should be NOT
NULL and then the value stored is a real value that is only changed by a
real UPDATE of some type. Certainly if a default value is a time stamp
then it is no different to a fixed default value once a new record is added.

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Adriano dos Santos Fernandes]

Em 24/03/2017 11:53, Vlad Khorsun escreveu:
> 24.03.2017 16:31, Adriano dos Santos Fernandes wrote:
> 
>> Or better, increase the record format count, which seems as a stupid limit.
> 
>It is really better. Are you have smart idea ?
> 
>>  From another side, storing the default value inside the format is a
>> smart hack that allows to avoid updating the table. It was designed to
>> act like an implicit update but without the overhead. And considering
>> the update semantics, the new behaviour may look correct.
>
>Also true
>
>> The big question is what expectations are better suitable to our users.
>
>The one that already works for years and well known ? ;)
>

 You mean, that behaviour that a default uses CURRENT_TIME and every time
 you select it you see a different value?
>>>
>>>You should know that default value for CURRENT_TIME (or any other 
>>> allowed)
>>> expression will be value of that expression evaluated at the DDL statement
>>> run time.
>>>
>>
>> You should known how bad this worked before the change that fixed it
>> with the current behaviour.
> 
>The change is good in this aspect and at least very questionable in 
> another one.
> 
>> BTW, don't mix implementation details with expected results, please,
>> this is wasting time.
> 
>This "implementation details" is too important to ignore it. Or do you 
> prefer
> to waste time updating table every time non-nullable column with default value
> is added ?
> 

Looks like you do not understand how it works. Re-read thread "Adding
NOT NULL fields with DEFAULT" from 2009.


>> Correct result is priority, and current implementation detail does it
>> very well
> 
>So far we have no agreement on what is "correct result".

And have no one agreeing with you so far.

> Current implementation
> changed well known old behaviour not claiming it as a bug. I'd say it looks 
> like
> a bug itself.
> 

Changed a bugged design/implementation since 2009. Looks a bit late to
say this.


Adriano

PS: I'm stopping to lose time in this thread.

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Adriano dos Santos Fernandes]

Em 24/03/2017 11:58, Vlad Khorsun escreveu:
>> Well, actually seems we do not known why you started this topic mixing
>> implementation details with users behaviour.
>Because you left blr filter for rdb$format broken and i going to fix it
> and start palying with default values.
> 

Where is the message that you reported it to me and I ignored?


Adriano

PS: Sorry, my code is not perfect like you one, that never has a single bug.

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

24.03.2017 16:34, Adriano dos Santos Fernandes wrote:
> Em 24/03/2017 06:44, Vlad Khorsun escreveu:
>
>>
>>I prefer to see it works this way. Probably read-committed tx could 
>> return new default value.
>>
>
> Not only it is in no way intuitive, it would be impossible to use indexes.

   Really ? Tell us how indices is used currently, it will be funny ;)

Regards,
Vlad


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

24.03.2017 16:37, Adriano dos Santos Fernandes wrote:
> Em 24/03/2017 11:15, Vlad Khorsun escreveu:
>> 24.03.2017 15:58, Adriano dos Santos Fernandes пишет:
>>> Em 23/03/2017 20:29, Mark Rotteveel escreveu:
>>>

 actual

   ID DESCRF1
   
1 No F1 field  XYZ
2 F1 field, default XYZXYZ
3 F1 field, default ABCABC

>>>
>>> PostgreSQL: tested now - same behaviour
>>>
>>> Oracle: AFAIR tested in the past - same behaviour
>>
>>It have no sense to compare Firebird with DBMS which do implicit update
>> when altering default value.
>>
>
> It have no sense to guess about others internal behaviours.

   It is not a guess and you perfectly knows it.

> We are talking here about an *user behaviour*.

   Yes. And what ?

> Well, actually seems we do not known why you started this topic mixing
> implementation details with users behaviour.

   Because you left blr filter for rdb$format broken and i going to fix it
and start palying with default values.

> It seems you're talking about something in your brain that clashes with
> the actual user behaviour and we don't know what is.

   Sure, all around is in my brain only, even you ;)

Vlad


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Adriano dos Santos Fernandes]

> 
> Note value of the field F1 at the first record: it is expected that is should 
> be the same as latest DEFAULT value.
> Also note that 2nd and 3rd INSERTs assigns correct value to the omitted field 
> - same as latest DEFAULT value.

If you re-read thread "Adding NOT NULL fields with DEFAULT" that
originated the change, you will see that at that moment you had thinking
in the correct way :D

-
> Vlad Khorsun wrote:
>>> Ann W. Harrison wrote:
 Here's the problem I see, and it may exist already.

 Create a table, which creates format 1 for that table.
 Store a bunch of records.

 Add a NOT NULL field with a default value of 'ABC',
 which creates format 2 for the table.  The new format
 includes the default value.

 Now if you read records for that table, the new field's
 value is 'ABC'.

>>> That's ok.
>>>
 Now alter the table, changing the default value to '123',
 creating a new format 3 which includes the new default
 for that field.

 Read the same records again and the value is probably going
 to be '123'.

>>> If the record format is 2, it should return 'ABC'. If the record is updated 
>>> and written in the 
>>> format 3, 'ABC' should be written to the field so it doesn't catch the 
>>> format 3 value when 
>>> reading it.
>>
>> What if record still stored in format 1 ?
>>
> Records at format 1 (which does not have the field added in 2) should use the 
> default value stored in format 2. 
> So 'ABC' should be written when updating to format 3 too.

We have record, stored in format 1, without additional field. We
have formats 2 and 3
with different default values for the same field. When engine read
record it converts it into
most current format - format 3 in this case. The question is - what
value should be in new
field, added with format 2 ? 'ABC' or '123' ? I think - 'ABC'.
-


Adriano

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

24.03.2017 16:31, Adriano dos Santos Fernandes wrote:

> Or better, increase the record format count, which seems as a stupid limit.

   It is really better. Are you have smart idea ?

>  From another side, storing the default value inside the format is a
> smart hack that allows to avoid updating the table. It was designed to
> act like an implicit update but without the overhead. And considering
> the update semantics, the new behaviour may look correct.

Also true

> The big question is what expectations are better suitable to our users.

The one that already works for years and well known ? ;)

>>>
>>> You mean, that behaviour that a default uses CURRENT_TIME and every time
>>> you select it you see a different value?
>>
>>You should know that default value for CURRENT_TIME (or any other allowed)
>> expression will be value of that expression evaluated at the DDL statement
>> run time.
>>
>
> You should known how bad this worked before the change that fixed it
> with the current behaviour.

   The change is good in this aspect and at least very questionable in another 
one.

> BTW, don't mix implementation details with expected results, please,
> this is wasting time.

   This "implementation details" is too important to ignore it. Or do you prefer
to waste time updating table every time non-nullable column with default value
is added ?

> Correct result is priority, and current implementation detail does it
> very well

   So far we have no agreement on what is "correct result". Current 
implementation
changed well known old behaviour not claiming it as a bug. I'd say it looks like
a bug itself.

Regards,
Vlad

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Adriano dos Santos Fernandes]

Em 24/03/2017 11:15, Vlad Khorsun escreveu:
> 24.03.2017 15:58, Adriano dos Santos Fernandes пишет:
>> Em 23/03/2017 20:29, Mark Rotteveel escreveu:
>>
>>>
>>> actual
>>>
>>>   ID DESCRF1
>>>   
>>>1 No F1 field  XYZ
>>>2 F1 field, default XYZXYZ
>>>3 F1 field, default ABCABC
>>>
>>
>> PostgreSQL: tested now - same behaviour
>>
>> Oracle: AFAIR tested in the past - same behaviour
> 
>It have no sense to compare Firebird with DBMS which do implicit update
> when altering default value.
> 

It have no sense to guess about others internal behaviours.

We are talking here about an *user behaviour*.

Well, actually seems we do not known why you started this topic mixing
implementation details with users behaviour.

It seems you're talking about something in your brain that clashes with
the actual user behaviour and we don't know what is.


Adriano

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Adriano dos Santos Fernandes]

Em 24/03/2017 06:44, Vlad Khorsun escreveu:

> 
>I prefer to see it works this way. Probably read-committed tx could return 
> new default value.
> 

Not only it is in no way intuitive, it would be impossible to use indexes.


Adriano

PS: still trying to understand why you want to change a standard
behaviour to change the implementation details.

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Adriano dos Santos Fernandes]

Em 24/03/2017 11:19, Vlad Khorsun escreveu:
> 24.03.2017 16:03, Adriano dos Santos Fernandes wrote:
>> Em 24/03/2017 03:33, Vlad Khorsun escreveu:
>>
>>>
>>>Another example - i add not null column with wrong default value and
>>> going to correct this wrong default value. Should i update whole table
>>> to do it ?
>>>
>>
>> Drop the column and recreate it, fixing your error.
> 
>And make two more record formats - no, thanks
> 

So, test your changes and don't make mistakes.

Firebird will not have incorrect behaviour so users do not create more
record formats.

Or better, increase the record format count, which seems as a stupid limit.


  From another side, storing the default value inside the format is a
 smart hack that allows to avoid updating the table. It was designed to
 act like an implicit update but without the overhead. And considering
 the update semantics, the new behaviour may look correct.
>>>
>>>Also true
>>>
 The big question is what expectations are better suitable to our users.
>>>
>>>The one that already works for years and well known ? ;)
>>>
>>
>> You mean, that behaviour that a default uses CURRENT_TIME and every time
>> you select it you see a different value?
> 
>You should know that default value for CURRENT_TIME (or any other allowed)
> expression will be value of that expression evaluated at the DDL statement
> run time.
> 

You should known how bad this worked before the change that fixed it
with the current behaviour.

BTW, don't mix implementation details with expected results, please,
this is wasting time.

Correct result is priority, and current implementation detail does it
very well (until you create a wrong default and want to fix it in the
incorrect way).


Adriano

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

24.03.2017 16:03, Adriano dos Santos Fernandes wrote:
> Em 24/03/2017 03:33, Vlad Khorsun escreveu:
>
>>
>>Another example - i add not null column with wrong default value and
>> going to correct this wrong default value. Should i update whole table
>> to do it ?
>>
>
> Drop the column and recreate it, fixing your error.

   And make two more record formats - no, thanks

>>>  From another side, storing the default value inside the format is a
>>> smart hack that allows to avoid updating the table. It was designed to
>>> act like an implicit update but without the overhead. And considering
>>> the update semantics, the new behaviour may look correct.
>>
>>Also true
>>
>>> The big question is what expectations are better suitable to our users.
>>
>>The one that already works for years and well known ? ;)
>>
>
> You mean, that behaviour that a default uses CURRENT_TIME and every time
> you select it you see a different value?

   You should know that default value for CURRENT_TIME (or any other allowed)
expression will be value of that expression evaluated at the DDL statement
run time.

Regards,
Vlad


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

24.03.2017 15:58, Adriano dos Santos Fernandes пишет:
> Em 23/03/2017 20:29, Mark Rotteveel escreveu:
>
>>
>> actual
>>
>>   ID DESCRF1
>>   
>>1 No F1 field  XYZ
>>2 F1 field, default XYZXYZ
>>3 F1 field, default ABCABC
>>
>
> PostgreSQL: tested now - same behaviour
>
> Oracle: AFAIR tested in the past - same behaviour

   It have no sense to compare Firebird with DBMS which do implicit update
when altering default value.

Regards,
Vlad


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Adriano dos Santos Fernandes]

Em 24/03/2017 04:18, Dmitry Yemanov escreveu:

> 
> Also, lets consider this:
> 
> SQL> create table t (col1 int);
> SQL> insert into t values (1);
> SQL> commit;
> SQL> alter table t add col2 int default 123 not null;
> SQL> select * from t;
> 
>  COL1 COL2
>  
> 1  123
> 
> SQL> alter table t alter col2 drop default;
> SQL> select * from t;
> 
>  COL1 COL2
>  
> 1  123
> 

Also, same behaviour as PostgreSQL.


Adriano

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Adriano dos Santos Fernandes]

Em 24/03/2017 03:33, Vlad Khorsun escreveu:

> 
>Another example - i add not null column with wrong default value and
> going to correct this wrong default value. Should i update whole table
> to do it ?
> 

Drop the column and recreate it, fixing your error.


>>  From another side, storing the default value inside the format is a
>> smart hack that allows to avoid updating the table. It was designed to
>> act like an implicit update but without the overhead. And considering
>> the update semantics, the new behaviour may look correct.
> 
>Also true
> 
>> The big question is what expectations are better suitable to our users.
> 
>The one that already works for years and well known ? ;)
> 

You mean, that behaviour that a default uses CURRENT_TIME and every time
you select it you see a different value?


Adriano

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Adriano dos Santos Fernandes]

Em 23/03/2017 20:29, Mark Rotteveel escreveu:

> 
> actual
> 
>   ID DESCRF1
>   
>1 No F1 field  XYZ
>2 F1 field, default XYZXYZ
>3 F1 field, default ABCABC
> 

PostgreSQL: tested now - same behaviour

Oracle: AFAIR tested in the past - same behaviour

Personal opinion: I see the "expected" behaviour in no way intuitive.

For me there is nothing to discuss.


Adriano

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Ann Harrison]

It's been a long time, but I think that's an ancient behavior that Jim and I 
argued about many years ago.  Maybe even in Rdb$ELN, InterBase's ancestor.  

Unless my memory fails me (again) the internal format rectifier doesn't go 
through all intermediate formats, it just converts from the stored format to 
the format requested.  I came up with some places where not considering the 
intermediate formats produced different results, but most were errors. Jim 
thought it was idiotic to go through a lot of extra work to discover errors 
that had been corrected.  My use case may have involved changing a field from 
varchar to double and back when the field contains alphabetic characters.  The 
non-errors might be that changing a field from double (format 1) to float 
(format 2) and back (format 3) had the result that format 1 records had 
truncated values (at the low end) when seen as format 2, and went back to full 
precision when viewed as format 3.

The case at hand includes a relatively new feature - new fields that are not 
null and include a default.

 Create a table with no field called "NewField".  (Format 1)
 Store a record with the primary key of 1.
Alter the table adding "NewField", not null, default "Ann".  (Format 2)
Store a record with the primary key 2 and no value for "New Field"
Alter the record again, changing the default to "Jim". (Format 3)
Store a record with the primary key 3 and no value for "New Field" 
Read all the records.

 1 Jim
2 Ann
3 Jim

The situation is that Firebird converts record 1 from format 1 to format 3 
without going through format 2.  If it had gone through format 2, the initial 
default value would be applied and you'd see

1 Ann
2 Ann
3 Jim

Should the behavior be changed?  It's ancient.  It has benefits (e.g. Changing 
a column from double to float and back).  The benefits are in dumb cases.  The 
new behavior might be more standard conformant, if the standard allows addind 
Not Null columns with defaults and the Standards Committee assumed that default 
values were added in the most crude way possible.  

Cheers,


Ann

Just explained this to Jim who said "That's a dumb case.  Who cares?"


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

24.03.2017 12:28, Dimitry Sibiryakov wrote:
> 24.03.2017 10:29, Vlad Khorsun wrote:
>>Not sure i understand what you mean but sweep never updates records.
>
>My knowledge of Firebird is overestimated, you know. Isn't there an 
> internal routine
> that converts records into the latest format whenever it reads them?

   See my answer to SET - conversion happens in memory only.


Vlad


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Dimitry Sibiryakov]

24.03.2017 10:29, Vlad Khorsun wrote:
>Not sure i understand what you mean but sweep never updates records.

   My knowledge of Firebird is overestimated, you know. Isn't there an internal 
routine 
that converts records into the latest format whenever it reads them?


-- 
   WBR, SD.

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

24.03.2017 10:50, Svein Erling Tysvær wrote:
> Being still on Fb 2.5, my voice is rather error-prone, but I'm definitely 
> outside the development team.

   Being long-time Firebird user your voice is very important for us

> It would confuse me if things worked like Vlad expects. Suppose the query was 
> "select * from t where ID between 1 and 2", then I
> would ask myself why record 1 changed value when record 2 retained it when 
> the only change done was the default value. From a
> theoretical point of view, I can see Vlads point of him expecting a 'floating 
> default' as opposed to the 'fixed default', but it
> does make it harder for users when selects may return values that aren't 
> really there. And if you didn't know whether record 1 was
> inserted before or after the addition of field1, is there a simple way to 
> tell that this record has a floating default whereas
> record 2 has a fixed default?

   We already have pseudo column with recno (dbkey) and transaction number. 
Probably
it is time to add pseudo column with format number ?

> I try to avoid making trouble through doing DDL and DML simultaneously, but 
> what if there was an active transaction that started
> before the default was changed to ABC. Would Vlad expect that transaction to 
> still return XYZ for record 1 whereas all newer
> transactions would return ABC?

   I prefer to see it works this way. Probably read-committed tx could return 
new default value.

>> Firebird is known to upgrade the record format while reading. "Upgrade"
>> here means using the latest (aka current) format. The current format is
>> the one that can be seen in RDB$RELATION_FIELDS. So one may expect that
>> the default value to be used is also the latest one, that's stored in
>> RDB$ tables
>
> This also complicates things. Does it mean that if we before creating F1 also 
> had inserted:
> INSERT INTO T (ID, DESCR) VALUES (4, 'No F1 field');
>
> and that this record had never been selected before the change of default 
> from XYZ to ABC, then "select F1 from t where ID in (1,
> 4)" after this change would return different values for F1 despite the only 
> difference between them being that one of them were
> selected when XYZ was the default?

   I don't see how it could return different values. v2.5 will return same new 
default values, while
v3 will return same initial default values. It doesn't matter if record was 
selected before
default changes or not.

> And does an "Upgrade" such as Dmitry describes also happen when doing "select 
> count(*) from t"
> and/or in read-only transaction or even read-only databases?

   Upgrade is happens with in-memory image of record only. No changes on disk 
until real explicit UPDATE.

> To me, the most logical thing would be that all fields got the default value 
> when the record was inserted, and if the record was
> inserted before the default was created, then that the first default would be 
> the value that the record got. I think this is more or
> less what Mark and Adriano wrote.

   This could make sence if one knows that default value was changed after 
column was added.
But when one don't knows it, one will confused why returned value is not the 
same as
recorded in system catalog.

> I would expect changing defaults for fields that didn't have a default when 
> records were inserted and that haven't explicitly
> received any value since, to be something that isn't all too common.

   Agree.

> Normally, I would expect running an "update T set F1 = 'XYZ'
> where F1 is null" shortly before or after setting such a default (assuming 
> this would set the value even if issued after setting the
> default), but this may be just me and/or Fb 2.5. Note that I never work 
> against huge databases.

   Thanks for the opinion, it is very important to have feedback

Vlad


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Alex Peshkoff]

On 03/24/17 06:25, Adriano dos Santos Fernandes wrote:
> Em 23/03/2017 20:29, Mark Rotteveel escreveu:
>> To me the behavior described under "actual" intuitively sounds like the
>> correct behavior. Why do you expect that the column value would change
>> to 'ABC'?
>>
>> The column was created with a default, which means that existing rows
>> will get that value, afaik it shouldn't change if the default later is
>> changed. Is there a requirement in the SQL standard that supports your
>> expectation?
>>
> I agree with you, Mark, that the current behavior is correct

+1

> unless
> someone quotes the standard saying the contrary.
>
>
> Adriano
>
> --
> 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
>


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Lester Caine]

On 24/03/17 08:50, Svein Erling Tysvær wrote:
> I would expect changing defaults for fields that didn't have a default
> when records were inserted and that haven't explicitly received any
> value since, to be something that isn't all too common. Normally, I
> would expect running an "update T set F1 = 'XYZ' where F1 is null"
> shortly before or after setting such a default (assuming this would set
> the value even if issued after setting the default), but this may be
> just me and/or Fb 2.5. Note that I never work against huge databases.

I'm in the same camp as SET, especially if one is now adding a 'NOT
NULL' constraint to a field that contains NULL values. If one is not
adding the 'NOT NULL' constraint, then *I* would expect those fields to
still return NULL even after adding a DEFAULT 'NEW' because that is
value to REPLACE the NULL when ADDING a new record. If this is not the
case now, then my understanding is wrong, but in my case adding the the
NOT NULL would go hand in hand and the old records HAVE to be populated
manually prior to adding it? If later you need change a default then it
is up to the business logic to decide if old records change to the new
default, and at this point it becomes even more important that the
'unset' fields can be identified from the 'old default' ones?

If your logic is now replacing the returned data with a different value
to that actually stored, then I would say THAT is a bug. If the stored
field is NULL then either the query should return that or contain the
logic to display it as something else, it should not be 'magic'
depending on some other settings?

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Dimitry Sibiryakov]

24.03.2017 6:53, Vlad Khorsun wrote:
>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.

   Is it possible that in your testcase sweep performed implicit update between 
DDLs?


-- 
   WBR, SD.

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Svein Erling Tysvær]

Being still on Fb 2.5, my voice is rather error-prone, but I'm definitely
outside the development team.

It would confuse me if things worked like Vlad expects. Suppose the query
was "select * from t where ID between 1 and 2", then I would ask myself why
record 1 changed value when record 2 retained it when the only change done
was the default value. From a theoretical point of view, I can see Vlads
point of him expecting a 'floating default' as opposed to the 'fixed
default', but it does make it harder for users when selects may return
values that aren't really there. And if you didn't know whether record 1
was inserted before or after the addition of field1, is there a simple way
to tell that this record has a floating default whereas record 2 has a
fixed default?

I try to avoid making trouble through doing DDL and DML simultaneously, but
what if there was an active transaction that started before the default was
changed to ABC. Would Vlad expect that transaction to still return XYZ for
record 1 whereas all newer transactions would return ABC?

> Firebird is known to upgrade the record format while reading. "Upgrade"
> here means using the latest (aka current) format. The current format is
> the one that can be seen in RDB$RELATION_FIELDS. So one may expect that
> the default value to be used is also the latest one, that's stored in
> RDB$ tables

This also complicates things. Does it mean that if we before creating F1
also had inserted:
INSERT INTO T (ID, DESCR) VALUES (4, 'No F1 field');

and that this record had never been selected before the change of default
from XYZ to ABC, then "select F1 from t where ID in (1, 4)" after this
change would return different values for F1 despite the only difference
between them being that one of them were selected when XYZ was the default?
And does an "Upgrade" such as Dmitry describes also happen when doing
"select count(*) from t" and/or in read-only transaction or even read-only
databases?

To me, the most logical thing would be that all fields got the default
value when the record was inserted, and if the record was inserted before
the default was created, then that the first default would be the value
that the record got. I think this is more or less what Mark and Adriano
wrote.

I would expect changing defaults for fields that didn't have a default when
records were inserted and that haven't explicitly received any value since,
to be something that isn't all too common. Normally, I would expect running
an "update T set F1 = 'XYZ' where F1 is null" shortly before or after
setting such a default (assuming this would set the value even if issued
after setting the default), but this may be just me and/or Fb 2.5. Note
that I never work against huge databases.

Set
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdotFirebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Molnár Attila]

Hi!

I expect form the FB engine to return the default value at INSERT/UPDATE 
TIME, not the current default value. So default values should be kept in 
the rdb$format table, because versioning is needed.

On 2017.03.24. 8:18, Dmitry Yemanov wrote:
> 24.03.2017 09:33, Vlad Khorsun wrote:
>>> Firebird is known to upgrade the record format while reading. "Upgrade"
>>> here means using the latest (aka current) format. The current format is
>>> the one that can be seen in RDB$RELATION_FIELDS. So one may expect that
>>> the default value to be used is also the latest one, that's stored in
>>> RDB$ tables. IIRC, this is how FB 2.5 works.
>> Exactly. When i read the table and know that some field should have default
>> value, i expect to see this value at system catalog.
> It depends on the definition. In fact, adding a NOT NULL DEFAULT X
> column means two things: (1) DEFAULT X will be used implicitly for
> inserts or explicitly for the DEFAULT keyword and (2) X will be used to
> replace the missing values. For (1), everything works as expected. For
> (2), your expectation relies on the fact that X is substituted while
> reading. But this is just an implementation detail. The contract is
> "replacing missing values", whatever it could mean. Someone else does
> not know such internals and expects this "replacement" happening by some
> voodoo magic during ALTER ;-)
>
> Also, lets consider this:
>
> SQL> create table t (col1 int);
> SQL> insert into t values (1);
> SQL> commit;
> SQL> alter table t add col2 int default 123 not null;
> SQL> select * from t;
>
>   COL1 COL2
>  
>  1  123
>
> SQL> alter table t alter col2 drop default;
> SQL> select * from t;
>
>   COL1 COL2
>  
>  1  123
>
> There's no default at the end, but COL2 is still returning 123. This
> matches the "update" expectation, not the "convert missing to the
> current default" one.
>
> Yesterday I considered this issue being a bug, but now I'm not that
> sure. It may look obvious for some users but counter-intuitive for the
> others.
>
> Anyone outside the development team cares to comment? :-)
>
>
> Dmitry
>
>
> --
> 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


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Dmitry Yemanov]

24.03.2017 09:33, Vlad Khorsun wrote:
>
>> Firebird is known to upgrade the record format while reading. "Upgrade"
>> here means using the latest (aka current) format. The current format is
>> the one that can be seen in RDB$RELATION_FIELDS. So one may expect that
>> the default value to be used is also the latest one, that's stored in
>> RDB$ tables. IIRC, this is how FB 2.5 works.
>
> Exactly. When i read the table and know that some field should have default
> value, i expect to see this value at system catalog.

It depends on the definition. In fact, adding a NOT NULL DEFAULT X 
column means two things: (1) DEFAULT X will be used implicitly for 
inserts or explicitly for the DEFAULT keyword and (2) X will be used to 
replace the missing values. For (1), everything works as expected. For 
(2), your expectation relies on the fact that X is substituted while 
reading. But this is just an implementation detail. The contract is 
"replacing missing values", whatever it could mean. Someone else does 
not know such internals and expects this "replacement" happening by some 
voodoo magic during ALTER ;-)

Also, lets consider this:

SQL> create table t (col1 int);
SQL> insert into t values (1);
SQL> commit;
SQL> alter table t add col2 int default 123 not null;
SQL> select * from t;

 COL1 COL2
 
1  123

SQL> alter table t alter col2 drop default;
SQL> select * from t;

 COL1 COL2
 
1  123

There's no default at the end, but COL2 is still returning 123. This 
matches the "update" expectation, not the "convert missing to the 
current default" one.

Yesterday I considered this issue being a bug, but now I'm not that 
sure. It may look obvious for some users but counter-intuitive for the 
others.

Anyone outside the development team cares to comment? :-)


Dmitry


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

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 

Function
Add a column to a table.

Format
 ::=
ADD [ COLUMN ] 
...
General Rules
1) The column defined by the  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  
immediately contained in the containing
 , let CN be the  immediately 
contained in ,
 and let GE be the generation expression included in the column 
descriptor of C. The following  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.

Regards,
Vlad


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Dmitry Yemanov]

24.03.2017 02:29, Mark Rotteveel wrote:

> To me the behavior described under "actual" intuitively sounds like the
> correct behavior. Why do you expect that the column value would change
> to 'ABC'?

This is really a tricky case. The "replace non-existing value with the 
default one" hack is a native Firebird feature that's not covered by the 
standard, it allows adding fields without updating the whole table. The 
question is what default value must be used, if there are/were many.

Firebird is known to upgrade the record format while reading. "Upgrade" 
here means using the latest (aka current) format. The current format is 
the one that can be seen in RDB$RELATION_FIELDS. So one may expect that 
the default value to be used is also the latest one, that's stored in 
RDB$ tables. IIRC, this is how FB 2.5 works.

 From another side, storing the default value inside the format is a 
smart hack that allows to avoid updating the table. It was designed to 
act like an implicit update but without the overhead. And considering 
the update semantics, the new behaviour may look correct.

The big question is what expectations are better suitable to our users.


Dmitry


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Vlad Khorsun]

24.03.2017 1:29, Mark Rotteveel wrote:
> To me the behavior described under "actual" intuitively sounds like the 
> correct behavior. Why do you expect that the column value
> would change to 'ABC'?

   Because Firebird doesn't update old records when new field was created.

> 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

Regards,
Vlad

PS Avoid dumb overquoting


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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Adriano dos Santos Fernandes]

Em 23/03/2017 20:29, Mark Rotteveel escreveu:
> To me the behavior described under "actual" intuitively sounds like the
> correct behavior. Why do you expect that the column value would change
> to 'ABC'? 
> 
> The column was created with a default, which means that existing rows
> will get that value, afaik it shouldn't change if the default later is
> changed. Is there a requirement in the SQL standard that supports your
> expectation?
> 

I agree with you, Mark, that the current behavior is correct unless
someone quotes the standard saying the contrary.


Adriano

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.

[Mark Rotteveel]

To me the behavior described under "actual" intuitively sounds like the correct 
behavior. Why do you expect that the column value would change to 'ABC'? 
The column was created with a default, which means that existing rows will get 
that value, afaik it shouldn't change if the default later is changed. Is there 
a requirement in the SQL standard that supports your expectation?

Mark

PS I will see if I can find something to back up my claim, but that will have 
to wait until Saturday.

- Bericht beantwoorden -
Van: "Vlad Khorsun (JIRA)" 
Aan: 
Onderwerp: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of 
the new field at the old records, created before that new field was added.
Datum: do, mrt. 23, 2017 23:11

Wrong value of the new field at the old records, created before that new field 
was added.
-

Key: CORE-5507
URL: http://tracker.firebirdsql.org/browse/CORE-5507
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.2
Reporter: Vlad Khorsun


RECREATE TABLE T (ID INT NOT NULL, DESCR VARCHAR(32) NOT NULL);
COMMIT;

INSERT INTO T (ID, DESCR) VALUES (1, 'No F1 field');
COMMIT;

ALTER TABLE T ADD F1 VARCHAR(16) DEFAULT 'XYZ' NOT NULL;
COMMIT;

INSERT INTO T (ID, DESCR) VALUES (2, 'F1 field, default XYZ');
COMMIT;

SELECT * FROM T;
COMMIT;

ALTER TABLE T ALTER COLUMN F1 SET DEFAULT 'ABC';
COMMIT;

INSERT INTO T (ID, DESCR) VALUES (3, 'F1 field, default ABC');
COMMIT;

SELECT * FROM T;
COMMIT;


After the first select all is as expected:

ID DESCRF1
  
1 No F1 field  XYZ
2 F1 field, default XYZXYZ

after the second select
expected

ID DESCRF1
  
1 No F1 field  ABC
2 F1 field, default XYZXYZ
3 F1 field, default ABCABC

actual

ID DESCRF1
  
1 No F1 field  XYZ
2 F1 field, default XYZXYZ
3 F1 field, default ABCABC

Note value of the field F1 at the first record: it is expected that is should 
be the same as latest DEFAULT value.
Also note that 2nd and 3rd INSERTs assigns correct value to the omitted field - 
same as latest DEFAULT value.


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira



--
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--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdotFirebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel