The following query in line 1548 of comment.module is causing problem in PostgreSQL 7.5.4. PostgreSQL complains that v.weight is not part of the GROUP BY clause.

$result = db_query('SELECT v.mid, v.vote, MAX(r.value) AS value FROM {moderation_votes} v INNER JOIN {moderation_roles} r ON r.mid = v.mid WHERE r.rid IN (%s) GROUP BY v.mid, v.vote ORDER BY weight', implode(', ', array_keys($user->roles)));
CommentFileSizeAuthor
#6 drupal-4-6-comment-12071.diff1.56 KBCvbge
#4 comment.module_1.patch2.07 KBmousse-man

Comments

mousse-man’s picture

mousse-man commented

The bug is also very visible in the workspace module. However, I have no clue where the workspace module is accessing functionality of the comments module.

If I feed it the query

SELECT v.mid, v.vote, MAX(r.value) AS value FROM moderation_votes v INNER JOIN moderation_roles r ON r.mid = v.mid WHERE r.rid IN (3) GROUP BY v.mid, v.vote, v.weight ORDER by v.weight DESC;

it works, and actually the 'only' trick to this is to add 'v.weight' in the GROUP BY clause.

This error message gets printed when I activate the workspace module and then click 'my workspace':

warning: pg_query(): Query failed: ERROR: column "v.weight" must appear in the GROUP BY clause or be used in an aggregate function
. in /var/www/html/includes/database.pgsql.inc on line 104.

user error:
query: SELECT v.mid, v.vote, MAX(r.value) AS value FROM moderation_votes v INNER JOIN moderation_roles r ON r.mid = v.mid WHERE r.rid IN (3) GROUP BY v.mid, v.vote ORDER BY weight in /var/www/html/includes/database.pgsql.inc on line 121.

This means that if we change the line 1548 to the following:

$result = db_query('SELECT v.mid, v.vote, MAX(r.value) AS value FROM {moderation_votes} v INNER JOIN {moderation_roles} r ON r.mid = v.mid WHERE r.rid IN (%s) GROUP BY v.mid, v.vote, v.weight ORDER BY weight', implode(', ', array_keys($user->roles)));

it doesn't display any error message anymore. Is this the solution?

Anonymous’s picture

(not verified) commented

I hade the same problem on my site today. I added "weight" column to the group by clause to fix the error.

The original query in comment.module is not correct in Postgress. You can not run it manually, as you will get the error message. I hope the fix will go quickly to the drupal core.

ttt’s picture

ttt commented

In addition, the query on line 776 does not work in PostgreSQL either.

'SELECT c.cid, c.pid, c.nid, c.subject, c.comment, c.format, c.timestamp, c.name, c.mail, c.homepage, u.uid, u.name AS registered_name, u.picture, u.data, c.score, c.users FROM {comments} c INNER JOIN {users} u ON c.uid = u.uid WHERE c.cid = %d AND c.status = 0 GROUP BY c.cid, c.pid, c.nid, c.subject, c.comment, c.timestamp, c.name, c.mail, u.picture, c.homepage, u.uid, u.name, u.picture, u.data, c.score, c.users'

Adding c.format as part of the GROUP BY clause solves the problem.

mousse-man’s picture

mousse-man commented
Assigned: Unassigned » mousse-man
StatusFileSize
new comment.module_1.patch2.07 KB

So this would give the following patch, as attached below, against the 4.5.1 comment module.

killes@www.drop.org’s picture

killes@www.drop.org commented

Doesn't apply anymore.

Cvbge’s picture

Cvbge commented
Status: Active » Reviewed & tested by the community
StatusFileSize
new drupal-4-6-comment-12071.diff1.56 KB

The first bug seems to be fixed. The SQL is a bit different, the weight is MAXed and works with postgres:

SELECT v.mid, v.vote, MAX(v.weight) AS weight, MAX(r.value) AS value FROM {moderation_votes} v INNER JOIN {moderation_roles} r ON r.mid = v.mid WHERE r.rid IN (%s) GROUP BY v.mid, v.vote ORDER BY weight

I don't know how to trigger the second bug (from #3), but I agree that the sql is not correct and proposed fix is ok.

Attached patch fixes the second bug, it's for 4.6.

Thanks you for your reports.

dries’s picture

dries commented
Status: Reviewed & tested by the community » Fixed

Committed to HEAD. Thanks.

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)