MySQL Bugs: #44879: bug found in left join example

Bug #44879	bug found in left join example
Submitted:	14 May 2009 16:01	Modified:	17 May 2009 2:19
Reporter:	Jan Engels	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S5 (Performance)
Version:		OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
found a bug in the documentation page:
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

Shouldn't the LEFT JOIN rather be like follows ?

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;

notice that i changed the join condition from:
ON s1.article = s2.article AND s1.price < s2.price
to only:
ON s1.price < s2.price

Cheers,
Jan

How to repeat:
check description please.

Query in manual:

mysql> SELECT s1.article, s1.dealer, s1.price
    -> FROM shop s1
    -> LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
    -> WHERE s2.article IS NULL;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 | 
|    0002 | A      | 10.99 | 
|    0003 | C      |  1.69 | 
|    0004 | D      | 19.95 | 
+---------+--------+-------+
4 rows in set (0.00 sec)

Proposed correction:

mysql> SELECT s1.article, s1.dealer, s1.price
    -> FROM shop s1
    -> LEFT JOIN shop s2 ON s1.price < s2.price
    -> WHERE s2.article IS NULL;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 | 
+---------+--------+-------+
1 row in set (0.00 sec)

The query in the manual is correct. (Produces same result as other equivalent queries in the section.)

oops, sorry. i didn't read carefully enough. i thought the examples were to get rows holding the maximum of a certain column. after posting the bug i discovered the appropriate page:

3.6.2. The Row Holding the Maximum of a Certain Column
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-row.html

though in this page i didn't find the left join i was searching for. i think it should be there (since the other page also contains a left join equivalent):

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Thanks, I've added your example.