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

8 files changed, 79 insertions, 16 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);