wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2009 August 06 |
2 | # |
||
3 | # The author disclaims copyright to this source code. In place of |
||
4 | # a legal notice, here is a blessing: |
||
5 | # |
||
6 | # May you do good and not evil. |
||
7 | # May you find forgiveness for yourself and forgive others. |
||
8 | # May you share freely, never taking more than you give. |
||
9 | # |
||
10 | #*********************************************************************** |
||
11 | # |
||
12 | # This file implements regression tests for SQLite library. This file |
||
13 | # implements tests for range and LIKE constraints that use bound variables |
||
14 | # instead of literal constant arguments. |
||
15 | # |
||
16 | |||
17 | set testdir [file dirname $argv0] |
||
18 | source $testdir/tester.tcl |
||
19 | |||
20 | ifcapable !stat2 { |
||
21 | finish_test |
||
22 | return |
||
23 | } |
||
24 | |||
25 | #---------------------------------------------------------------------- |
||
26 | # Test Organization: |
||
27 | # |
||
28 | # analyze3-1.*: Test that the values of bound parameters are considered |
||
29 | # in the same way as constants when planning queries that |
||
30 | # use range constraints. |
||
31 | # |
||
32 | # analyze3-2.*: Test that the values of bound parameters are considered |
||
33 | # in the same way as constants when planning queries that |
||
34 | # use LIKE expressions in the WHERE clause. |
||
35 | # |
||
36 | # analyze3-3.*: Test that binding to a variable does not invalidate the |
||
37 | # query plan when there is no way in which replanning the |
||
38 | # query may produce a superior outcome. |
||
39 | # |
||
40 | # analyze3-4.*: Test that SQL or authorization callback errors occuring |
||
41 | # within sqlite3Reprepare() are handled correctly. |
||
42 | # |
||
43 | # analyze3-5.*: Check that the query plans of applicable statements are |
||
44 | # invalidated if the values of SQL parameter are modified |
||
45 | # using the clear_bindings() or transfer_bindings() APIs. |
||
46 | # |
||
47 | |||
48 | proc getvar {varname} { uplevel #0 set $varname } |
||
49 | db function var getvar |
||
50 | |||
51 | proc eqp {sql {db db}} { |
||
52 | uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db |
||
53 | } |
||
54 | |||
55 | proc sf_execsql {sql {db db}} { |
||
56 | set ::sqlite_search_count 0 |
||
57 | set r [uplevel [list execsql $sql $db]] |
||
58 | |||
59 | concat $::sqlite_search_count [$db status step] $r |
||
60 | } |
||
61 | |||
62 | #------------------------------------------------------------------------- |
||
63 | # |
||
64 | # analyze3-1.1.1: |
||
65 | # Create a table with two columns. Populate the first column (affinity |
||
66 | # INTEGER) with integer values from 100 to 1100. Create an index on this |
||
67 | # column. ANALYZE the table. |
||
68 | # |
||
69 | # analyze3-1.1.2 - 3.1.3 |
||
70 | # Show that there are two possible plans for querying the table with |
||
71 | # a range constraint on the indexed column - "full table scan" or "use |
||
72 | # the index". When the range is specified using literal values, SQLite |
||
73 | # is able to pick the best plan based on the samples in sqlite_stat2. |
||
74 | # |
||
75 | # analyze3-1.1.4 - 3.1.9 |
||
76 | # Show that using SQL variables produces the same results as using |
||
77 | # literal values to constrain the range scan. |
||
78 | # |
||
79 | # These tests also check that the compiler code considers column |
||
80 | # affinities when estimating the number of rows scanned by the "use |
||
81 | # index strategy". |
||
82 | # |
||
83 | do_test analyze3-1.1.1 { |
||
84 | execsql { |
||
85 | BEGIN; |
||
86 | CREATE TABLE t1(x INTEGER, y); |
||
87 | CREATE INDEX i1 ON t1(x); |
||
88 | } |
||
89 | for {set i 0} {$i < 1000} {incr i} { |
||
90 | execsql { INSERT INTO t1 VALUES($i+100, $i) } |
||
91 | } |
||
92 | execsql { |
||
93 | COMMIT; |
||
94 | ANALYZE; |
||
95 | } |
||
96 | } {} |
||
97 | |||
98 | do_eqp_test analyze3-1.1.2 { |
||
99 | SELECT sum(y) FROM t1 WHERE x>200 AND x<300 |
||
100 | } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~100 rows)}} |
||
101 | do_eqp_test analyze3-1.1.3 { |
||
102 | SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 |
||
103 | } {0 0 0 {SCAN TABLE t1 (~111 rows)}} |
||
104 | |||
105 | do_test analyze3-1.1.4 { |
||
106 | sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 } |
||
107 | } {199 0 14850} |
||
108 | do_test analyze3-1.1.5 { |
||
109 | set l [string range "200" 0 end] |
||
110 | set u [string range "300" 0 end] |
||
111 | sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u } |
||
112 | } {199 0 14850} |
||
113 | do_test analyze3-1.1.6 { |
||
114 | set l [expr int(200)] |
||
115 | set u [expr int(300)] |
||
116 | sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u } |
||
117 | } {199 0 14850} |
||
118 | do_test analyze3-1.1.7 { |
||
119 | sf_execsql { SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 } |
||
120 | } {999 999 499500} |
||
121 | do_test analyze3-1.1.8 { |
||
122 | set l [string range "0" 0 end] |
||
123 | set u [string range "1100" 0 end] |
||
124 | sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u } |
||
125 | } {999 999 499500} |
||
126 | do_test analyze3-1.1.9 { |
||
127 | set l [expr int(0)] |
||
128 | set u [expr int(1100)] |
||
129 | sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u } |
||
130 | } {999 999 499500} |
||
131 | |||
132 | |||
133 | # The following tests are similar to the block above. The difference is |
||
134 | # that the indexed column has TEXT affinity in this case. In the tests |
||
135 | # above the affinity is INTEGER. |
||
136 | # |
||
137 | do_test analyze3-1.2.1 { |
||
138 | execsql { |
||
139 | BEGIN; |
||
140 | CREATE TABLE t2(x TEXT, y); |
||
141 | INSERT INTO t2 SELECT * FROM t1; |
||
142 | CREATE INDEX i2 ON t2(x); |
||
143 | COMMIT; |
||
144 | ANALYZE; |
||
145 | } |
||
146 | } {} |
||
147 | do_eqp_test analyze3-1.2.2 { |
||
148 | SELECT sum(y) FROM t2 WHERE x>1 AND x<2 |
||
149 | } {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~200 rows)}} |
||
150 | do_eqp_test analyze3-1.2.3 { |
||
151 | SELECT sum(y) FROM t2 WHERE x>0 AND x<99 |
||
152 | } {0 0 0 {SCAN TABLE t2 (~111 rows)}} |
||
153 | do_test analyze3-1.2.4 { |
||
154 | sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 } |
||
155 | } {161 0 4760} |
||
156 | do_test analyze3-1.2.5 { |
||
157 | set l [string range "12" 0 end] |
||
158 | set u [string range "20" 0 end] |
||
159 | sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u} |
||
160 | } {161 0 text text 4760} |
||
161 | do_test analyze3-1.2.6 { |
||
162 | set l [expr int(12)] |
||
163 | set u [expr int(20)] |
||
164 | sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u} |
||
165 | } {161 0 integer integer 4760} |
||
166 | do_test analyze3-1.2.7 { |
||
167 | sf_execsql { SELECT sum(y) FROM t2 WHERE x>0 AND x<99 } |
||
168 | } {999 999 490555} |
||
169 | do_test analyze3-1.2.8 { |
||
170 | set l [string range "0" 0 end] |
||
171 | set u [string range "99" 0 end] |
||
172 | sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u} |
||
173 | } {999 999 text text 490555} |
||
174 | do_test analyze3-1.2.9 { |
||
175 | set l [expr int(0)] |
||
176 | set u [expr int(99)] |
||
177 | sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u} |
||
178 | } {999 999 integer integer 490555} |
||
179 | |||
180 | # Same tests a third time. This time, column x has INTEGER affinity and |
||
181 | # is not the leftmost column of the table. This triggered a bug causing |
||
182 | # SQLite to use sub-optimal query plans in 3.6.18 and earlier. |
||
183 | # |
||
184 | do_test analyze3-1.3.1 { |
||
185 | execsql { |
||
186 | BEGIN; |
||
187 | CREATE TABLE t3(y TEXT, x INTEGER); |
||
188 | INSERT INTO t3 SELECT y, x FROM t1; |
||
189 | CREATE INDEX i3 ON t3(x); |
||
190 | COMMIT; |
||
191 | ANALYZE; |
||
192 | } |
||
193 | } {} |
||
194 | do_eqp_test analyze3-1.3.2 { |
||
195 | SELECT sum(y) FROM t3 WHERE x>200 AND x<300 |
||
196 | } {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~100 rows)}} |
||
197 | do_eqp_test analyze3-1.3.3 { |
||
198 | SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 |
||
199 | } {0 0 0 {SCAN TABLE t3 (~111 rows)}} |
||
200 | |||
201 | do_test analyze3-1.3.4 { |
||
202 | sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 } |
||
203 | } {199 0 14850} |
||
204 | do_test analyze3-1.3.5 { |
||
205 | set l [string range "200" 0 end] |
||
206 | set u [string range "300" 0 end] |
||
207 | sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u } |
||
208 | } {199 0 14850} |
||
209 | do_test analyze3-1.3.6 { |
||
210 | set l [expr int(200)] |
||
211 | set u [expr int(300)] |
||
212 | sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u } |
||
213 | } {199 0 14850} |
||
214 | do_test analyze3-1.3.7 { |
||
215 | sf_execsql { SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 } |
||
216 | } {999 999 499500} |
||
217 | do_test analyze3-1.3.8 { |
||
218 | set l [string range "0" 0 end] |
||
219 | set u [string range "1100" 0 end] |
||
220 | sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u } |
||
221 | } {999 999 499500} |
||
222 | do_test analyze3-1.3.9 { |
||
223 | set l [expr int(0)] |
||
224 | set u [expr int(1100)] |
||
225 | sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u } |
||
226 | } {999 999 499500} |
||
227 | |||
228 | #------------------------------------------------------------------------- |
||
229 | # Test that the values of bound SQL variables may be used for the LIKE |
||
230 | # optimization. |
||
231 | # |
||
232 | drop_all_tables |
||
233 | do_test analyze3-2.1 { |
||
234 | execsql { |
||
235 | PRAGMA case_sensitive_like=off; |
||
236 | BEGIN; |
||
237 | CREATE TABLE t1(a, b TEXT COLLATE nocase); |
||
238 | CREATE INDEX i1 ON t1(b); |
||
239 | } |
||
240 | for {set i 0} {$i < 1000} {incr i} { |
||
241 | set t "" |
||
242 | append t [lindex {a b c d e f g h i j} [expr $i/100]] |
||
243 | append t [lindex {a b c d e f g h i j} [expr ($i/10)%10]] |
||
244 | append t [lindex {a b c d e f g h i j} [expr ($i%10)]] |
||
245 | execsql { INSERT INTO t1 VALUES($i, $t) } |
||
246 | } |
||
247 | execsql COMMIT |
||
248 | } {} |
||
249 | do_eqp_test analyze3-2.2 { |
||
250 | SELECT count(a) FROM t1 WHERE b LIKE 'a%' |
||
251 | } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?) (~30000 rows)}} |
||
252 | do_eqp_test analyze3-2.3 { |
||
253 | SELECT count(a) FROM t1 WHERE b LIKE '%a' |
||
254 | } {0 0 0 {SCAN TABLE t1 (~500000 rows)}} |
||
255 | |||
256 | do_test analyze3-2.4 { |
||
257 | sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' } |
||
258 | } {101 0 100} |
||
259 | do_test analyze3-2.5 { |
||
260 | sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE '%a' } |
||
261 | } {999 999 100} |
||
262 | |||
263 | do_test analyze3-2.4 { |
||
264 | set like "a%" |
||
265 | sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like } |
||
266 | } {101 0 100} |
||
267 | do_test analyze3-2.5 { |
||
268 | set like "%a" |
||
269 | sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like } |
||
270 | } {999 999 100} |
||
271 | do_test analyze3-2.6 { |
||
272 | set like "a" |
||
273 | sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like } |
||
274 | } {101 0 0} |
||
275 | do_test analyze3-2.7 { |
||
276 | set like "ab" |
||
277 | sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like } |
||
278 | } {11 0 0} |
||
279 | do_test analyze3-2.8 { |
||
280 | set like "abc" |
||
281 | sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like } |
||
282 | } {2 0 1} |
||
283 | do_test analyze3-2.9 { |
||
284 | set like "a_c" |
||
285 | sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like } |
||
286 | } {101 0 10} |
||
287 | |||
288 | |||
289 | #------------------------------------------------------------------------- |
||
290 | # This block of tests checks that statements are correctly marked as |
||
291 | # expired when the values bound to any parameters that may affect the |
||
292 | # query plan are modified. |
||
293 | # |
||
294 | drop_all_tables |
||
295 | db auth auth |
||
296 | proc auth {args} { |
||
297 | set ::auth 1 |
||
298 | return SQLITE_OK |
||
299 | } |
||
300 | |||
301 | do_test analyze3-3.1 { |
||
302 | execsql { |
||
303 | BEGIN; |
||
304 | CREATE TABLE t1(a, b, c); |
||
305 | CREATE INDEX i1 ON t1(b); |
||
306 | } |
||
307 | for {set i 0} {$i < 100} {incr i} { |
||
308 | execsql { INSERT INTO t1 VALUES($i, $i, $i) } |
||
309 | } |
||
310 | execsql COMMIT |
||
311 | execsql ANALYZE |
||
312 | } {} |
||
313 | |||
314 | do_test analyze3-3.2.1 { |
||
315 | set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b>?" -1 dummy] |
||
316 | sqlite3_expired $S |
||
317 | } {0} |
||
318 | do_test analyze3-3.2.2 { |
||
319 | sqlite3_bind_text $S 1 "abc" 3 |
||
320 | sqlite3_expired $S |
||
321 | } {1} |
||
322 | do_test analyze3-3.2.4 { |
||
323 | sqlite3_finalize $S |
||
324 | } {SQLITE_OK} |
||
325 | |||
326 | do_test analyze3-3.2.5 { |
||
327 | set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b=?" -1 dummy] |
||
328 | sqlite3_expired $S |
||
329 | } {0} |
||
330 | do_test analyze3-3.2.6 { |
||
331 | sqlite3_bind_text $S 1 "abc" 3 |
||
332 | sqlite3_expired $S |
||
333 | } {0} |
||
334 | do_test analyze3-3.2.7 { |
||
335 | sqlite3_finalize $S |
||
336 | } {SQLITE_OK} |
||
337 | |||
338 | do_test analyze3-3.4.1 { |
||
339 | set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy] |
||
340 | sqlite3_expired $S |
||
341 | } {0} |
||
342 | do_test analyze3-3.4.2 { |
||
343 | sqlite3_bind_text $S 1 "abc" 3 |
||
344 | sqlite3_expired $S |
||
345 | } {0} |
||
346 | do_test analyze3-3.4.3 { |
||
347 | sqlite3_bind_text $S 2 "def" 3 |
||
348 | sqlite3_expired $S |
||
349 | } {1} |
||
350 | do_test analyze3-3.4.4 { |
||
351 | sqlite3_bind_text $S 2 "ghi" 3 |
||
352 | sqlite3_expired $S |
||
353 | } {1} |
||
354 | do_test analyze3-3.4.5 { |
||
355 | sqlite3_expired $S |
||
356 | } {1} |
||
357 | do_test analyze3-3.4.6 { |
||
358 | sqlite3_finalize $S |
||
359 | } {SQLITE_OK} |
||
360 | |||
361 | do_test analyze3-3.5.1 { |
||
362 | set S [sqlite3_prepare_v2 db { |
||
363 | SELECT * FROM t1 WHERE a IN ( |
||
364 | ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, |
||
365 | ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20, |
||
366 | ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31 |
||
367 | ) AND b>?32; |
||
368 | } -1 dummy] |
||
369 | sqlite3_expired $S |
||
370 | } {0} |
||
371 | do_test analyze3-3.5.2 { |
||
372 | sqlite3_bind_text $S 31 "abc" 3 |
||
373 | sqlite3_expired $S |
||
374 | } {0} |
||
375 | do_test analyze3-3.5.3 { |
||
376 | sqlite3_bind_text $S 32 "def" 3 |
||
377 | sqlite3_expired $S |
||
378 | } {1} |
||
379 | do_test analyze3-3.5.5 { |
||
380 | sqlite3_finalize $S |
||
381 | } {SQLITE_OK} |
||
382 | |||
383 | do_test analyze3-3.6.1 { |
||
384 | set S [sqlite3_prepare_v2 db { |
||
385 | SELECT * FROM t1 WHERE a IN ( |
||
386 | ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, |
||
387 | ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20, |
||
388 | ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31, ?32 |
||
389 | ) AND b>?33; |
||
390 | } -1 dummy] |
||
391 | sqlite3_expired $S |
||
392 | } {0} |
||
393 | do_test analyze3-3.6.2 { |
||
394 | sqlite3_bind_text $S 32 "abc" 3 |
||
395 | sqlite3_expired $S |
||
396 | } {1} |
||
397 | do_test analyze3-3.6.3 { |
||
398 | sqlite3_bind_text $S 33 "def" 3 |
||
399 | sqlite3_expired $S |
||
400 | } {1} |
||
401 | do_test analyze3-3.6.5 { |
||
402 | sqlite3_finalize $S |
||
403 | } {SQLITE_OK} |
||
404 | |||
405 | do_test analyze3-3.7.1 { |
||
406 | set S [sqlite3_prepare_v2 db { |
||
407 | SELECT * FROM t1 WHERE a IN ( |
||
408 | ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?33, |
||
409 | ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20, |
||
410 | ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31, ?32 |
||
411 | ) AND b>?10; |
||
412 | } -1 dummy] |
||
413 | sqlite3_expired $S |
||
414 | } {0} |
||
415 | do_test analyze3-3.7.2 { |
||
416 | sqlite3_bind_text $S 32 "abc" 3 |
||
417 | sqlite3_expired $S |
||
418 | } {0} |
||
419 | do_test analyze3-3.7.3 { |
||
420 | sqlite3_bind_text $S 33 "def" 3 |
||
421 | sqlite3_expired $S |
||
422 | } {0} |
||
423 | do_test analyze3-3.7.4 { |
||
424 | sqlite3_bind_text $S 10 "def" 3 |
||
425 | sqlite3_expired $S |
||
426 | } {1} |
||
427 | do_test analyze3-3.7.6 { |
||
428 | sqlite3_finalize $S |
||
429 | } {SQLITE_OK} |
||
430 | |||
431 | do_test analyze3-3.8.1 { |
||
432 | execsql { |
||
433 | CREATE TABLE t4(x, y TEXT COLLATE NOCASE); |
||
434 | CREATE INDEX i4 ON t4(y); |
||
435 | } |
||
436 | } {} |
||
437 | do_test analyze3-3.8.2 { |
||
438 | set S [sqlite3_prepare_v2 db { |
||
439 | SELECT * FROM t4 WHERE x != ? AND y LIKE ? |
||
440 | } -1 dummy] |
||
441 | sqlite3_expired $S |
||
442 | } {0} |
||
443 | do_test analyze3-3.8.3 { |
||
444 | sqlite3_bind_text $S 1 "abc" 3 |
||
445 | sqlite3_expired $S |
||
446 | } {0} |
||
447 | do_test analyze3-3.8.4 { |
||
448 | sqlite3_bind_text $S 2 "def" 3 |
||
449 | sqlite3_expired $S |
||
450 | } {1} |
||
451 | do_test analyze3-3.8.7 { |
||
452 | sqlite3_bind_text $S 2 "ghi%" 4 |
||
453 | sqlite3_expired $S |
||
454 | } {1} |
||
455 | do_test analyze3-3.8.8 { |
||
456 | sqlite3_expired $S |
||
457 | } {1} |
||
458 | do_test analyze3-3.8.9 { |
||
459 | sqlite3_bind_text $S 2 "ghi%def" 7 |
||
460 | sqlite3_expired $S |
||
461 | } {1} |
||
462 | do_test analyze3-3.8.10 { |
||
463 | sqlite3_expired $S |
||
464 | } {1} |
||
465 | do_test analyze3-3.8.11 { |
||
466 | sqlite3_bind_text $S 2 "%ab" 3 |
||
467 | sqlite3_expired $S |
||
468 | } {1} |
||
469 | do_test analyze3-3.8.12 { |
||
470 | sqlite3_expired $S |
||
471 | } {1} |
||
472 | do_test analyze3-3.8.12 { |
||
473 | sqlite3_bind_text $S 2 "%de" 3 |
||
474 | sqlite3_expired $S |
||
475 | } {1} |
||
476 | do_test analyze3-3.8.13 { |
||
477 | sqlite3_expired $S |
||
478 | } {1} |
||
479 | do_test analyze3-3.8.14 { |
||
480 | sqlite3_finalize $S |
||
481 | } {SQLITE_OK} |
||
482 | |||
483 | #------------------------------------------------------------------------- |
||
484 | # These tests check that errors encountered while repreparing an SQL |
||
485 | # statement within sqlite3Reprepare() are handled correctly. |
||
486 | # |
||
487 | |||
488 | # Check a schema error. |
||
489 | # |
||
490 | do_test analyze3-4.1.1 { |
||
491 | set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy] |
||
492 | sqlite3_step $S |
||
493 | } {SQLITE_DONE} |
||
494 | do_test analyze3-4.1.2 { |
||
495 | sqlite3_reset $S |
||
496 | sqlite3_bind_text $S 2 "abc" 3 |
||
497 | execsql { DROP TABLE t1 } |
||
498 | sqlite3_step $S |
||
499 | } {SQLITE_ERROR} |
||
500 | do_test analyze3-4.1.3 { |
||
501 | sqlite3_finalize $S |
||
502 | } {SQLITE_ERROR} |
||
503 | |||
504 | # Check an authorization error. |
||
505 | # |
||
506 | do_test analyze3-4.2.1 { |
||
507 | execsql { |
||
508 | BEGIN; |
||
509 | CREATE TABLE t1(a, b, c); |
||
510 | CREATE INDEX i1 ON t1(b); |
||
511 | } |
||
512 | for {set i 0} {$i < 100} {incr i} { |
||
513 | execsql { INSERT INTO t1 VALUES($i, $i, $i) } |
||
514 | } |
||
515 | execsql COMMIT |
||
516 | execsql ANALYZE |
||
517 | set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy] |
||
518 | sqlite3_step $S |
||
519 | } {SQLITE_DONE} |
||
520 | db auth auth |
||
521 | proc auth {args} { |
||
522 | if {[lindex $args 0] == "SQLITE_READ"} {return SQLITE_DENY} |
||
523 | return SQLITE_OK |
||
524 | } |
||
525 | do_test analyze3-4.2.2 { |
||
526 | sqlite3_reset $S |
||
527 | sqlite3_bind_text $S 2 "abc" 3 |
||
528 | sqlite3_step $S |
||
529 | } {SQLITE_AUTH} |
||
530 | do_test analyze3-4.2.4 { |
||
531 | sqlite3_finalize $S |
||
532 | } {SQLITE_AUTH} |
||
533 | |||
534 | # Check the effect of an authorization error that occurs in a re-prepare |
||
535 | # performed by sqlite3_step() is the same as one that occurs within |
||
536 | # sqlite3Reprepare(). |
||
537 | # |
||
538 | do_test analyze3-4.3.1 { |
||
539 | db auth {} |
||
540 | set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy] |
||
541 | execsql { CREATE TABLE t2(d, e, f) } |
||
542 | db auth auth |
||
543 | sqlite3_step $S |
||
544 | } {SQLITE_AUTH} |
||
545 | do_test analyze3-4.3.2 { |
||
546 | sqlite3_finalize $S |
||
547 | } {SQLITE_AUTH} |
||
548 | db auth {} |
||
549 | |||
550 | #------------------------------------------------------------------------- |
||
551 | # Test that modifying bound variables using the clear_bindings() or |
||
552 | # transfer_bindings() APIs works. |
||
553 | # |
||
554 | # analyze3-5.1.*: sqlite3_clear_bindings() |
||
555 | # analyze3-5.2.*: sqlite3_transfer_bindings() |
||
556 | # |
||
557 | do_test analyze3-5.1.1 { |
||
558 | drop_all_tables |
||
559 | execsql { |
||
560 | CREATE TABLE t1(x TEXT COLLATE NOCASE); |
||
561 | CREATE INDEX i1 ON t1(x); |
||
562 | INSERT INTO t1 VALUES('aaa'); |
||
563 | INSERT INTO t1 VALUES('abb'); |
||
564 | INSERT INTO t1 VALUES('acc'); |
||
565 | INSERT INTO t1 VALUES('baa'); |
||
566 | INSERT INTO t1 VALUES('bbb'); |
||
567 | INSERT INTO t1 VALUES('bcc'); |
||
568 | } |
||
569 | |||
570 | set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE x LIKE ?" -1 dummy] |
||
571 | sqlite3_bind_text $S 1 "a%" 2 |
||
572 | set R [list] |
||
573 | while { "SQLITE_ROW" == [sqlite3_step $S] } { |
||
574 | lappend R [sqlite3_column_text $S 0] |
||
575 | } |
||
576 | concat [sqlite3_reset $S] $R |
||
577 | } {SQLITE_OK aaa abb acc} |
||
578 | do_test analyze3-5.1.2 { |
||
579 | sqlite3_clear_bindings $S |
||
580 | set R [list] |
||
581 | while { "SQLITE_ROW" == [sqlite3_step $S] } { |
||
582 | lappend R [sqlite3_column_text $S 0] |
||
583 | } |
||
584 | concat [sqlite3_reset $S] $R |
||
585 | } {SQLITE_OK} |
||
586 | do_test analyze3-5.1.3 { |
||
587 | sqlite3_finalize $S |
||
588 | } {SQLITE_OK} |
||
589 | |||
590 | do_test analyze3-5.1.1 { |
||
591 | set S1 [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE x LIKE ?" -1 dummy] |
||
592 | sqlite3_bind_text $S1 1 "b%" 2 |
||
593 | set R [list] |
||
594 | while { "SQLITE_ROW" == [sqlite3_step $S1] } { |
||
595 | lappend R [sqlite3_column_text $S1 0] |
||
596 | } |
||
597 | concat [sqlite3_reset $S1] $R |
||
598 | } {SQLITE_OK baa bbb bcc} |
||
599 | |||
600 | do_test analyze3-5.1.2 { |
||
601 | set S2 [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE x = ?" -1 dummy] |
||
602 | sqlite3_bind_text $S2 1 "a%" 2 |
||
603 | sqlite3_transfer_bindings $S2 $S1 |
||
604 | set R [list] |
||
605 | while { "SQLITE_ROW" == [sqlite3_step $S1] } { |
||
606 | lappend R [sqlite3_column_text $S1 0] |
||
607 | } |
||
608 | concat [sqlite3_reset $S1] $R |
||
609 | } {SQLITE_OK aaa abb acc} |
||
610 | do_test analyze3-5.1.3 { |
||
611 | sqlite3_finalize $S2 |
||
612 | sqlite3_finalize $S1 |
||
613 | } {SQLITE_OK} |
||
614 | |||
615 | finish_test |