Query error
| Project: | Privatemsg |
| Version: | 7.x-1.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed |
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

#1
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.
#2
Thanks, fixed in 6.x-1.x-dev and 7.x-1.x-dev.
#3
Thanx. Works for me too, like a charm. But I have no clue where to set RTBC.
#4
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
#5
Automatically closed -- issue fixed for 2 weeks with no activity.