xref: /sqlite-3.40.0/test/skipscan5.test (revision 4e42ba4a) 
1# 2013-11-13
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# This file implements tests of the "skip-scan" query strategy. In
13# particular it tests that stat4 data can be used by a range query
14# that uses the skip-scan approach.
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19set testprefix skipscan5
20
21ifcapable !stat4 {
22  finish_test
23  return
24}
25
26do_execsql_test 1.1 {
27  CREATE TABLE t1(a INT, b INT, c INT);
28  CREATE INDEX i1 ON t1(a, b);
29} {}
30
31expr srand(4)
32do_test 1.2 {
33  for {set i 0} {$i < 100} {incr i} {
34    set a [expr int(rand()*4.0) + 1]
35    set b [expr int(rand()*20.0) + 1]
36    execsql { INSERT INTO t1 VALUES($a, $b, NULL) }
37  }
38  execsql ANALYZE
39} {}
40
41do_eqp_test 1.3 {
42  SELECT * FROM t1 WHERE b = 5;
43} {
44  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b=?)}
45}
46
47do_eqp_test 1.4 {
48  SELECT * FROM t1 WHERE b > 12 AND b < 16;
49} {
50  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>? AND b<?)}
51}
52
53do_eqp_test 1.5 {
54  SELECT * FROM t1 WHERE b > 2 AND b < 16;
55} {
56  0 0 0 {SCAN TABLE t1}
57}
58
59do_eqp_test 1.6 {
60  SELECT * FROM t1 WHERE b > 18 AND b < 25;
61} {
62  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>? AND b<?)}
63}
64
65do_eqp_test 1.7 {
66  SELECT * FROM t1 WHERE b > 18 AND b < 25;
67} {
68  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>? AND b<?)}
69}
70
71do_eqp_test 1.8 {
72  SELECT * FROM t1 WHERE b > 15;
73} {
74  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>?)}
75}
76
77do_eqp_test 1.9 {
78  SELECT * FROM t1 WHERE b > 5;
79} {
80  0 0 0 {SCAN TABLE t1}
81}
82
83do_eqp_test 1.10 {
84  SELECT * FROM t1 WHERE b < 5;
85} {
86  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b<?)}
87}
88
89do_eqp_test 1.11 {
90  SELECT * FROM t1 WHERE b < 15;
91} {
92  0 0 0 {SCAN TABLE t1}
93}
94
95finish_test
96