3
# The author disclaims copyright to this source code. In place of
4
# a legal notice, here is a blessing:
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.
10
#***********************************************************************
12
# This file implements regression tests for SQLite library. This file
13
# implements tests for the extra functionality provided by the ANALYZE
14
# command when the library is compiled with SQLITE_ENABLE_STAT2 defined.
17
set testdir [file dirname $argv0]
18
source $testdir/tester.tcl
25
#--------------------------------------------------------------------
28
# analyze2-1.*: Tests to verify that ANALYZE creates and populates the
29
# sqlite_stat2 table as expected.
31
# analyze2-2.*: Test that when a table has two indexes on it and either
32
# index may be used for the scan, the index suggested by
33
# the contents of sqlite_stat2 table is prefered.
35
# analyze2-3.*: Similar to the previous block of tests, but using tables
36
# that contain a mixture of NULL, numeric, text and blob
39
# analyze2-4.*: Check that when an indexed column uses a collation other
40
# than BINARY, the collation is taken into account when
41
# using the contents of sqlite_stat2 to estimate the cost
44
# analyze2-5.*: Check that collation sequences are used as described above
45
# even when the only available version of the collation
46
# function require UTF-16 encoded arguments.
48
# analyze2-6.*: Check that the library behaves correctly when one of the
49
# sqlite_stat2 or sqlite_stat1 tables are missing.
51
# analyze2-7.*: Check that in a shared-schema situation, nothing goes
52
# wrong if sqlite_stat2 data is read by one connection,
53
# and freed by another.
56
proc eqp {sql {db db}} {
57
uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
60
do_test analyze2-1.1 {
61
execsql { CREATE TABLE t1(x PRIMARY KEY) }
62
for {set i 0} {$i < 1000} {incr i} {
63
execsql { INSERT INTO t1 VALUES($i) }
67
SELECT * FROM sqlite_stat2;
69
} [list t1 sqlite_autoindex_t1_1 0 50 \
70
t1 sqlite_autoindex_t1_1 1 149 \
71
t1 sqlite_autoindex_t1_1 2 249 \
72
t1 sqlite_autoindex_t1_1 3 349 \
73
t1 sqlite_autoindex_t1_1 4 449 \
74
t1 sqlite_autoindex_t1_1 5 549 \
75
t1 sqlite_autoindex_t1_1 6 649 \
76
t1 sqlite_autoindex_t1_1 7 749 \
77
t1 sqlite_autoindex_t1_1 8 849 \
78
t1 sqlite_autoindex_t1_1 9 949 \
81
do_test analyze2-1.2 {
83
DELETE FROM t1 WHERe x>9;
85
SELECT tbl, idx, group_concat(sample, ' ') FROM sqlite_stat2;
87
} {t1 sqlite_autoindex_t1_1 {0 1 2 3 4 5 6 7 8 9}}
88
do_test analyze2-1.3 {
90
DELETE FROM t1 WHERE x>8;
92
SELECT * FROM sqlite_stat2;
95
do_test analyze2-1.4 {
99
SELECT * FROM sqlite_stat2;
103
do_test analyze2-2.1 {
107
CREATE TABLE t1(x, y);
108
CREATE INDEX t1_x ON t1(x);
109
CREATE INDEX t1_y ON t1(y);
111
for {set i 0} {$i < 1000} {incr i} {
112
execsql { INSERT INTO t1 VALUES($i, $i) }
117
do_test analyze2-2.2 {
118
eqp "SELECT * FROM t1 WHERE x>500 AND y>700"
119
} {0 0 {TABLE t1 WITH INDEX t1_y}}
120
do_test analyze2-2.3 {
121
eqp "SELECT * FROM t1 WHERE x>700 AND y>500"
122
} {0 0 {TABLE t1 WITH INDEX t1_x}}
123
do_test analyze2-2.3 {
124
eqp "SELECT * FROM t1 WHERE y>700 AND x>500"
125
} {0 0 {TABLE t1 WITH INDEX t1_y}}
126
do_test analyze2-2.4 {
127
eqp "SELECT * FROM t1 WHERE y>500 AND x>700"
128
} {0 0 {TABLE t1 WITH INDEX t1_x}}
129
do_test analyze2-2.5 {
130
eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 200 AND y BETWEEN 400 AND 700"
131
} {0 0 {TABLE t1 WITH INDEX t1_x}}
132
do_test analyze2-2.6 {
133
eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 400 AND 700"
134
} {0 0 {TABLE t1 WITH INDEX t1_y}}
135
do_test analyze2-2.7 {
136
eqp "SELECT * FROM t1 WHERE x BETWEEN -400 AND -300 AND y BETWEEN 100 AND 300"
137
} {0 0 {TABLE t1 WITH INDEX t1_x}}
138
do_test analyze2-2.8 {
139
eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN -400 AND -300"
140
} {0 0 {TABLE t1 WITH INDEX t1_y}}
141
do_test analyze2-2.9 {
142
eqp "SELECT * FROM t1 WHERE x BETWEEN 500 AND 100 AND y BETWEEN 100 AND 300"
143
} {0 0 {TABLE t1 WITH INDEX t1_x}}
144
do_test analyze2-2.10 {
145
eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN 500 AND 100"
146
} {0 0 {TABLE t1 WITH INDEX t1_y}}
148
do_test analyze2-3.1 {
149
set alphabet [list a b c d e f g h i j]
151
for {set i 0} {$i < 1000} {incr i} {
152
set str [lindex $alphabet [expr ($i/100)%10]]
153
append str [lindex $alphabet [expr ($i/ 10)%10]]
154
append str [lindex $alphabet [expr ($i/ 1)%10]]
155
execsql { INSERT INTO t1 VALUES($str, $str) }
160
SELECT tbl,idx,group_concat(sample,' ')
165
} {t1 t1_x {100 299 499 699 899 ajj cjj ejj gjj ijj}}
166
do_test analyze2-3.2 {
168
SELECT tbl,idx,group_concat(sample,' ')
173
} {t1 t1_y {100 299 499 699 899 ajj cjj ejj gjj ijj}}
175
do_test analyze2-3.3 {
176
eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'"
177
} {0 0 {TABLE t1 WITH INDEX t1_y}}
178
do_test analyze2-3.4 {
179
eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 400 AND y BETWEEN 'a' AND 'h'"
180
} {0 0 {TABLE t1 WITH INDEX t1_x}}
181
do_test analyze2-3.5 {
182
eqp "SELECT * FROM t1 WHERE x<'a' AND y>'h'"
183
} {0 0 {TABLE t1 WITH INDEX t1_y}}
184
do_test analyze2-3.6 {
185
eqp "SELECT * FROM t1 WHERE x<444 AND y>'h'"
186
} {0 0 {TABLE t1 WITH INDEX t1_y}}
187
do_test analyze2-3.7 {
188
eqp "SELECT * FROM t1 WHERE x<221 AND y>'g'"
189
} {0 0 {TABLE t1 WITH INDEX t1_x}}
191
do_test analyze2-4.1 {
192
execsql { CREATE TABLE t3(a COLLATE nocase, b) }
193
execsql { CREATE INDEX t3a ON t3(a) }
194
execsql { CREATE INDEX t3b ON t3(b) }
195
set alphabet [list A b C d E f G h I j]
197
for {set i 0} {$i < 1000} {incr i} {
198
set str [lindex $alphabet [expr ($i/100)%10]]
199
append str [lindex $alphabet [expr ($i/ 10)%10]]
200
append str [lindex $alphabet [expr ($i/ 1)%10]]
201
execsql { INSERT INTO t3 VALUES($str, $str) }
206
do_test analyze2-4.2 {
208
SELECT tbl,idx,group_concat(sample,' ')
213
} {t3 t3a {AfA bEj CEj dEj EEj fEj GEj hEj IEj jEj}}
214
do_test analyze2-4.3 {
216
SELECT tbl,idx,group_concat(sample,' ')
221
} {t3 t3b {AbA CIj EIj GIj IIj bIj dIj fIj hIj jIj}}
223
do_test analyze2-4.4 {
224
eqp "SELECT * FROM t3 WHERE a > 'A' AND a < 'C' AND b > 'A' AND b < 'C'"
225
} {0 0 {TABLE t3 WITH INDEX t3b}}
226
do_test analyze2-4.5 {
227
eqp "SELECT * FROM t3 WHERE a > 'A' AND a < 'c' AND b > 'A' AND b < 'c'"
228
} {0 0 {TABLE t3 WITH INDEX t3a}}
231
proc test_collate {enc lhs rhs} {
233
return [string compare $lhs $rhs]
235
do_test analyze2-5.1 {
236
add_test_collate db 0 0 1
237
execsql { CREATE TABLE t4(x COLLATE test_collate) }
238
execsql { CREATE INDEX t4x ON t4(x) }
239
set alphabet [list a b c d e f g h i j]
241
for {set i 0} {$i < 1000} {incr i} {
242
set str [lindex $alphabet [expr ($i/100)%10]]
243
append str [lindex $alphabet [expr ($i/ 10)%10]]
244
append str [lindex $alphabet [expr ($i/ 1)%10]]
245
execsql { INSERT INTO t4 VALUES($str) }
250
do_test analyze2-5.2 {
252
SELECT tbl,idx,group_concat(sample,' ')
257
} {t4 t4x {afa bej cej dej eej fej gej hej iej jej}}
258
do_test analyze2-5.3 {
259
eqp "SELECT * FROM t4 WHERE x>'ccc'"
260
} {0 0 {TABLE t4 WITH INDEX t4x}}
261
do_test analyze2-5.4 {
262
eqp "SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ccc' AND t42.x>'ggg'"
263
} {0 1 {TABLE t4 AS t42 WITH INDEX t4x} 1 0 {TABLE t4 AS t41 WITH INDEX t4x}}
264
do_test analyze2-5.5 {
265
eqp "SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ddd' AND t42.x>'ccc'"
266
} {0 0 {TABLE t4 AS t41 WITH INDEX t4x} 1 1 {TABLE t4 AS t42 WITH INDEX t4x}}
269
#--------------------------------------------------------------------
270
# These tests, analyze2-6.*, verify that the library behaves correctly
271
# when one of the sqlite_stat1 and sqlite_stat2 tables is missing.
273
# If the sqlite_stat1 table is not present, then the sqlite_stat2
274
# table is not read. However, if it is the sqlite_stat2 table that
275
# is missing, the data in the sqlite_stat1 table is still used.
277
# Tests analyze2-6.1.* test the libary when the sqlite_stat2 table
278
# is missing. Tests analyze2-6.2.* test the library when sqlite_stat1
281
do_test analyze2-6.0 {
283
DROP TABLE IF EXISTS t4;
284
CREATE TABLE t5(a, b); CREATE INDEX t5i ON t5(a, b);
285
CREATE TABLE t6(a, b); CREATE INDEX t6i ON t6(a, b);
287
for {set ii 0} {$ii < 20} {incr ii} {
289
INSERT INTO t5 VALUES($ii, $ii);
290
INSERT INTO t6 VALUES($ii/10, $ii/10);
294
CREATE TABLE master AS
295
SELECT * FROM sqlite_master WHERE name LIKE 'sqlite_stat%'
299
do_test analyze2-6.1.1 {
300
eqp {SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
302
t6.a = 1 AND t6.b = 1
304
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
305
do_test analyze2-6.1.2 {
308
eqp {SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
310
t6.a = 1 AND t6.b = 1
312
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
313
do_test analyze2-6.1.3 {
315
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
317
t6.a = 1 AND t6.b = 1
319
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
320
do_test analyze2-6.1.4 {
322
PRAGMA writable_schema = 1;
323
DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2';
326
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
328
t6.a = 1 AND t6.b = 1
330
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
331
do_test analyze2-6.1.5 {
333
PRAGMA writable_schema = 1;
334
DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1';
337
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
339
t6.a = 1 AND t6.b = 1
341
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
342
do_test analyze2-6.1.6 {
344
PRAGMA writable_schema = 1;
345
INSERT INTO sqlite_master SELECT * FROM master;
348
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
350
t6.a = 1 AND t6.b = 1
352
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
354
do_test analyze2-6.2.1 {
356
DELETE FROM sqlite_stat1;
357
DELETE FROM sqlite_stat2;
360
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
361
t5.a>1 AND t5.a<15 AND
364
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
365
do_test analyze2-6.2.2 {
368
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
369
t5.a>1 AND t5.a<15 AND
372
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
373
do_test analyze2-6.2.3 {
375
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
376
t5.a>1 AND t5.a<15 AND
379
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
380
do_test analyze2-6.2.4 {
382
PRAGMA writable_schema = 1;
383
DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1';
386
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
387
t5.a>1 AND t5.a<15 AND
390
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
391
do_test analyze2-6.2.5 {
393
PRAGMA writable_schema = 1;
394
DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2';
397
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
398
t5.a>1 AND t5.a<15 AND
401
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
402
do_test analyze2-6.2.6 {
404
PRAGMA writable_schema = 1;
405
INSERT INTO sqlite_master SELECT * FROM master;
409
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
410
t5.a>1 AND t5.a<15 AND
413
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
415
#--------------------------------------------------------------------
416
# These tests, analyze2-7.*, test that the sqlite_stat2 functionality
417
# works in shared-cache mode. Note that these tests reuse the database
418
# created for the analyze2-6.* tests.
420
ifcapable shared_cache {
422
set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
424
proc incr_schema_cookie {zDb} {
425
foreach iOffset {24 40} {
426
set cookie [hexio_get_int [hexio_read $zDb $iOffset 4]]
428
hexio_write $zDb $iOffset [hexio_render_int32 $cookie]
432
do_test analyze2-7.1 {
437
execsql { SELECT count(*) FROM t5 } db1
439
do_test analyze2-7.2 {
440
incr_schema_cookie test.db
441
execsql { SELECT count(*) FROM t5 } db2
443
do_test analyze2-7.3 {
444
incr_schema_cookie test.db
445
execsql { SELECT count(*) FROM t5 } db1
447
do_test analyze2-7.4 {
448
incr_schema_cookie test.db
449
execsql { SELECT count(*) FROM t5 } db2
452
do_test analyze2-7.5 {
453
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
454
t5.a>1 AND t5.a<15 AND
457
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
458
do_test analyze2-7.6 {
459
incr_schema_cookie test.db
460
execsql { SELECT * FROM sqlite_master } db2
461
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
462
t5.a>1 AND t5.a<15 AND
465
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
466
do_test analyze2-7.7 {
467
incr_schema_cookie test.db
468
execsql { SELECT * FROM sqlite_master } db1
469
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
470
t5.a>1 AND t5.a<15 AND
473
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
475
do_test analyze2-7.8 {
476
execsql { DELETE FROM sqlite_stat2 } db2
477
execsql { SELECT * FROM sqlite_master } db1
478
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
479
t5.a>1 AND t5.a<15 AND
482
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
483
do_test analyze2-7.9 {
484
execsql { SELECT * FROM sqlite_master } db2
485
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
486
t5.a>1 AND t5.a<15 AND
489
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
491
do_test analyze2-7.10 {
492
incr_schema_cookie test.db
493
execsql { SELECT * FROM sqlite_master } db1
494
eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND
495
t5.a>1 AND t5.a<15 AND
498
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
502
sqlite3_enable_shared_cache $::enable_shared_cache
added
removed
test/analyze2.test
