found a glitch in ALTER TABLE RENAME (3.25.x)

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

found a glitch in ALTER TABLE RENAME (3.25.x)

a.furieri
Hallo,

I've casually discovered that the behavior of ALTER TABLE RENAME TO
(versione 3.25.x) seems to be affected by an odd glitch; FOREIGN KEY
constraints are updated as expected only when PRAGMA foreign_keys=1,
otherwise they are just ignored.

example (common part)
---------------------
CREATE TABLE mother (
     id INTEGER PRIMARY KEY,
     name TEXT);
CREATE TABLE daughter (
     id INTEGER PRIMARY KEY,
     id_mother INTEGER,
     name TEXT,
     CONSTRAINT fk_one FOREIGN KEY (id_mother)
     REFERENCES mother (id));

test #1
----------------------
PRAGMA foreign_keys=0;
ALTER TABLE mother RENAME TO mom;
SELECT sql FROM sqlite_master WHERE name = 'daughter';
============
CREATE TABLE daughter (
     id INTEGER PRIMARY KEY,
     id_mother INTEGER,
     name TEXT,
     CONSTRAINT fk_one FOREIGN KEY (id_mother)
     REFERENCES mother (id))

as you can see, the FK constraint definition has not been
updated.


test #2
----------------------
PRAGMA foreign_keys=1;
ALTER TABLE mother RENAME TO mom;
SELECT sql FROM sqlite_master WHERE name = 'daughter';
------------------------------------------------------
CREATE TABLE daughter (
     id INTEGER PRIMARY KEY,
     id_mother INTEGER,
     name TEXT,
     CONSTRAINT fk_one FOREIGN KEY (id_mother)
     REFERENCES "mom" (id))

this second time the FK constraint has been properly updated.

Note: ALTER TABLE RENAME COLUMN seems to be immune from
the issue.

test #3
----------------------
PRAGMA foreign_keys=0;
ALTER TABLE mother RENAME COLUMN id TO pkuid;
SELECT sql FROM sqlite_master WHERE name = 'daughter';
============
CREATE TABLE daughter (
     id INTEGER PRIMARY KEY,
     id_mother INTEGER,
     name TEXT,
     CONSTRAINT fk_one FOREIGN KEY (id_mother)
     REFERENCES mother (pkuid))

test #3
----------------------
PRAGMA foreign_keys=1;
ALTER TABLE mother RENAME COLUMN id TO pkuid;
SELECT sql FROM sqlite_master WHERE name = 'daughter';
============
CREATE TABLE daughter (
     id INTEGER PRIMARY KEY,
     id_mother INTEGER,
     name TEXT,
     CONSTRAINT fk_one FOREIGN KEY (id_mother)
     REFERENCES mother (pkuid))

the FK constraint definition is correctly updated in
both cases, the actual setting of PRAGMA foreign_key
is not relevant.

best regards,
Sandro Furieri (developer of SpatiaLite)


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] found a glitch in ALTER TABLE RENAME (3.25.x)

Hick Gunter
Foreign keys are ignored by default and need to be explicitly enabled. I would expect this to include everything that relates to foreign keys.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von [hidden email]
Gesendet: Donnerstag, 08. November 2018 17:43
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] found a glitch in ALTER TABLE RENAME (3.25.x)

Hallo,

I've casually discovered that the behavior of ALTER TABLE RENAME TO (versione 3.25.x) seems to be affected by an odd glitch; FOREIGN KEY constraints are updated as expected only when PRAGMA foreign_keys=1, otherwise they are just ignored.

example (common part)
---------------------
CREATE TABLE mother (
     id INTEGER PRIMARY KEY,
     name TEXT);
CREATE TABLE daughter (
     id INTEGER PRIMARY KEY,
     id_mother INTEGER,
     name TEXT,
     CONSTRAINT fk_one FOREIGN KEY (id_mother)
     REFERENCES mother (id));

test #1
----------------------
PRAGMA foreign_keys=0;
ALTER TABLE mother RENAME TO mom;
SELECT sql FROM sqlite_master WHERE name = 'daughter'; ============ CREATE TABLE daughter (
     id INTEGER PRIMARY KEY,
     id_mother INTEGER,
     name TEXT,
     CONSTRAINT fk_one FOREIGN KEY (id_mother)
     REFERENCES mother (id))

