Progress Report on WordPress-pg 1.5

Here’s some SQL…

SELECT meta_key, meta_id FROM wp_postmeta GROUP BY meta_id ORDER BY meta_id DESC LIMIT 10;

This is just one of the many issues I’ve been working through in porting this code. Initial table creation is now successful and the sample post is populated and at least partially displayed. Editing is not working, new posts aren’t working, new pages aren’t working, new comments aren’t… well, you get the idea. At least I’m making forward progress though…

Let’s get back to that snippet at the top. Apparently in the latest revision of WordPress the authors decided that adding lots of “GROUP BY some-primary-key” statements would make things better. Now in ordinary database land, if you are “grouping by” some field, you have to give the database instructions on how to aggregate the other fields that you want displayed. In the above example, there may be 3 or 4 rows of meta_keys to choose from. As you can see above the database is given no instructions on which one of those 3 or 4 rows to use. PostgreSQL treats this as a malformed query and returns an error. MySQL in fairly typical fashion for it, just picks an arbitrary row to use. I think it’s using the first row in the table that it happens to come across during your query, but I could be wrong. The point is that it is not well-defined, and in ordinary database land, not well-defined behavior is considered harmful.

So, to make progress I basically have to strip all these GROUP BY statements back out again. And honestly, I’m unable to see what these statements accomplish. The code is “grouping by” a primary key sequence on a given table in all the cases I’ve happened across so far. By definition there should only be one row with a given primary key. But perhaps that’s only true in ordinary database land…