1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
|
# 2013-11-13
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file implements tests of the "skip-scan" query strategy. In
# particular it tests that stat4 data can be used by a range query
# that uses the skip-scan approach.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix skipscan5
ifcapable !stat4 {
finish_test
return
}
do_execsql_test 1.1 {
CREATE TABLE t1(a INT, b INT, c INT);
CREATE INDEX i1 ON t1(a, b);
} {}
expr srand(4)
do_test 1.2 {
for {set i 0} {$i < 1000} {incr i} {
set a [expr int(rand()*4.0) + 1]
set b [expr int(rand()*20.0) + 1]
execsql { INSERT INTO t1 VALUES($a, $b, NULL) }
}
execsql ANALYZE
} {}
foreach {tn q res} {
1 "b = 5" {/*ANY(a) AND b=?*/}
2 "b > 12 AND b < 16" {/*ANY(a) AND b>? AND b<?*/}
3 "b > 2 AND b < 16" {/*SCAN t1*/}
4 "b > 18 AND b < 25" {/*ANY(a) AND b>? AND b<?*/}
5 "b > 16" {/*ANY(a) AND b>?*/}
6 "b > 5" {/*SCAN t1*/}
7 "b < 15" {/*SCAN t1*/}
8 "b < 5" {/*ANY(a) AND b<?*/}
9 "5 > b" {/*ANY(a) AND b<?*/}
10 "b = '5'" {/*ANY(a) AND b=?*/}
11 "b > '12' AND b < '16'" {/*ANY(a) AND b>? AND b<?*/}
12 "b > '2' AND b < '16'" {/*SCAN t1*/}
13 "b > '18' AND b < '25'" {/*ANY(a) AND b>? AND b<?*/}
14 "b > '16'" {/*ANY(a) AND b>?*/}
15 "b > '5'" {/*SCAN t1*/}
16 "b < '15'" {/*SCAN t1*/}
17 "b < '5'" {/*ANY(a) AND b<?*/}
18 "'5' > b" {/*ANY(a) AND b<?*/}
} {
set sql "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE $q"
do_execsql_test 1.3.$tn $sql $res
}
#-------------------------------------------------------------------------
# Test that range-query/skip-scan estimation works with text values.
# And on UTF-16 databases when there is no UTF-16 collation sequence
# available.
#
proc test_collate {enc lhs rhs} {
string compare $lhs $rhs
}
foreach {tn dbenc coll} {
1 UTF-8 { add_test_collate db 0 0 1 }
2 UTF-16 { add_test_collate db 1 0 0 }
3 UTF-8 { add_test_collate db 0 1 0 }
} {
reset_db
eval $coll
do_execsql_test 2.$tn.1 " PRAGMA encoding = '$dbenc' "
do_execsql_test 2.$tn.2 {
CREATE TABLE t2(a TEXT, b TEXT, c TEXT COLLATE test_collate, d TEXT);
CREATE INDEX i2 ON t2(a, b, c);
}
set vocab(d) { :) }
set vocab(c) { a b c d e f g h i j k l m n o p q r s t }
set vocab(b) { one two three }
set vocab(a) { sql }
do_test 2.$tn.3 {
for {set i 0} {$i < 100} {incr i} {
foreach var {a b c d} {
set $var [lindex $vocab($var) [expr $i % [llength $vocab($var)]]]
}
execsql { INSERT INTO t2 VALUES($a, $b, $c, $d) }
}
execsql ANALYZE
} {}
foreach {tn2 q res} {
1 { c BETWEEN 'd' AND 'e' } {/*ANY(a) AND ANY(b) AND c>? AND c<?*/}
2 { c BETWEEN 'b' AND 'r' } {/*SCAN t2*/}
3 { c > 'q' } {/*ANY(a) AND ANY(b) AND c>?*/}
4 { c > 'e' } {/*SCAN t2*/}
5 { c < 'q' } {/*SCAN t2*/}
6 { c < 'b' } {/*ANY(a) AND ANY(b) AND c<?*/}
} {
set sql "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE $q"
do_execsql_test 2.$tn.$tn2 $sql $res
}
}
#-------------------------------------------------------------------------
# Test that range-query/skip-scan estimation works on columns that contain
# a variety of types.
#
reset_db
do_execsql_test 3.1 {
CREATE TABLE t3(a, b, c);
CREATE INDEX i3 ON t3(a, b);
}
set values {
NULL NULL NULL
NULL -9567 -9240
-8725 -8659 -8248.340244520614
-8208 -7939 -7746.985758536954
-7057 -6550 -5916
-5363 -4935.781822975623 -4935.063633571875
-3518.4554911770183 -2537 -2026
-1511.2603881914456 -1510.4195994839156 -1435
-1127.4210136045804 -1045 99
1353 1457 1563.2908193223611
2245 2286 2552
2745.18831295203 2866.279926554429 3075.0468527316334
3447 3867 4237.892420141907
4335 5052.9775000424015 5232.178240656935
5541.784919585003 5749.725576373621 5758
6005 6431 7263.477992854769
7441 7541 8667.279760663994
8857 9199.638673662972 'dl'
'dro' 'h' 'igprfq'
'jnbd' 'k' 'kordee'
'lhwcv' 'mzlb' 'nbjked'
'nufpo' 'nxqkdq' 'shelln'
'tvzn' 'wpnt' 'wylf'
'ydkgu' 'zdb' X''
X'0a' X'203f6429f1f33f' X'23858e324545e0362b'
X'3f9f8a' X'516f7ddd4b' X'68f1df0930ac6b'
X'9ea60d' X'a06f' X'aefd342a39ce36df'
X'afaa020fe2' X'be201c' X'c47d97b209601e45'
}
do_test 3.2 {
set c 0
foreach v $values {
execsql "INSERT INTO t3 VALUES($c % 2, $v, $c)"
incr c
}
execsql ANALYZE
} {}
foreach {tn q res} {
1 "b BETWEEN -10000 AND -8000" {/*ANY(a) AND b>? AND b<?*/}
2 "b BETWEEN -10000 AND 'qqq'" {/*SCAN t3*/}
3 "b < X'5555'" {/*SCAN t3*/}
4 "b > X'5555'" {/*ANY(a) AND b>?*/}
5 "b > 'zzz'" {/*ANY(a) AND b>?*/}
6 "b < 'zzz'" {/*SCAN t3*/}
} {
set sql "EXPLAIN QUERY PLAN SELECT * FROM t3 WHERE $q"
do_execsql_test 3.3.$tn $sql $res
}
finish_test
|
