[wp-hackers] group by in SQL queries
peter.westwood at ftwr.co.uk
Mon Jan 14 18:05:00 GMT 2008
On 14 Jan 2008, at 15:07, Johannes Ruthenberg wrote:
> 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
>> 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
> After having a quick look at the change log, I think it's this
> change that causes this effect:
>  http://dev.mysql.com/doc/refman/5.0/en/releasenotes-cs-5-0-51.html
If you read this you can see at the bottom in links to  where a
fix to this issue was introduced for mysql 5.0.52.
So we shouldn't need to do anything - this is a straight mysql bug!
http://blog.ftwr.co.uk | http://westi.wordpress.com
C53C F8FC 8796 8508 88D6 C950 54F4 5DCD A834 01C5
More information about the wp-hackers