as you can see, the FK constraint definition has not been updated.


test #2
----------------------
PRAGMA foreign_keys=1;
ALTER TABLE mother RENAME TO mom;
SELECT sql FROM sqlite_master WHERE name = 'daughter';
------------------------------------------------------
CREATE TABLE daughter (
     id INTEGER PRIMARY KEY,
     id_mother INTEGER,
     name TEXT,
     CONSTRAINT fk_one FOREIGN KEY (id_mother)
     REFERENCES "mom" (id))

this second time the FK constraint has been properly updated.

Note: ALTER TABLE RENAME COLUMN seems to be immune from the issue.

test #3
----------------------
PRAGMA foreign_keys=0;
ALTER TABLE mother RENAME COLUMN id TO pkuid; SELECT sql FROM sqlite_master WHERE name = 'daughter'; ============ CREATE TABLE daughter (
     id INTEGER PRIMARY KEY,
     id_mother INTEGER,
     name TEXT,
     CONSTRAINT fk_one FOREIGN KEY (id_mother)
     REFERENCES mother (pkuid))

test #3
----------------------
PRAGMA foreign_keys=1;
ALTER TABLE mother RENAME COLUMN id TO pkuid; SELECT sql FROM sqlite_master WHERE name = 'daughter'; ============ CREATE TABLE daughter (
     id INTEGER PRIMARY KEY,
     id_mother INTEGER,
     name TEXT,
     CONSTRAINT fk_one FOREIGN KEY (id_mother)
     REFERENCES mother (pkuid))

the FK constraint definition is correctly updated in both cases, the actual setting of PRAGMA foreign_key is not relevant.

best regards,
Sandro Furieri (developer of SpatiaLite)


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] found a glitch in ALTER TABLE RENAME (3.25.x)

Simon Slavin-3
On 9 Nov 2018, at 7:11am, Hick Gunter <[hidden email]> wrote:

> Foreign keys are ignored by default and need to be explicitly enabled. I would expect this to include everything that relates to foreign keys.
>
> -----Urspr√ľngliche Nachricht-----
>
> I've casually discovered that the behavior of ALTER TABLE RENAME TO (versione 3.25.x) seems to be affected by an odd glitch; FOREIGN KEY constraints are updated as expected only when PRAGMA foreign_keys=1, otherwise they are just ignored.

While your statement is correct, I would argue that the behaviour is harmful, and cannot possibly be what any programmer could want to happen.  Think how difficult it would be to diagnose this having happened, and then fix the problem it would create.

I might argue that correct behaviour would be to transform FOREIGN KEY constraints even when the PRAGMA is off.  Or I might argue that the attempt should generate an error result (SQLITE_MISUSE ?).

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] found a glitch in ALTER TABLE RENAME (3.25.x)

Dominique Devienne
On Fri, Nov 9, 2018 at 8:26 AM Simon Slavin <[hidden email]> wrote:

> On 9 Nov 2018, at 7:11am, Hick Gunter <[hidden email]> wrote:
> > Foreign keys are ignored by default and need to be explicitly enabled. I
> would expect this to include everything that relates to foreign keys.
> > I've casually discovered that the behavior of ALTER TABLE RENAME TO
> (versione 3.25.x) seems to be affected by an odd glitch; FOREIGN KEY
> constraints are updated as expected only when PRAGMA foreign_keys=1,
> otherwise they are just ignored.
>
> While your statement is correct, I would argue that the behaviour is
> harmful, and cannot possibly be what any programmer could want to happen.
> Think how difficult it would be to diagnose this having happened, and then
> fix the problem it would create.
> I might argue that correct behaviour would be to transform FOREIGN KEY
> constraints even when the PRAGMA is off.  Or I might argue that the attempt
> should generate an error result (SQLITE_MISUSE ?).
>

Foreign keys are ignored in the sense they are not enforced, but they are
still parsed AFAIK.
As such, I agree with Simon that it's harmful to not update them on table
(or column) renames. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] found a glitch in ALTER TABLE RENAME (3.25.x)

