[wp-hackers] group by in SQL queries

[Johannes Ruthenberg]

Hi Otto!

Otto schrieb am 14.01.2008 14:23:
> I don't get it. If the order is explicitly defined as ORDER BY
> post_date DESC, why would they come back in a different order? How
> they are grouped shouldn't make any difference.
> 
> If this behavior is actually occurring due to this, then it seems that
> it should be reported as a bug in MySQL.

As far as I have understood it (and I'm by no means an SQL guru, so I 
could be wrong here), the GROUP BY has preference over the ORDER BY. 
Once the result is grouped, it would be ordered within each group. Now 
the ID column is an auto-increment column, so each group consists of 
only one post and therefore it doesn't get ordered.

Previously, MySQL apparently knew that a GROUP BY on an auto-increment 
column doesn't make sense and optimized it away. With the new MySQL 
version this doesn't happen anymore. I don't know if this a MySQL bug, 
but even if it is it has an effect on WordPress that's rather severe. 
And we can't tell the usual user to upgrade his MySQL, because that's 
usually done by the provider in rather larger intervals.

After having a quick look at the change log[1], I think it's this change 
that causes this effect:
http://bugs.mysql.com/bug.php?id=30596

Greetings,
Johannes


[1] http://dev.mysql.com/doc/refman/5.0/en/releasenotes-cs-5-0-51.html


-- 


"Life brings hope and pain, but revenge never brings redemption."
[Highlander: Endgame]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://johannes-ruthenberg.de
http://www.ammaletu.de
http://www.bolarus.de
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~