On a 6.20 site, with blogs enabled, going to http:///blog produces the errors:

query: SELECT DISTINCT n.nid, n.created FROM node n WHERE n.type = 'blog' AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC LIMIT 10 OFFSET 0 in /home/suresupply/public_html/modules/blog/blog.pages.inc on line 67.

pg_query(): Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 1: ...n WHERE n.type = 'blog' AND n.status = 1 ORDER BY n.sticky D... ^ in /home/suresupply/public_html/includes/database.pgsql.inc on line 139.

This is because PostgreSQL requires ORDER BY expressions to be in the select list. Perhaps this is not a MySQL requirement, but I don't think this is going to hurt for MySQL. Patch attached.

Files: 
CommentFileSizeAuthor
#6 blog-query-fix-1149628-6.patch739 bytesBen Coleman
PASSED: [[SimpleTest]]: [MySQL] 190 pass(es).
[ View ]
#4 blog-query-fix-1149628-4.patch743 bytesBen Coleman
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch blog-query-fix-1149628-4.patch.
[ View ]
blog.pages_.inc_.patch661 bytesBen Coleman
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch blog.pages_.inc__8.patch.
[ View ]

Comments

Note that while the original query doesn't have SELECT DISTINCT, it is being run through db_rewrite_sql, which means some other module can change it to a SELECT DISTINCT (which is obviously happening in my own situation - I'm not sure at the moment which module is making the change). This is not the only place where this happens (there's a query in taxonomy that has the same thing happening to it via a rewrite from Forum Access). Given that it doesn't hurt to make sure ORDER BY expressions are in the select list, queries being run through db_rewrite_sql should include ORDER BY expressions in the select list, even if the unmodified query works.

Status:Patch (to be ported)» Needs review

Status:Needs review» Needs work

The last submitted patch, blog.pages_.inc_.patch, failed testing.

Status:Needs work» Needs review
StatusFileSize
new743 bytes
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch blog-query-fix-1149628-4.patch.
[ View ]

The same patch, generated from git.

Status:Needs review» Needs work

The last submitted patch, blog-query-fix-1149628-4.patch, failed testing.

Version:6.x-dev» 6.20
StatusFileSize
new739 bytes
PASSED: [[SimpleTest]]: [MySQL] 190 pass(es).
[ View ]

One more time. If this doesn't pass testing, I'm really confused as to why.

The last submitted patch, blog-query-fix-1149628-6.patch, failed testing.

Version:6.20» 6.x-dev
Status:Needs work» Needs review

#6: blog-query-fix-1149628-6.patch queued for re-testing.

Title:PostgreSQL incompatibility in blog.pages.incFix ORDER BY query in blog.pages.inc to avoid db_rewrite_sql() producing PostgreSQL syntax error
Version:6.20» 6.x-dev

Is there anything else needs to be done with this?

Status:Needs review» Reviewed & tested by the community
Issue tags:+PostgreSQL

This is indeed a trivial fix. Postgres requires all ORDER BY columns to appear in the queried data set.

Issue tags:-PostgreSQL

As Ben Coleman said, the key is the DISTINCT clause, otherwise ORDER BY could be done with a column that is not in the select list.
I have this fix in production on a 6.22 D6 site w/postgresql. It is mandatory for the /blog page to be displayed and should be harmless with mysql.
Glad to see that the issue is moving forward for the sake of future 6.x updates.

Status:Reviewed & tested by the community» Fixed

Thanks, committed to Drupal 6.

Status:Fixed» Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Issue tags:+PostgreSQL

Tagging