306117 - Search on "Percentage Completed" field returns "unknown column 'bugs.remaining_time' in 'having clause'"

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[Lance Larsh]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.3) Gecko/20041119
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.3) Gecko/20041119

In the Advanced Search page, the search returns a database error if "Percentage
Complete" is included in the search terms under "Advanced Searching Using
Boolean Charts" AND neither "Hours Left" nor "%Complete" is selected in the Bug
List display columns.  If "Hours Left" or "%Complete" is included in the Bug
List display columns, then the query executes successfully.

Mysql returns an error because the bugs.remaining_time column is referenced in
the HAVING clause of the query, but the bugs.remaining_time column is not
included in the query's SELECT list.  Enabling "Hours Left" or "%Complete"
columns for display causes bugs.remaining_time to be added to the SELECT list.

The obvious user workaround is to enable the "Hours Left" or "%Complete" column
for display in the Bug List before doing any search on "Percentage Complete".


Reproducible: Always

Steps to Reproduce:
1. Enable time tracking by setting a valid group name on the Parameters page.
2. Go to the Advanced Search page.
3. Under "Advanced Searching Using Boolean Charts", choose "Percentage
Complete", "is less than", and "50" in the search parameters.
4. Click "Search".
5. If this does not reproduce the error, go to the "Change Columns" page from
the Bug List page and make sure "%Complete" and "Hours Left" are not selected.
Actual Results:  
The following message is displayed (formatted for clarity):

   Please stand by ...

   Software error:

   DBD::mysql::st execute failed:
      Unknown column 'bugs.remaining_time' in 'having clause' [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)
      INNER JOIN longdescs
            AS longdescs_0
            ON (longdescs_0.bug_id = bugs.bug_id) 
      LEFT JOIN bug_group_map
            ON bug_group_map.bug_id = bugs.bug_id
           AND bug_group_map.group_id NOT IN (3,7,6,5,2,4,1,8)
      LEFT JOIN cc
            ON cc.bug_id = bugs.bug_id
           AND cc.who = 1
      WHERE ((bugs.bug_status IN ('NEW','ASSIGNED','REOPENED')))
        AND ((0=0))
        AND bugs.creation_ts IS NOT NULL
        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
      HAVING (100 * ((SUM(longdescs_0.work_time) *
                        COUNT(DISTINCT longdescs_0.bug_when) /
                        COUNT(bugs.bug_id)) /
                    ((SUM(longdescs_0.work_time) *
                         COUNT(DISTINCT longdescs_0.bug_when) /
                         COUNT(bugs.bug_id)) +
                         bugs.remaining_time))) < '50'"]
      at /var/www/html/bz/buglist.cgi line 834


Expected Results:  
The search should display a Bug List with the search results.

Comment 1

[Lance Larsh]

Attachment: Proposed patch against CVS bugzilla

This patch is a possible fix for the problem, but since I'm not that familiar
with this code, I don't know if this is the right way to fix it.

I've only tested this against MySQL 4.1.13, and it appears to work.

Comment 2

[Max Kanat-Alexander]

Comment on attachment 193983 [details] [diff] [review]
Proposed patch against CVS bugzilla

Eek. That HAVING clause is pretty crazy, too... I wonder if that works at all
on PostgreSQL.

Thanks for the excellent bug report and patch, by the way.

Joel knows this code a bit better than I do, I think he should be the one to
look at it.

(For details on who to ask for review, see
http://www.bugzilla.org/docs/reviewer-list.html )

Comment 3

[Joel Peshkin]

Comment on attachment 193983 [details] [diff] [review]
Proposed patch against CVS bugzilla

Logic is fine, but the patch fails runtests (tabs). Fix the tabs and this can
land.

The percentage query needs a lot of rework.  The HAVING needs to go away. 
But... that is another bug.

Comment 4

[Lance Larsh]

Attachment: Patch without tabs

This patch is the same attacment #193983, but without tabs.  (Sorry about the
tabs- thought I had checked for them already!)

Comment 5

[Frédéric Buclin]

The updated patch still has tabs. I removed them on checkin as runtests.pl was
still complaining.

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