J. King-3
On November 9, 2018 2:50:56 AM EST, Dominique Devienne <[hidden email]> wrote:

>On Fri, Nov 9, 2018 at 8:26 AM Simon Slavin <[hidden email]>
>wrote:
>
>> On 9 Nov 2018, at 7:11am, Hick Gunter <[hidden email]> wrote:
>> > Foreign keys are ignored by default and need to be explicitly
>enabled. I
>> would expect this to include everything that relates to foreign keys.
>> > I've casually discovered that the behavior of ALTER TABLE RENAME TO
>> (versione 3.25.x) seems to be affected by an odd glitch; FOREIGN KEY
>> constraints are updated as expected only when PRAGMA foreign_keys=1,
>> otherwise they are just ignored.
>>
>> While your statement is correct, I would argue that the behaviour is
>> harmful, and cannot possibly be what any programmer could want to
>happen.
>> Think how difficult it would be to diagnose this having happened, and
>then
>> fix the problem it would create.
>> I might argue that correct behaviour would be to transform FOREIGN
>KEY
>> constraints even when the PRAGMA is off.  Or I might argue that the
>attempt
>> should generate an error result (SQLITE_MISUSE ?).
>>
>
>Foreign keys are ignored in the sense they are not enforced, but they
>are
>still parsed AFAIK.
>As such, I agree with Simon that it's harmful to not update them on
>table
>(or column) renames. --DD
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

That could lead to loss of referential integrity when modifying a table in a way not supported by ALTER TABLE, I believe.  One usual method is to turn foreign keys off, rename the old table, create a new modified table under the old name, insert the old table's data into the new, and then drop the old table. If foreign keys were, for lack of a better term, partially enforced, this process would yield new and backwards-incompatible results: when the old table is renamed all referring tables would continue to refer to the old table, and when the table is dropped all references would become invalid.
--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] found a glitch in ALTER TABLE RENAME (3.25.x)

Keith Medcalf

On Friday, 9 November, 2018 16:20, J. King wrote:
>To: SQLite mailing list

>That could lead to loss of referential integrity when modifying a
>table in a way not supported by ALTER TABLE, I believe.  One usual
>method is to turn foreign keys off, rename the old table, create a
>new modified table under the old name, insert the old table's data
>into the new, and then drop the old table. If foreign keys were, for
>lack of a better term, partially enforced, this process would yield
>new and backwards-incompatible results: when the old table is renamed
>all referring tables would continue to refer to the old table, and
>when the table is dropped all references would become invalid.

Foreign Key constraints should be updated notwithstanding the status of PRAGMA foreign_keys at the time the ALTER command is given.  The current behaviour is inconsistent and incorrect.

If one wants the "legacy behaviour" for the ALTER commands because one wants those commands to operate in the legacy fashion, then one should be using the pragma that has been provided for that purpose.  This would revert to the "legacy" way of doing things.  Which means doing whatever was done in legacy processing of the ALTER commands including the handling of foreign key constraints.

https://sqlite.org/pragma.html#pragma_legacy_alter_table

When processing an "ALTER" statement under the "new" (vs the legacy) rules, leaving a dependancy on some other setting is surprising.  Unless one specifically requests "legacy" processing, the processing of an ALTER statement should never result in a database which is self-inconsistent.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>
>On November 9, 2018 2:50:56 AM EST, Dominique Devienne
><[hidden email]> wrote:
>>On Fri, Nov 9, 2018 at 8:26 AM Simon Slavin <[hidden email]>
>>wrote:
>>
>>> On 9 Nov 2018, at 7:11am, Hick Gunter <[hidden email]> wrote:
>>> > Foreign keys are ignored by default and need to be explicitly
>>enabled. I
>>> would expect this to include everything that relates to foreign
>keys.
>>> > I've casually discovered that the behavior of ALTER TABLE RENAME
>TO
>>> (versione 3.25.x) seems to be affected by an odd glitch; FOREIGN
>KEY
>>> constraints are updated as expected only when PRAGMA
>foreign_keys=1,
>>> otherwise they are just ignored.
>>>
>>> While your statement is correct, I would argue that the behaviour
>is
>>> harmful, and cannot possibly be what any programmer could want to
>>happen.
>>> Think how difficult it would be to diagnose this having happened,
>and
>>then
>>> fix the problem it would create.
>>> I might argue that correct behaviour would be to transform FOREIGN
>>KEY
>>> constraints even when the PRAGMA is off.  Or I might argue that
>the
>>attempt
>>> should generate an error result (SQLITE_MISUSE ?).
>>>
>>
>>Foreign keys are ignored in the sense they are not enforced, but
>they
>>are
>>still parsed AFAIK.
>>As such, I agree with Simon that it's harmful to not update them on
>>table
>>(or column) renames. --DD
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>--
>J. King
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] found a glitch in ALTER TABLE RENAME (3.25.x)

