[wp-hackers] group by in SQL queries

[Otto]

It's still a MySQL bug, and it violates the SQL92 specification. If
you specify an ORDER BY clause, then the order of the rows in the
result set should be ordered that way. How you group them is
irrelevant, as grouping is a separate operation. You're doing one
select here, the order by must apply to the results of that select.

Not saying we don't need to work around it in WordPress, but if MySQL
is this badly broken, then I will certainly be rethinking the idea of
using it for any production level systems in the future.

-Otto



On 1/14/08, Johannes Ruthenberg <johannes at bolarus.de> 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
>
>
> --
>
>
> "Life brings hope and pain, but revenge never brings redemption."
> [Highlander: Endgame]
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> http://johannes-ruthenberg.de
> http://www.ammaletu.de
> http://www.bolarus.de
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>