1 # name : bug53761.patch
4 # Backport of the fix for MySQL bug #53761 to 5.1
6 --- a/storage/innodb_plugin/btr/btr0cur.c
7 +++ b/storage/innodb_plugin/btr/btr0cur.c
14 ut_a(cursor->path_arr);
16 @@ -3260,8 +3261,155 @@
18 slot = cursor->path_arr + (root_height - height);
20 + page = page_align(rec);
22 slot->nth_rec = page_rec_get_n_recs_before(rec);
23 - slot->n_recs = page_get_n_recs(page_align(rec));
24 + slot->n_recs = page_get_n_recs(page);
25 + slot->page_no = page_get_page_no(page);
26 + slot->page_level = btr_page_get_level_low(page);
29 +/*******************************************************************//**
30 +Estimate the number of rows between slot1 and slot2 for any level on a
31 +B-tree. This function starts from slot1->page and reads a few pages to
32 +the right, counting their records. If we reach slot2->page quickly then
33 +we know exactly how many records there are between slot1 and slot2 and
34 +we set is_n_rows_exact to TRUE. If we cannot reach slot2->page quickly
35 +then we calculate the average number of records in the pages scanned
36 +so far and assume that all pages that we did not scan up to slot2->page
37 +contain the same number of records, then we multiply that average to
38 +the number of pages between slot1->page and slot2->page (which is
39 +n_rows_on_prev_level). In this case we set is_n_rows_exact to FALSE.
40 +@return number of rows (exact or estimated) */
43 +btr_estimate_n_rows_in_range_on_level(
44 +/*==================================*/
45 + dict_index_t* index, /*!< in: index */
46 + btr_path_t* slot1, /*!< in: left border */
47 + btr_path_t* slot2, /*!< in: right border */
48 + ib_int64_t n_rows_on_prev_level, /*!< in: number of rows
49 + on the previous level for the
50 + same descend paths; used to
51 + determine the numbe of pages
53 + ibool* is_n_rows_exact) /*!< out: TRUE if the returned
54 + value is exact i.e. not an
64 + space = dict_index_get_space(index);
69 + /* Assume by default that we will scan all pages between
70 + slot1->page_no and slot2->page_no */
71 + *is_n_rows_exact = TRUE;
73 + /* add records from slot1->page_no which are to the right of
74 + the record which serves as a left border of the range, if any */
75 + if (slot1->nth_rec < slot1->n_recs) {
76 + n_rows += slot1->n_recs - slot1->nth_rec;
79 + /* add records from slot2->page_no which are to the left of
80 + the record which servers as a right border of the range, if any */
81 + if (slot2->nth_rec > 1) {
82 + n_rows += slot2->nth_rec - 1;
85 + /* count the records in the pages between slot1->page_no and
86 + slot2->page_no (non inclusive), if any */
88 + zip_size = fil_space_get_zip_size(space);
90 + /* Do not read more than this number of pages in order not to hurt
91 + performance with this code which is just an estimation. If we read
92 + this many pages before reaching slot2->page_no then we estimate the
93 + average from the pages scanned so far */
94 + #define N_PAGES_READ_LIMIT 10
96 + page_no = slot1->page_no;
97 + level = slot1->page_level;
102 + buf_block_t* block;
106 + /* fetch the page */
107 + block = buf_page_get(space, zip_size, page_no, RW_S_LATCH,
110 + page = buf_block_get_frame(block);
112 + /* It is possible that the tree has been reorganized in the
113 + meantime and this is a different page. If this happens the
114 + calculated estimate will be bogus, which is not fatal as
115 + this is only an estimate. We are sure that a page with
116 + page_no exists because InnoDB never frees pages, only
118 + if (fil_page_get_type(page) != FIL_PAGE_INDEX
119 + || ut_dulint_cmp(btr_page_get_index_id(page), index->id)
120 + || btr_page_get_level_low(page) != level) {
122 + /* The page got reused for something else */
128 + if (page_no != slot1->page_no) {
129 + /* Do not count the records on slot1->page_no,
130 + we already counted them before this loop. */
131 + n_rows += page_get_n_recs(page);
134 + page_no = btr_page_get_next(page, &mtr);
138 + if (n_pages_read == N_PAGES_READ_LIMIT
139 + || page_no == FIL_NULL) {
140 + /* Either we read too many pages or
141 + we reached the end of the level without passing
142 + through slot2->page_no, the tree must have changed
147 + } while (page_no != slot2->page_no);
153 + *is_n_rows_exact = FALSE;
155 + /* We did interrupt before reaching slot2->page */
157 + if (n_pages_read > 0) {
158 + /* The number of pages on this level is
159 + n_rows_on_prev_level, multiply it by the
160 + average number of recs per page so far */
161 + n_rows = n_rows_on_prev_level
162 + * n_rows / n_pages_read;
164 + /* The tree changed before we could even
165 + start with slot1->page_no */
172 /*******************************************************************//**
173 @@ -3286,6 +3434,7 @@
175 ulint divergence_level;
177 + ibool is_n_rows_exact;
181 @@ -3328,6 +3477,7 @@
182 /* We have the path information for the range in path1 and path2 */
185 + is_n_rows_exact = TRUE;
186 diverged = FALSE; /* This becomes true when the path is not
188 diverged_lot = FALSE; /* This becomes true when the paths are
189 @@ -3343,7 +3493,7 @@
190 if (slot1->nth_rec == ULINT_UNDEFINED
191 || slot2->nth_rec == ULINT_UNDEFINED) {
193 - if (i > divergence_level + 1) {
194 + if (i > divergence_level + 1 && !is_n_rows_exact) {
195 /* In trees whose height is > 1 our algorithm
196 tends to underestimate: multiply the estimate
198 @@ -3355,7 +3505,9 @@
199 to over 1 / 2 of the estimated rows in the whole
202 - if (n_rows > index->table->stat_n_rows / 2) {
203 + if (n_rows > index->table->stat_n_rows / 2
204 + && !is_n_rows_exact) {
206 n_rows = index->table->stat_n_rows / 2;
208 /* If there are just 0 or 1 rows in the table,
209 @@ -3381,10 +3533,15 @@
210 divergence_level = i;
213 - /* Maybe the tree has changed between
217 + /* It is possible that
218 + slot1->nth_rec >= slot2->nth_rec
219 + if, for example, we have a single page
220 + tree which contains (inf, 5, 6, supr)
221 + and we select where x > 20 and x < 30;
222 + in this case slot1->nth_rec will point
223 + to the supr record and slot2->nth_rec
228 } else if (diverged && !diverged_lot) {
229 @@ -3408,8 +3565,9 @@
231 } else if (diverged_lot) {
233 - n_rows = (n_rows * (slot1->n_recs + slot2->n_recs))
235 + n_rows = btr_estimate_n_rows_in_range_on_level(
236 + index, slot1, slot2, n_rows,
241 --- a/storage/innodb_plugin/include/btr0cur.h
242 +++ b/storage/innodb_plugin/include/btr0cur.h
244 order); value ULINT_UNDEFINED
246 ulint n_recs; /*!< number of records on the page */
247 + ulint page_no; /*!< no of the page containing the record */
248 + ulint page_level; /*!< level of the page, if later we fetch
249 + the page under page_no and it is no different
250 + level then we know that the tree has been
254 #define BTR_PATH_ARRAY_N_SLOTS 250 /*!< size of path array (in slots) */
255 --- a/mysql-test/suite/innodb_plugin/r/innodb_gis.result
256 +++ b/mysql-test/suite/innodb_plugin/r/innodb_gis.result
259 SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
260 id select_type table type possible_keys key key_len ref rows Extra
261 -1 SIMPLE t2 ref p p 28 const 1 Using where
262 +1 SIMPLE t2 ref p p 28 const 2 Using where
263 SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
266 --- a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result
267 +++ b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result
268 @@ -889,13 +889,13 @@
281 -Extra Using where; Using index; Using filesort
284 SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
287 @@ -934,12 +934,12 @@
299 Extra Using where; Using index
300 SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
308 Extra Using where; Using index; Using filesort
309 SELECT * FROM t2 WHERE b=1 ORDER BY a;
311 @@ -1018,7 +1018,7 @@
317 Extra Using where; Using index
318 SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
320 @@ -1047,7 +1047,7 @@
326 Extra Using where; Using index
327 SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
329 @@ -1076,7 +1076,7 @@
335 Extra Using where; Using index
336 SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
338 @@ -1211,7 +1211,7 @@
344 Extra Using where; Using index
345 SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
347 @@ -1226,7 +1226,7 @@
353 Extra Using where; Using index
354 SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
356 @@ -1370,7 +1370,7 @@
357 INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1;
358 EXPLAIN SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5;
359 id select_type table type possible_keys key key_len ref rows Extra
360 -1 SIMPLE t1 index t1_b PRIMARY 4 NULL 8 Using where
361 +1 SIMPLE t1 range t1_b t1_b 5 NULL 8 Using where
362 SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5;
365 @@ -1729,7 +1729,7 @@
366 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
367 id select_type table type possible_keys key key_len ref rows Extra
368 1 PRIMARY <derived2> system NULL NULL NULL NULL 1
369 -2 DERIVED t1 index c3,c2 c2 10 NULL 5
370 +2 DERIVED t1 ALL c3,c2 c3 5 5 Using filesort
372 CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3))
374 @@ -1743,7 +1743,7 @@
375 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
376 id select_type table type possible_keys key key_len ref rows Extra
377 1 PRIMARY <derived2> system NULL NULL NULL NULL 1
378 -2 DERIVED t1 index c3,c2 c2 18 NULL 5
379 +2 DERIVED t1 ALL c3,c2 c3 9 5 Using filesort
381 CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2),
382 KEY (c3), KEY (c2, c3))
383 @@ -1758,7 +1758,7 @@
384 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
385 id select_type table type possible_keys key key_len ref rows Extra
386 1 PRIMARY <derived2> system NULL NULL NULL NULL 1
387 -2 DERIVED t1 index c3,c2 c2 14 NULL 5
388 +2 DERIVED t1 ALL c3,c2 c3 7 5 Using filesort
391 drop table if exists t1, t2, t3;
392 @@ -1834,7 +1834,7 @@
398 Extra Using where; Using index
399 EXPLAIN SELECT c FROM bar WHERE c>2;;
401 @@ -2430,7 +2430,7 @@
402 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
403 id select_type table type possible_keys key key_len ref rows Extra
404 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
405 -2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 3537 Using sort_union(idx,PRIMARY); Using where
406 +2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 1536 Using sort_union(idx,PRIMARY); Using where
408 (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
409 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
410 --- a/mysql-test/r/index_merge_innodb.result
411 +++ b/mysql-test/r/index_merge_innodb.result
414 WHERE c = 1 AND b = 1 AND d = 1;
415 id select_type table type possible_keys key key_len ref rows Extra
416 -1 SIMPLE t1 index_merge c,bd c,bd 5,10 NULL 1 Using intersect(c,bd); Using where; Using index
417 +1 SIMPLE t1 ref c,bd bd 10 const,const 2 Using where
418 CREATE TABLE t2 ( a INT )
421 --- a/mysql-test/r/rowid_order_innodb.result
422 +++ b/mysql-test/r/rowid_order_innodb.result
425 explain select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
426 id select_type table type possible_keys key key_len ref rows Extra
427 -1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 4 Using sort_union(key1,key2); Using where
428 +1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 5 Using sort_union(key1,key2); Using where
429 select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
432 --- a/mysql-test/r/type_bit_innodb.result
433 +++ b/mysql-test/r/type_bit_innodb.result
436 explain select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
437 id select_type table type possible_keys key key_len ref rows Extra
438 -1 SIMPLE t1 range a a 2 NULL 19 Using where; Using index; Using filesort
439 +1 SIMPLE t1 range a a 2 NULL 27 Using where; Using index; Using filesort
440 select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
443 --- a/mysql-test/r/endspace.result
444 +++ b/mysql-test/r/endspace.result
445 @@ -201,12 +201,12 @@
449 -select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%';
450 +select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%' order by 1, 2;
455 -select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t';
456 +select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t' order by 1, 2;
460 --- a/mysql-test/t/endspace.test
461 +++ b/mysql-test/t/endspace.test
463 select * from t1 where text1 like 'teststring_%';
465 # The following gives wrong result in InnoDB
466 -select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%';
467 -select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t';
468 +select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%' order by 1, 2;
469 +select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t' order by 1, 2;
470 select concat('|', text1, '|') from t1 order by text1;