The Debian security tracker <https://security-tracker.debian.org/>
uses an SQLite database to keep track of vulnerabilites and
generate reports.
We recently upgraded SQLite from 3.7.13 to 3.8.7.1 as part of an
operating system upgrade and experienced a crippling query planner
change. I verified that the issue is present in 3.8.10.2 as well.
Here are the details. A sample database file is available at
<https://people.debian.org/~fw/security.db.gz>.
CREATE TABLE source_packages
(name TEXT NOT NULL,
release TEXT NOT NULL,
subrelease TEXT NOT NULL,
archive TEXT NOT NULL,
version TEXT NOT NULL,
version_id INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (name, release, subrelease, archive));
CREATE TABLE package_notes
(id INTEGER NOT NULL PRIMARY KEY,
bug_name TEXT NOT NULL,
package TEXT NOT NULL,
fixed_version TEXT
CHECK (fixed_version IS NULL OR fixed_version <> ''),
fixed_version_id INTEGER NOT NULL DEFAULT 0,
release TEXT NOT NULL,
package_kind TEXT NOT NULL DEFAULT 'unknown',
urgency TEXT NOT NULL,
bug_origin TEXT NOT NULL DEFAULT '');
CREATE TABLE debian_bugs
(bug INTEGER NOT NULL,
note INTEGER NOT NULL,
PRIMARY KEY (bug, note));
CREATE TABLE bugs
(name TEXT NOT NULL PRIMARY KEY,
cve_status TEXT NOT NULL
CHECK (cve_status IN
('', 'CANDIDATE', 'ASSIGNED', 'RESERVED', 'REJECTED')),
not_for_us INTEGER NOT NULL CHECK (not_for_us IN (0, 1)),
description TEXT NOT NULL,
release_date TEXT NOT NULL,
source_file TEXT NOT NULL,
source_line INTEGER NOT NULL);
CREATE TABLE bugs_notes
(bug_name TEXT NOT NULL CHECK (typ <> ''),
typ TEXT NOT NULL CHECK (typ IN ('TODO', 'NOTE')),
release TEXT NOT NULL DEFAULT '',
comment TEXT NOT NULL CHECK (comment <> ''));
CREATE TABLE bugs_xref
(source TEXT NOT NULL,
target TEXT NOT NULL,
PRIMARY KEY (source, target));
CREATE TABLE bug_status
(bug_name TEXT NOT NULL,
release TEXT NOT NULL,
status TEXT NOT NULL
CHECK (status IN ('vulnerable', 'fixed', 'unknown', 'undetermined',
'partially-fixed', 'todo')),
reason TEXT NOT NULL,
PRIMARY KEY (bug_name, release));
CREATE TABLE source_package_status
(bug_name TEXT NOT NULL,
package INTEGER NOT NULL,
vulnerable INTEGER NOT NULL,
urgency TEXT NOT NULL,
PRIMARY KEY (bug_name, package));
CREATE TABLE removed_packages (name TEXT NOT NULL PRIMARY KEY);
CREATE TABLE nvd_data
(cve_name TEXT NOT NULL PRIMARY KEY,
cve_desc TEXT NOT NULL,
discovered TEXT NOT NULL,
published TEXT NOT NULL,
severity TEXT NOT NULL,
range_local INTEGER,
range_remote INTEGER,
range_user_init INTEGER,
loss_avail INTEGER NOT NULL,
loss_conf INTEGER NOT NULL,
loss_int INTEGER NOT NULL,
loss_sec_prot_user INTEGER NOT NULL,
loss_sec_prot_admin INTEGER NOT NULL,
loss_sec_prot_other INTEGER NOT NULL);
CREATE TABLE debsecan_data
(name TEXT NOT NULL PRIMARY KEY,
data TEXT NOT NULL);
CREATE TABLE package_notes_nodsa
(bug_name TEXT NOT NULL,
package TEXT NOT NULL,
release TEXT NOT NULL,
reason TEXT NOT NULL,
comment TEXT NOT NULL,
PRIMARY KEY (bug_name, package, release));
CREATE INDEX package_notes_package
ON package_notes(package);
CREATE INDEX bugs_xref_target ON bugs_xref(target);
CREATE INDEX source_package_status_package
ON source_package_status(package);
CREATE UNIQUE INDEX package_notes_bug
ON package_notes(bug_name, package, release);
CREATE VIEW debian_cve AS
SELECT DISTINCT debian_bugs.bug, st.bug_name
FROM package_notes, debian_bugs, source_package_status AS st
WHERE package_notes.bug_name = st.bug_name
AND debian_bugs.note = package_notes.id
ORDER BY debian_bugs.bug;
The offending query is:
SELECT sp.name, st.bug_name,
(SELECT cve_desc FROM nvd_data
WHERE cve_name = st.bug_name),
(SELECT debian_cve.bug FROM debian_cve
WHERE debian_cve.bug_name = st.bug_name
ORDER BY debian_cve.bug),
sp.release, sp.subrelease,
sp.version,
(SELECT pn.fixed_version FROM package_notes AS pn
WHERE pn.bug_name = st.bug_name
AND pn.package = sp.name AND
(pn.release = sp.release OR (pn.release = '' AND fixed_version != ''))),
st.vulnerable, st.urgency,
(SELECT range_remote FROM nvd_data
WHERE cve_name = st.bug_name),
(SELECT comment FROM package_notes_nodsa AS nd
WHERE nd.package = sp.name AND nd.release = sp.release
AND nd.bug_name = st.bug_name) AS nodsa
FROM source_package_status AS st, source_packages AS sp, bugs
WHERE sp.rowid = st.package AND st.bug_name = bugs.name
AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )
AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release = 'jessie'
OR sp.release = 'wheezy' OR sp.release = 'squeeze' )
ORDER BY sp.name, st.bug_name, sp.release, sp.subrelease;
The *old* explain output:
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00
1 SorterOpen 11 6 0 keyinfo(4,BINARY,BINARY) 00
2 String8 0 1 0 sid 00
3 String8 0 2 0 stretch 00
4 String8 0 3 0 jessie 00
5 String8 0 4 0 wheezy 00
6 String8 0 5 0 squeeze 00
7 Goto 0 255 0 00
8 OpenRead 0 14 0 4 00
9 OpenRead 1 2 0 5 00
10 OpenRead 12 8 0 keyinfo(1,BINARY) 00
11 Null 0 7 0 00
12 Integer 31 6 0 00
13 Rewind 0 22 0 00
14 String8 0 10 0 CVE-% 00
15 Column 0 0 11 00
16 Function 1 10 9 like(2) 02
17 IfNot 9 21 1 00
18 Rowid 0 8 0 00
19 RowSetTest 7 21 8 0 00
20 Gosub 6 32 0 00
21 Next 0 14 0 01
22 Rewind 0 31 0 00
23 String8 0 10 0 TEMP-% 00
24 Column 0 0 11 00
25 Function 1 10 9 like(2) 02
26 IfNot 9 30 1 00
27 Rowid 0 8 0 00
28 RowSetTest 7 30 8 -1 00
29 Gosub 6 32 0 00
30 Next 0 23 0 01
31 Goto 0 231 0 00
32 String8 0 10 0 CVE-% 00
33 Column 0 0 11 00
34 Function 1 10 9 like(2) 02
35 If 9 40 0 00
36 String8 0 10 0 TEMP-% 00
37 Column 0 0 11 00
38 Function 1 10 9 like(2) 02
39 IfNot 9 230 1 00
40 Column 0 1 9 00
41 MustBeInt 9 230 0 00
42 NotExists 1 230 9 00
43 Column 1 1 12 00
44 Eq 1 49 12 collseq(BINARY) 61
45 Eq 2 49 12 collseq(BINARY) 61
46 Eq 3 49 12 collseq(BINARY) 61
47 Eq 4 49 12 collseq(BINARY) 61
48 Ne 5 230 12 collseq(BINARY) 69
49 Column 0 0 14 00
50 IsNull 14 230 0 00
51 SeekGe 12 230 14 1 00
52 IdxGE 12 230 14 1 01
53 Column 1 0 15 00
54 Column 0 0 16 00
55 Null 0 27 0 00
56 Integer 1 28 0 00
57 OpenRead 3 18 0 2 00
58 OpenRead 13 19 0 keyinfo(1,BINARY) 00
59 Column 0 0 29 00
60 IsNull 29 68 0 00
61 SeekGe 13 68 29 1 00
62 IdxGE 13 68 29 1 01
63 IdxRowid 13 9 0 00
64 Seek 3 9 0 00
65 Column 3 1 30 00
66 Move 30 27 1 00
67 IfZero 28 68 -1 00
68 Close 3 0 0 00
69 Close 13 0 0 00
70 Copy 27 17 0 00
71 Null 0 31 0 00
72 Integer 101 32 0 00
73 Once 0 101 0 00
74 Noop 0 0 0 00
75 OpenEphemeral 4 2 0 00
76 OpenEphemeral 15 0 0 keyinfo(2,BINARY,BINARY) 08
77 OpenRead 16 6 0 keyinfo(2,BINARY,BINARY) 00
78 OpenRead 5 4 0 2 00
79 OpenRead 17 15 0 keyinfo(2,BINARY,BINARY) 00
80 Rewind 16 98 33 0 00
81 Column 16 1 9 00
82 MustBeInt 9 97 0 00
83 NotExists 5 97 9 00
84 Column 5 1 33 00
85 IsNull 33 97 0 00
86 SeekGe 17 97 33 1 00
87 IdxGE 17 97 33 1 01
88 Column 16 0 34 00
89 Column 17 0 35 00
90 Found 15 96 34 2 00
91 MakeRecord 34 2 9 00
92 IdxInsert 15 9 0 00
93 MakeRecord 34 2 9 00
94 NewRowid 4 13 0 00
95 Insert 4 9 13 08
96 Next 17 87 0 00
97 Next 16 81 0 00
98 Close 16 0 0 00
99 Close 5 0 0 00
100 Close 17 0 0 00
101 Return 32 0 0 00
102 OpenEphemeral 18 3 0 keyinfo(1,BINARY) 00
103 Integer 1 36 0 00
104 Once 1 113 0 00
105 OpenAutoindex 19 3 0 keyinfo(2,BINARY,BINARY) 00
106 Rewind 4 113 0 00
107 Rowid 4 40 0 00
108 Column 4 1 38 00
109 Column 4 0 39 00
110 MakeRecord 38 3 37 00
111 IdxInsert 19 37 0 10
112 Next 4 107 0 03
113 Column 0 0 41 00
114 IsNull 41 129 0 00
115 SeekGe 19 129 41 1 00
116 IdxGE 19 129 41 1 01
117 Column 19 1 42 00
118 Column 19 1 38 00
119 Sequence 18 39 0 00
120 Move 42 40 1 00
121 MakeRecord 38 3 37 00
122 IdxInsert 18 37 0 00
123 IfZero 36 126 0 00
124 AddImm 36 -1 0 00
125 Goto 0 128 0 00
126 Last 18 0 0 00
127 Delete 18 0 0 00
128 Next 19 116 0 00
129 Sort 18 133 0 00
130 Column 18 2 37 00
131 Move 37 31 1 00
132 Next 18 130 0 00
133 Copy 31 18 0 00
134 Column 1 1 19 00
135 Column 1 2 20 00
136 Column 1 4 21 00
137 Null 0 44 0 00
138 Integer 1 45 0 00
139 OpenRead 8 4 0 6 00
140 Null 0 47 0 00
141 Integer 170 46 0 00
142 OpenRead 20 29 0 keyinfo(3,BINARY,BINARY) 00
143 Column 0 0 49 00
144 IsNull 49 155 0 00
145 Column 1 0 50 00
146 IsNull 50 155 0 00
147 Column 1 1 51 00
148 IsNull 51 155 0 00
149 SeekGe 20 155 49 3 00
150 IdxGE 20 155 49 3 01
151 IdxRowid 20 43 0 00
152 Seek 8 43 0 00
153 RowSetTest 47 155 43 0 00
154 Gosub 46 171 0 00
155 OpenRead 21 29 0 keyinfo(3,BINARY,BINARY) 00
156 Column 0 0 52 00
157 IsNull 52 170 0 00
158 Column 1 0 53 00
159 IsNull 53 170 0 00
160 String8 0 54 0 00
161 SeekGe 21 170 52 3 00
162 IdxGE 21 170 52 3 01
163 IdxRowid 21 37 0 00
164 Seek 8 37 0 00
165 Column 8 3 55 00
166 String8 0 56 0 00
167 Eq 56 170 55 collseq(BINARY) 69
168 RowSetTest 47 170 37 1 00
169 Gosub 46 171 0 00
170 Goto 0 181 0 00
171 Column 8 1 55 00
172 Column 0 0 56 00
173 Ne 56 180 55 collseq(BINARY) 6a
174 Column 8 2 57 00
175 Column 1 0 58 00
176 Ne 58 180 57 collseq(BINARY) 6a
177 Column 8 3 59 00
178 Move 59 44 1 00
179 IfZero 45 181 -1 00
180 Return 46 0 0 00
181 Close 8 0 0 00
182 Copy 44 22 0 00
183 Column 0 2 23 00
184 Column 0 3 24 00
185 Null 0 60 0 00
186 Integer 1 61 0 00
187 OpenRead 9 18 0 7 00
188 OpenRead 22 19 0 keyinfo(1,BINARY) 00
189 Column 0 0 62 00
190 IsNull 62 198 0 00
191 SeekGe 22 198 62 1 00
192 IdxGE 22 198 62 1 01
193 IdxRowid 22 58 0 00
194 Seek 9 58 0 00
195 Column 9 6 63 00
196 Move 63 60 1 00
197 IfZero 61 198 -1 00
198 Close 9 0 0 00
199 Close 22 0 0 00
200 Copy 60 25 0 00
201 Null 0 64 0 00
202 Integer 1 65 0 00
203 OpenRead 10 22 0 5 00
204 OpenRead 23 23 0 keyinfo(3,BINARY,BINARY) 00
205 Column 0 0 66 00
206 IsNull 66 218 0 00
207 Column 1 0 67 00
208 IsNull 67 218 0 00
209 Column 1 1 68 00
210 IsNull 68 218 0 00
211 SeekGe 23 218 66 3 00
212 IdxGE 23 218 66 3 01
213 IdxRowid 23 58 0 00
214 Seek 10 58 0 00
215 Column 10 4 69 00
216 Move 69 64 1 00
217 IfZero 65 218 -1 00
218 Close 10 0 0 00
219 Close 23 0 0 00
220 Copy 64 26 0 00
221 MakeRecord 15 12 58 00
222 Column 1 0 70 00
223 Column 0 0 71 00
224 Column 1 1 72 00
225 Column 1 2 73 00
226 Sequence 11 74 0 00
227 Move 58 75 1 00
228 MakeRecord 70 6 57 00
229 SorterInsert 11 57 0 00
230 Return 6 0 0 00
231 Close 0 0 0 00
232 Close 1 0 0 00
233 Close 12 0 0 00
234 OpenPseudo 24 58 12 00
235 OpenPseudo 25 76 6 00
236 SorterSort 11 253 0 00
237 SorterData 11 76 0 00
238 Column 25 5 58 20
239 Column 24 0 15 20
240 Column 24 1 16 00
241 Column 24 2 17 00
242 Column 24 3 18 00
243 Column 24 4 19 00
244 Column 24 5 20 00
245 Column 24 6 21 00
246 Column 24 7 22 00
247 Column 24 8 23 00
248 Column 24 9 24 00
249 Column 24 10 25 00
250 Column 24 11 26 00
251 ResultRow 15 12 0 00
252 SorterNext 11 237 0 00
253 Close 24 0 0 00
254 Halt 0 0 0 00
255 Transaction 0 0 0 00
256 VerifyCookie 0 27 0 00
257 TableLock 0 14 0 source_package_status 00
258 TableLock 0 2 0 source_packages 00
259 TableLock 0 7 0 bugs 00
260 TableLock 0 18 0 nvd_data 00
261 TableLock 0 5 0 debian_bugs 00
262 TableLock 0 4 0 package_notes 00
263 TableLock 0 22 0 package_notes_nodsa 00
264 Goto 0 8 0 00
The *new* explain output:
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 190 0 00
1 SorterOpen 11 17 0 k(4,B,B,B,B) 00
2 OpenRead 0 14 0 4 00
3 OpenRead 1 2 0 5 00
4 OpenRead 12 8 0 k(1,nil) 02
5 Rewind 0 169 0 00
6 Column 0 0 3 00
7 Function 1 2 1 like(2) 02
8 If 1 12 0 00
9 Column 0 0 5 00
10 Function 1 4 1 like(2) 02
11 IfNot 1 168 1 00
12 Column 0 1 6 00
13 MustBeInt 6 168 0 00
14 NotExists 1 168 6 00
15 Column 1 1 1 00
16 Eq 7 21 1 (BINARY) 42
17 Eq 9 21 1 (BINARY) 42
18 Eq 10 21 1 (BINARY) 42
19 Eq 11 21 1 (BINARY) 42
20 Ne 12 168 1 (BINARY) 52
21 Column 0 0 13 00
22 SeekGE 12 168 13 1 00
23 IdxGT 12 168 13 1 00
24 Column 1 0 18 00
25 Copy 13 19 0 00
26 Null 0 30 0 00
27 Integer 1 31 0 00
28 OpenRead 3 18 0 2 00
29 OpenRead 13 19 0 k(1,nil) 02
30 Column 0 0 32 00
31 SeekGE 13 37 32 1 00
32 IdxGT 13 37 32 1 00
33 IdxRowid 13 33 0 00
34 Seek 3 33 0 00
35 Column 3 1 30 00
36 DecrJumpZero 31 37 0 00
37 Close 3 0 0 00
38 Close 13 0 0 00
39 Copy 30 20 0 00
40 Null 0 34 0 00
41 InitCoroutine 35 66 42 00
42 Noop 0 0 0 00
43 OpenEphemeral 15 0 0 k(2,B,B) 08
44 OpenRead 16 6 0 k(2,nil,nil) 00
45 OpenRead 5 4 0 2 00
46 OpenRead 17 15 0 k(2,nil,nil) 02
47 Rewind 16 62 36 0 00
48 Column 16 1 36 00
49 MustBeInt 36 61 0 00
50 NotExists 5 61 36 00
51 Column 5 1 37 00
52 SeekGE 17 61 37 1 00
53 IdxGT 17 61 37 1 00
54 Column 16 0 38 00
55 Column 17 0 39 00
56 Found 15 60 38 2 00
57 MakeRecord 38 2 8 00
58 IdxInsert 15 8 0 00
59 Yield 35 0 0 00
60 Next 17 53 0 00
61 Next 16 48 0 01
62 Close 16 0 0 00
63 Close 5 0 0 00
64 Close 17 0 0 00
65 EndCoroutine 35 0 0 00
66 OpenEphemeral 18 3 0 k(1,B) 00
67 Integer 1 40 0 00
68 InitCoroutine 35 0 42 00
69 Yield 35 83 0 00
70 Copy 39 41 0 00
71 Column 0 0 42 00
72 Ne 42 82 41 (BINARY) 51
73 Copy 38 34 0 00
74 Copy 34 44 0 00
75 Sequence 18 45 0 00
76 Move 34 46 1 00
77 MakeRecord 44 3 43 00
78 IdxInsert 18 43 0 00
79 IfNotZero 40 82 -1 00
80 Last 18 0 0 00
81 Delete 18 0 0 00
82 Goto 0 69 0 00
83 Sort 18 87 0 00
84 Column 18 2 41 00
85 Move 41 34 1 00
86 Next 18 84 0 00
87 Copy 34 21 0 00
88 Column 1 1 22 00
89 Column 1 2 23 00
90 Column 1 4 24 00
91 Null 0 47 0 00
92 Integer 1 48 0 00
93 OpenRead 8 4 0 6 00
94 Null 0 50 0 00
95 Integer 118 49 0 00
96 OpenRead 19 29 0 k(3,nil,nil,nil) 02
97 Column 0 0 52 00
98 Column 1 0 53 00
99 Column 1 1 54 00
100 SeekGE 19 106 52 3 00
101 IdxGT 19 106 52 3 00
102 IdxRowid 19 55 0 00
103 Seek 8 55 0 00
104 RowSetTest 50 106 55 0 00
105 Gosub 49 119 0 00
106 ReopenIdx 19 29 0 k(3,nil,nil,nil) 02
107 Column 0 0 56 00
108 Column 1 0 57 00
109 String8 0 58 0 00
110 SeekGE 19 118 56 3 00
111 IdxGT 19 118 56 3 00
112 IdxRowid 19 59 0 00
113 Seek 8 59 0 00
114 Column 8 3 42 00
115 Eq 60 118 42 (BINARY) 52
116 RowSetTest 50 118 59 1 00
117 Gosub 49 119 0 00
118 Goto 0 128 0 00
119 Column 19 0 42 00
120 Column 0 0 41 00
121 Ne 41 127 42 (BINARY) 51
122 Column 19 1 61 00
123 Column 1 0 62 00
124 Ne 62 127 61 (BINARY) 51
125 Column 8 3 47 00
126 DecrJumpZero 48 128 0 00
127 Return 49 0 0 00
128 Close 8 0 0 00
129 Copy 47 25 0 00
130 Column 0 2 26 00
131 Column 0 3 27 00
132 Null 0 63 0 00
133 Integer 1 64 0 00
134 OpenRead 9 18 0 7 00
135 OpenRead 20 19 0 k(1,nil) 02
136 Column 0 0 65 00
137 SeekGE 20 143 65 1 00
138 IdxGT 20 143 65 1 00
139 IdxRowid 20 66 0 00
140 Seek 9 66 0 00
141 Column 9 6 63 00
142 DecrJumpZero 64 143 0 00
143 Close 9 0 0 00
144 Close 20 0 0 00
145 Copy 63 28 0 00
146 Null 0 67 0 00
147 Integer 1 68 0 00
148 OpenRead 10 22 0 5 00
149 OpenRead 21 23 0 k(3,nil,nil,nil) 02
150 Column 0 0 69 00
151 Column 1 0 70 00
152 Column 1 1 71 00
153 SeekGE 21 159 69 3 00
154 IdxGT 21 159 69 3 00
155 IdxRowid 21 72 0 00
156 Seek 10 72 0 00
157 Column 10 4 67 00
158 DecrJumpZero 68 159 0 00
159 Close 10 0 0 00
160 Close 21 0 0 00
161 Copy 67 29 0 00
162 Column 1 0 14 00
163 Column 0 0 15 00
164 Column 1 1 16 00
165 Column 1 2 17 00
166 MakeRecord 14 16 73 00
167 SorterInsert 11 73 0 00
168 Next 0 6 0 01
169 Close 0 0 0 00
170 Close 1 0 0 00
171 Close 12 0 0 00
172 OpenPseudo 22 74 17 00
173 SorterSort 11 189 0 00
174 SorterData 11 74 22 00
175 Column 22 4 18 00
176 Column 22 5 19 00
177 Column 22 6 20 00
178 Column 22 7 21 00
179 Column 22 8 22 00
180 Column 22 9 23 00
181 Column 22 10 24 00
182 Column 22 11 25 00
183 Column 22 12 26 00
184 Column 22 13 27 00
185 Column 22 14 28 00
186 Column 22 15 29 00
187 ResultRow 18 12 0 00
188 SorterNext 11 174 0 00
189 Halt 0 0 0 00
190 Transaction 0 0 27 0 01
191 TableLock 0 14 0 source_package_status 00
192 TableLock 0 2 0 source_packages 00
193 TableLock 0 7 0 bugs 00
194 TableLock 0 18 0 nvd_data 00
195 TableLock 0 5 0 debian_bugs 00
196 TableLock 0 4 0 package_notes 00
197 TableLock 0 22 0 package_notes_nodsa 00
198 String8 0 2 0 CVE-% 00
199 String8 0 4 0 TEMP-% 00
200 String8 0 7 0 sid 00
201 String8 0 9 0 stretch 00
202 String8 0 10 0 jessie 00
203 String8 0 11 0 wheezy 00
204 String8 0 12 0 squeeze 00
205 String8 0 60 0 00
206 Goto 0 1 0 00
I have not run the query to completion using the new SQLite version.
I will figure out a way to rewrite the query so that it runs
reasonably fast again (which will address our immediate needs), but
maybe there is something that can be fixed in the planner as well.