Bug #36422 EXPLAIN EXTENDED; SHOW WARNINGS doesn't have ON expression for semi-joins
Submitted: 30 Apr 2008 2:19 Modified: 30 Apr 2008 2:20
Reporter: Sergey Petrunya Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0.x OS:Any
Assigned to:
Tags: subqueries, subquery
Triage: Triaged: D3 (Medium)

[30 Apr 2008 2:19] Sergey Petrunya
Description:
For semi-join subqueries, EXPLAIN EXTENDED ... ; SHOW WARNINGS output contains lines like

... outer_table semi join (inner_table) ... 

that is, ON expression for semi-joins is not printed. This is confusing because semi-join operation only makes sense when there is an ON expression.

How to repeat:
Run this:

create table t1(a int); 
insert into t1 values (1),(2);
create table t2 as select * from t1;
explain extended select * from t1 where a in (select a from t2);
show warnings\G

And see:

*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test2`.`t1`.`a` AS `a` from `test2`.`t1` semi join (`test2`.`t2`) where (`test2`.`t2`.`a` = `test2`.`t1`.`a`)
1 row in set (0.08 sec)

... from `test2`.`t1` semi join (`test2`.`t2`) where ... - semi join is listed without the ON expression.

Suggested fix:
Print semi-join's ON expression (like we do it for outer joins).
[5 Jun 2008 21:38] Sergey Petrunya
Sergey Petrunia wrote:
> ON expression for semi-joins is not printed. This is confusing because
> semi-join operation only makes sense when there is an ON expression.

On a second thought, semi join *does* make sense when there is no ON expression but there is WHERE clause (which is always the case). The semantics are clear - it's like join but without any record combinations that differ in inner tables only.

Keeping the bug verified until we make up our mind.