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|
------
