Bug #23124 Severe re-ordering when using DISTINCT, if(...), and ORDER BY
Submitted: 10 Oct 2006 8:24 Modified: 10 Oct 2006 13:25
Reporter: Robin Powell Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.24a OS:Linux (Debian)
Assigned to: CPU Architecture:Any

[10 Oct 2006 8:24] Robin Powell
Description:
mysql> select if(parentid=0, threadid, parentid) as id from tiki_comments order by commentdate desc;
+------+
| id   |
+------+
| 5650 |
| 5650 |
| 5746 |
| 5746 |
| 5746 |
| 5746 |
| 5746 |
[snip]

BUT:

mysql> select distinct if(parentid=0, threadid, parentid) as id from tiki_comments order by commentdate desc;
+------+
| id   |
+------+
| 6021 |
| 6007 |
| 5986 |
| 5965 |
| 5963 |

How to repeat:
Grab http://teddyb.org/~rlpowell/media/regular/tiki_comments.dump.gz

gunzip tiki_comments.dump.gz

mysql test </tmp/tiki_comments.dump

mysql test

Run the commands above.
[10 Oct 2006 8:53] Robin Powell
Note that it seems to work without the "desc" just fine.  I therefore suggest that the problem is that somehow the "distinct" is being applied with the ordering being ascending, and then the ordering is reversed.  Preliminary checking seems to bear this out: 6021 has only repeat lines below it when ordered asc, so if distinct was done that way and the results flipped, 6021 would be at the top.

-Robin
[10 Oct 2006 10:33] Robin Powell
Same behaviour with "group by", btw:

mysql> select if(parentid=0, threadid, parentid) as id from tiki_comments group by id order by commentdate desc;
+------+
| id   |
+------+
| 6021 |
| 6007 |
| 5986 |
| 5965 |
| 5963 |
[10 Oct 2006 11:17] Valeriy Kravchuk
Sorry, but you are ordering by column that is not selected (with distinct or without it):
 
select distinct if(parentid=0, threadid, parentid) as id from
tiki_comments order by commentdate desc;

So, why do you expect any specific order on "id" column (with distinct or without) if you are ordering explicitely by other column, commentdate? Please, explain.
[10 Oct 2006 11:37] Robin Powell
I need the list of threadids (sort of) in order of latest post (this is a web forum).  This doesn't seem like too much to ask for, and the version without "distinct" does exactly what I expect.  If you think I'm doing something deeply wrong, can you point me to documentation to that effect?  Since the version without distinct works perfectly, though, I don't think there's a problem with the statement itself.

-Robin
[10 Oct 2006 11:44] Robin Powell
Here's the same problem without the missing column, or even the distinct:

mysql> select if(parentid=0, threadid, parentid) as id, commentdate from tiki_comments order by commentdate desc limit 5;           +------+-------------+
| id   | commentdate |
+------+-------------+
| 5650 |  1158613767 |
| 5650 |  1158611967 |
| 5746 |  1158356290 |
| 5746 |  1158335893 |
| 5746 |  1158159147 |
+------+-------------+
5 rows in set (0.02 sec)

mysql> select if(parentid=0, threadid, parentid) as id, commentdate from tiki_comments group by id order by commentdate desc limit 5;
+------+-------------+
| id   | commentdate |
+------+-------------+
| 6021 |  1156823024 |
| 6007 |  1156203544 |
| 5986 |  1155788188 |
| 5965 |  1155247714 |
| 5963 |  1155246813 |
+------+-------------+
5 rows in set (0.00 sec)

The same thing holds without the limits: the order of results returned is *dramatically* different in the two cases.

I'm totally willing to accept the fact that I'm doing something wrong, but I don't know what it is.  :-(

-Robin
[10 Oct 2006 11:47] Robin Powell
The following query seems to do what I want:

select if(parentid=0, threadid, parentid) as id from tiki_comments group by id order by max(commentdate) desc;

Whereas this doesn't:

select if(parentid=0, threadid, parentid) as id from tiki_comments group by id order by commentdate desc;

I have no idea why that max(...) should be necessary, but if it's not a bug, I'd love to see some docs explaining it!

-Robin
[10 Oct 2006 13:25] Valeriy Kravchuk
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

For your last queries with "group by id", please, read http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html.