Return to autoindex1.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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