From 666d1f4bf2c3fe31dba8cdcac742c5fc4ddeb986 Mon Sep 17 00:00:00 2001
From: "mkanat%kerio.com" <>
Date: Sat, 16 Apr 2005 07:37:23 +0000
Subject: Bug 286360: ANSI SQL does not allow aliases to be used in HAVING
 clause Patch By Tomas Kopal <Tomas.Kopal@altap.cz> r=myk, r=joel, a=myk

---
 Bugzilla/Search.pm | 23 ++++++++++++++---------
 1 file changed, 14 insertions(+), 9 deletions(-)

(limited to 'Bugzilla/Search.pm')

diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm
index 0283b7f76..bebbfd94a 100644
--- a/Bugzilla/Search.pm
+++ b/Bugzilla/Search.pm
@@ -600,8 +600,8 @@ sub init {
              # (see http://bugzilla.mozilla.org/show_bug.cgi?id=145588#c35).
              my $select_term =
                "(SUM($term1)/COUNT($term1) + $term2) AS relevance";
-
-             # add the column not used in aggregate function explicitly
+
+             # add the column not used in aggregate function explicitly
              push(@groupby, 'bugs.short_desc');
 
              # Users can specify to display the relevance field, in which case
@@ -726,10 +726,15 @@ sub init {
                  my $table = "longdescs_$chartid";
                  push(@supptables, "INNER JOIN longdescs AS $table " .
                                    "ON $table.bug_id = bugs.bug_id");
-                 my $field = "(100*((SUM($table.work_time)*COUNT(DISTINCT $table.bug_when)/COUNT(bugs.bug_id))/((SUM($table.work_time)*COUNT(DISTINCT $table.bug_when)/COUNT(bugs.bug_id))+bugs.remaining_time))) AS percentage_complete_$table";
-                 push(@fields, $field);
-                 push(@having, 
-                      "percentage_complete_$table $oper " . &::SqlQuote($v));
+                 my $expression = "(100 * ((SUM($table.work_time) *
+                                             COUNT(DISTINCT $table.bug_when) /
+                                             COUNT(bugs.bug_id)) /
+                                            ((SUM($table.work_time) *
+                                              COUNT(DISTINCT $table.bug_when) /
+                                              COUNT(bugs.bug_id)) +
+                                             bugs.remaining_time)))";
+                 push(@having, "$expression $oper " . &::SqlQuote($v));
+                 push(@groupby, "bugs.remaining_time");
              }
              $term = "0=0";
          },
@@ -814,9 +819,9 @@ sub init {
              # If the numbers are the same, all flags match the condition,
              # so this bug should be included.
              if ($t =~ m/not/) {
-                push(@fields, "SUM(CASE WHEN $ff IS NOT NULL THEN 1 ELSE 0 END) AS allflags_$chartid");
-                push(@fields, "SUM(CASE WHEN $term THEN 1 ELSE 0 END) AS matchingflags_$chartid");
-                push(@having, "allflags_$chartid = matchingflags_$chartid");
+                push(@having,
+                     "SUM(CASE WHEN $ff IS NOT NULL THEN 1 ELSE 0 END) = " .
+                     "SUM(CASE WHEN $term THEN 1 ELSE 0 END)");
                 $term = "0=0";
              }
          },
-- 
cgit v1.2.3-24-g4f1b