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
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
|
#!/usr/bin/env tarantool
test = require("sqltester")
test:plan(15)
--!./tcltestrunner.lua
-- 2011 August 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 for sql library. The focus of the tests
-- in this file is testing the capabilities of sql_stat3.
--
testprefix = "analyze8"
-- Scenario:
--
-- Two indices. One has mostly singleton entries, but for a few
-- values there are hundreds of entries. The other has 10-20
-- entries per value.
--
-- Verify that the query planner chooses the first index for the singleton
-- entries and the second index for the others.
--
test:do_test(
1.0,
function()
test:execsql([[
CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a INT ,b INT ,c INT ,d INT );
CREATE INDEX t1a ON t1(a);
CREATE INDEX t1b ON t1(b);
CREATE INDEX t1c ON t1(c);
]])
for i=0, 1000 do
if (i % 2 == 0)
then
a = i
else
a =(i % 8) * 100
end
b = math.floor(i / 10)
c = math.floor(i / 8)
c = ((c * c) * c)
test:execsql("INSERT INTO t1 (a,b,c,d) VALUES("..a..", "..b..", "..c..", "..i..")")
end
return test:execsql("ANALYZE")
end, {
-- <1.0>
-- </1.0>
})
-- The a==100 comparison is expensive because there are many rows
-- with a==100. And so for those cases, choose the t1b index.
-- But for a==99 and a==101, there are far fewer rows so choose
-- the t1a index.
test:do_eqp_test(
1.1,
[[SELECT * FROM t1 WHERE a=100 AND b=55]],
{
-- <1.1>
{0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX T1B (B=?)"}
-- </1.1>
})
test:do_eqp_test(
1.2,
[[SELECT * FROM t1 WHERE a=99 AND b=55]],
{
-- <1.2>
{0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX T1A (A=?)"}
-- </1.2>
})
test:do_eqp_test(
1.3,
[[SELECT * FROM t1 WHERE a=101 AND b=55]],
{
-- <1.3>
{0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX T1A (A=?)"}
-- </1.3>
})
test:do_eqp_test(
1.4,
[[SELECT * FROM t1 WHERE a=100 AND b=56]],
{
-- <1.4>
{0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX T1B (B=?)"}
-- </1.4>
})
test:do_eqp_test(
1.5,
[[SELECT * FROM t1 WHERE a=99 AND b=56]],
{
-- <1.5>
{0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX T1A (A=?)"}
-- </1.5>
})
test:do_eqp_test(
1.6,
[[SELECT * FROM t1 WHERE a=101 AND b=56]],
{
-- <1.6>
{0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX T1A (A=?)"}
-- </1.6>
})
test:do_eqp_test(
2.1,
[[SELECT * FROM t1 WHERE a=100 AND b BETWEEN 50 AND 54]],
{
-- <2.1>
{0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX T1B (B>? AND B<?)"}
-- </2.1>
})
-- There are many more values of c between 0 and 100000 than there are
-- between 800000 and 900000. So t1c is more selective for the latter
-- range.
--
-- Test 3.2 is a little unstable. It depends on the planner estimating
-- that (b BETWEEN 30 AND 34) will match more rows than (c BETWEEN
-- 800000 AND 900000). Which is a pretty close call (50 vs. 32), so
-- the planner could get it wrong with an unlucky set of samples. This
-- case happens to work, but others ("b BETWEEN 40 AND 44" for example)
-- will fail.
--
test:do_execsql_test(
"3.0.0",
[[
SELECT count(*) FROM t1 WHERE b BETWEEN 30 AND 34
]], {
-- <3.0>
50
-- </3.0>
})
test:do_execsql_test(
"3.0.1",
[[
SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 100000;
]], {
-- <3.0>
376
-- </3.0>
})
test:do_execsql_test(
"3.0.2",
[[
SELECT count(*) FROM t1 WHERE c BETWEEN 800000 AND 900000;
]], {
-- <3.0>
32
-- </3.0>
})
test:do_eqp_test(
"3.1",
[[SELECT * FROM t1 WHERE b BETWEEN 30 AND 34 AND c BETWEEN 0 AND 100000]],
{
-- <3.1>
{0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX T1B (B>? AND B<?)"}
-- </3.1>
})
test:do_eqp_test(
"3.2",
[[SELECT * FROM t1
WHERE b BETWEEN 30 AND 34 AND c BETWEEN 800000 AND 900000]],
{
-- <3.2>
{0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX T1C (C>? AND C<?)"}
-- {0, 0, 0, "SEARCH TABLE t1 USING COVERING INDEX t1b (b>? AND b<?)"}
})
test:do_eqp_test(
3.3,
[[SELECT * FROM t1 WHERE a=100 AND c BETWEEN 0 AND 100000]],
{
-- <3.3>
{0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX T1A (A=?)"}
-- </3.3>
})
test:do_eqp_test(
"3.4",
[[SELECT * FROM t1
WHERE a=100 AND c BETWEEN 800000 AND 900000]],
{
-- <3.4>
{0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX T1C (C>? AND C<?)"}
-- </3.4>
})
test:finish_test()
|
