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
#***********************************************************************
11
# This file implements regression tests for SQLite library. The
12
# focus of this file is testing the use of indices in WHERE clauses
13
# based on recent changes to the optimizer.
15
# $Id: where2.test,v 1.9 2006/05/11 13:26:26 drh Exp $
17
set testdir [file dirname $argv0]
18
source $testdir/tester.tcl
20
# Build some test data
25
CREATE TABLE t1(w int, x int, y int, z int);
27
for {set i 1} {$i<=100} {incr i} {
29
set x [expr {int(log($i)/log(2))}]
30
set y [expr {$i*$i + 2*$i + 1}]
33
execsql {INSERT INTO t1 VALUES($::w,$::x,$::y,$::z)}
35
execsql {INSERT INTO t1 VALUES(:w,:x,:y,:z)}
39
CREATE UNIQUE INDEX i1w ON t1(w);
40
CREATE INDEX i1xy ON t1(x,y);
41
CREATE INDEX i1zyx ON t1(z,y,x);
46
# Do an SQL statement. Append the search count to the end of the result.
49
set ::sqlite_search_count 0
50
return [concat [execsql $sql] $::sqlite_search_count]
53
# This procedure executes the SQL. Then it checks to see if the OP_Sort
54
# opcode was executed. If an OP_Sort did occur, then "sort" is appended
55
# to the result. If no OP_Sort happened, then "nosort" is appended.
57
# This procedure is used to check to make sure sorting is or is not
58
# occurring as expected.
61
set ::sqlite_sort_count 0
62
set data [execsql $sql]
63
if {$::sqlite_sort_count} {set x sort} {set x nosort}
68
# This procedure executes the SQL. Then it appends to the result the
69
# "sort" or "nosort" keyword (as in the cksort procedure above) then
70
# it appends the ::sqlite_query_plan variable.
72
proc queryplan {sql} {
73
set ::sqlite_sort_count 0
74
set data [execsql $sql]
75
if {$::sqlite_sort_count} {set x sort} {set x nosort}
77
return [concat $data $::sqlite_query_plan]
81
# Prefer a UNIQUE index over another index.
85
SELECT * FROM t1 WHERE w=85 AND x=6 AND y=7396
87
} {85 6 7396 7402 nosort t1 i1w}
89
# Always prefer a rowid== constraint over any other index.
93
SELECT * FROM t1 WHERE w=85 AND x=6 AND y=7396 AND rowid=85
95
} {85 6 7396 7402 nosort t1 *}
97
# When constrained by a UNIQUE index, the ORDER BY clause is always ignored.
101
SELECT * FROM t1 WHERE w=85 ORDER BY random(5);
103
} {85 6 7396 7402 nosort t1 i1w}
106
SELECT * FROM t1 WHERE x=6 AND y=7396 ORDER BY random(5);
108
} {85 6 7396 7402 sort t1 i1xy}
111
SELECT * FROM t1 WHERE rowid=85 AND x=6 AND y=7396 ORDER BY random(5);
113
} {85 6 7396 7402 nosort t1 *}
116
# Efficient handling of forward and reverse table scans.
120
SELECT * FROM t1 ORDER BY rowid LIMIT 2
122
} {1 0 4 4 2 1 9 10 nosort t1 *}
125
SELECT * FROM t1 ORDER BY rowid DESC LIMIT 2
127
} {100 6 10201 10207 99 6 10000 10006 nosort t1 *}
129
# The IN operator can be used by indices at multiple layers
134
SELECT * FROM t1 WHERE z IN (10207,10006) AND y IN (10000,10201)
138
} {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
141
SELECT * FROM t1 WHERE z IN (10207,10006) AND y=10000
145
} {99 6 10000 10006 sort t1 i1zyx}
148
SELECT * FROM t1 WHERE z=10006 AND y IN (10000,10201)
152
} {99 6 10000 10006 sort t1 i1zyx}
156
SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
157
AND y IN (10000,10201)
161
} {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
164
SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
165
AND y IN (SELECT 10000 UNION SELECT 10201)
169
} {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
174
WHERE x IN (1,2,3,4,5,6,7,8)
175
AND y IN (10000,10001,10002,10003,10004,10005)
178
} {99 6 10000 10006 sort t1 i1xy}
180
# Duplicate entires on the RHS of an IN operator do not cause duplicate
185
SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207)
188
} {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
192
SELECT * FROM t1 WHERE z IN (
193
SELECT 10207 UNION ALL SELECT 10006
194
UNION ALL SELECT 10006 UNION ALL SELECT 10207)
197
} {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
200
} ;# ifcapable subquery
202
# The use of an IN operator disables the index as a sorter.
206
SELECT * FROM t1 WHERE w=99 ORDER BY w
208
} {99 6 10000 10006 nosort t1 i1w}
213
SELECT * FROM t1 WHERE w IN (99) ORDER BY w
215
} {99 6 10000 10006 sort t1 i1w}
218
# Verify that OR clauses get translated into IN operators.
221
ifcapable subquery {set ::idx i1w}
224
SELECT * FROM t1 WHERE w=99 OR w=100 ORDER BY +w
226
} [list 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
229
SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=w ORDER BY +w
231
} [list 6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
235
SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=+w ORDER BY +w
237
} {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}}
240
SELECT * FROM t1 WHERE w=99 OR +w=100 OR 6=w ORDER BY +w
242
} {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}}
245
ifcapable subquery {set ::idx i1zyx}
248
SELECT b.* FROM t1 a, t1 b
249
WHERE a.w=1 AND (a.y=b.z OR b.z=10)
252
} [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
255
SELECT b.* FROM t1 a, t1 b
256
WHERE a.w=1 AND (b.z=10 OR a.y=b.z OR b.z=10)
259
} [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
261
# Unique queries (queries that are guaranteed to return only a single
262
# row of result) do not call the sorter. But all tables must give
263
# a unique result. If any one table in the join does not give a unique
264
# result then sorting is necessary.
268
create table t8(a unique, b, c);
269
insert into t8 values(1,2,3);
270
insert into t8 values(2,3,4);
271
create table t9(x,y);
272
insert into t9 values(2,4);
273
insert into t9 values(2,3);
274
select y from t8, t9 where a=1 order by a, y;
279
select * from t8 where a=1 order by b, c
284
select * from t8, t9 where a=1 and y=3 order by b, x
289
create unique index i9y on t9(y);
290
select * from t8, t9 where a=1 and y=3 order by b, x
294
# Ticket #1807. Using IN constrains on multiple columns of
295
# a multi-column index.
300
SELECT * FROM t1 WHERE x IN (20,21) AND y IN (1,2)
305
SELECT * FROM t1 WHERE x IN (1,2) AND y IN (-5,-6)
308
execsql {CREATE TABLE tx AS SELECT * FROM t1}
312
WHERE x IN (SELECT x FROM tx WHERE rowid<0)
313
AND +y IN (SELECT y FROM tx WHERE rowid=1)
319
WHERE x IN (SELECT x FROM tx WHERE rowid=1)
320
AND y IN (SELECT y FROM tx WHERE rowid<0)
323
#set sqlite_where_trace 1
326
CREATE INDEX tx_xyz ON tx(x, y, z, w);
328
WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
329
AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
330
AND z IN (SELECT z FROM t1 WHERE w BETWEEN 12 AND 14)
336
WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
337
AND y IN (SELECT y FROM t1 WHERE w BETWEEN 12 AND 14)
338
AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
344
WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 12 AND 14)
345
AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
346
AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
348
} {10 11 12 13 14 15}
352
WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
353
AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
354
AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
356
} {10 11 12 13 14 15 16 17 18 19 20}
360
WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
361
AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
362
AND z IN (SELECT z FROM t1 WHERE w BETWEEN 2 AND 4)
365
do_test where2-8.10 {
368
WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
369
AND y IN (SELECT y FROM t1 WHERE w BETWEEN 2 AND 4)
370
AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
373
do_test where2-8.11 {
376
WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 2 AND 4)
377
AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
378
AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
381
do_test where2-8.12 {
384
WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
385
AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
386
AND z IN (SELECT z FROM t1 WHERE w BETWEEN -4 AND -2)
389
do_test where2-8.13 {
392
WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
393
AND y IN (SELECT y FROM t1 WHERE w BETWEEN -4 AND -2)
394
AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
397
do_test where2-8.14 {
400
WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN -4 AND -2)
401
AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
402
AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
405
do_test where2-8.15 {
408
WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
409
AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
410
AND z IN (SELECT z FROM t1 WHERE w BETWEEN 200 AND 300)
413
do_test where2-8.16 {
416
WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
417
AND y IN (SELECT y FROM t1 WHERE w BETWEEN 200 AND 300)
418
AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
421
do_test where2-8.17 {
424
WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 200 AND 300)
425
AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
426
AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
429
do_test where2-8.18 {
432
WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20)
433
AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20)
434
AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 200 AND 300)
437
do_test where2-8.19 {
440
WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20)
441
AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 200 AND 300)
442
AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20)
445
do_test where2-8.20 {
448
WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 200 AND 300)
449
AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20)
450
AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20)
added
removed
test/where2.test
