Bug #6396 | Left Join does not return all rows | ||
---|---|---|---|
Submitted: | 3 Nov 2004 8:09 | Modified: | 3 Nov 2004 17:17 |
Reporter: | Sergei Kulakov (Candidate Quality Contributor) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: MyISAM storage engine | Severity: | S3 (Non-critical) |
Version: | 3.23.58, 4.0.15-nt | OS: | Unix (3.23.58), Windows (both) |
Assigned to: | CPU Architecture: | Any |
[3 Nov 2004 8:09]
Sergei Kulakov
[3 Nov 2004 17:17]
MySQL Verification Team
Thank you for the bug report. I was able to repeat with version 4.0.15 however with 4.0.22 this issue was already fixed: mysql> Select Ratings.Id, DT, AreaId, Rate, RatingsNew.Id as New -> From Ratings -> Left Join RatingsNew Using(Id) -> Left Join Rates On (Rates.RatingId=Ratings.Id And RatingsNew.Id Is Null); +----+---------------------+--------+------+------+ | Id | DT | AreaId | Rate | New | +----+---------------------+--------+------+------+ | 1 | 2004-11-03 15:13:48 | 1 | 5 | NULL | | 1 | 2004-11-03 15:13:48 | 2 | 6 | NULL | | 2 | 2004-11-03 15:13:48 | 1 | 4 | NULL | | 2 | 2004-11-03 15:13:48 | 2 | 5 | NULL | | 3 | 2004-11-03 15:13:48 | 3 | 6 | NULL | +----+---------------------+--------+------+------+ 5 rows in set (0.01 sec) mysql> select version(); +---------------+ | version() | +---------------+ | 4.0.15-nt-log | +---------------+ 1 row in set (0.01 sec) mysql> Select Ratings.Id, DT, AreaId, Rate, RatingsNew.Id as New -> From Ratings -> Left Join RatingsNew Using(Id) -> Left Join Rates On (Rates.RatingId=Ratings.Id And RatingsNew.Id Is Null); +----+---------------------+--------+------+------+ | Id | DT | AreaId | Rate | New | +----+---------------------+--------+------+------+ | 1 | 2004-11-03 15:00:15 | 1 | 5 | NULL | | 1 | 2004-11-03 15:00:15 | 2 | 6 | NULL | | 2 | 2004-11-03 15:00:15 | 1 | 4 | NULL | | 2 | 2004-11-03 15:00:15 | 2 | 5 | NULL | | 3 | 2004-11-03 15:00:15 | 3 | 6 | NULL | | 4 | 2004-11-03 15:00:15 | NULL | NULL | 4 | | 5 | 2004-11-03 15:00:15 | NULL | NULL | 5 | +----+---------------------+--------+------+------+ 7 rows in set (0.00 sec) mysql> select version(); +---------------+ | version() | +---------------+ | 4.0.22-nt-log | +---------------+ 1 row in set (0.00 sec)