MySQL Bugs: #6396: Left Join does not return all rows

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:	None
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

Description:
I got a query in which a condition in its Left Join prevents certain rows from appearing in the result set, like if it were placed in Where clause. I know how to get the correct result in 2 ways but I do inform you of this as it's not the way it should be. I searched for similar bugs and found some but I think they are different. 

How to repeat:
I got the following tables:

The table Ratings stores hotel ratings data:

CREATE TABLE `Ratings` (
  `Id` smallint(5) unsigned NOT NULL auto_increment,
  `DT` datetime NOT NULL default '0000-00-00 00:00:00',
  `HotelId` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Id`),
  KEY `HotelId` (`HotelId`)
) TYPE=MyISAM

The table RatingsNew stores new ratings' (not verified yet) ids:

CREATE TABLE `RatingsNew` (
  `Id` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Id`)
) TYPE=MyISAM

The table Rates stores rates (1-10 in 10 areas) for each rating:

CREATE TABLE `Rates` (
  `RatingId` smallint(5) unsigned NOT NULL default '0',
  `AreaId` tinyint(3) unsigned NOT NULL default '0',
  `Rate` tinyint(3) unsigned NOT NULL default '0',
  UNIQUE KEY `RatingId` (`RatingId`,`AreaId`)
) TYPE=MyISAM

Fill the tables with sample data:

Insert Into Ratings Values
		(Null, Now(), 1), (Null, Now(), 2), (Null, Now(), 3),
		(Null, Now(), 3), (Null, Now(), 4)

Insert Into RatingsNew Values (4), (5)

Insert Into Rates Values
		(1, 1, 5), (1, 2, 6), (2, 1, 4), (2, 2, 5),
		(3, 3, 6), (4, 4, 5), (5, 1, 1)

So, we have 5 ratings, of which 2 are new. I want to get a list of all ratings and show rates for all but new ones (never mind why, it's just an example!), so I use:

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)

But the query doesn't output new ratings at all. If I remove "And RatingsNew.Id Is Null" it outputs all ratings and rates:

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)

So, the condition  "And RatingsNew.Id Is Null" in the left join prevents certain rows from appearing in the result set, while it shouldn't. It works as if it were placed in the where clause. I know how to get the correct result in 2 ways. One is change the field Rate into:

If(RatingsNew.Id Is Null, Rate, Null) as Rate

and the other is to change "And" into "if" in the left join:

Left Join Rates On (Rates.RatingId=If(RatingsNew.Id Is Null, Ratings.Id, Null))

Suggested fix:
I don't know

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)