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

Ben Coleman’s picture

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.

Ben Coleman’s picture

Status:Patch (to be ported)» Needs review

Status:Needs review» Needs work

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

Ben Coleman’s picture

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.

Ben Coleman’s picture

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.

Ben Coleman’s picture

Version:6.20» 6.x-dev
Status:Needs work» Needs review
Ben Coleman’s picture

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

Ben Coleman’s picture

Title:PostgreSQL incompatibility in blog.pages.inc» Fix 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?

sun’s picture

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.

dgv’s picture

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.

Gábor Hojtsy’s picture

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.

Liam Morland’s picture

Issue tags:+PostgreSQL

Tagging