Re: [sqlite] INSERT OR FAIL inserts row although it violates a table constraint
On 5/7/19, Manuel Rigger wrote: > Great, thanks! In which commits were they addressed? I saw two commits that > address https://www.sqlite.org/src/info/e63cbcfd3378afe6980d6. Was this > issue derived from the test case? Issue 1: Foreign key constraints are not checked until the end of a statement, so the INSERT OR IGNORE needs to act like INSERT OR ABORT when dealing with FK constraints. This will involve a (pending) change to the documentation as well. Check-in https://www.sqlite.org/src/info/659c551dcc374a0d Issue 2: Foreign key constraint checking might cause some elements of an inserted or updated row to change datatypes, which causes the row to be constructed incorrectly. Fixed by check-in https://www.sqlite.org/src/info/3f1c8051648a341d -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR FAIL inserts row although it violates a table constraint
Great, thanks! In which commits were they addressed? I saw two commits that address https://www.sqlite.org/src/info/e63cbcfd3378afe6980d6. Was this issue derived from the test case? Best, Manuel On Tue, May 7, 2019 at 10:08 PM Richard Hipp wrote: > Two separate problems, both now fixed on trunk. > > On 5/6/19, Manuel Rigger wrote: > > Hi everyone, > > > > consider the following example: > > > > PRAGMA foreign_keys=true; > > CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES > t0(c1)); > > INSERT OR FAIL INTO t0(c0, c1) VALUES > > (0, 1), > > (0, 2); > > SELECT * FROM t0; -- returns 0|1 > > > > I expect the INSERT to fail, since both the UNIQUE and the FOREIGN KEY > > constraints are violated. However, the (0, 1) row is inserted, as the > > result of the SELECT query above demonstrates. When splitting up the > INSERT > > into two INSERTS, no row is inserted, as expected: > > > > PRAGMA foreign_keys=true; > > CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES > t0(c1)); > > INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1); > > INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 2); > > SELECT * FROM t0; -- returns no row > > > > I found this bug because a WHERE clause did not fetch the row after a > > REINDEX: > > > > PRAGMA foreign_keys=true; > > CREATE TABLE t0 (c0 INT UNIQUE, c1 TEXT UNIQUE, FOREIGN KEY(c0) > REFERENCES > > t0(c1)); > > INSERT OR FAIL INTO t0(c0, c1) VALUES > > (0, 1), > > (0, 2); > > REINDEX;; > > SELECT * FROM t0; -- returns 0|1 > > SELECT * FROM t0 WHERE c1=1; -- returns nothing > > > > Best, > > Manuel > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR FAIL inserts row although it violates a table constraint
Two separate problems, both now fixed on trunk. On 5/6/19, Manuel Rigger wrote: > Hi everyone, > > consider the following example: > > PRAGMA foreign_keys=true; > CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1)); > INSERT OR FAIL INTO t0(c0, c1) VALUES > (0, 1), > (0, 2); > SELECT * FROM t0; -- returns 0|1 > > I expect the INSERT to fail, since both the UNIQUE and the FOREIGN KEY > constraints are violated. However, the (0, 1) row is inserted, as the > result of the SELECT query above demonstrates. When splitting up the INSERT > into two INSERTS, no row is inserted, as expected: > > PRAGMA foreign_keys=true; > CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1)); > INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1); > INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 2); > SELECT * FROM t0; -- returns no row > > I found this bug because a WHERE clause did not fetch the row after a > REINDEX: > > PRAGMA foreign_keys=true; > CREATE TABLE t0 (c0 INT UNIQUE, c1 TEXT UNIQUE, FOREIGN KEY(c0) REFERENCES > t0(c1)); > INSERT OR FAIL INTO t0(c0, c1) VALUES > (0, 1), > (0, 2); > REINDEX;; > SELECT * FROM t0; -- returns 0|1 > SELECT * FROM t0 WHERE c1=1; -- returns nothing > > Best, > Manuel > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INSERT OR FAIL inserts row although it violates a table constraint
Hi everyone, consider the following example: PRAGMA foreign_keys=true; CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1)); INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1), (0, 2); SELECT * FROM t0; -- returns 0|1 I expect the INSERT to fail, since both the UNIQUE and the FOREIGN KEY constraints are violated. However, the (0, 1) row is inserted, as the result of the SELECT query above demonstrates. When splitting up the INSERT into two INSERTS, no row is inserted, as expected: PRAGMA foreign_keys=true; CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1)); INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1); INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 2); SELECT * FROM t0; -- returns no row I found this bug because a WHERE clause did not fetch the row after a REINDEX: PRAGMA foreign_keys=true; CREATE TABLE t0 (c0 INT UNIQUE, c1 TEXT UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1)); INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1), (0, 2); REINDEX;; SELECT * FROM t0; -- returns 0|1 SELECT * FROM t0 WHERE c1=1; -- returns nothing Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
