Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.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.
| Comment | File | Size | Author |
|---|---|---|---|
| #6 | blog-query-fix-1149628-6.patch | 739 bytes | Ben Coleman |
| #4 | blog-query-fix-1149628-4.patch | 743 bytes | Ben Coleman |
| blog.pages_.inc_.patch | 661 bytes | Ben Coleman | |
Comments
Comment #1
Ben Coleman CreditAttribution: Ben Coleman commentedNote 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.
Comment #2
Ben Coleman CreditAttribution: Ben Coleman commentedComment #4
Ben Coleman CreditAttribution: Ben Coleman commentedThe same patch, generated from git.
Comment #6
Ben Coleman CreditAttribution: Ben Coleman commentedOne more time. If this doesn't pass testing, I'm really confused as to why.
Comment #8
Ben Coleman CreditAttribution: Ben Coleman commentedComment #9
Ben Coleman CreditAttribution: Ben Coleman commented#6: blog-query-fix-1149628-6.patch queued for re-testing.
Comment #10
Ben Coleman CreditAttribution: Ben Coleman commentedIs there anything else needs to be done with this?
Comment #11
sunThis is indeed a trivial fix. Postgres requires all ORDER BY columns to appear in the queried data set.
Comment #13
dgv CreditAttribution: dgv commentedAs 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.
Comment #14
Gábor HojtsyThanks, committed to Drupal 6.
Comment #16
Liam MorlandTagging