Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
The following shows a performance optimization that we can make for NATURAL FULL JOIN.
explain extended
|
select * from t1 natural full join t2 where
|
t1.a is not null and t1.b is not null and
|
t2.a is not null and t2.b is not null;
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
|
Warnings:
|
Note 1003 select coalesce(`test`.`t1`.`a`,`test`.`t2`.`a`) AS `a`,coalesce(`test`.`t1`.`b`,`test`.`t2`.`b`) AS `b` from `test`.`t2` join `test`.`t1` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t2`.`b` = `test`.`t1`.`b` and `test`.`t1`.`a` is not null and `test`.`t1`.`b` is not null and `test`.`t1`.`a` is not null and `test`.`t1`.`b` is not null
|
As t1.a cannot be null, we don't need COALESCE here. However we cannot easy find that out at the point where (new) Item_func_coalesce is called as we have not yet evaluated the WHERE clause. table->maybe_null is reset in simplify_joins. The question is if we call setup_natural_join_row_types() too early. Why not call this after simplify_join()?
Attachments
Issue Links
- is caused by
-
-
- In Review
-