[wp-hackers] group by in SQL queries
johannes at bolarus.de
Mon Jan 14 09:53:04 GMT 2008
Just wanted to share something strange that caused some confusion among
German WordPress users lately. I don't know if this is already known and
if anything needs to be done about it (I usually don't read SQL specific
mails on this list too carefully). Couldn't find something in Trac, but
I thought a lot of you would know if this needs to be fixed.
For all German speaking people, here is a detailed report on this:
For everyone else I'll try to summarize it:
In WP 2.0.x queries for posts contain the following:
GROUP BY wp_posts.ID
ORDER BY post_date DESC
The "group by" has no real effect here and has been removed in WP 2.1.
Now lately a lot of providers updated their MySQL to 5.0.51 where this
group by doesn't get optimized out of the query anymore. So the effect
was that suddenly the postings in the blog were not descending by date
anymore but ascending by ID. So the oldest entry was at the top. A very
strange effect, and it took some time for a clever user to find the reason.
So the first question is does this need to be fixed in the WP 2.0.x
releases? I seem to remember that this branch still gets bugfix releases
every now and then?!
And then for the current WordPress version: The user who reported this
says it also effects the current versions of WP if you use categories or
tags in the query, like this:
query_posts( $query_string.'&cat=-8' );
So there is the possibility that something needs to be changed in the
current version, too. Sorry if I made any mistake in summarizing this. I
haven't looked into it too closely. Just thought I should mention it
More information about the wp-hackers