Debian Codegraph @

mysql-test/suite/innodb/t/records_in_range.test

raw
1 #
2 # Test btr_estimate_n_rows_in_range() which is used by
3 # ha_innobase::records_in_range()
4 #
5
6 -- source include/have_debug.inc
7 -- source include/have_innodb.inc
8 -- source include/innodb_page_size_small.inc
9
10 CREATE TABLE records_in_range_test (
11 c1 VARCHAR(16),
12 c2 VARCHAR(512),
13 PRIMARY KEY (c1)
14 ) ENGINE=INNODB STATS_PERSISTENT=1;
15
16 # Insert some records so that they cannot fit in one page for some page sizes
17 # in order to exercise records_in_range() where 1, 2 or more pages are sampled
18 INSERT INTO records_in_range_test VALUES
19 ('ccc', REPEAT('v', 512)),
20 ('kkk01', REPEAT('v', 512)),
21 ('kkk02', REPEAT('v', 512)),
22 ('kkk03', REPEAT('v', 512)),
23 ('kkk04', REPEAT('v', 512)),
24 ('kkk05', REPEAT('v', 512)),
25 ('kkk06', REPEAT('v', 512)),
26 ('kkk07', REPEAT('v', 512)),
27 ('kkk08', REPEAT('v', 512)),
28 ('mmm', REPEAT('v', 512)),
29 ('nnn', REPEAT('v', 512)),
30 ('uuu01', REPEAT('v', 512)),
31 ('uuu02', REPEAT('v', 512)),
32 ('uuu03', REPEAT('v', 512)),
33 ('uuu04', REPEAT('v', 512)),
34 ('uuu05', REPEAT('v', 512)),
35 ('uuu06', REPEAT('v', 512)),
36 ('uuu07', REPEAT('v', 512)),
37 ('uuu08', REPEAT('v', 512)),
38 ('xxx', REPEAT('v', 512));
39
40 SET STATEMENT use_stat_tables=never FOR
41 ANALYZE TABLE records_in_range_test;
42
43 # 16k or bigger page size: 1 leaf page
44 # 8k page size: 2 leaf pages
45 # 4k page size: 4 leaf pages
46 SELECT index_name, stat_name, stat_value
47 FROM mysql.innodb_index_stats
48 WHERE
49 table_name='records_in_range_test' AND stat_name = 'n_leaf_pages';
50
51 # 16k or bigger page size: 1 page in total (leaf + nonleaf)
52 # 8k page size: 3 pages in total (leaf + nonleaf)
53 # 4k page size: 5 pages in total (leaf + nonleaf)
54 SELECT index_name, stat_name, stat_value
55 FROM mysql.innodb_index_stats
56 WHERE
57 table_name='records_in_range_test' AND stat_name = 'size';
58
59 # We exploit the warning mechanism here to display the return value from
60 # btr_estimate_n_rows_in_range()
61 SET @save_dbug = @@debug_dbug;
62 SET DEBUG_DBUG='+d,print_btr_estimate_n_rows_in_range_return_value';
63
64 -- echo
65 -- echo In all SELECTs below the number of the records in the range returned
66 -- echo by COUNT(*) must be the same as the number returned by
67 -- echo btr_estimate_n_rows_in_range() which can be seen inside the artificial
68 -- echo warning
69
70 -- echo
71 -- echo Test left-unbounded, right-open intervals
72 -- echo
73 SELECT COUNT(*) FROM records_in_range_test WHERE c1 < 'aaa';
74 SELECT COUNT(*) FROM records_in_range_test WHERE c1 < 'ccc';
75 SELECT COUNT(*) FROM records_in_range_test WHERE c1 < 'eee';
76 SELECT COUNT(*) FROM records_in_range_test WHERE c1 < 'mmm';
77 SELECT COUNT(*) FROM records_in_range_test WHERE c1 < 'nnn';
78 SELECT COUNT(*) FROM records_in_range_test WHERE c1 < 'qqq';
79 SELECT COUNT(*) FROM records_in_range_test WHERE c1 < 'xxx';
80 SELECT COUNT(*) FROM records_in_range_test WHERE c1 < 'zzz';
81
82 -- echo
83 -- echo Test left-unbounded, right-closed intervals
84 -- echo
85 SELECT COUNT(*) FROM records_in_range_test WHERE c1 <= 'aaa';
86 SELECT COUNT(*) FROM records_in_range_test WHERE c1 <= 'ccc';
87 SELECT COUNT(*) FROM records_in_range_test WHERE c1 <= 'eee';
88 SELECT COUNT(*) FROM records_in_range_test WHERE c1 <= 'mmm';
89 SELECT COUNT(*) FROM records_in_range_test WHERE c1 <= 'nnn';
90 SELECT COUNT(*) FROM records_in_range_test WHERE c1 <= 'qqq';
91 SELECT COUNT(*) FROM records_in_range_test WHERE c1 <= 'xxx';
92 SELECT COUNT(*) FROM records_in_range_test WHERE c1 <= 'zzz';
93
94 -- echo
95 -- echo Test left-open, right-unbounded intervals
96 -- echo
97 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa';
98 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc';
99 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee';
100 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm';
101 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn';
102 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq';
103 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx';
104 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz';
105
106 -- echo
107 -- echo Test left-closed, right-unbounded intervals
108 -- echo
109 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa';
110 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc';
111 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee';
112 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm';
113 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn';
114 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq';
115 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx';
116 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz';
117
118 -- echo
119 -- echo Test left-open, right-open intervals
120 -- echo In some cases here the optimizer is smart enough not to call
121 -- echo ha_innobase::records_in_range() at all, so we get no warning containing
122 -- echo the value returned from btr_estimate_n_rows_in_range()
123 -- echo
124 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 < 'bbb';
125 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 < 'ccc';
126 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 < 'eee';
127 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 < 'mmm';
128 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 < 'nnn';
129 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 < 'qqq';
130 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 < 'xxx';
131 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 < 'zzz';
132 -- echo
133 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 < 'bbb';
134 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 < 'ccc';
135 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 < 'eee';
136 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 < 'mmm';
137 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 < 'nnn';
138 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 < 'qqq';
139 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 < 'xxx';
140 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 < 'zzz';
141 -- echo
142 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 < 'bbb';
143 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 < 'ccc';
144 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 < 'eee';
145 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 < 'mmm';
146 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 < 'nnn';
147 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 < 'qqq';
148 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 < 'xxx';
149 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 < 'zzz';
150 -- echo
151 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 < 'bbb';
152 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 < 'ccc';
153 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 < 'eee';
154 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 < 'mmm';
155 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 < 'nnn';
156 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 < 'qqq';
157 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 < 'xxx';
158 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 < 'zzz';
159 -- echo
160 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 < 'bbb';
161 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 < 'ccc';
162 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 < 'eee';
163 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 < 'mmm';
164 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 < 'nnn';
165 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 < 'qqq';
166 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 < 'xxx';
167 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 < 'zzz';
168 -- echo
169 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 < 'bbb';
170 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 < 'ccc';
171 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 < 'eee';
172 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 < 'mmm';
173 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 < 'nnn';
174 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 < 'qqq';
175 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 < 'xxx';
176 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 < 'zzz';
177 -- echo
178 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 < 'bbb';
179 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 < 'ccc';
180 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 < 'eee';
181 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 < 'mmm';
182 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 < 'nnn';
183 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 < 'qqq';
184 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 < 'xxx';
185 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 < 'zzz';
186 -- echo
187 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 < 'bbb';
188 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 < 'ccc';
189 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 < 'eee';
190 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 < 'mmm';
191 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 < 'nnn';
192 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 < 'qqq';
193 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 < 'xxx';
194 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 < 'zzz';
195
196 -- echo
197 -- echo Test left-closed, right-open intervals
198 -- echo In some cases here the optimizer is smart enough not to call
199 -- echo ha_innobase::records_in_range() at all, so we get no warning containing
200 -- echo the value returned from btr_estimate_n_rows_in_range()
201 -- echo
202 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 < 'bbb';
203 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 < 'ccc';
204 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 < 'eee';
205 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 < 'mmm';
206 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 < 'nnn';
207 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 < 'qqq';
208 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 < 'xxx';
209 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 < 'zzz';
210 -- echo
211 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 < 'bbb';
212 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 < 'ccc';
213 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 < 'eee';
214 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 < 'mmm';
215 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 < 'nnn';
216 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 < 'qqq';
217 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 < 'xxx';
218 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 < 'zzz';
219 -- echo
220 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 < 'bbb';
221 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 < 'ccc';
222 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 < 'eee';
223 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 < 'mmm';
224 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 < 'nnn';
225 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 < 'qqq';
226 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 < 'xxx';
227 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 < 'zzz';
228 -- echo
229 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 < 'bbb';
230 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 < 'ccc';
231 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 < 'eee';
232 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 < 'mmm';
233 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 < 'nnn';
234 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 < 'qqq';
235 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 < 'xxx';
236 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 < 'zzz';
237 -- echo
238 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 < 'bbb';
239 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 < 'ccc';
240 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 < 'eee';
241 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 < 'mmm';
242 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 < 'nnn';
243 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 < 'qqq';
244 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 < 'xxx';
245 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 < 'zzz';
246 -- echo
247 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 < 'bbb';
248 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 < 'ccc';
249 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 < 'eee';
250 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 < 'mmm';
251 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 < 'nnn';
252 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 < 'qqq';
253 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 < 'xxx';
254 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 < 'zzz';
255 -- echo
256 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 < 'bbb';
257 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 < 'ccc';
258 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 < 'eee';
259 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 < 'mmm';
260 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 < 'nnn';
261 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 < 'qqq';
262 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 < 'xxx';
263 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 < 'zzz';
264 -- echo
265 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 < 'bbb';
266 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 < 'ccc';
267 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 < 'eee';
268 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 < 'mmm';
269 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 < 'nnn';
270 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 < 'qqq';
271 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 < 'xxx';
272 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 < 'zzz';
273
274 -- echo
275 -- echo Test left-open, right-closed intervals
276 -- echo In some cases here the optimizer is smart enough not to call
277 -- echo ha_innobase::records_in_range() at all, so we get no warning containing
278 -- echo the value returned from btr_estimate_n_rows_in_range()
279 -- echo
280 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 <= 'bbb';
281 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 <= 'ccc';
282 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 <= 'eee';
283 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 <= 'mmm';
284 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 <= 'nnn';
285 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 <= 'qqq';
286 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 <= 'xxx';
287 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'aaa' AND c1 <= 'zzz';
288 -- echo
289 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 <= 'bbb';
290 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 <= 'ccc';
291 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 <= 'eee';
292 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 <= 'mmm';
293 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 <= 'nnn';
294 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 <= 'qqq';
295 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 <= 'xxx';
296 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'ccc' AND c1 <= 'zzz';
297 -- echo
298 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 <= 'bbb';
299 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 <= 'ccc';
300 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 <= 'eee';
301 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 <= 'mmm';
302 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 <= 'nnn';
303 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 <= 'qqq';
304 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 <= 'xxx';
305 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'eee' AND c1 <= 'zzz';
306 -- echo
307 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 <= 'bbb';
308 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 <= 'ccc';
309 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 <= 'eee';
310 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 <= 'mmm';
311 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 <= 'nnn';
312 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 <= 'qqq';
313 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 <= 'xxx';
314 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'mmm' AND c1 <= 'zzz';
315 -- echo
316 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 <= 'bbb';
317 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 <= 'ccc';
318 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 <= 'eee';
319 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 <= 'mmm';
320 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 <= 'nnn';
321 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 <= 'qqq';
322 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 <= 'xxx';
323 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'nnn' AND c1 <= 'zzz';
324 -- echo
325 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 <= 'bbb';
326 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 <= 'ccc';
327 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 <= 'eee';
328 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 <= 'mmm';
329 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 <= 'nnn';
330 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 <= 'qqq';
331 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 <= 'xxx';
332 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'qqq' AND c1 <= 'zzz';
333 -- echo
334 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 <= 'bbb';
335 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 <= 'ccc';
336 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 <= 'eee';
337 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 <= 'mmm';
338 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 <= 'nnn';
339 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 <= 'qqq';
340 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 <= 'xxx';
341 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'xxx' AND c1 <= 'zzz';
342 -- echo
343 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 <= 'bbb';
344 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 <= 'ccc';
345 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 <= 'eee';
346 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 <= 'mmm';
347 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 <= 'nnn';
348 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 <= 'qqq';
349 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 <= 'xxx';
350 SELECT COUNT(*) FROM records_in_range_test WHERE c1 > 'zzz' AND c1 <= 'zzz';
351
352 -- echo
353 -- echo Test left-closed, right-closed intervals
354 -- echo In some cases here the optimizer is smart enough not to call
355 -- echo ha_innobase::records_in_range() at all, so we get no warning containing
356 -- echo the value returned from btr_estimate_n_rows_in_range()
357 -- echo
358 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 <= 'bbb';
359 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 <= 'ccc';
360 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 <= 'eee';
361 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 <= 'mmm';
362 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 <= 'nnn';
363 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 <= 'qqq';
364 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 <= 'xxx';
365 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'aaa' AND c1 <= 'zzz';
366 -- echo
367 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 <= 'bbb';
368 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 <= 'ccc';
369 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 <= 'eee';
370 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 <= 'mmm';
371 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 <= 'nnn';
372 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 <= 'qqq';
373 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 <= 'xxx';
374 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'ccc' AND c1 <= 'zzz';
375 -- echo
376 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 <= 'bbb';
377 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 <= 'ccc';
378 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 <= 'eee';
379 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 <= 'mmm';
380 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 <= 'nnn';
381 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 <= 'qqq';
382 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 <= 'xxx';
383 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'eee' AND c1 <= 'zzz';
384 -- echo
385 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 <= 'bbb';
386 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 <= 'ccc';
387 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 <= 'eee';
388 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 <= 'mmm';
389 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 <= 'nnn';
390 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 <= 'qqq';
391 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 <= 'xxx';
392 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'mmm' AND c1 <= 'zzz';
393 -- echo
394 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 <= 'bbb';
395 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 <= 'ccc';
396 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 <= 'eee';
397 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 <= 'mmm';
398 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 <= 'nnn';
399 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 <= 'qqq';
400 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 <= 'xxx';
401 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'nnn' AND c1 <= 'zzz';
402 -- echo
403 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 <= 'bbb';
404 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 <= 'ccc';
405 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 <= 'eee';
406 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 <= 'mmm';
407 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 <= 'nnn';
408 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 <= 'qqq';
409 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 <= 'xxx';
410 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'qqq' AND c1 <= 'zzz';
411 -- echo
412 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 <= 'bbb';
413 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 <= 'ccc';
414 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 <= 'eee';
415 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 <= 'mmm';
416 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 <= 'nnn';
417 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 <= 'qqq';
418 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 <= 'xxx';
419 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'xxx' AND c1 <= 'zzz';
420 -- echo
421 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 <= 'bbb';
422 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 <= 'ccc';
423 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 <= 'eee';
424 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 <= 'mmm';
425 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 <= 'nnn';
426 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 <= 'qqq';
427 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 <= 'xxx';
428 SELECT COUNT(*) FROM records_in_range_test WHERE c1 >= 'zzz' AND c1 <= 'zzz';
429
430 SET DEBUG_DBUG = @save_dbug;
431
432 DROP TABLE records_in_range_test;