293015 - PostgreSQL: GROUP BY error when searching for bugs

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect, P1)

Description

[Olivier Sirven]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; fr; rv:1.7.6) Gecko/20050405 Firefox/1.0 (Ubuntu package 1.0.2)
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; fr; rv:1.7.6) Gecko/20050405 Firefox/1.0 (Ubuntu package 1.0.2)

When using bugzilla cvs version with a postgreSQL database, executing a saved
query will fail because of a group by error

Reproducible: Always

Steps to Reproduce:
1. Just save any query
2. Execute this saved query
Actual Results:  
Software error:

DBD::Pg::st execute failed: ERROR:  column "bug_status.sortkey" must appear in
the GROUP BY clause or be used in an aggregate function
 [for Statement "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority,
bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority,
bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution,
bugs.short_desc FROM bugs  INNER JOIN profiles AS map_assigned_to ON
(bugs.assigned_to = map_assigned_to.userid) AND (bugs.assigned_to =
map_assigned_to.userid) INNER JOIN profiles AS map_reporter ON (bugs.reporter =
map_reporter.userid) LEFT JOIN bug_status ON (bug_status.value =
bugs.bug_status) LEFT JOIN priority ON (priority.value = bugs.priority) LEFT
JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND
bug_group_map.group_id NOT IN (3,7,12,10,5,1,8,11,4,2,6,9)  LEFT JOIN cc ON
cc.bug_id = bugs.bug_id AND cc.who = 1 WHERE ((bugs.bug_status IN
('NEW','ASSIGNED','REOPENED')) AND ((map_assigned_to.login_name IN
('[e-mail]')) OR (map_reporter.login_name IN
('[e-mail]')))) AND ((bug_group_map.group_id IS NULL)    OR
(bugs.reporter_accessible = 1 AND bugs.reporter = 1)     OR
(bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to =
1) ) GROUP BY bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status,
bugs.resolution, bugs.rep_platform, map_assigned_to.login_name, bugs.short_desc
ORDER BY bug_status.sortkey ,bug_status.value ,priority.sortkey ,priority.value
,map_assigned_to.login_name ,bugs.bug_id "] at
/home/httpd/htdocs/bugzilla/buglist.cgi line 820

Comment 1

[Olivier Sirven]

Attachment: A correction to this bug report

The purpose of this patch is to add to the group by clause any field being used
in the order by clause and which is not already in the group by clause.

Comment 2

[Max Kanat-Alexander]

I thought that either we fixed this or there was another bug we had for it, but
if I can't find that I'll confirm this bug and review the patch.

Comment 3

[Olivier Sirven]

(In reply to comment #2)
> I thought that either we fixed this

My cvs version is up to date so if it is already fixed, it has not been commited yet

Comment 4

[Max Kanat-Alexander]

*Please* don't change the version field to unspecified. In our case, 2.19.2
means "2.19.2 and the tip after 2.19.2."

Comment 5

[Olivier Sirven]

it must be a bug with my browser because i did not change anything about the
version stuff. When I added my last comment, bugzilla crash with an error
telling me I have to retry because of a bug with mozilla navigator so I guess it
must come from this

Comment 6

[Max Kanat-Alexander]

*** Bug 292866 has been marked as a duplicate of this bug. ***

Comment 7

[Max Kanat-Alexander]

Comment on attachment 182676 [details] [diff] [review]
A correction to this bug report

We could do this even more simply and reliably, by adding the @orderby array to
the foreach above.

Comment 8

[Max Kanat-Alexander]

Olivier, would you like to produce a new version of the patch, or would you like
me to do it?

Comment 9

[Olivier Sirven]

Attachment: new version of the patch

A new version of the patch merging the two foreach statements into one

Comment 10

[Olivier Sirven]

(In reply to comment #8)
> Olivier, would you like to produce a new version of the patch, or would you like
> me to do it?

It's done ;)
Review it and tell me if it is what you wanted.

Comment 11

[Max Kanat-Alexander]

Comment on attachment 182949 [details] [diff] [review]
new version of the patch

Yes, that's exactly what I wanted! Great! :-)

In the future, make sure to set the review flag to ? with my username, so that
I actually get a review request for the patch. :-)

Comment 12

[Olivier Sirven]

(In reply to comment #11)
> In the future, make sure to set the review flag to ? with my username, so that
> I actually get a review request for the patch. :-)

Woups sorry fo that ;)

Comment 13

[Myk Melez [:myk] [@mykmelez]]

Good polish fix.  Approved for checkin during 2.20 freeze.

Comment 14

[Frédéric Buclin]

Checking in Bugzilla/Search.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v  <--  Search.pm
new revision: 1.96; previous revision: 1.95
done