"Range checked for each record" is not used if condition refers to outside of subquery

Bug #716293 reported by Sergey Petrunia
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
Medium
Sergey Petrunia

Bug Description

"Range checked for each record" optimization is not used if condition refers to outside of the subquery.

create table ten (a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1big (a int, b int, `filler` char(200), key(a), key (b));
insert into t1big select A.a + 10*B.a + 100 * C.a, A.a + 10*B.a + 100 * C.a, 'filler' from ten A, ten B, ten C;

MariaDB [test]> explain select a, (select sum(X.a+B.b) from ten X, t1big B where B.a=A.a or B.b=A.a) from ten A;
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| 1 | PRIMARY | A | ALL | NULL | NULL | NULL | NULL | 10 | |
| 2 | DEPENDENT SUBQUERY | X | ALL | NULL | NULL | NULL | NULL | 10 | |
| 2 | DEPENDENT SUBQUERY | B | ALL | a,b | NULL | NULL | NULL | 1000 | Using where; Using join buffer (flat, BNL join) |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+

The above query could have been much more efficient if access to table B was done with "Range checked for each record" over the condition of "B.a=A.a or B.b=A.a" (every time one would be able to find an index_merge plan that only scans two records).

The problem can be observed on MariaDB 5.x as well as MySQL 5.x

Changed in maria:
assignee: nobody → Sergey Petrunia (sergefp)
importance: Undecided → Medium
status: New → Fix Committed
milestone: none → 5.3
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.