Query error

gatoth - November 5, 2009 - 10:45
Project:Privatemsg
Version:7.x-1.x-dev
Component:Code
Category:bug report
Priority:normal
Assigned:Unassigned
Status:closed
Description

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

Berdir - November 5, 2009 - 16:12
Status:active» needs review

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.

AttachmentSize
privatemsg.pgsql_groupby_fix.patch 1006 bytes

#2

Berdir - November 6, 2009 - 12:48
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.

#3

gatoth - November 7, 2009 - 11:45

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

#4

Berdir - November 7, 2009 - 11:53

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

System Message - November 21, 2009 - 12:00
Status:fixed» closed

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

 
 

Drupal is a registered trademark of Dries Buytaert.