Some time ago, version 6.16 of Drupal was released and hit the Debian archives shortly after. It fixed some security issues, but apparently introduced a regression for PostgreSQL users. The problem seems to be a MySQLism in Drupal or in other words: Drupal is not following the SQL92 standard.
The error is:
2010-05-02 06:36:23.155 CEST drupal6_mycr drupal6 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
2010-05-02 06:36:23.155 CEST drupal6_mycr drupal6 STATEMENT: SELECT DISTINCT nc.nid FROM node_comment_statistics nc INNER JOIN node_access na ON na.nid = nc.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = ‘all’) OR (na.gid = 0 AND na.realm = ‘content_access_author’) OR (na.gid = 1 AND na.realm = ‘content_access_rid’))) AND ( nc.comment_count > 0 )ORDER BY nc.last_comment_timestamp DESC LIMIT 10 OFFSET 0
There are several issues open in the Drupal issue tracker like #742896 and #742006 and #735120. Apparently there are several ways to address this issue, but none seem to be the preferred solution. In #735120 there’s a patch, but it seems not to be accepted so far.
So, is there a good and viable patch for that issue that will go into the next update of Drupal?