Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 12.1
Description
ref_or_null optimizer in merge_key_fields() depends on the order in which conditions are specified in the WHERE. The "IS NULL" must be second. If it comes first, ref_or_null is not constructed.
Testcase:
create table t1 (a int); |
insert into t1 values (1),(2),(3),(4),(5); |
create table t2 (a int, index(a)); |
insert into t2 select A.a + 5 * B.a + 25 * C.a from t1 A, t1 B, t1 C; |
Using equality OR is-null constructs ref_or_null:
explain select * from t1, t2 where t2.a=t1.a or t2.a is null;
|
+------+-------------+-------+-------------+---------------+------+---------+---------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------------+---------------+------+---------+---------+------+--------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 5 | |
|
| 1 | SIMPLE | t2 | ref_or_null | a | a | 5 | j1.t1.a | 2 | Using where; Using index |
|
+------+-------------+-------+-------------+---------------+------+---------+---------+------+--------------------------+
|
But if IS NULL comes first, we get a cross-join:
explain select * from t1, t2 where t2.a is null or t2.a=t1.a;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 5 | |
|
| 1 | SIMPLE | t2 | index | a | a | 5 | NULL | 125 | Using where; Using index; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------------------+
|
It's the same in MySQL.
Attachments
Issue Links
- relates to
-
-
- Needs Feedback
-