[wp-hackers] group by in SQL queries

[Otto]

Ahh, yes. I see it now.

http://bugs.mysql.com/bug.php?id=32202

The bug is fixed in 5.0.52, according to that. I say that people who
are experiencing the problem need to tell their hosts to upgrade, as
this is surely the cause of the recent weirdness we've seen with
reversed post orders and such.

-Otto




On 1/14/08, Peter Westwood <peter.westwood at ftwr.co.uk> wrote:
>
> 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
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>