Bug 174295: ANSI SQL requires all columns in SELECT to be in GROUP BY, unless they are in "aggregate" functions

Patch By Tomas Kopal <Tomas.Kopal@altap.cz> r=joel, a=myk

19 files changed, 166 insertions, 71 deletions

diff --git a/Bugzilla/Bug.pm b/Bugzilla/Bug.pm
index 6c444a013..31b8f4944 100755
--- a/Bugzilla/Bug.pm
+++ b/Bugzilla/Bug.pm
@@ -166,8 +166,15 @@ sub initBug  {
     WHERE bugs.bug_id = ?
       AND classifications.id = products.classification_id
       AND products.id = bugs.product_id
-      AND components.id = bugs.component_id
-    GROUP BY bugs.bug_id";
+      AND components.id = bugs.component_id " .
+    $dbh->sql_group_by('bugs.bug_id', 'alias, products.classification_id,
+      classifications.name, bugs.product_id, products.name, version,
+      rep_platform, op_sys, bug_status, resolution, priority,
+      bug_severity, bugs.component_id, components.name, assigned_to,
+      reporter, bug_file_loc, short_desc, target_milestone,
+      qa_contact, status_whiteboard, creation_ts, 
+      delta_ts, reporter_accessible, cclist_accessible,
+      estimated_time, remaining_time, deadline');
 
   my $bug_sth = $dbh->prepare($query);
   $bug_sth->execute($bug_id);
@@ -717,12 +724,12 @@ sub CountOpenDependencies {
     my $dbh = Bugzilla->dbh;
 
     my $sth = $dbh->prepare(
-          "SELECT blocked, count(bug_status) " .
+          "SELECT blocked, COUNT(bug_status) " .
             "FROM bugs, dependencies " .
            "WHERE blocked IN (" . (join "," , @bug_list) . ") " .
              "AND bug_id = dependson " .
              "AND bug_status IN ('" . (join "','", &::OpenStates())  . "') " .
-        "GROUP BY blocked ");
+          $dbh->sql_group_by('blocked'));
     $sth->execute();
 
     while (my ($bug_id, $dependencies) = $sth->fetchrow_array()) {
diff --git a/Bugzilla/Chart.pm b/Bugzilla/Chart.pm
index 6dabea5a3..beb5a9b9d 100644
--- a/Bugzilla/Chart.pm
+++ b/Bugzilla/Chart.pm
@@ -320,7 +320,8 @@ sub getVisibleSeries {
                         "    AND cgm.group_id NOT IN($grouplist) " .
                         "WHERE creator = " . Bugzilla->user->id . " OR " .
                         "      cgm.category_id IS NULL " . 
-                        "GROUP BY series_id");
+                   $dbh->sql_group_by('series_id', 'cc1.name, cc2.name, ' .
+                                      'series.name'));
     foreach my $series (@$serieses) {
         my ($cat, $subcat, $name, $series_id) = @$series;
         $cats{$cat}{$subcat}{$name} = $series_id;
diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm
index 098d10ba1..0d41bbd01 100644
--- a/Bugzilla/DB.pm
+++ b/Bugzilla/DB.pm
@@ -213,9 +213,14 @@ sub sql_position {
     return "POSITION($fragment IN $text)";
 }
 
-#####################################################################
-# General Info Methods
-#####################################################################
+sub sql_group_by {
+    my ($self, $needed_columns, $optional_columns) = @_;
+
+    my $expression = "GROUP BY $needed_columns";
+    $expression .= ", " . $optional_columns if defined($optional_columns);
+    
+    return $expression;
+}
 
 sub sql_string_concat {
     my ($self, @params) = @_;
@@ -246,6 +251,10 @@ sub sql_fulltext_search {
            "%${quote}) THEN 1 ELSE 0 END";
 }
 
+#####################################################################
+# General Info Methods
+#####################################################################
+
 # XXX - Needs to be documented.
 sub bz_server_version {
     my ($self) = @_;
@@ -786,6 +795,21 @@ formatted SQL command have prefix C<sql_>. All other methods have prefix C<bz_>.
               $text = the text to search (scalar)
  Returns:     formatted SQL for substring search (scalar)
 
+=item C<sql_group_by>
+
+ Description: Outputs proper SQL syntax for grouping the result of a query.
+              For ANSI SQL databases, we need to group by all columns we are
+              querying for (except for columns used in aggregate functions).
+              Some databases require (or even allow) to specify only one
+              or few columns if the result is uniquely defined. For those
+              databases, the default implementation needs to be overloaded.
+ Params:      $needed_columns = string with comma separated list of columns
+              we need to group by to get expected result (scalar)
+              $optional_columns = string with comma separated list of all
+              other columns we are querying for, but which are not in the
+              required list.
+ Returns:     formatted SQL for row grouping (scalar)
+
 =item C<sql_string_concat>
 
  Description: Returns SQL syntax for concatenating multiple strings (constants
diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm
index 33354cb44..7b2a7b2c2 100644
--- a/Bugzilla/DB/Mysql.pm
+++ b/Bugzilla/DB/Mysql.pm
@@ -143,6 +143,17 @@ sub sql_position {
     }
 }
 
+sub sql_group_by {
+    my ($self, $needed_columns, $optional_columns) = @_;
+
+    # MySQL allows to specify all columns as ANSI SQL requires, but also
+    # allow you to specify just minimal subset to get unique result.
+    # According to MySQL documentation, the less columns you specify
+    # the faster the query runs.
+    return "GROUP BY $needed_columns";
+}
+
+
 sub bz_lock_tables {
     my ($self, @tables) = @_;
 
diff --git a/Bugzilla/Flag.pm b/Bugzilla/Flag.pm
index ea60eebe4..7245edbfa 100644
--- a/Bugzilla/Flag.pm
+++ b/Bugzilla/Flag.pm
@@ -567,6 +567,8 @@ sub GetBug {
     # Returns a hash of information about a target bug.
     my ($id) = @_;
 
+    my $dbh = Bugzilla->dbh;
+
     # Save the currently running query (if any) so we do not overwrite it.
     &::PushGlobalSQLState();
 
@@ -574,8 +576,9 @@ sub GetBug {
                           COUNT(bug_group_map.group_id)
                 FROM      bugs LEFT JOIN bug_group_map
                             ON (bugs.bug_id = bug_group_map.bug_id)
-                WHERE     bugs.bug_id = $id
-                GROUP BY  bugs.bug_id");
+                WHERE     bugs.bug_id = $id " .
+                $dbh->sql_group_by('bugs.bug_id',
+                                   'short_desc, product_id, component_id'));
 
     my $bug = { 'id' => $id };
     
diff --git a/Bugzilla/FlagType.pm b/Bugzilla/FlagType.pm
index 5b681dc0c..8a6eb0272 100644
--- a/Bugzilla/FlagType.pm
+++ b/Bugzilla/FlagType.pm
@@ -120,7 +120,8 @@ sub match {
     my @tables = @base_tables;
     my @columns = @base_columns;
     my $having = "";
-    
+    my $dbh = Bugzilla->dbh;
+
     # Include a count of the number of flags per type if requested.
     if ($include_count) { 
         push(@columns, "COUNT(flags.id)");
@@ -136,7 +137,9 @@ sub match {
     my $where_clause = "WHERE " . join(" AND ", @criteria);
     
     my $query = "$select_clause $from_clause $where_clause";
-    $query .= " GROUP BY flagtypes.id " if ($include_count || $having ne "");
+    $query .= " " . $dbh->sql_group_by('flagtypes.id',
+              join(', ', @base_columns[2..$#base_columns]))
+                    if ($include_count || $having ne "");
     $query .= " HAVING $having " if $having ne "";
     $query .= " ORDER BY flagtypes.sortkey, flagtypes.name";
     
diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm
index af8df0ab2..3aba68ad9 100644
--- a/Bugzilla/Search.pm
+++ b/Bugzilla/Search.pm
@@ -104,6 +104,7 @@ sub init {
     my @supptables;
     my @wherepart;
     my @having;
+    my @groupby;
     @fields = @$fieldsref if $fieldsref;
     my @specialchart;
     my @andlist;
@@ -597,6 +598,9 @@ 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

+             push(@groupby, 'bugs.short_desc');
 
              # Users can specify to display the relevance field, in which case
              # it'll show up in the list of fields being selected, and we need
@@ -1302,8 +1306,6 @@ sub init {
         if ($specialorderjoin{$splitfield[0]}) {
             push(@supptables, $specialorderjoin{$splitfield[0]});
         }
-        # FIXME: Some DBs require ORDER BY items to also
-        # be in GROUP BY.
     }
 
     my %suppseen = ("bugs" => 1);
@@ -1358,7 +1360,17 @@ sub init {
         }
     }
 
-    $query .= ") GROUP BY bugs.bug_id";
+    foreach my $field (@fields) {
+        next if ($field =~ /(AVG|SUM|COUNT|MAX|MIN|VARIANCE)\s*\(/i ||
+                 $field =~ /^\d+$/ || $field eq "bugs.bug_id");
+        if ($field =~ /.*AS\s+(\w+)$/i) {
+            push(@groupby, $1) if !grep($_ eq $1, @groupby);
+        } else {
+            push(@groupby, $field) if !grep($_ eq $field, @groupby);
+        }
+    }
+    $query .= ") " . $dbh->sql_group_by("bugs.bug_id", join(', ', @groupby));
+
 
     if (@having) {
         $query .= " HAVING " . join(" AND ", @having);
diff --git a/Bugzilla/Series.pm b/Bugzilla/Series.pm
index 3d4f301b9..e1d37423b 100644
--- a/Bugzilla/Series.pm
+++ b/Bugzilla/Series.pm
@@ -106,7 +106,9 @@ sub initFromDatabase {
         "WHERE series.series_id = $series_id AND " .
         "(public = 1 OR creator = " . Bugzilla->user->id . " OR " .
         "(ugm.group_id IS NOT NULL)) " . 
-        "GROUP BY series_id");
+        $dbh->sql_group_by('series.series_id', 'cc1.name, cc2.name, ' .
+                           'series.name, series.creator, series.frequency, ' .
+                           'series.query, series.public'));
     
     if (@series) {
         $self->initFromParameters(@series);
diff --git a/buglist.cgi b/buglist.cgi
index c401c43c2..d6567852b 100755
--- a/buglist.cgi
+++ b/buglist.cgi
@@ -475,9 +475,9 @@ DefineColumn("assigned_to"       , "map_assigned_to.login_name" , "Assignee"
 DefineColumn("reporter"          , "map_reporter.login_name"    , "Reporter"         );
 DefineColumn("qa_contact"        , "map_qa_contact.login_name"  , "QA Contact"       );
 if ($format->{'extension'} eq 'html') {
-    DefineColumn("assigned_to_realname", "CASE WHEN map_assigned_to.realname = '' THEN map_assigned_to.login_name ELSE map_assigned_to.realname END", "Assignee"  );
-    DefineColumn("reporter_realname"   , "CASE WHEN map_reporter.realname    = '' THEN map_reporter.login_name    ELSE map_reporter.realname    END", "Reporter"  );
-    DefineColumn("qa_contact_realname" , "CASE WHEN map_qa_contact.realname  = '' THEN map_qa_contact.login_name  ELSE map_qa_contact.realname  END", "QA Contact");
+    DefineColumn("assigned_to_realname", "CASE WHEN map_assigned_to.realname = '' THEN map_assigned_to.login_name ELSE map_assigned_to.realname END AS assigned_to_realname", "Assignee"  );
+    DefineColumn("reporter_realname"   , "CASE WHEN map_reporter.realname    = '' THEN map_reporter.login_name    ELSE map_reporter.realname    END AS reporter_realname"   , "Reporter"  );
+    DefineColumn("qa_contact_realname" , "CASE WHEN map_qa_contact.realname  = '' THEN map_qa_contact.login_name  ELSE map_qa_contact.realname  END AS qa_contact_realname" , "QA Contact");
 } else {
     DefineColumn("assigned_to_realname", "map_assigned_to.realname" , "Assignee"         );
     DefineColumn("reporter_realname"   , "map_reporter.realname"    , "Reporter"         );
@@ -501,7 +501,7 @@ DefineColumn("remaining_time"    , "bugs.remaining_time"        , "Remaining Hou
 DefineColumn("actual_time"       , "(SUM(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id)) AS actual_time", "Actual Hours");
 DefineColumn("percentage_complete","(100*((SUM(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id))/((SUM(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id))+bugs.remaining_time))) AS percentage_complete", "% Complete"); 
 DefineColumn("relevance"         , "relevance"                  , "Relevance"        );
-DefineColumn("deadline"          , $dbh->sql_date_format('bugs.deadline', '%Y-%m-%d'), "Deadline");
+DefineColumn("deadline"          , $dbh->sql_date_format('bugs.deadline', '%Y-%m-%d') . " AS deadline", "Deadline");
 
 ################################################################################
 # Display Column Determination
@@ -881,7 +881,7 @@ if (@bugidlist) {
             "AND group_control_map.group_id=bug_group_map.group_id " .
             "WHERE bugs.bug_id = bug_group_map.bug_id " .
             "AND bugs.bug_id IN (" . join(',',@bugidlist) . ") " .
-            "GROUP BY bugs.bug_id");
+            $dbh->sql_group_by('bugs.bug_id'));
     $sth->execute();
     while (my ($bug_id, $min_membercontrol) = $sth->fetchrow_array()) {
         $min_membercontrol{$bug_id} = $min_membercontrol;
diff --git a/describekeywords.cgi b/describekeywords.cgi
index dd25c40d6..e7b3b759b 100755
--- a/describekeywords.cgi
+++ b/describekeywords.cgi
@@ -35,11 +35,14 @@ use vars qw($vars $template);
 Bugzilla->login();
 
 my $cgi = Bugzilla->cgi;
+my $dbh = Bugzilla->dbh;
 
 SendSQL("SELECT keyworddefs.name, keyworddefs.description, 
                 COUNT(keywords.bug_id)
-         FROM keyworddefs LEFT JOIN keywords ON keyworddefs.id=keywords.keywordid
-         GROUP BY keyworddefs.id
+         FROM keyworddefs LEFT JOIN keywords
+         ON keyworddefs.id = keywords.keywordid " .
+         $dbh->sql_group_by('keyworddefs.id',
+                            'keyworddefs.name, keyworddefs.description') . "
          ORDER BY keyworddefs.name");
 
 my @keywords;
diff --git a/editclassifications.cgi b/editclassifications.cgi
index 1a1e48026..0e8ffde99 100755
--- a/editclassifications.cgi
+++ b/editclassifications.cgi
@@ -111,12 +111,15 @@ unless ($action) {
     #   - must use "group by classifications.id" instead of
     #     products.classification_id. Otherwise it won't look for all
     #     classification ids, just the ones used by the products.
-    my $sth = $dbh->prepare("SELECT classifications.id,classifications.name,
+    my $sth = $dbh->prepare("SELECT classifications.id, classifications.name,
                                     classifications.description,
-                                    COUNT(classification_id) as total
+                                    COUNT(classification_id) AS total
                              FROM classifications
-                             LEFT JOIN products ON classifications.id=products.classification_id
-                             GROUP BY classifications.id
+                             LEFT JOIN products
+                             ON classifications.id = products.classification_id
+                            " . $dbh->sql_group_by('classifications.id',
+                                         'classifications.name,
+                                          classifications.description') . "
                              ORDER BY name");
     $sth->execute();
     while (my ($id,$classification,$description,$total) = $sth->fetchrow_array()) {
diff --git a/editcomponents.cgi b/editcomponents.cgi
index a8d1f1f90..9a1e7934e 100755
--- a/editcomponents.cgi
+++ b/editcomponents.cgi
@@ -139,8 +139,9 @@ unless ($product) {
 
     if ($showbugcounts){
         SendSQL("SELECT products.name, products.description, COUNT(bug_id)
-                 FROM products LEFT JOIN bugs ON products.id = bugs.product_id
-                 GROUP BY products.name
+                 FROM products LEFT JOIN bugs
+                   ON products.id = bugs.product_id " .
+                $dbh->sql_group_by('products.name', 'products.description') . "
                  ORDER BY products.name");
     } else {
         SendSQL("SELECT products.name, products.description
@@ -184,17 +185,19 @@ unless ($action) {
     my @components = ();
 
     if ($showbugcounts) {
-        SendSQL("SELECT name,description, initialowner,
+        SendSQL("SELECT name, description, initialowner,
                         initialqacontact, COUNT(bug_id)
-                 FROM components LEFT JOIN bugs ON 
-                      components.id = bugs.component_id
-                 WHERE components.product_id = $product_id
-                 GROUP BY name");
+                 FROM components LEFT JOIN bugs
+                   ON components.id = bugs.component_id
+                 WHERE components.product_id = $product_id " .
+                $dbh->sql_group_by('name',
+                    'description, initialowner, initialqacontact'));
     } else {
         SendSQL("SELECT name, description, initialowner, initialqacontact
                  FROM components 
-                 WHERE product_id = $product_id
-                 GROUP BY name");
+                 WHERE product_id = $product_id " .
+                $dbh->sql_group_by('name',
+                    'description, initialowner, initialqacontact'));
     }        
 
     while (MoreSQLData()) {
diff --git a/editkeywords.cgi b/editkeywords.cgi
index f5fcf6a57..8ad74710e 100755
--- a/editkeywords.cgi
+++ b/editkeywords.cgi
@@ -30,6 +30,7 @@ use Bugzilla::Config qw(:DEFAULT $datadir);
 use Bugzilla::User;
 
 my $cgi = Bugzilla->cgi;
+my $dbh = Bugzilla->dbh;
 
 use vars qw($template $vars);
 
@@ -70,8 +71,10 @@ if ($action eq "") {
 
     SendSQL("SELECT keyworddefs.id, keyworddefs.name, keyworddefs.description,
                     COUNT(keywords.bug_id)
-             FROM keyworddefs LEFT JOIN keywords ON keyworddefs.id = keywords.keywordid
-             GROUP BY keyworddefs.id
+             FROM keyworddefs LEFT JOIN keywords
+               ON keyworddefs.id = keywords.keywordid " .
+             $dbh->sql_group_by('keyworddefs.id',
+                    'keyworddefs.name, keyworddefs.description') . "
              ORDER BY keyworddefs.name");
 
     while (MoreSQLData()) {
diff --git a/editmilestones.cgi b/editmilestones.cgi
index e62dcc4cb..0edbd1897 100755
--- a/editmilestones.cgi
+++ b/editmilestones.cgi
@@ -310,8 +310,9 @@ if ($action eq 'del') {
     my $dbh = Bugzilla->dbh;
 
     my $sth = $dbh->prepare('SELECT count(bug_id), product_id, target_milestone
-                             FROM bugs
-                             GROUP BY product_id, target_milestone
+                             FROM bugs ' .
+                            $dbh->sql_group_by('product_id,
+                                                target_milestone') . '
                              HAVING product_id = ?
                                 AND target_milestone = ?');
 
diff --git a/editproducts.cgi b/editproducts.cgi
index aff79871c..823ec92c2 100755
--- a/editproducts.cgi
+++ b/editproducts.cgi
@@ -297,10 +297,14 @@ if (Param('useclassification')) {
     unless ($classification) {
         PutHeader("Select classification");
 
-        SendSQL("SELECT classifications.name,classifications.description,COUNT(classification_id) as total
+        SendSQL("SELECT classifications.name, classifications.description,
+                        COUNT(classification_id) AS total
              FROM classifications
-             LEFT JOIN products ON classifications.id=products.classification_id
-             GROUP BY classifications.id
+             LEFT JOIN products
+               ON classifications.id = products.classification_id " .
+             $dbh->sql_group_by('classifications.id',
+                                'classifications.name,
+                                 classifications.description') . "
              ORDER BY name");
         print "<TABLE BORDER=1 CELLPADDING=4 CELLSPACING=0><TR BGCOLOR=\"#6666FF\">\n";
         print "  <TH ALIGN=\"left\">Edit products of ...</TH>\n";
@@ -361,7 +365,11 @@ unless ($action) {
              $classification);
     }
 
-    $query .= " GROUP BY products.name ORDER BY products.name";
+    $query .= " " . $dbh->sql_group_by('products.name',
+                                       'products.description, disallownew,
+                                        votesperuser, maxvotesperbug,
+                                        votestoconfirm');
+    $query .= " ORDER BY products.name";
 
     $vars->{'products'} = $dbh->selectall_arrayref($query,
                                                    {'Slice' => {}},
@@ -727,10 +735,10 @@ if ($action eq 'del') {
     print "</TD>\n</TR><TR>\n";
     print "  <TD VALIGN=\"top\">Bugs:</TD>\n";
     print "  <TD VALIGN=\"top\">";
-    SendSQL("SELECT count(bug_id),product_id
-             FROM bugs
-             GROUP BY product_id
-             HAVING product_id=$product_id");
+    SendSQL("SELECT count(bug_id), product_id
+             FROM bugs " .
+            $dbh->sql_group_by('product_id') . "
+             HAVING product_id = $product_id");
     my $bugs = FetchOneColumn();
     print $bugs || 'none';
 
@@ -978,10 +986,10 @@ if ($action eq 'edit') {
     print "</TD>\n</TR><TR>\n";
     print "  <TH ALIGN=\"right\">Bugs:</TH>\n";
     print "  <TD>";
-    SendSQL("SELECT count(bug_id),product_id
-             FROM bugs
-             GROUP BY product_id
-             HAVING product_id=$product_id");
+    SendSQL("SELECT count(bug_id), product_id
+             FROM bugs " .
+            $dbh->sql_group_by('product_id') . "
+             HAVING product_id = $product_id");
     my $bugs = '';
     $bugs = FetchOneColumn() if MoreSQLData();
     print $bugs || 'none';
@@ -1038,11 +1046,11 @@ if ($action eq 'updategroupcontrols') {
         if (@now_na) {
             SendSQL("SELECT groups.name, COUNT(bugs.bug_id) 
                      FROM bugs, bug_group_map, groups
-                     WHERE groups.id IN(" . join(',',@now_na) . ")
+                     WHERE groups.id IN(" . join(', ', @now_na) . ")
                      AND bug_group_map.group_id = groups.id
                      AND bug_group_map.bug_id = bugs.bug_id
-                     AND bugs.product_id = $product_id
-                     GROUP BY groups.name");
+                     AND bugs.product_id = $product_id " .
+                    $dbh->sql_group_by('groups.name'));
             while (MoreSQLData()) {
                 my ($groupname, $bugcount) = FetchSQLData();
                 my %g = ();
@@ -1059,10 +1067,10 @@ if ($action eq 'updategroupcontrols') {
                      LEFT JOIN bug_group_map
                      ON bug_group_map.group_id = groups.id
                      AND bug_group_map.bug_id = bugs.bug_id
-                     WHERE groups.id IN(" . join(',',@now_mandatory) . ")
+                     WHERE groups.id IN(" . join(', ', @now_mandatory) . ")
                      AND bugs.product_id = $product_id
-                     AND bug_group_map.bug_id IS NULL
-                     GROUP BY groups.name");
+                     AND bug_group_map.bug_id IS NULL " .
+                    $dbh->sql_group_by('groups.name'));
             while (MoreSQLData()) {
                 my ($groupname, $bugcount) = FetchSQLData();
                 my %g = ();
@@ -1487,7 +1495,8 @@ if ($action eq 'editgroupcontrols') {
             "WHERE isbuggroup != 0 " .
             "AND (isactive != 0 OR entry IS NOT NULL " .
             "OR bugs.bug_id IS NOT NULL) " .
-            "GROUP BY name");
+            $dbh->sql_group_by('name', 'id, entry, membercontrol,
+                                othercontrol, canedit, isactive'));
     my @groups = ();
     while (MoreSQLData()) {
         my %group = ();
diff --git a/editusers.cgi b/editusers.cgi
index 82ad50bcf..f72866982 100755
--- a/editusers.cgi
+++ b/editusers.cgi
@@ -682,6 +682,7 @@ sub userDataToVars {
     my $userid = shift;
     my $user = new Bugzilla::User($userid);
     my $query;
+    my $dbh = Bugzilla->dbh;
 
     $user->derive_groups();
 
@@ -717,8 +718,7 @@ sub userDataToVars {
                  AND directbless.user_id = ?
                  AND directbless.isbless = 1
                  AND directbless.grant_type = ?
-           GROUP BY id
-          },
+          } . $dbh->sql_group_by('id'),
         'id', undef,
         ($userid, GRANT_DIRECT,
          $userid, GRANT_REGEXP,
@@ -733,8 +733,7 @@ sub userDataToVars {
                   AND ggm.member_id = ugm.group_id
                   AND ugm.isbless = 0
                   AND ggm.grant_type = ?
-                GROUP BY id
-               };
+               } . $dbh->sql_group_by('id');
     foreach (@{$dbh->selectall_arrayref($query, undef, ($userid, GROUP_BLESS))}) {
         # Merge indirect bless permissions into permission variable.
         $vars->{'permissions'}{${$_}[0]}{'indirectbless'} = 1;
diff --git a/request.cgi b/request.cgi
index fa37344e2..7f3e6351c 100755
--- a/request.cgi
+++ b/request.cgi
@@ -206,8 +206,15 @@ sub queue {
     # Group the records by flag ID so we don't get multiple rows of data
     # for each flag.  This is only necessary because of the code that
     # removes flags on bugs the user is unauthorized to access.
-    $query .= " GROUP BY flags.id " . 
-              "HAVING cntuseringroups = cntbugingroups OR canseeanyway ";
+    $query .= ' ' . $dbh->sql_group_by('flags.id',
+               'flagtypes.name, flags.status, flags.bug_id, bugs.short_desc,
+                products.name, components.name, flags.attach_id,
+                attachments.description, requesters.realname,
+                requesters.login_name, requestees.realname,
+                requestees.login_name, flags.creation_date,
+                cclist_accessible, bugs.reporter, bugs.reporter_accessible,
+                bugs.assigned_to');
+    $query .= " HAVING cntuseringroups = cntbugingroups OR canseeanyway ";
 
     # Group the records, in other words order them by the group column
     # so the loop in the display template can break them up into separate
diff --git a/sanitycheck.cgi b/sanitycheck.cgi
index 6f85e6cfd..84f41c398 100755
--- a/sanitycheck.cgi
+++ b/sanitycheck.cgi
@@ -102,7 +102,8 @@ if (defined $cgi->param('rebuildvotecache')) {
     Status("OK, now rebuilding vote cache.");
     $dbh->bz_lock_tables('bugs WRITE', 'votes READ');
     SendSQL("UPDATE bugs SET votes = 0");
-    SendSQL("SELECT bug_id, SUM(vote_count) FROM votes GROUP BY bug_id");
+    SendSQL("SELECT bug_id, SUM(vote_count) FROM votes " .
+            $dbh->sql_group_by('bug_id'));
     my %votes;
     while (@row = FetchSQLData()) {
         my ($id, $v) = (@row);
@@ -482,7 +483,8 @@ while (@row = FetchSQLData()) {
 }
 
 Status("Checking cached vote counts");
-SendSQL("SELECT bug_id, SUM(vote_count) FROM votes GROUP BY bug_id");
+SendSQL("SELECT bug_id, SUM(vote_count) FROM votes " .
+        $dbh->sql_group_by('bug_id'));
 
 while (@row = FetchSQLData()) {
     my ($id, $v) = (@row);
diff --git a/summarize_time.cgi b/summarize_time.cgi
index 8992918cc..ab8a6bf50 100755
--- a/summarize_time.cgi
+++ b/summarize_time.cgi
@@ -236,8 +236,9 @@ sub query_work_by_buglist {
                WHERE  longdescs.bug_id IN ($buglist) AND 
                       longdescs.who = profiles.userid AND
                       bugs.bug_id = longdescs.bug_id 
-                      $date_bits
-               GROUP BY longdescs.bug_id, profiles.login_name
+                      $date_bits } .
+            $dbh->sql_group_by('longdescs.bug_id, profiles.login_name',
+                               'bugs.short_desc, bugs.bug_status') . qq{
                ORDER BY longdescs.bug_when};
     my $sth = $dbh->prepare($q);
     $sth->execute(@{$date_values});
@@ -314,8 +315,9 @@ sub get_inactive_bugs {
             FROM   longdescs, bugs
             WHERE  longdescs.bug_id IN ($buglist) AND 
                    bugs.bug_id = longdescs.bug_id 
-                   $date_bits
-            GROUP BY longdescs.bug_id
+                   $date_bits } .
+         $dbh->sql_group_by('longdescs.bug_id',
+                            'bugs.short_desc, bugs.bug_status') . qq{
             ORDER BY longdescs.bug_when};
     $sth = $dbh->prepare($q);
     $sth->execute(@{$date_values});