Bug #80022 LEFT JOIN with Null test NOW Fails
Submitted: 18 Jan 2016 3:14 Modified: 18 Jan 2016 4:41
Reporter: Michael Lewis Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DML Severity:S5 (Performance)
Version:5.6.21 OS:Windows (Microsoft Windows 7 Home Premium)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[18 Jan 2016 3:14] Michael Lewis
Description:
----[For better reports, please attach the log file after submitting. You can find it in C:\Users\Michael\AppData\Roaming\MySQL\Workbench\log\wb.log]

SELECT TempIndex.ID, 'z' AS H, TempIndex.BLU, TempIndex.PersonID, TempIndex.Surname, TempIndex.GivenName, TempIndex.Title
FROM TempIndex LEFT JOIN TempHeader ON TempIndex.ID = TempHeader.id
WHERE (((TempHeader.id) Is Null));

There are over 920,000 records in TempIndex and 500,000+ in Tempheader.
This process (plus an Insert into a 3rd Table) has executed virtually straight away previously. It is selecting those records in TempIndex which are NOT in TempHeader. (The fields in both tables are the same).

It now "hangs" - it MAY complete in several hours. 

How to repeat:
Create 2 tables with same set of fields - add the same set of data - but with the TempHeader table having less records. Left Join on the ID (long integer) field and search for those records which are in TempIndex but not in TempHeader - where the TempHeader ID is null.
[18 Jan 2016 4:41] MySQL Verification Team
Hello Michael Lewis,

Thank you for the report.
Please could you try with 5.6.28/5.7.10 as 5.6.21 is very old and many bugs are fixed since then? If you are still having issue with 5.6.28/5.7.10 then report us back with exact conf file used, test case to reproduce.

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Umesh
[18 Jan 2016 4:45] MySQL Verification Team
If this behavior is only seen with WB(lets say no issues with mysql CLI), then probably you are hitting this issue Bug #79840