[wp-hackers] group by in SQL queries

[Peter Westwood]

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  
>> 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
>

If you read this you can see at the bottom in links to [2] 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!

westi
-- 
Peter Westwood
http://blog.ftwr.co.uk | http://westi.wordpress.com
  C53C F8FC 8796 8508 88D6 C950 54F4 5DCD A834 01C5