Bug #38201 Wrong result when left join + subquery + semijoin
Submitted: 17 Jul 2008 15:36 Modified: 8 Dec 2009 21:06
Reporter: Philip Stoev Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:6.0 OS:Any
Assigned to: Øystein Grøvlen CPU Architecture:Any
Tags: optimizer_switch, outerjoin, semijoin, subquery

[17 Jul 2008 15:36] Philip Stoev 
Description:
This query 

SELECT OUTR . `varchar_nokey`
FROM C AS OUTR2
LEFT JOIN B AS OUTR
ON ( OUTR2 . `varchar_nokey` < OUTR . `varchar_nokey` )
WHERE OUTR . `varchar_nokey` IN (
 SELECT INNR . `varchar_key` AS Y FROM CC AS INNR
 WHERE INNR . `pk` >= 6
);

returns a partial result when executed with semijoin.

How to repeat:
A test case will be uploaded shortly.
[17 Jul 2008 15:42] Philip Stoev 
Test case for bug 38201

Attachment: bug38201.test (application/octet-stream, text), 6.57 KiB.
[17 Jul 2008 15:43] Philip Stoev 
A test case has been uploaded for this bug. The test case uses Falcon because the query execution plan for Innodb is different and this bug does not show up.
[17 Jul 2008 18:22] MySQL Verification Team 
Thank you for the bug report. Released version 6.0.4 not presents this bug.
[8 Dec 2009 21:06] Øystein Grøvlen 
This was fixed long time ago by the following patch:
Revision: 
2630.35.2 revid:sergefp@mysql.com-20080726193713-c1erp8endm8gchen

Check-in comment:

WL#3985 Subquery optimization: smart choice between semi-join and materialization, patch 2
- Review1 changes: removed join optimization forking, switched to "late subquery strategy detection"
- Caught and fixed several bugs
- Code cleanup