MySQL Bugs: #6071: ORDER BY does not work correctly when combined with GROUP BY and LEFT JOIN

Bug #6071	ORDER BY does not work correctly when combined with GROUP BY and LEFT JOIN
Submitted:	13 Oct 2004 18:43	Modified:	13 Oct 2004 20:08
Reporter:	Glen Gibb	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.21	OS:	Linux (Fedora Core 1)
Assigned to:	Matthew Lord	CPU Architecture:	Any

Description:
The ordering is wrong when using ORDER BY with a GROUP BY when doing a LEFT JOIN.

If the LEFT JOIN is converted to a JOIN or the GROUP BY is dropped then the order of results is correct.

How to repeat:
Create a new database and populate it with the following data:

CREATE TABLE tt_news (
  uid int(11) unsigned NOT NULL auto_increment,
  title tinytext NOT NULL,
  KEY uid (uid)
) TYPE=MyISAM;

INSERT INTO tt_news VALUES (1,'News Article 1');
INSERT INTO tt_news VALUES (2,'News Article 2');
INSERT INTO tt_news VALUES (3,'News Article 3');

CREATE TABLE tt_news_cat (
  uid int(11) unsigned NOT NULL auto_increment,
  priority tinyint(4) unsigned NOT NULL default '1',
  name tinytext NOT NULL,
  KEY uid (uid)
) TYPE=MyISAM;

INSERT INTO tt_news_cat VALUES (1,1,'Headline');
INSERT INTO tt_news_cat VALUES (2, 3,'Seminar');
INSERT INTO tt_news_cat VALUES (3,10,'Partner News');

CREATE TABLE tt_news_cat_mm (
  uid_local int(11) unsigned NOT NULL default '0',
  uid_foreign int(11) unsigned NOT NULL default '0',
  KEY uid_local (uid_local),
  KEY uid_foreign (uid_foreign)
) TYPE=MyISAM;

INSERT INTO tt_news_cat_mm VALUES (1,4);
INSERT INTO tt_news_cat_mm VALUES (2,1);
INSERT INTO tt_news_cat_mm VALUES (2,3);
INSERT INTO tt_news_cat_mm VALUES (3,3);
INSERT INTO tt_news_cat_mm VALUES (3,1);

Execute the following query (which uses JOIN):

SELECT tt_news.uid, MIN(priority) FROM tt_news JOIN tt_news_cat_mm ON tt_news.uid = tt_news_cat_mm.uid_local JOIN tt_news_cat ON tt_news_cat_mm.uid_foreign = tt_news_cat.uid GROUP BY tt_news.uid ORDER BY priority;

The result returned is correct and is:
+-----+---------------+
| uid | MIN(priority) |
+-----+---------------+
|   2 |             1 |
|   3 |             1 |
|   1 |             3 |
+-----+---------------+

Now try executing the following query which uses LEFT JOIN instead of JOIN, but is otherwise identical to the previous query:

SELECT tt_news.uid, MIN(priority) FROM tt_news LEFT JOIN tt_news_cat_mm ON tt_news.uid = tt_news_cat_mm.uid_local LEFT JOIN tt_news_cat ON tt_news_cat_mm.uid_foreign = tt_news_cat.uid GROUP BY tt_news.uid ORDER BY priority;

The result set returned is:
+-----+---------------+
| uid | MIN(priority) |
+-----+---------------+
|   2 |             1 |
|   1 |             3 |
|   3 |             1 |
+-----+---------------+

This result set contains the correct results but the order is wrong. It should be sorted by the priority.

Hi,

Thank you for your bug report!

You are ordering by a value which you do not see in the results.  Do you mean to be using 
order by 2 or alternately aliasing the min(priority) as minprioirty and order by minpriority?

Can you see the incorrect results this way?

Here is what I get when cutting and pasting your case:

+-----+---------------+
| uid | MIN(priority) |
+-----+---------------+
|   2 |             1 |
|   3 |             1 |
+-----+---------------+
2 rows in set (0.03 sec)

and

+-----+---------------+
| uid | MIN(priority) |
+-----+---------------+
|   1 |          NULL |
|   2 |             1 |
|   3 |             1 |
+-----+---------------+
3 rows in set (0.00 sec)

Best Regards

Sorry to the testers.

One of the lines I copied into the section to set up the database was wrong. Please replace this line:

INSERT INTO tt_news_cat_mm VALUES (1,4);

with this line:

INSERT INTO tt_news_cat_mm VALUES (1,2);

Note the 2nd field should read 2 not 4.

I'll also attach the SQL create statements which I have stored in a file (which will hopefully be error free ;) )

SQL statements to create tables and populate with data

Attachment: jointest2.sql (application/octet-stream, text), 1.44 KiB.

Hi,

This is as I expected.  You got fortunate in the first query that it was sorted as you wanted.
When you do a group by and order by a column that is not part of the aggregate results, you are
only guaranteed a value from one of the rows in that group.  This holds true if you select a 
column as well, as in select uid, priority, min(priority).... the priority in this case is only 
guaranteed to be a value for that column for one of the records in the group.

You should alter your query like such:

SELECT tt_news.uid, MIN(priority) as minpriority FROM tt_news LEFT JOIN tt_news_cat_mm ON 
tt_news.uid = tt_news_cat_mm.uid_local LEFT JOIN tt_news_cat ON tt_news_cat_mm.uid_foreign 
= tt_news_cat.uid GROUP BY tt_news.uid ORDER BY minpriority;

Best Regards