xref: /sqlite-3.40.0/test/coveridxscan.test (revision 8210233c) 
1# 2012 September 17
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#
12# Tests for the optimization which attempts to use a covering index
13# for a full-table scan (under the theory that the index will be smaller
14# and require less I/O and hence will run faster.)
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20set testprefix coveridxscan
21
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 increasing
31} {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 order
38} {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 columns
45} {2 4 8}
46
47do_test 2.1 {
48  optimization_control db cover-idx-scan 0
49  db eval {SELECT a FROM t1}
50  # With the optimization turned off, output in rowid order
51} {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 close
60sqlite3_shutdown
61sqlite3_config_cis 0
62sqlite3 db test.db
63
64do_test 3.1 {
65  db eval {SELECT a FROM t1}
66  # With the optimization configured off, output in rowid order
67} {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 close
76sqlite3_shutdown
77sqlite3_config_cis 1
78sqlite3 db test.db
79
80# The CIS optimization is enabled again.  Covering indices are once again
81# 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_db
96set 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_test
120