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

classic Classic list List threaded Threaded
49 messages Options
123
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

JIRA tracker@firebirdsql.org
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 DESCR                            F1
============ ================================ ================
           1 No F1 field                      XYZ
           2 F1 field, default XYZ            XYZ

after the second select
expected

          ID DESCR                            F1
============ ================================ ================
           1 No F1 field                      ABC
           2 F1 field, default XYZ            XYZ
           3 F1 field, default ABC            ABC

actual

          ID DESCR                            F1
============ ================================ ================
           1 No F1 field                      XYZ
           2 F1 field, default XYZ            XYZ
           3 F1 field, default ABC            ABC

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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Mark Rotteveel-2
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)" <[hidden email]>
Aan: <[hidden email]>
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 DESCR                            F1
============ ================================ ================
           1 No F1 field                      XYZ
           2 F1 field, default XYZ            XYZ

after the second select
expected

          ID DESCR                            F1
============ ================================ ================
           1 No F1 field                      ABC
           2 F1 field, default XYZ            XYZ
           3 F1 field, default ABC            ABC

actual

          ID DESCR                            F1
============ ================================ ================
           1 No F1 field                      XYZ
           2 F1 field, default XYZ            XYZ
           3 F1 field, default ABC            ABC

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/slashdot
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [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-3
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Vlad Khorsun-2
In reply to this post by JIRA tracker@firebirdsql.org
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Dmitry Yemanov-3
In reply to this post by JIRA tracker@firebirdsql.org
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Vlad Khorsun-2
In reply to this post by Vlad Khorsun-2
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.

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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Vlad Khorsun-2
In reply to this post by Dmitry Yemanov-3
24.03.2017 8:14, Dmitry Yemanov wrote:

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

   Exactly. When i read the table and know that some field should have default
value, i expect to see this value at system catalog.

   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 ?

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

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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Dmitry Yemanov-3
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [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
In reply to this post by Dmitry Yemanov-3
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/slashdot
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Dimitry Sibiryakov-3
In reply to this post by Vlad Khorsun-2
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Lester Caine
In reply to this post by Svein Erling Tysvær
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Alex Peshkoff
In reply to this post by Adriano dos Santos Fernandes-3
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Vlad Khorsun-2
In reply to this post by Dimitry Sibiryakov-3
24.03.2017 11:17, Dimitry Sibiryakov wrote:
> 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?

   Not sure i understand what you mean but sweep never updates records.

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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Vlad Khorsun-2
In reply to this post by Svein Erling Tysvær
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Dimitry Sibiryakov-3
In reply to this post by Vlad Khorsun-2
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Vlad Khorsun-2
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Ann Harrison-3
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Martijn Tonies (Upscene Productions)
Hello Ann,

On first sight, I'd say your first list of records is a list with the
correct values. But, given that the new 'default value' isn't stored, but
applied on reading the record, this fails when the default is modified,
right?

Would it be correct to say that when adding a new non-null column, (with a
default clause, ) it would make sense to choose between applying the new
value to NULL valued records?

alter table mytable add myfield varchar(20) not null default 'Jim' <some
command to apply the value here>;

Wouldn't this make absolutely clear the old records have a value instead of
applying the default value on reading the record?


With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

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


------------------------------------------------------------------------------
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [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-3
In reply to this post by Mark Rotteveel-2
Em 23/03/2017 20:29, Mark Rotteveel escreveu:

>
> actual
>
>           ID DESCR                            F1
> ============ ================================ ================
>            1 No F1 field                      XYZ
>            2 F1 field, default XYZ            XYZ
>            3 F1 field, default ABC            ABC
>

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
123
Loading...