Backlinks view broken under postgres 8.2.3
jstrat - January 15, 2008 - 08:52
| Project: | Backlinks |
| Version: | 5.x-1.1 |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | closed |
Description
When clicking on the "What Links Here" tab the view reports the following error and no results....
warning: pg_query() [function.pg-query]: Query failed: ERROR: column "search_index.fromsid" must appear in the GROUP BY clause or be used in an aggregate function in /Users/jasonstratford/Sites/cms/includes/database.pgsql.inc on line 125.
user warning: query: SELECT DISTINCT(node.nid), search_index.fromsid, node.title AS node_title, node.changed AS node_changed FROM (SELECT DISTINCT ON (nid) * FROM node) node INNER JOIN search_index search_index ON node.nid = search_index.fromsid AND search_index.type = 'node' AND search_index.fromtype = 'node' WHERE (node.status = '1') AND (search_index.sid = 4) AND (search_index.fromsid != search_index.sid) GROUP BY node.nid ORDER BY node_changed DESC LIMIT 10 OFFSET 0 in /Users/jasonstratford/Sites/cms/includes/database.pgsql.inc on line 144.I am not up to speed on the methods used to generate these queries so would appreciate some assistance in tracking down the cause.
Many thanks
Jason
#1
Thanks for the report! I'm not sure off hand where the problem is, but I just installed postgresql on my laptop so hopefully I can take a look at it soon.
#2
Here's a patch that seems to resolve the issue. It removes the node distinct filter, which is adding a GROUP BY clause which pgsql is not happy with -- all selected fields need to be either in the GROUP BY clause or wrapped in an aggregate function like MAX(). In order to make this work you will probably need to flush your views caches.
#3
I went ahead and committed a patch for this, as it seems to work.
#4
Automatically closed -- issue fixed for two weeks with no activity.