[sqlite] Potential bug with compile-time option SQLITE_OMIT_AUTOVACUUM

[Bernard Schurdevin]

Hi,

Building amalgamation sqlite3.c with option SQLITE_OMIT_AUTOVACUUM=1 
leads to malformed database on following case :


Windows 7 x64, mingw64 5.4 : gcc -s -static -DSQLITE_OMIT_AUTOVACUUM=1 
shell.c sqlite3.c -o sqlite3.exe


using CLI (compiled with SQLITE_OMIT_AUTOVACUUM=1) :

.open base.db
CREATE TABLE foo (num INTEGER PRIMARY KEY,comment TEXT COLLATE NOCASE) 
WITHOUT ROWID;

PRAGMA integrity_check; ===> ok
DROP TABLE foo;
Error: database disk image is malformed

CLI refuses to drop the table and don't corrupt database, but direct 
usage of API (sqlite3_step) corrupt the database :

*** in database main ***
2nd reference to page ...

Best regards

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users