124127 - Software error when sorting buglist by "Opened" date

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[Andreas Franke (gone)]

Sorting a buglist by "Opened" date doesn't work:

Software error:

Invalid order: unix_timestamp(bugs.creation_ts), map_assigned_to.login_name,
bugs.bug_id at /home/bugzilla/apache/htdocs/buglist.cgi line
1161. 

With the new templatized buglist.cgi from bug 103778 the message is different:

Software error:

SELECT DISTINCT bugs.bug_id, bugs.groupset, unix_timestamp(bugs.creation_ts),
bugs.bug_type, bugs.bug_severity, bugs.priority,
map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.product,
bugs.component, bugs.target_milestone, bugs.short_desc FROM
bugs, profiles map_assigned_to, profiles map_reporter LEFT JOIN profiles
map_qa_contact ON bugs.qa_contact = map_qa_contact.userid LEFT
JOIN cc selectVisible_cc ON bugs.bug_id = selectVisible_cc.bug_id AND
selectVisible_cc.who = 5 WHERE ((bugs.groupset & 31736) =
bugs.groupset OR (bugs.reporter_accessible = 1 AND bugs.reporter = 5) OR
(bugs.assignee_accessible = 1 AND bugs.assigned_to = 5) OR
(bugs.qacontact_accessible = 1 AND bugs.qa_contact = 5) OR
(bugs.cclist_accessible = 1 AND selectVisible_cc.who = 5 AND not
isnull(selectVisible_cc.who))) AND bugs.assigned_to = map_assigned_to.userid AND
bugs.reporter = map_reporter.userid AND (bugs.bug_status
= 'UNCONFIRMED' OR bugs.bug_status = 'NEW' OR bugs.bug_status = 'ASSIGNED' OR
bugs.bug_status = 'REOPENED') GROUP BY bugs.bug_id ORDER BY
unix_timestamp(bugs.creation_ts), map_assigned_to.login_name, bugs.bug_id : You
have an error in your SQL syntax near
'unix_timestamp(bugs.creation_ts), map_assigned_to.login_name, bugs.bug_id ' at
line 6 at globals.pl line 249.

Comment 1

[Andreas Franke (gone)]

Hm, actually this currently works on bugzilla.mozilla.org .

Can someone please test whether this is broken in current cvs or not?
If it's not broken in cvs, is it broken with the buglist patch from bug 103778?
If it's neither of these two, I can take a further look which of my patches may
have caused it...

Comment 2

[Andreas Franke (gone)]

This works fine on http://landfill.tequilarista.org/bugzilla-tip/ .

And it seems I can't test it on http://landfill.tequilarista.org/bztemplate/
because it the database schema does not seem to work with the bugzilla version:

> Unknown column 'cryptpassword' in 'field list' at globals.pl line 222. 

Comment 3

[Andreas Franke (gone)]

Hm, actually, on landfill-bztemplate I get a nice error message in html:

Error 

   The custom sort order you specified in your form submission or cookie    
   contains an invalid column name bugs.creation_ts.

Am I the only one getting a software error?

Comment 4

[Andreas Franke (gone)]

Now I am confused.

After removing a cookie, I was able to run the query on bztemplate without
error, and clicking on the "Opened" column header did not produce an error at
all. But clicking on any other column header did not reorder the buglist at all.
The URL after clicking on "Summary" looks like this:

http://landfill.tequilarista.org/bztemplate/buglist.cgi?bug_status=UNCONFIRMED&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED%26oldorder%3Dbugs.bug_status%2C%20bugs.priority%2C%20map_assigned_to.login_name%2C%20bugs.bug_id%26oldorder%3Dbugs.bug_status%2C%20bugs.priority%2C%20map_assigned_to.login_name%2C%20bugs.bug_id%26oldorder%3Dbugs.bug_status%2C%20bugs.priority%2C%20map_assigned_to.login_name%2C%20bugs.bug_id%26oldorder%3Dbugs.bug_status%2C%20bugs.priority%2C%20map_assigned_to.login_name%2C%20bugs.bug_id%26oldorder%3Dbugs.bug_status%2C%20bugs.priority%2C%20map_assigned_to.login_name%2C%20bugs.bug_id&order=summary

Maybe the code there is not up-to-date?

Comment 5

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

Attachment: patch v1: locks the tables

Comment 6

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

Ignore that patch.  It is supposed to be on bug 124587.

Comment 7

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

>Maybe the code there is not up-to-date?

That's correct, the code there was way out of date.  This has been corrected.

Comment 8

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

This is either a MySQL bug or a feature that doesn't exist in older versions of
MySQL.  The query works on 3.23.41 (on my workstation) and 3.23.44 (on mothra),
but not on 3.22.30 (on landfill).  unix_timestamp() was introduced in 3.20.17. 
Maybe it can't be used in an ORDER BY statement on older versions?

Comment 9

[Bradley Baetz (:bbaetz)]

Do you need to order by the function? Can't you just ORDER BY the actual value?
mysql should do the sorting for you, shouldn't it?

Comment 10

[Dave Miller [:justdave]]

maybe we should just up the minimum MySQL version.  We're already planning to
anyway, aren't we?

Comment 11

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

We could order by the actual value, but it means hacking buglist.cgi to order by
different values than it selects.  We could also up the MySQL requirement, but
that's too drastic a measure for a bug of this size (unless we are going to do
it anyway).  We should remove the unix_timestamp call and use the "str2time"
function of Date::Parse (which we already require) to convert the MySQL
date/time value to a Unix time value in DiffDate.  I'll add this to my patch in
bug 103778.

Comment 12

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

This bug has been fixed by the check-in for bug 103778.