298400 - [PostgreSQL] SQL error using buglist.cgi and full text search. Column "relevance desc" not valid in GROUP BY

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[David Lawrence [:dkl]]

When using search-specific.html.tmpl (fulltext), the order by is set to "relevance  
desc". This gets added to the ORDER BY clause of the SQL statement properly, but
it also gets added to the field list in the GROUP BY clause which causes an SQL
error. 

     foreach my $field (@fields, @orderby) {
         next if ($field =~ /(AVG|SUM|COUNT|MAX|MIN|VARIANCE)\s*\(/i ||
-                 $field =~ /^\d+$/ || $field eq "bugs.bug_id");
+                 $field =~ /^\d+$/ || $field eq "bugs.bug_id" || $field eq
"relevance desc");
         if ($field =~ /.*AS\s+(\w+)$/i) {
             push(@groupby, $1) if !grep($_ eq $1, @groupby);
         } else {


The diff above in Bugzilla/Search.pm fixes the problem but I am not sure if it
is the most elegant way to do it.

Comment 1

[Max Kanat-Alexander]

Hrm... I might just have to hack in a fix for that manually. I'll see if I can't
come up with something better, though.

Comment 2

[Max Kanat-Alexander]

Ohhhh... the problem is that it's using "desc" in the GROUP BY. I think that's
all we need to fix. :-)

Comment 3

[Max Kanat-Alexander]

Attachment: Exclude relevance from GROUP BY

Yeah, I basically used your solution.

At first, I tried just removing the DESC with a regex, but then Pg also
complained that aggregates aren't allowed in the GROUP BY clause. :-)

So instead I'm just removing relevance entirely.

Comment 4

[Max Kanat-Alexander]

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

Comment 5

[Max Kanat-Alexander]

Comment on attachment 189279 [details] [diff] [review]
Exclude relevance from GROUP BY

This is really brief; one changed line, if you have a second to review it,
LpSolit.

Comment 6

[Frédéric Buclin]

Comment on attachment 189279 [details] [diff] [review]
Exclude relevance from GROUP BY

I prefer joel to look at this patch. A one-liner doesn't mean it has no
side-effect. And I'm not familiar enough with the code in Search.pm to
guarantee it has none.

Comment 7

[Max Kanat-Alexander]

Comment on attachment 189279 [details] [diff] [review]
Exclude relevance from GROUP BY

I'm not sure joel has the time to do reviews, nowadays, but perhaps glob does?
:-)

Comment 8

[Marc Schumann [:Wurblzap]]

Comment on attachment 189279 [details] [diff] [review]
Exclude relevance from GROUP BY

I can't reproduce finding relevance in GROUP BY, but it looks sensible to me,
and it doesn't seem to break anything.

Asking for a second review.

Comment 9

[Joel Peshkin]

Comment on attachment 189279 [details] [diff] [review]
Exclude relevance from GROUP BY

r=joel by inspection.

Comment 10

[Frédéric Buclin]

the patch applies cleanly to both 2.20 and tip. Requesting approval.

Comment 11

[Dave Miller [:justdave]]

Agreed.  ANSI SQL spec says GROUP BY only needs to include real columns, not
aggregates (and Postgres is enforcing that).  This is the right thing to do.

Comment 12

[Max Kanat-Alexander]

Tip:

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

2.20:

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

Comment 13

[Frédéric Buclin]

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

Comment 14

[Frédéric Buclin]

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

Comment 15

[Frédéric Buclin]

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