potential bug

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

potential bug

Bernard Schurdevin
Hi,

I get weird results (false positive) to PRAGMA foreign_key_check on
WITHOUT ROWID table depending on foreign key field position.

Kind regards.


=====================================================================================

-- tested with Window CLI, versions 3.8.5, 3.9.2, 3.14.1, 3.18.0

PRAGMA foreign_keys=ON;

-- bad case
CREATE TABLE masters_bad (id INTEGER PRIMARY KEY AUTOINCREMENT,
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_bad (line INTEGER NOT NULL, quantity
INTEGER NOT NULL, master INTEGER NOT NULL REFERENCES masters_bad(id),
PRIMARY KEY(master, line)) WITHOUT ROWID;

BEGIN;
INSERT INTO masters_bad (reference) VALUES ('this is a ref');
INSERT INTO details_bad (master, line, quantity) VALUES
(last_insert_rowid(), 1, 999);
COMMIT;

-- ok case 1 (fk in front of fields)
CREATE TABLE masters_ok1 (id INTEGER PRIMARY KEY AUTOINCREMENT,
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_ok1 (master INTEGER NOT NULL
REFERENCES masters_ok1(id), line INTEGER NOT NULL, quantity INTEGER NOT
NULL, PRIMARY KEY(master,line)) WITHOUT ROWID;

BEGIN;
INSERT INTO masters_ok1 (reference) VALUES ('this is a ref');
INSERT INTO details_ok1 (master, line, quantity) VALUES
(last_insert_rowid(), 1, 999);
COMMIT;

-- ok case 2 (no more quantity field)
CREATE TABLE masters_ok2 (id INTEGER PRIMARY KEY AUTOINCREMENT,
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_ok2 (line INTEGER NOT NULL, master
INTEGER NOT NULL REFERENCES masters_ok2(id), PRIMARY KEY(master, line))
WITHOUT ROWID;

BEGIN;
INSERT INTO masters_ok2 (reference) VALUES ('this is a ref');
INSERT INTO details_ok2 (master, line) VALUES (last_insert_rowid(), 1);
COMMIT;

-- ok case 3 fields order changed
CREATE TABLE masters_ok3 (id INTEGER PRIMARY KEY AUTOINCREMENT,
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_ok3 (line INTEGER NOT NULL, master
INTEGER NOT NULL REFERENCES masters_ok3(id), quantity INTEGER NOT NULL,
PRIMARY KEY(master, line)) WITHOUT ROWID;

BEGIN;
INSERT INTO masters_ok3 (reference) VALUES ('this is a ref');
INSERT INTO details_ok3 (master, line, quantity) VALUES
(last_insert_rowid(), 1, 999);
COMMIT;

-- checking
PRAGMA foreign_key_check;


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

Re: potential bug

Dan Kennedy-4
On 04/17/2017 10:42 PM, Bernard Schurdevin wrote:
> Hi,
>
> I get weird results (false positive) to PRAGMA foreign_key_check on
> WITHOUT ROWID table depending on foreign key field position.

Thanks for reporting this. Should be fixed here:

   http://www.sqlite.org/src/info/690870bd7b2e607b

Dan.




>
> Kind regards.
>
>
> =====================================================================================
>
>
> -- tested with Window CLI, versions 3.8.5, 3.9.2, 3.14.1, 3.18.0
>
> PRAGMA foreign_keys=ON;
>
> -- bad case
> CREATE TABLE masters_bad (id INTEGER PRIMARY KEY AUTOINCREMENT,
> reference TEXT NOT NULL);
> CREATE TABLE IF NOT EXISTS details_bad (line INTEGER NOT NULL,
> quantity INTEGER NOT NULL, master INTEGER NOT NULL REFERENCES
> masters_bad(id), PRIMARY KEY(master, line)) WITHOUT ROWID;
>
> BEGIN;
> INSERT INTO masters_bad (reference) VALUES ('this is a ref');
> INSERT INTO details_bad (master, line, quantity) VALUES
> (last_insert_rowid(), 1, 999);
> COMMIT;
>
> -- ok case 1 (fk in front of fields)
> CREATE TABLE masters_ok1 (id INTEGER PRIMARY KEY AUTOINCREMENT,
> reference TEXT NOT NULL);
> CREATE TABLE IF NOT EXISTS details_ok1 (master INTEGER NOT NULL
> REFERENCES masters_ok1(id), line INTEGER NOT NULL, quantity INTEGER
> NOT NULL, PRIMARY KEY(master,line)) WITHOUT ROWID;
>
> BEGIN;
> INSERT INTO masters_ok1 (reference) VALUES ('this is a ref');
> INSERT INTO details_ok1 (master, line, quantity) VALUES
> (last_insert_rowid(), 1, 999);
> COMMIT;
>
> -- ok case 2 (no more quantity field)
> CREATE TABLE masters_ok2 (id INTEGER PRIMARY KEY AUTOINCREMENT,
> reference TEXT NOT NULL);
> CREATE TABLE IF NOT EXISTS details_ok2 (line INTEGER NOT NULL, master
> INTEGER NOT NULL REFERENCES masters_ok2(id), PRIMARY KEY(master,
> line)) WITHOUT ROWID;
>
> BEGIN;
> INSERT INTO masters_ok2 (reference) VALUES ('this is a ref');
> INSERT INTO details_ok2 (master, line) VALUES (last_insert_rowid(), 1);
> COMMIT;
>
> -- ok case 3 fields order changed
> CREATE TABLE masters_ok3 (id INTEGER PRIMARY KEY AUTOINCREMENT,
> reference TEXT NOT NULL);
> CREATE TABLE IF NOT EXISTS details_ok3 (line INTEGER NOT NULL, master
> INTEGER NOT NULL REFERENCES masters_ok3(id), quantity INTEGER NOT
> NULL, PRIMARY KEY(master, line)) WITHOUT ROWID;
>
> BEGIN;
> INSERT INTO masters_ok3 (reference) VALUES ('this is a ref');
> INSERT INTO details_ok3 (master, line, quantity) VALUES
> (last_insert_rowid(), 1, 999);
> COMMIT;
>
> -- checking
> PRAGMA foreign_key_check;
>
>
> _______________________________________________
> 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
Loading...