Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 5.5.35, 10.0.7
-
Fix Version/s: 10.0.8
-
Component/s: None
-
Labels:None
Description
Query plans produced by range optimizer depend on whether the condition has form "X < Y", or "Y >X".
Example:
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table one_k(a int);
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
alter table one_k add key(a);
explain select * from ten, one_k where one_k.a < ten.a; +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+ | 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | one_k | ALL | a | NULL | NULL | NULL | 1148 | Range checked for each record (index map: 0x1) | +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
now, the same query with changed WHERE:
mysql> explain select * from ten, one_k where ten.a > one_k.a; +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------------------+ | 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | one_k | index | a | a | 5 | NULL | 1148 | Using where; Using index; Using join buffer (flat, BNL join) | +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------------------+
The problem can be only observed if there is a table field on the other side of the comparison. Besides "range checked for each record", the optimizer may miss regular plans:
create table t2 (a int primary key, b int);
insert into t2 select a,a from ten;
mysql> explain select * from t2, one_k where one_k.a < t2.b and t2.a=1; +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+ | 1 | SIMPLE | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | one_k | range | a | a | 5 | NULL | 1 | Using where; Using index | +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
mysql> explain select * from t2, one_k where t2.b > one_k.a and t2.a=1; +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+ | 1 | SIMPLE | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | one_k | index | a | a | 5 | NULL | 1148 | Using where; Using index | +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The reason it's not repeatable on MySQL 5.6 is this fix:
jorgen.loland@oracle.com-20120314131055-ml54x9deueqfsff4
BUG#13701206: WHERE A>=B DOES NOT GIVE SAME EXECUTION PLAN
AS WHERE B<=A (RANGE OPTIMIZER)
The bug doesn't have its own testcase (although it changes the results for some other test cases).
I don't think I 100% agree with the patch. I think a more straightforward fix is possible.
Repeatable on MariaDB 5.5, MariaDB 10.0.
Repeatable on MySQL 5.5, not repeatable on MySQL 5.6.