PostgreSQL complains about the following query:
SELECT DISTINCT rev_user_text FROM revision WHERE rev_page = 42 ORDER BY rev_timestamp DESC LIMIT 10
with the following error:
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
This makes sense: Consider if user A edits the page first, then users B to Z edit, then A edits again. Should the above query sort A at the beginning or the end of the list? It seems MySQL only gets the expected result here by chance, BTW: see http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html and http://archives.postgresql.org/pgsql-sql/2007-02/msg00169.php for more info. When I was testing this on a simplified table, MySQL was giving the "wrong" answer until I added a "rev_page" column and the page_timestamp index.
Unfortunately, I can't think of an alternate query that won't make MySQL filesort.
Version: unspecified
Severity: normal