Hello ! After submitting several emails with subject "Bug in sqlite3_trace/trigger/delete" and only one reply to then so far I decided to look a bit more deep on the problem I'm facing using sqlite3 with one specific database and created a simpler database that can show several problems and opportunities for improvements in sqlite3.
I probably only discovery this problem because I was using sqlite3_trace to output the sql from a server application I'm doing. The bugs/opportunities for improvements found: 1- Missing comma separating table constraints not reported as error. 2- Duplicated table constraints not reported as error. 3- The sqlite3_trace hook function been called with misleading info from sqlite3 internal DML operations to satisfy "ON DELETE SET NULL". See bellow the output of the C program with comments. Some applications use the output of sqlite3_trace to replicate the database and having internal only operations been send to it will create problems. 4- Sqlite3 do not perform any optimization by joining "table scans" searching for the same value on more than one column on the same table. See bellow the output of sqlite3 test-fkbugs.db "explain query plan delete from aa where id=10"; ? Based on this experience I'm suggesting to remove the output of internal operations from sqlite3_trace (see the dml operations to satisfy "ON DELETE SET NULL") and have another trace hook "sqlite3_trace_explain_query" that would also show at high level the internal sqlite3 operations a kind of mix of sqlite3_trace + "explain" that would give for this database example an output like this: ------- /test-sqlite-bug SQL: INSERT INTO "aa"("id","name") VALUES(10, 'daddad') SQL: DELETE FROM aa WHERE id=10 0|0|0|SEARCH TABLE aa USING INTEGER PRIMARY KEY (rowid=?) SQL: -- TRIGGER aa_delete_trigger 0|0|0|SCAN TABLE tbl for constrained_fkey_aa_id 0|0|0|SCAN TABLE tbl for constrained_fkey_ab_id 0|0|0|SCAN TABLE tbl for constrained_fkey_ac_id 0|0|0|SCAN TABLE tbl for constrained_fkey_ad_id 0|0|0|SCAN TABLE tbl for constrained_fkey_ad_id << duplicated constraint wold not exists if detected on create table 0|0|0|SCAN TABLE tbl for constrained_fkey_ad_id << duplicated constraint wold not exists if detected on create table 0|0|0|SCAN TABLE tbl for constrained_fkey_ad_id << duplicated constraint wold not exists if detected on create table 0|0|0|UPDATE TABLE tbl constrained_fkey_aa_id? "ON DELETE SET NULL" 0|0|0|UPDATE TABLE tbl constrained_fkey_aa_id? "ON DELETE SET NULL" ------- ? ----- the database "test-fkbugs.db" PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE aa(id INTEGER PRIMARY KEY, name TEXT); INSERT OR IGNORE INTO aa(id, name) VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'); CREATE TABLE IF NOT EXISTS tbl( ??? id? INTEGER PRIMARY KEY, ??? name varchar, ??? a_id INTEGER, ??? b_id INTEGER, ??? c_id INTEGER, ??? d_id INTEGER, ??? CONSTRAINT constrained_fkey_aa_id FOREIGN KEY(a_id) REFERENCES aa(id) ON DELETE SET NULL?? -- missing comma separator not detected ??? CONSTRAINT constrained_fkey_ab_id FOREIGN KEY(b_id) REFERENCES aa(id) ON DELETE SET NULL, ??? CONSTRAINT constrained_fkey_ac_id FOREIGN KEY(c_id) REFERENCES aa(id)? -- missing comma separator ??? CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES aa(id) ??? CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES aa(id) -- duplicated constraint not detected ??? CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES aa(id) -- duplicated constraint not detected ??? CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES aa(id) -- duplicated constraint not detected ); INSERT OR IGNORE INTO tbl(id, name) VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'); CREATE TRIGGER IF NOT EXISTS aa_delete_trigger BEFORE DELETE ON aa BEGIN ??? SELECT RAISE(ABORT, 'Can not remove root/admin user!') WHERE OLD.id = 1; END; COMMIT; ----- ----- the C program to see the sqlite3_trace/constraint problem "test-sqlite-bug.c" #include <stdio.h> #include "sqlite3.h" static void db_trace_callback(void *user, const char *sql) { ??? printf("SQL: %s\n", sql ? sql : "??"); } int main(int argc, char *argv[]) { ??? sqlite3 *db; ??? int rc = sqlite3_open("test-fkbugs.db", &db); ??? if(rc == SQLITE_OK) ??? { ??? ??? char *errmsg; ??? ??? const char insert_szSQL[] = "INSERT INTO aa(id,name) VALUES(10, 'daddad')"; ??? ??? const char delete_szSQL[] = "DELETE FROM aa WHERE id=10"; ??? ??? sqlite3_trace(db, db_trace_callback, NULL); ??? ??? rc = sqlite3_exec(db, insert_szSQL, NULL, NULL, &errmsg); ??? ??? rc = sqlite3_exec(db, delete_szSQL, NULL, NULL, &errmsg); ??? ??? sqlite3_close(db); ??? } ??? return 0; } ----- ----- the shell script to compile the C program MYINC=. gcc \ ??? -DTHREADSAFE=1 \ ??? -DSQLITE_DEFAULT_FILE_FORMAT=4 \ ??? -DSQLITE_DEFAULT_AUTOVACUUM=1 \ ??? -DSQLITE_DEFAULT_FOREIGN_KEYS=1 \ ??? -DSQLITE_ENABLE_COLUMN_METADATA=1 \ ??? -DSQLITE_ENABLE_FTS4=1 \ ??? -DSQLITE_ENABLE_FTS3_PARENTHESIS=1 \ ??? -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 \ ??? -DSQLITE_ENABLE_RTREE=1 \ ??? -DSQLITE_ENABLE_STAT4=1 \ ??? -DSQLITE_OMIT_TCL_VARIABLE=1 \ ??? -DSQLITE_USE_URI=1 \ ??? -DSQLITE_SOUNDEX=1\ ??? -o test-sqlite-bug test-sqlite-bug.c -I $MYINC $MYINC/sqlite3.c -lpthread -lm -ldl ----- ----- the output of the C program with comments /test-sqlite-bug SQL: INSERT INTO "aa"("id","name") VALUES(10, 'daddad') SQL: DELETE FROM aa WHERE id=10 SQL: -- TRIGGER aa_delete_trigger SQL: -- TRIGGER <<<<< this seems to be generated by the? "ON DELETE SET NULL" SQL: -- TRIGGER <<<<< this seems to be generated by the? "ON DELETE SET NULL" ----- ----- the output of sqlite3 test-fkbugs.db "explain query plan delete from aa where id=10"; 0|0|0|SEARCH TABLE aa USING INTEGER PRIMARY KEY (rowid=?) 0|0|0|SCAN TABLE tbl 0|0|0|SCAN TABLE tbl 0|0|0|SCAN TABLE tbl <<<<< one full scan for each foreign key even the duplicated ones not detected as error 0|0|0|SCAN TABLE tbl 0|0|0|SCAN TABLE tbl 0|0|0|SCAN TABLE tbl 0|0|0|SCAN TABLE tbl ------ ------ the output of sqlite3 test-fkbugs.db "explain? delete from aa where id=10"; 0|Init|0|64|0||00| 1|Null|0|1|0||00| 2|OpenWrite|0|2|0|2|00| 3|Integer|10|2|0||00| 4|MustBeInt|2|7|0||00| 5|NotExists|0|7|2||00| 6|Goto|0|8|0||00| 7|Goto|0|63|0||00| 8|Copy|2|3|0||00| 9|Program|3|63|6|program|01| 10|NotExists|0|63|2|1|00| 11|OpenRead|1|3|0|6|00| 12|Rewind|1|17|0||00| 13|Column|1|5|7||00| 14|Ne|3|16|7|(BINARY)|53| 15|FkCounter|0|1|0||00| 16|Next|1|13|0||01| 17|Close|1|0|0||00| 18|OpenRead|2|3|0|6|00| 19|Rewind|2|24|0||00| 20|Column|2|5|7||00| 21|Ne|3|23|7|(BINARY)|53| 22|FkCounter|0|1|0||00| 23|Next|2|20|0||01| 24|Close|2|0|0||00| 25|OpenRead|3|3|0|6|00| 26|Rewind|3|31|0||00| 27|Column|3|5|7||00| 28|Ne|3|30|7|(BINARY)|53| 29|FkCounter|0|1|0||00| 30|Next|3|27|0||01| 31|Close|3|0|0||00| 32|OpenRead|4|3|0|6|00| 33|Rewind|4|38|0||00| 34|Column|4|5|7||00| 35|Ne|3|37|7|(BINARY)|53| 36|FkCounter|0|1|0||00| 37|Next|4|34|0||01| 38|Close|4|0|0||00| 39|OpenRead|5|3|0|5|00| 40|Rewind|5|45|0||00| 41|Column|5|4|7||00| 42|Ne|3|44|7|(BINARY)|53| 43|FkCounter|0|1|0||00| 44|Next|5|41|0||01| 45|Close|5|0|0||00| 46|OpenRead|6|3|0|4|00| 47|Rewind|6|52|0||00| 48|Column|6|3|7||00| 49|Ne|3|51|7|(BINARY)|53| 50|FkCounter|0|1|0||00| 51|Next|6|48|0||01| 52|Close|6|0|0||00| 53|OpenRead|7|3|0|3|00| 54|Rewind|7|59|0||00| 55|Column|7|2|7||00| 56|Ne|3|58|7|(BINARY)|53| 57|FkCounter|0|1|0||00| 58|Next|7|55|0||01| 59|Close|7|0|0||00| 60|Delete|0|1|0|aa|00| 61|Program|3|0|9|program|00| 62|Program|3|0|10|program|00| 63|Halt|0|0|0||00| 64|Transaction|0|1|3|0|01| 65|TableLock|0|2|1|aa|00| 66|TableLock|0|3|1|tbl|00| 67|Goto|0|1|0||00| 0|Init|0|0|0|-- TRIGGER aa_delete_trigger|00| 1|Param|0|1|0||00| 2|Integer|1|2|0||00| 3|Ne|2|5|1||54| 4|Halt|1811|2|0|Can not remove root/admin user!|00| 5|Halt|0|0|0||00| 0|Init|0|0|0|-- TRIGGER |00|?? <<<<<<<< here should not be generating sqlite3_trace for the internal "ON DELETE SET NULL" 1|Null|0|1|2||00| 2|OpenRead|0|3|0|4|00| 3|Rewind|0|10|0||00| 4|Column|0|3|16||00| 5|Param|0|17|0||00| 6|Ne|17|9|16|(BINARY)|53| 7|Rowid|0|2|0||00| 8|RowSetAdd|1|2|0||00| 9|Next|0|4|0||01| 10|Close|0|0|0||00| 11|OpenWrite|0|3|0|6|00| 12|RowSetRead|1|40|2||00| 13|NotExists|0|12|2||00| 14|Rowid|0|3|0||00| 15|Null|0|4|0||00| 16|Column|0|2|5||00| 17|Column|0|3|6||00| 18|Column|0|4|7||00| 19|Column|0|5|8||00| 20|Copy|2|9|0||00| 21|Null|0|10|0||00| 22|Column|0|1|11||00| 23|Column|0|2|12||00| 24|Null|0|13|0||00| 25|Column|0|4|14||00| 26|Column|0|5|15||00| 27|FkIfZero|0|35|0||00| 28|IsNull|6|35|0||00| 29|SCopy|6|16|0||00| 30|MustBeInt|16|34|0||00| 31|OpenRead|1|2|0|2|00| 32|NotExists|1|34|16||00| 33|Goto|0|35|0||00| 34|FkCounter|0|-1|0||00| 35|Close|1|0|0||00| 36|Delete|0|0|0||00| 37|MakeRecord|10|6|16|DBDDDD|00| 38|Insert|0|16|9|tbl|05| 39|Goto|0|12|0||00| 40|Close|0|0|0||00| 41|ResetCount|0|0|0||00| 42|Halt|0|0|0||00| 0|Init|0|0|0|-- TRIGGER |00| 1|Null|0|1|2||00| 2|OpenRead|0|3|0|3|00| 3|Rewind|0|10|0||00| 4|Column|0|2|16||00| 5|Param|0|17|0||00| 6|Ne|17|9|16|(BINARY)|53| 7|Rowid|0|2|0||00| 8|RowSetAdd|1|2|0||00| 9|Next|0|4|0||01| 10|Close|0|0|0||00| 11|OpenWrite|0|3|0|6|00| 12|RowSetRead|1|40|2||00| 13|NotExists|0|12|2||00| 14|Rowid|0|3|0||00| 15|Null|0|4|0||00| 16|Column|0|2|5||00| 17|Column|0|3|6||00| 18|Column|0|4|7||00| 19|Column|0|5|8||00| 20|Copy|2|9|0||00| 21|Null|0|10|0||00| 22|Column|0|1|11||00| 23|Null|0|12|0||00| 24|Column|0|3|13||00| 25|Column|0|4|14||00| 26|Column|0|5|15||00| 27|FkIfZero|0|35|0||00| 28|IsNull|5|35|0||00| 29|SCopy|5|16|0||00| 30|MustBeInt|16|34|0||00| 31|OpenRead|1|2|0|2|00| 32|NotExists|1|34|16||00| 33|Goto|0|35|0||00| 34|FkCounter|0|-1|0||00| 35|Close|1|0|0||00| 36|Delete|0|0|0||00| 37|MakeRecord|10|6|16|DBDDDD|00| 38|Insert|0|16|9|tbl|05| 39|Goto|0|12|0||00| 40|Close|0|0|0||00| 41|ResetCount|0|0|0||00| 42|Halt|0|0|0||00| ------