AryaWu/sqlite

1# 2012 September 172#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# Tests for the optimization which attempts to use a covering index13# for a full-table scan (under the theory that the index will be smaller14# and require less I/O and hence will run faster.)15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20set testprefix coveridxscan21 22do_test 1.1 {23  db eval {24    CREATE TABLE t1(a,b,c);25    INSERT INTO t1 VALUES(5,4,3), (4,8,2), (3,2,1);26    CREATE INDEX t1ab ON t1(a,b);27    CREATE INDEX t1b ON t1(b);28    SELECT a FROM t1;29  }30  # covering index used for the scan, hence values are increasing31} {3 4 5}32 33do_test 1.2 {34  db eval {35    SELECT a, c FROM t1;36  }37  # There is no covering index, hence the values are in rowid order38} {5 3 4 2 3 1}39 40do_test 1.3 {41  db eval {42    SELECT b FROM t1;43  }44  # Choice of two indices: use the one with fewest columns45} {2 4 8}46 47do_test 2.1 {48  optimization_control db cover-idx-scan 049  db eval {SELECT a FROM t1}50  # With the optimization turned off, output in rowid order51} {5 4 3}52do_test 2.2 {53  db eval {SELECT a, c FROM t1}54} {5 3 4 2 3 1}55do_test 2.3 {56  db eval {SELECT b FROM t1}57} {4 8 2}58 59db close60sqlite3_shutdown61sqlite3_config_cis 062sqlite3 db test.db63 64do_test 3.1 {65  db eval {SELECT a FROM t1}66  # With the optimization configured off, output in rowid order67} {5 4 3}68do_test 3.2 {69  db eval {SELECT a, c FROM t1}70} {5 3 4 2 3 1}71do_test 3.3 {72  db eval {SELECT b FROM t1}73} {4 8 2}74 75db close76sqlite3_shutdown77sqlite3_config_cis 178sqlite3 db test.db79 80# The CIS optimization is enabled again.  Covering indices are once again81# used for all table scans.82do_test 4.1 {83  db eval {SELECT a FROM t1}84} {3 4 5}85do_test 4.2 {86  db eval {SELECT a, c FROM t1}87} {5 3 4 2 3 1}88do_test 4.3 {89  db eval {SELECT b FROM t1}90} {2 4 8}91 92#-------------------------------------------------------------------------93# Test that indexes with large numbers of columns can be correctly 94# identified as covering indexes.95reset_db96set L [list]97for {set i 1} {$i<120} {incr i} {98  lappend L "c$i"99}100set cols [join $L ,]101 102do_execsql_test 5.1.0 "103  CREATE TABLE t1(a, b, c, $cols, PRIMARY KEY(a, b, c)) WITHOUT ROWID;104  CREATE INDEX i1 ON t1($cols);105 106  CREATE TABLE t2(i INTEGER PRIMARY KEY, $cols);107  CREATE INDEX i2 ON t2($cols);108"109 110do_eqp_test 5.1.1 {111  SELECT * FROM t1 ORDER BY c1, c2;112} {SCAN t1 USING COVERING INDEX i1}113 114do_eqp_test 5.1.2 {115  SELECT * FROM t2 ORDER BY c1, c2;116} {SCAN t2 USING COVERING INDEX i2}117 118 119finish_test120