293678 - Sorting bug list by Assignee Real Name failing in latest nightly

Status: RESOLVED FIXED

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

Description

[Gary Penn]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.7) Gecko/20050414 Firefox/1.0.3
Build Identifier: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.7) Gecko/20050414 Firefox/1.0.3

Latest nightly 2.19.2+ has broken the sort if sorting by Assignee Real Name
(regular Assignee does work).  Here's the error:

Software error:
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near 'bugs.bug_id' at line 1 [for Statement "SELECT bugs.bug_id,
bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution,
bugs.bug_severity, bugs.priority, CASE WHEN map_assigned_to.realname = '' THEN
map_assigned_to.login_name ELSE map_assigned_to.realname END AS
assigned_to_realname, bugs.bug_status, bugs.resolution, map_products.name,
map_components.name, bugs.short_desc FROM bugs  INNER JOIN profiles AS
map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) INNER JOIN
products AS map_products ON (bugs.product_id = map_products.id) INNER JOIN
components AS map_components ON (bugs.component_id = map_components.id) LEFT
JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND
bug_group_map.group_id NOT IN (10,8,3,4,1,6,9,2,5,7)  LEFT JOIN cc ON cc.bug_id
= bugs.bug_id AND cc.who = 22 WHERE 1 = 1 AND ((bug_group_map.group_id IS NULL)
   OR (bugs.reporter_accessible = 1 AND bugs.reporter = 22)     OR
(bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to =
22) OR (bugs.qa_contact = 22) ) GROUP BY bugs.bug_id ORDER BY CASE
WHEN,bugs.bug_id "] at /var/www/html/bugzilla/buglist.cgi line 820

For help, please send mail to the webmaster (root@localhost), giving this error
message and the time and date of the error.

Reproducible: Always

Steps to Reproduce:
1. Search for any bugs (i.e. My Bugs or whatever)
2. Add the Column "Assignee Real Name" to the listing of #!
3. Try to sort by column from #2.  It fails with attached error.

Actual Results:  
Errors out with SQL syntax error as seen in details.

Expected Results:  
Should sort.

Comment 1

[Max Kanat-Alexander]

Yep, I think we knew that that would break, but we didn't think we already had
code that used CASE WHEN in there.

Comment 2

[Vadász Zoltán B.]

(In reply to comment #1)
> Yep, I think we knew that that would break, but we didn't think we already had
> code that used CASE WHEN in there.

I'm forced to use 2.19.3 in production (see bugs bug 126266 and bug 280633) so I
had to solve it quick and dirty:

==== cut patch here ====
--- Bugzilla/Search.pm.orig     2005-05-31 18:20:16.837106952 +0200
+++ Bugzilla/Search.pm  2005-05-31 18:22:21.019228400 +0200
@@ -1304,7 +1304,13 @@
     # to other parts of the query, so we want to create it before we
     # write the FROM clause.
     foreach my $orderitem (@inputorder) {
-       BuildOrderBy($orderitem, \@orderby);
+       if ($orderitem=~/CASE\s+WHEN\s+(\S+)\s+=\s+''\s+THEN\s+(\S+)/) {
+           BuildOrderBy($1, \@orderby);
+           BuildOrderBy($2, \@orderby);
+       }
+       else {
+           BuildOrderBy($orderitem, \@orderby);
+       }
     }
     # Now JOIN the correct tables in the FROM clause.
     # This is done separately from the above because it's
==== cut patch here ====

Comment 3

[Max Kanat-Alexander]

Attachment: Fix Search.pm to understand AS for the ORDER BY

OK. So the problem was that in general, Search.pm doesn't like it if you pass
it something with an "AS" for the ORDER BY.

So, I fixed that.

Comment 4

[Joel Peshkin]

Max: Does this have anything in common with bug 282737
They sound like they might need a common solution, but I have not delved into it
enough to be sure.

Comment 5

[Max Kanat-Alexander]

I think they are related. I think the good common solution is that in general we
need to centralize all this stuff about the columns into using the fielddefs
table instead of it being spread around in a million different places, like it
is now.

The solution is that the columns should know if they have an alias, and pass
that around as their "name" instead of passing around their actual formula. But
that's a more architectural change that we should do when the tree re-opens.

Comment 6

[Marc Schumann [:Wurblzap]]

I'd say this is a regression of bug 189366.

Comment 7

[Marc Schumann [:Wurblzap]]

Comment on attachment 185914 [details] [diff] [review]
Fix Search.pm to understand AS for the ORDER BY

Tested; works.

I know this is another bug: the CASE WHEN stuff is bound to trigger bug 284667
quickly because the "order" CGI param gets very large. Can't we, in addition to
what you're doing, perhaps make the UI contain the aliases as sorting params
directly?

Comment 8

[Max Kanat-Alexander]

(In reply to comment #7)
> Can't we, in addition to
> what you're doing, perhaps make the UI contain the aliases as sorting params
> directly?

  I'd love to. :-) See comment 5. It's actually a massive change, due to how
strange all the code is around that area.

Comment 9

[Marc Schumann [:Wurblzap]]

Well, what I was thinking of was to change the links behind the column headers
in buglist.cgi only, so that they don't contain the full "CASE WHEN ... AS xxx"
stuff, but "xxx" only. This wouldn't be too massive a change.

Comment 10

[Max Kanat-Alexander]

(In reply to comment #9)
> Well, what I was thinking of was to change the links behind the column headers
> in buglist.cgi only, so that they don't contain the full "CASE WHEN ... AS xxx"
> stuff, but "xxx" only. This wouldn't be too massive a change.

  Well, look at the code, and see if you can come up with an easy way to do it.
:-) I looked at the code, and I didn't see a good, clean way of handling it with
the current code structure. :-)

Comment 11

[Marc Schumann [:Wurblzap]]

Added bug 302326 for this.

Comment 12

[Max Kanat-Alexander]

Tip:

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

2.20:

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