AryaWu/sqlite

1# 2014-04-262#3# The author disclaims copyright to this source code.  In place of4# 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# 12 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15set testprefix cost16 17 18do_execsql_test 1.1 {19  CREATE TABLE t3(id INTEGER PRIMARY KEY, b NOT NULL);20  CREATE TABLE t4(c, d, e);21  CREATE UNIQUE INDEX i3 ON t3(b);22  CREATE UNIQUE INDEX i4 ON t4(c, d);23}24do_eqp_test 1.2 {25  SELECT e FROM t3, t4 WHERE b=c ORDER BY b, d;26} {27  QUERY PLAN28  |--SCAN t3 USING COVERING INDEX i329  `--SEARCH t4 USING INDEX i4 (c=?)30}31 32 33do_execsql_test 2.1 {34  CREATE TABLE t1(a, b);35  CREATE INDEX i1 ON t1(a);36}37 38# It is better to use an index for ORDER BY than sort externally, even 39# if the index is a non-covering index.40do_eqp_test 2.2 {41  SELECT * FROM t1 ORDER BY a;42} {SCAN t1 USING INDEX i1}43 44do_execsql_test 3.1 {45  CREATE TABLE t5(a INTEGER PRIMARY KEY,b,c,d,e,f,g);46  CREATE INDEX t5b ON t5(b);47  CREATE INDEX t5c ON t5(c);48  CREATE INDEX t5d ON t5(d);49  CREATE INDEX t5e ON t5(e);50  CREATE INDEX t5f ON t5(f);51  CREATE INDEX t5g ON t5(g);52}53 54do_eqp_test 3.2 {55  SELECT a FROM t5 56  WHERE b IS NULL OR c IS NULL OR d IS NULL 57  ORDER BY a;58} {59  QUERY PLAN60  |--MULTI-INDEX OR61  |  |--INDEX 162  |  |  `--SEARCH t5 USING INDEX t5b (b=?)63  |  |--INDEX 264  |  |  `--SEARCH t5 USING INDEX t5c (c=?)65  |  `--INDEX 366  |     `--SEARCH t5 USING INDEX t5d (d=?)67  `--USE TEMP B-TREE FOR ORDER BY68}69 70#-------------------------------------------------------------------------71# If there is no likelihood() or stat3 data, SQLite assumes that a closed72# range scan (e.g. one constrained by "col BETWEEN ? AND ?" constraint)73# visits 1/64 of the rows in a table.74#75# Note: 1/63 =~ 0.01676# Note: 1/65 =~ 0.01577#78reset_db79do_execsql_test 4.1 {80  CREATE TABLE t1(a, b);81  CREATE INDEX i1 ON t1(a);82  CREATE INDEX i2 ON t1(b);83}84do_eqp_test 4.2 {85  SELECT * FROM t1 WHERE likelihood(a=?, 0.014) AND b BETWEEN ? AND ?;86} {SEARCH t1 USING INDEX i1 (a=?)}87 88do_eqp_test 4.3 {89  SELECT * FROM t1 WHERE likelihood(a=?, 0.016) AND b BETWEEN ? AND ?;90} {SEARCH t1 USING INDEX i2 (b>? AND b<?)}91 92 93#-------------------------------------------------------------------------94#95reset_db96do_execsql_test 5.1 {97  CREATE TABLE t2(x, y);98  CREATE INDEX t2i1 ON t2(x);99}100 101do_eqp_test 5.2 {102  SELECT * FROM t2 ORDER BY x, y;103} {104  QUERY PLAN105  |--SCAN t2 USING INDEX t2i1106  `--USE TEMP B-TREE FOR LAST TERM OF ORDER BY107}108 109do_eqp_test 5.3 {110  SELECT * FROM t2 WHERE x BETWEEN ? AND ? ORDER BY rowid;111} {112  QUERY PLAN113  |--SEARCH t2 USING INDEX t2i1 (x>? AND x<?)114  `--USE TEMP B-TREE FOR ORDER BY115}116 117# where7.test, where8.test:118#119do_execsql_test 6.1 {120  CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c);121  CREATE INDEX t3i1 ON t3(b);122  CREATE INDEX t3i2 ON t3(c);123}124 125do_eqp_test 6.2 {126  SELECT a FROM t3 WHERE (b BETWEEN 2 AND 4) OR c=100 ORDER BY a127} {128  QUERY PLAN129  |--MULTI-INDEX OR130  |  |--INDEX 1131  |  |  `--SEARCH t3 USING INDEX t3i1 (b>? AND b<?)132  |  `--INDEX 2133  |     `--SEARCH t3 USING INDEX t3i2 (c=?)134  `--USE TEMP B-TREE FOR ORDER BY135}136 137#-------------------------------------------------------------------------138#139reset_db140do_execsql_test 7.1 {141  CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d,e,f,g);142  CREATE INDEX t1b ON t1(b);143  CREATE INDEX t1c ON t1(c);144  CREATE INDEX t1d ON t1(d);145  CREATE INDEX t1e ON t1(e);146  CREATE INDEX t1f ON t1(f);147  CREATE INDEX t1g ON t1(g);148}149 150do_eqp_test 7.2 {151  SELECT a FROM t1152     WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)153  ORDER BY a154} {155  QUERY PLAN156  |--MULTI-INDEX OR157  |  |--INDEX 1158  |  |  `--SEARCH t1 USING INDEX t1b (b>? AND b<?)159  |  `--INDEX 2160  |     `--SEARCH t1 USING INDEX t1b (b=?)161  `--USE TEMP B-TREE FOR ORDER BY162}163 164do_eqp_test 7.3 {165  SELECT rowid FROM t1166  WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)167        OR (b NOT NULL AND c IS NULL AND d NOT NULL)168        OR (b NOT NULL AND c NOT NULL AND d IS NULL)169} {SCAN t1}170 171do_eqp_test 7.4 {172  SELECT rowid FROM t1 WHERE (+b IS NULL AND c NOT NULL) OR c IS NULL173} {SCAN t1}174 175#-------------------------------------------------------------------------176#177reset_db178do_execsql_test 8.1 {179  CREATE TABLE composer(180    cid INTEGER PRIMARY KEY,181    cname TEXT182  );183  CREATE TABLE album(184    aid INTEGER PRIMARY KEY,185    aname TEXT186  );187  CREATE TABLE track(188    tid INTEGER PRIMARY KEY,189    cid INTEGER REFERENCES composer,190    aid INTEGER REFERENCES album,191    title TEXT192  );193  CREATE INDEX track_i1 ON track(cid);194  CREATE INDEX track_i2 ON track(aid);195}196 197do_eqp_test 8.2 {198  SELECT DISTINCT aname199    FROM album, composer, track200   WHERE cname LIKE '%bach%'201     AND unlikely(composer.cid=track.cid)202     AND unlikely(album.aid=track.aid);203} {204  QUERY PLAN205  |--SCAN track206  |--SEARCH album USING INTEGER PRIMARY KEY (rowid=?)207  |--SEARCH composer USING INTEGER PRIMARY KEY (rowid=?)208  `--USE TEMP B-TREE FOR DISTINCT209}210 211#-------------------------------------------------------------------------212#213do_execsql_test 9.1 {214  CREATE TABLE t1(215    a,b,c,d,e, f,g,h,i,j,216    k,l,m,n,o, p,q,r,s,t217  );218  CREATE INDEX i1 ON t1(k,l,m,n,o,p,q,r,s,t);219}220do_test 9.2 {221  for {set i 0} {$i < 100} {incr i} {222    execsql { INSERT INTO t1 DEFAULT VALUES }223  }224  execsql {225    ANALYZE;226    CREATE INDEX i2 ON t1(a,b,c,d,e,f,g,h,i,j);227  }228} {}229 230set L [list a=? b=? c=? d=? e=? f=? g=? h=? i=? j=?]231foreach {tn nTerm nRow} {232  1   1 10233  2   2 10234  3   3  8235  4   4  7236  5   5  7237  6   6  5238  7   7  5239  8   8  5240  9   9  5241  10 10  5242} {243  set w [join [lrange $L 0 [expr $nTerm-1]] " AND "]244  set p1 [expr ($nRow-1) / 100.0]245  set p2 [expr ($nRow+1) / 100.0]246 247  set sql1 "SELECT * FROM t1 WHERE likelihood(k=?, $p1) AND $w"248  set sql2 "SELECT * FROM t1 WHERE likelihood(k=?, $p2) AND $w"249 250  do_eqp_test 9.3.$tn.1 $sql1 {/INDEX i1/}251  do_eqp_test 9.3.$tn.2 $sql2 {/INDEX i2/}252}253 254 255#-------------------------------------------------------------------------256#257 258ifcapable stat4 {259  do_execsql_test 10.1 {260    CREATE TABLE t6(a, b, c);261    CREATE INDEX t6i1 ON t6(a, b);262    CREATE INDEX t6i2 ON t6(c);263  }264  265  do_test 10.2 {266    for {set i 0} {$i < 16} {incr i} {267      execsql { INSERT INTO t6 VALUES($i%4, 'xyz', $i%8) }268    }269    execsql ANALYZE270  } {}271 272  do_eqp_test 10.3 {273    SELECT rowid FROM t6 WHERE a=0 AND c=0274  } {SEARCH t6 USING INDEX t6i2 (c=?)}275 276  do_eqp_test 10.4 {277    SELECT rowid FROM t6 WHERE a=0 AND b='xyz' AND c=0278  } {SEARCH t6 USING INDEX t6i2 (c=?)}279 280  do_eqp_test 10.5 {281    SELECT rowid FROM t6 WHERE likelihood(a=0, 0.1) AND c=0282  } {SEARCH t6 USING INDEX t6i1 (a=?)}283 284  do_eqp_test 10.6 {285    SELECT rowid FROM t6 WHERE likelihood(a=0, 0.1) AND b='xyz' AND c=0286  } {SEARCH t6 USING INDEX t6i1 (a=? AND b=?)}287}288 289finish_test290