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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 6.0.x
• OS: Any
• CPU Architecture: Any
• Tags: subqueries, subquery

[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.