/trunk/test/autoindex1.test |
@@ -0,0 +1,251 @@ |
# 2010 April 07 |
# |
# The author disclaims copyright to this source code. In place of |
# a legal notice, here is a blessing: |
# |
# May you do good and not evil. |
# May you find forgiveness for yourself and forgive others. |
# May you share freely, never taking more than you give. |
# |
#************************************************************************* |
# This file implements regression tests for SQLite library. The |
# focus of this script is testing automatic index creation logic. |
# |
|
set testdir [file dirname $argv0] |
source $testdir/tester.tcl |
|
# If the library is not compiled with automatic index support then |
# skip all tests in this file. |
# |
ifcapable {!autoindex} { |
finish_test |
return |
} |
|
# With automatic index turned off, we do a full scan of the T2 table |
do_test autoindex1-100 { |
db eval { |
CREATE TABLE t1(a,b); |
INSERT INTO t1 VALUES(1,11); |
INSERT INTO t1 VALUES(2,22); |
INSERT INTO t1 SELECT a+2, b+22 FROM t1; |
INSERT INTO t1 SELECT a+4, b+44 FROM t1; |
CREATE TABLE t2(c,d); |
INSERT INTO t2 SELECT a, 900+b FROM t1; |
} |
db eval { |
PRAGMA automatic_index=OFF; |
SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b; |
} |
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} |
do_test autoindex1-101 { |
db status step |
} {63} |
do_test autoindex1-102 { |
db status autoindex |
} {0} |
|
# With autoindex turned on, we build an index once and then use that index |
# to find T2 values. |
do_test autoindex1-110 { |
db eval { |
PRAGMA automatic_index=ON; |
SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b; |
} |
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} |
do_test autoindex1-111 { |
db status step |
} {7} |
do_test autoindex1-112 { |
db status autoindex |
} {7} |
|
# The same test as above, but this time the T2 query is a subquery rather |
# than a join. |
do_test autoindex1-200 { |
db eval { |
PRAGMA automatic_index=OFF; |
SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1; |
} |
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} |
do_test autoindex1-201 { |
db status step |
} {35} |
do_test autoindex1-202 { |
db status autoindex |
} {0} |
do_test autoindex1-210 { |
db eval { |
PRAGMA automatic_index=ON; |
SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1; |
} |
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} |
do_test autoindex1-211 { |
db status step |
} {7} |
do_test autoindex1-212 { |
db status autoindex |
} {7} |
|
|
# Modify the second table of the join while the join is in progress |
# |
do_test autoindex1-300 { |
set r {} |
db eval {SELECT b, d FROM t1 JOIN t2 ON (c=a)} { |
lappend r $b $d |
db eval {UPDATE t2 SET d=d+1} |
} |
set r |
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} |
do_test autoindex1-310 { |
db eval {SELECT d FROM t2 ORDER BY d} |
} {919 930 941 952 963 974 985 996} |
|
# The next test does a 10-way join on unindexed tables. Without |
# automatic indices, the join will take a long time to complete. |
# With automatic indices, it should only take about a second. |
# |
do_test autoindex1-400 { |
db eval { |
CREATE TABLE t4(a, b); |
INSERT INTO t4 VALUES(1,2); |
INSERT INTO t4 VALUES(2,3); |
} |
for {set n 2} {$n<4096} {set n [expr {$n+$n}]} { |
db eval {INSERT INTO t4 SELECT a+$n, b+$n FROM t4} |
} |
db eval { |
SELECT count(*) FROM t4; |
} |
} {4096} |
do_test autoindex1-401 { |
db eval { |
SELECT count(*) |
FROM t4 AS x1 |
JOIN t4 AS x2 ON x2.a=x1.b |
JOIN t4 AS x3 ON x3.a=x2.b |
JOIN t4 AS x4 ON x4.a=x3.b |
JOIN t4 AS x5 ON x5.a=x4.b |
JOIN t4 AS x6 ON x6.a=x5.b |
JOIN t4 AS x7 ON x7.a=x6.b |
JOIN t4 AS x8 ON x8.a=x7.b |
JOIN t4 AS x9 ON x9.a=x8.b |
JOIN t4 AS x10 ON x10.a=x9.b; |
} |
} {4087} |
|
# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08 |
# Make sure automatic indices are not created for the RHS of an IN expression |
# that is not a correlated subquery. |
# |
do_execsql_test autoindex1-500 { |
CREATE TABLE t501(a INTEGER PRIMARY KEY, b); |
CREATE TABLE t502(x INTEGER PRIMARY KEY, y); |
EXPLAIN QUERY PLAN |
SELECT b FROM t501 |
WHERE t501.a IN (SELECT x FROM t502 WHERE y=?); |
} { |
0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)} |
0 0 0 {EXECUTE LIST SUBQUERY 1} |
1 0 0 {SCAN TABLE t502 (~100000 rows)} |
} |
do_execsql_test autoindex1-501 { |
EXPLAIN QUERY PLAN |
SELECT b FROM t501 |
WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b); |
} { |
0 0 0 {SCAN TABLE t501 (~500000 rows)} |
0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} |
1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)} |
} |
do_execsql_test autoindex1-502 { |
EXPLAIN QUERY PLAN |
SELECT b FROM t501 |
WHERE t501.a=123 |
AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b); |
} { |
0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} |
0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} |
1 0 0 {SCAN TABLE t502 (~100000 rows)} |
} |
|
|
# The following code checks a performance regression reported on the |
# mailing list on 2010-10-19. The problem is that the nRowEst field |
# of ephermeral tables was not being initialized correctly and so no |
# automatic index was being created for the emphemeral table when it was |
# used as part of a join. |
# |
do_execsql_test autoindex1-600 { |
CREATE TABLE flock_owner( |
owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY, |
flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no), |
owner_person_id INTEGER NOT NULL REFERENCES person (person_id), |
owner_change_date TEXT, last_changed TEXT NOT NULL, |
CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date) |
); |
CREATE TABLE sheep ( |
Sheep_No char(7) NOT NULL, |
Date_of_Birth char(8), |
Sort_DoB text, |
Flock_Book_Vol char(2), |
Breeder_No char(6), |
Breeder_Person integer, |
Originating_Flock char(6), |
Registering_Flock char(6), |
Tag_Prefix char(9), |
Tag_No char(15), |
Sort_Tag_No integer, |
Breeders_Temp_Tag char(15), |
Sex char(1), |
Sheep_Name char(32), |
Sire_No char(7), |
Dam_No char(7), |
Register_Code char(1), |
Colour char(48), |
Colour_Code char(2), |
Pattern_Code char(8), |
Horns char(1), |
Litter_Size char(1), |
Coeff_of_Inbreeding real, |
Date_of_Registration text, |
Date_Last_Changed text, |
UNIQUE(Sheep_No)); |
CREATE INDEX fo_flock_no_index |
ON flock_owner (flock_no); |
CREATE INDEX fo_owner_change_date_index |
ON flock_owner (owner_change_date); |
CREATE INDEX fo_owner_person_id_index |
ON flock_owner (owner_person_id); |
CREATE INDEX sheep_org_flock_index |
ON sheep (originating_flock); |
CREATE INDEX sheep_reg_flock_index |
ON sheep (registering_flock); |
EXPLAIN QUERY PLAN |
SELECT x.sheep_no, x.registering_flock, x.date_of_registration |
FROM sheep x LEFT JOIN |
(SELECT s.sheep_no, prev.flock_no, prev.owner_person_id, |
s.date_of_registration, prev.owner_change_date |
FROM sheep s JOIN flock_owner prev ON s.registering_flock = |
prev.flock_no |
AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00') |
WHERE NOT EXISTS |
(SELECT 'x' FROM flock_owner later |
WHERE prev.flock_no = later.flock_no |
AND later.owner_change_date > prev.owner_change_date |
AND later.owner_change_date <= s.date_of_registration||' 00:00:00') |
) y ON x.sheep_no = y.sheep_no |
WHERE y.sheep_no IS NULL |
ORDER BY x.registering_flock; |
} { |
1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} |
1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)} |
1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} |
2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)} |
0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)} |
0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~8 rows)} |
} |
|
finish_test |