After making the patch in #1335644: PostgreSQL incompatibility: LIMIT #,# syntax is not supported, editing a node on a PostgreSQL-based Drupal site using Link Intelligence produces multiple warnings:
warning: pg_query() [function.pg-query]: Query failed: ERROR: column "r.rid" must appear in the GROUP BY clause or be used in an aggregate function LINE 2: SELECT r.*, r.path AS pid, k.keyword, k.keyword_stem, p.... ^ in /home/friendsofqola/public_html/includes/database.pgsql.inc on line 138.
user warning: query: SELECT r.*, r.path AS pid, k.keyword, k.keyword_stem, p.priority AS page_priority, kwr.kid AS kwr_kid, kwr.priority AS keyword_priority, (r.priority * IF(p.priority >= 0, p.priority, IF(p.type_priority >= 0, p.type_priority, 50)) * IF(kwr.priority >= 0, kwr.priority, 50) / 1250) AS score, n.type AS node_type FROM linkintel_request r JOIN linkintel_keyword k ON k.kid = r.kid LEFT JOIN linkintel_page p ON p.path = r.path LEFT JOIN kwresearch_keyword kwr ON kwr.keyword = k.keyword LEFT JOIN node n ON r.nid = n.nid WHERE r.path NOT IN ('node/24','http://liferenewed.net/sites/default/files/QuickQOLA-1.2.6-install.exe%20','http://www.shopnotforgotten.org/','http://www.shopnotforgotten.org/','http://veteranscrisisline.net/','http://veteranscrisisline.net/') GROUP BY k.keyword, r.*, k.keyword_stem, p.priority, kwr.kid, kwr.priority, p.type_priority, n.type, k.word_count ORDER BY score DESC, k.word_count DESC LIMIT 100 OFFSET 900 in /home/friendsofqola/public_html/sites/all/modules/linkintel/linkintel.module on line 1001.
The problem is that (as described in the PostgreSQL documentation):
"When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column."
This is actually a standard SQL restriction. The ability to select ungrouped columns in MySQL is a MySQL extension to the standard.
| Comment | File | Size | Author |
|---|---|---|---|
| #1 | Pg_GROUPBY_Compatibility_Fix-1335762-1.patch | 1006 bytes | ben coleman |
Comments
Comment #1
ben coleman commentedThe attached patch shuts up the PostgreSQL warnings. I'm not sure, though, if this produces the desired results. I think the original, under MySQL, produces one row per distinct k.keyword. This patch, though it runs on PostgreSQL, I think will produce one record for each permutation of the specified fields. If this is a problem, then it may be necessary to create a different query instead of just adding the GROUP BY clause to the current query in linkintel_load_filtered_requests_result.