In postgreSQL all selected columns (which are not aggregated) must appear in GROUP BY clause.

In error log:
query: SELECT t.tag, t.tag_id, t.public, COUNT(ti.thread_id) as count FROM prod_pm_tags t LEFT JOIN prod_pm_tags_index ti ON t.tag_id = ti.tag_id WHERE (ti.uid = 90) GROUP BY t.tag_id, t.tag ORDER BY t.tag ASC in modules/privatemsg/privatemsg_filter/privatemsg_filter.module on line 297.

In psql command line:
# SELECT t.tag, t.tag_id, t.public, COUNT(ti.thread_id) as count FROM prod_pm_tags t LEFT JOIN prod_pm_tags_index ti ON t.tag_id = ti.tag_id WHERE (ti.uid = 90) GROUP BY t.tag_id, t.tag ORDER BY t.tag ASC ;
ERROR: column "t.public" must appear in the GROUP BY clause or be used in an aggregate function

The correct query would be:
# SELECT t.tag, t.tag_id, t.public, COUNT(ti.thread_id) as count FROM prod_pm_tags t LEFT JOIN prod_pm_tags_index ti ON t.tag_id = ti.tag_id WHERE (ti.uid = 90) GROUP BY t.tag_id, t.tag, t.public ORDER BY t.tag ASC ;

Please fix.

Thank you,
Gabor

CommentFileSizeAuthor
#1 privatemsg.pgsql_groupby_fix.patch1006 bytesberdir

Comments

berdir’s picture

berdir
Primary language German
Location Switzerland
commented
Status: Active » Needs review
StatusFileSize
new privatemsg.pgsql_groupby_fix.patch1006 bytes

Thanks. I'll try to remember to our test suite (which would have found that bug) more often on PostgreSQL :)

Attached is a patch that should fix this, please test and set to RTBC.

berdir’s picture

berdir
Primary language German
Location Switzerland
commented
Version: 6.x-1.0-rc4 » 7.x-1.x-dev
Status: Needs review » Fixed

Thanks, fixed in 6.x-1.x-dev and 7.x-1.x-dev.

gatoth’s picture

gatoth commented

Thanx. Works for me too, like a charm. But I have no clue where to set RTBC.

berdir’s picture

berdir
Primary language German
Location Switzerland
commented

I've already commited it, so you don't need to to that anymore.

For the next time, RTBC is short for "reviewed & tested by the community" which is status you can set. It means that you tested a patch and can verify that it does work as expected.

You can find more information about that here: http://drupal.org/node/156119

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.