320289 - [PostgreSQL] Ordering a buglist by "Actual Hours" fails

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[Frédéric Buclin]

DBD::Pg::st execute failed: ERROR:  aggregates not allowed in GROUP BY clause [for Statement "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.op_sys, (SUM(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id)) AS actual_time, bugs.short_desc FROM bugs  INNER JOIN profiles AS map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) INNER JOIN longdescs AS ldtime ON (ldtime.bug_id = bugs.bug_id) LEFT JOIN profiles AS map_qa_contact ON (bugs.qa_contact = map_qa_contact.userid) LEFT JOIN priority ON (priority.value = bugs.priority) LEFT JOIN bug_severity ON (bug_severity.value = bugs.bug_severity) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN (8,16,4,9,14,18,2,17,12,3,10,11,13,1,6,15,5,7)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 10195 WHERE (((bugs.assigned_to IN (10195)) OR (bugs.reporter IN (10195)) OR (COALESCE(map_qa_contact.login_name,'') IN ('[e-mail]')))) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND bugs.reporter = 10195)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to = 10195) OR (bugs.qa_contact = 10195) ) GROUP BY bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, map_assigned_to.login_name, bugs.op_sys, bugs.short_desc, actual_time, priority.sortkey, priority.value, bug_severity.sortkey, bug_severity.value ORDER BY actual_time,priority.sortkey,priority.value,bug_severity.sortkey,bug_severity.value"] at /var/www/html/qa222pg/buglist.cgi line 886



The reason is that Search.pm only keeps the field aliases in @orderby:

        if ($orderitem =~ /\s+AS\s+(.+)$/i) {
            $orderitem = $1;
        }
        BuildOrderBy($orderitem, \@orderby);


and so the regexp which tries to prevent aggregates in @groupby misses this field:

    foreach my $field (@fields, @orderby) {
        next if ($field =~ /(AVG|SUM|COUNT|MAX|MIN|VARIANCE)\s*\(/i ||
                 $field =~ /^\d+$/ || $field eq "bugs.bug_id" ||
                 $field =~ /^relevance/);

because SUM() has already been removed in favor of actual_time.

Comment 1

[Frédéric Buclin]

Attachment: patch, v1

Comment 2

[Max Kanat-Alexander]

Comment on attachment 205887 [details] [diff] [review]
patch, v1

r=mkanat by inspection

Comment 3

[Max Kanat-Alexander]

I think the combination of "Pg" and "time-tracking only" makes this not a blocker. But we'll probably still get it in anyhow. :-)

Comment 4

[Max Kanat-Alexander]

This bug doesn't affect the 2.20 branch? I think we should get it into 2.20 also, if it does.

Comment 5

[Frédéric Buclin]

Bonsai says this part of the code hasn't change since 2.20, so 2.20 is affected too (and the patch applies cleanly).

manu, if you use Bugzilla 2.20 together with PostgreSQL, could you please do a quick test of this patch? I only have a Pg installation for 2.22. Note that this patch only fixes the problem for "Actual Hours" till bug 320291 is fixed.

Comment 6

[Emmanuel Seyman]

(In reply to comment #5)
> 
> manu, if you use Bugzilla 2.20 together with PostgreSQL, could you please do a
> quick test of this patch? I only have a Pg installation for 2.22. Note that
> this patch only fixes the problem for "Actual Hours" till bug 320291 is fixed.

I can reproduce the problem on the tip of the 2.20 branch.
The patch resolves this.

Comment 7

[Frédéric Buclin]

tip:

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

2.20:

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