Dan Kennedy-4
On 11/10/2018 08:18 AM, Keith Medcalf wrote:

>
> On Friday, 9 November, 2018 16:20, J. King wrote:
>> To: SQLite mailing list
>
>> That could lead to loss of referential integrity when modifying a
>> table in a way not supported by ALTER TABLE, I believe.  One usual
>> method is to turn foreign keys off, rename the old table, create a
>> new modified table under the old name, insert the old table's data
>> into the new, and then drop the old table. If foreign keys were, for
>> lack of a better term, partially enforced, this process would yield
>> new and backwards-incompatible results: when the old table is renamed
>> all referring tables would continue to refer to the old table, and
>> when the table is dropped all references would become invalid.
>
> Foreign Key constraints should be updated notwithstanding the status of PRAGMA foreign_keys at the time the ALTER command is given.  The current behaviour is inconsistent and incorrect.
>
> If one wants the "legacy behaviour" for the ALTER commands because one wants those commands to operate in the legacy fashion, then one should be using the pragma that has been provided for that purpose.  This would revert to the "legacy" way of doing things.  Which means doing whatever was done in legacy processing of the ALTER commands including the handling of foreign key constraints.


I think this is a good answer. Now implemented here:

   https://sqlite.org/src/info/ae9638e9c0ad0c36

Dan.





>
> https://sqlite.org/pragma.html#pragma_legacy_alter_table
>
> When processing an "ALTER" statement under the "new" (vs the legacy) rules, leaving a dependancy on some other setting is surprising.  Unless one specifically requests "legacy" processing, the processing of an ALTER statement should never result in a database which is self-inconsistent.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
>
>>
>> On November 9, 2018 2:50:56 AM EST, Dominique Devienne
>> <[hidden email]> wrote:
>>> On Fri, Nov 9, 2018 at 8:26 AM Simon Slavin <[hidden email]>
>>> wrote:
>>>
>>>> On 9 Nov 2018, at 7:11am, Hick Gunter <[hidden email]> wrote:
>>>>> Foreign keys are ignored by default and need to be explicitly
>>> enabled. I
>>>> would expect this to include everything that relates to foreign
>> keys.
>>>>> I've casually discovered that the behavior of ALTER TABLE RENAME
>> TO
>>>> (versione 3.25.x) seems to be affected by an odd glitch; FOREIGN
>> KEY
>>>> constraints are updated as expected only when PRAGMA
>> foreign_keys=1,
>>>> otherwise they are just ignored.
>>>>
>>>> While your statement is correct, I would argue that the behaviour
>> is
>>>> harmful, and cannot possibly be what any programmer could want to
>>> happen.
>>>> Think how difficult it would be to diagnose this having happened,
>> and
>>> then
>>>> fix the problem it would create.
>>>> I might argue that correct behaviour would be to transform FOREIGN
>>> KEY
>>>> constraints even when the PRAGMA is off.  Or I might argue that
>> the
>>> attempt
>>>> should generate an error result (SQLITE_MISUSE ?).
>>>>
>>>
>>> Foreign keys are ignored in the sense they are not enforced, but
>> they
>>> are
>>> still parsed AFAIK.
>>> As such, I agree with Simon that it's harmful to not update them on
>>> table
>>> (or column) renames. --DD
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> --
>> J. King
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users