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

[30 Apr 2008 4: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 23: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.