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