Group by query error on PostgreSQL
Reported by robinst | October 9th, 2010 @ 12:30 PM
There are multiple queries which prevent gitorious from working on PostgreSQL. One example is in app/models/project.rb in the method self.most_active_recently:
find(:all, :joins => :events, :limit => limit,
:select => 'distinct projects.*, count(events.id) as event_count',
:order => "event_count desc", :group => "projects.id",
:conditions => ["events.created_at > ?", number_of_days.days.ago])
The problem is that projects.* is selected, but in the group by only projects.id is grouped by. MySQL just lets you get away with that, but PostgreSQL (and possibly other DBMS) is more strict and prints the following error:
ERROR: column "projects.name" must appear in the GROUP BY clause
or be used in an aggregate function
See here for an explanation: http://archives.postgresql.org/pgsql-general/2004-02/msg01197.php
One solution for this problem would be to select all the needed fields of "projects" by name and also group by these fields.
Another possibility is getting the count with a subselect.
What do you guys think?
Comments and changes to this ticket
-
Marius Mathiesen October 27th, 2010 @ 12:51 PM
- → State changed from “new” to “resolved”
If someone creates a merge request that addresses this, we'd we glad to accept it. Please do make sure that the test suites pass, these are our guarantee that Gitorious still works after the change has been introduced.
-
Please Sign in or create a free account to add a new ticket.
With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.
Create new ticket
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
Use https://issues.gitorious.org/ - this site is not in use
People watching this ticket
Tags
Referenced by
-
#11 Hide/Show on merge request inline comments when comment exists on same line in multiple files
When a merge request contains multiple files with identic...