Annotation of embedaddon/sqlite3/test/autoindex1.test, revision 1.1
1.1 ! misho 1: # 2010 April 07
! 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: # This file implements regression tests for SQLite library. The
! 12: # focus of this script is testing automatic index creation logic.
! 13: #
! 14:
! 15: set testdir [file dirname $argv0]
! 16: source $testdir/tester.tcl
! 17:
! 18: # If the library is not compiled with automatic index support then
! 19: # skip all tests in this file.
! 20: #
! 21: ifcapable {!autoindex} {
! 22: finish_test
! 23: return
! 24: }
! 25:
! 26: # With automatic index turned off, we do a full scan of the T2 table
! 27: do_test autoindex1-100 {
! 28: db eval {
! 29: CREATE TABLE t1(a,b);
! 30: INSERT INTO t1 VALUES(1,11);
! 31: INSERT INTO t1 VALUES(2,22);
! 32: INSERT INTO t1 SELECT a+2, b+22 FROM t1;
! 33: INSERT INTO t1 SELECT a+4, b+44 FROM t1;
! 34: CREATE TABLE t2(c,d);
! 35: INSERT INTO t2 SELECT a, 900+b FROM t1;
! 36: }
! 37: db eval {
! 38: PRAGMA automatic_index=OFF;
! 39: SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
! 40: }
! 41: } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
! 42: do_test autoindex1-101 {
! 43: db status step
! 44: } {63}
! 45: do_test autoindex1-102 {
! 46: db status autoindex
! 47: } {0}
! 48:
! 49: # With autoindex turned on, we build an index once and then use that index
! 50: # to find T2 values.
! 51: do_test autoindex1-110 {
! 52: db eval {
! 53: PRAGMA automatic_index=ON;
! 54: SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
! 55: }
! 56: } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
! 57: do_test autoindex1-111 {
! 58: db status step
! 59: } {7}
! 60: do_test autoindex1-112 {
! 61: db status autoindex
! 62: } {7}
! 63:
! 64: # The same test as above, but this time the T2 query is a subquery rather
! 65: # than a join.
! 66: do_test autoindex1-200 {
! 67: db eval {
! 68: PRAGMA automatic_index=OFF;
! 69: SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
! 70: }
! 71: } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
! 72: do_test autoindex1-201 {
! 73: db status step
! 74: } {35}
! 75: do_test autoindex1-202 {
! 76: db status autoindex
! 77: } {0}
! 78: do_test autoindex1-210 {
! 79: db eval {
! 80: PRAGMA automatic_index=ON;
! 81: SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
! 82: }
! 83: } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
! 84: do_test autoindex1-211 {
! 85: db status step
! 86: } {7}
! 87: do_test autoindex1-212 {
! 88: db status autoindex
! 89: } {7}
! 90:
! 91:
! 92: # Modify the second table of the join while the join is in progress
! 93: #
! 94: do_test autoindex1-300 {
! 95: set r {}
! 96: db eval {SELECT b, d FROM t1 JOIN t2 ON (c=a)} {
! 97: lappend r $b $d
! 98: db eval {UPDATE t2 SET d=d+1}
! 99: }
! 100: set r
! 101: } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
! 102: do_test autoindex1-310 {
! 103: db eval {SELECT d FROM t2 ORDER BY d}
! 104: } {919 930 941 952 963 974 985 996}
! 105:
! 106: # The next test does a 10-way join on unindexed tables. Without
! 107: # automatic indices, the join will take a long time to complete.
! 108: # With automatic indices, it should only take about a second.
! 109: #
! 110: do_test autoindex1-400 {
! 111: db eval {
! 112: CREATE TABLE t4(a, b);
! 113: INSERT INTO t4 VALUES(1,2);
! 114: INSERT INTO t4 VALUES(2,3);
! 115: }
! 116: for {set n 2} {$n<4096} {set n [expr {$n+$n}]} {
! 117: db eval {INSERT INTO t4 SELECT a+$n, b+$n FROM t4}
! 118: }
! 119: db eval {
! 120: SELECT count(*) FROM t4;
! 121: }
! 122: } {4096}
! 123: do_test autoindex1-401 {
! 124: db eval {
! 125: SELECT count(*)
! 126: FROM t4 AS x1
! 127: JOIN t4 AS x2 ON x2.a=x1.b
! 128: JOIN t4 AS x3 ON x3.a=x2.b
! 129: JOIN t4 AS x4 ON x4.a=x3.b
! 130: JOIN t4 AS x5 ON x5.a=x4.b
! 131: JOIN t4 AS x6 ON x6.a=x5.b
! 132: JOIN t4 AS x7 ON x7.a=x6.b
! 133: JOIN t4 AS x8 ON x8.a=x7.b
! 134: JOIN t4 AS x9 ON x9.a=x8.b
! 135: JOIN t4 AS x10 ON x10.a=x9.b;
! 136: }
! 137: } {4087}
! 138:
! 139: # Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
! 140: # Make sure automatic indices are not created for the RHS of an IN expression
! 141: # that is not a correlated subquery.
! 142: #
! 143: do_execsql_test autoindex1-500 {
! 144: CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
! 145: CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
! 146: EXPLAIN QUERY PLAN
! 147: SELECT b FROM t501
! 148: WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
! 149: } {
! 150: 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)}
! 151: 0 0 0 {EXECUTE LIST SUBQUERY 1}
! 152: 1 0 0 {SCAN TABLE t502 (~100000 rows)}
! 153: }
! 154: do_execsql_test autoindex1-501 {
! 155: EXPLAIN QUERY PLAN
! 156: SELECT b FROM t501
! 157: WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
! 158: } {
! 159: 0 0 0 {SCAN TABLE t501 (~500000 rows)}
! 160: 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1}
! 161: 1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)}
! 162: }
! 163: do_execsql_test autoindex1-502 {
! 164: EXPLAIN QUERY PLAN
! 165: SELECT b FROM t501
! 166: WHERE t501.a=123
! 167: AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
! 168: } {
! 169: 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
! 170: 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1}
! 171: 1 0 0 {SCAN TABLE t502 (~100000 rows)}
! 172: }
! 173:
! 174:
! 175: # The following code checks a performance regression reported on the
! 176: # mailing list on 2010-10-19. The problem is that the nRowEst field
! 177: # of ephermeral tables was not being initialized correctly and so no
! 178: # automatic index was being created for the emphemeral table when it was
! 179: # used as part of a join.
! 180: #
! 181: do_execsql_test autoindex1-600 {
! 182: CREATE TABLE flock_owner(
! 183: owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
! 184: flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
! 185: owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
! 186: owner_change_date TEXT, last_changed TEXT NOT NULL,
! 187: CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
! 188: );
! 189: CREATE TABLE sheep (
! 190: Sheep_No char(7) NOT NULL,
! 191: Date_of_Birth char(8),
! 192: Sort_DoB text,
! 193: Flock_Book_Vol char(2),
! 194: Breeder_No char(6),
! 195: Breeder_Person integer,
! 196: Originating_Flock char(6),
! 197: Registering_Flock char(6),
! 198: Tag_Prefix char(9),
! 199: Tag_No char(15),
! 200: Sort_Tag_No integer,
! 201: Breeders_Temp_Tag char(15),
! 202: Sex char(1),
! 203: Sheep_Name char(32),
! 204: Sire_No char(7),
! 205: Dam_No char(7),
! 206: Register_Code char(1),
! 207: Colour char(48),
! 208: Colour_Code char(2),
! 209: Pattern_Code char(8),
! 210: Horns char(1),
! 211: Litter_Size char(1),
! 212: Coeff_of_Inbreeding real,
! 213: Date_of_Registration text,
! 214: Date_Last_Changed text,
! 215: UNIQUE(Sheep_No));
! 216: CREATE INDEX fo_flock_no_index
! 217: ON flock_owner (flock_no);
! 218: CREATE INDEX fo_owner_change_date_index
! 219: ON flock_owner (owner_change_date);
! 220: CREATE INDEX fo_owner_person_id_index
! 221: ON flock_owner (owner_person_id);
! 222: CREATE INDEX sheep_org_flock_index
! 223: ON sheep (originating_flock);
! 224: CREATE INDEX sheep_reg_flock_index
! 225: ON sheep (registering_flock);
! 226: EXPLAIN QUERY PLAN
! 227: SELECT x.sheep_no, x.registering_flock, x.date_of_registration
! 228: FROM sheep x LEFT JOIN
! 229: (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
! 230: s.date_of_registration, prev.owner_change_date
! 231: FROM sheep s JOIN flock_owner prev ON s.registering_flock =
! 232: prev.flock_no
! 233: AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
! 234: WHERE NOT EXISTS
! 235: (SELECT 'x' FROM flock_owner later
! 236: WHERE prev.flock_no = later.flock_no
! 237: AND later.owner_change_date > prev.owner_change_date
! 238: AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
! 239: ) y ON x.sheep_no = y.sheep_no
! 240: WHERE y.sheep_no IS NULL
! 241: ORDER BY x.registering_flock;
! 242: } {
! 243: 1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)}
! 244: 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)}
! 245: 1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
! 246: 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)}
! 247: 0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)}
! 248: 0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~8 rows)}
! 249: }
! 250:
! 251:
! 252: do_execsql_test autoindex1-700 {
! 253: CREATE TABLE t5(a, b, c);
! 254: EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c;
! 255: } {
! 256: 0 0 0 {SCAN TABLE t5 (~100000 rows)}
! 257: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 258: }
! 259:
! 260:
! 261: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to autoindex1.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test