summaryrefslogtreecommitdiffstats
path: root/Bugzilla
diff options
context:
space:
mode:
Diffstat (limited to 'Bugzilla')
-rwxr-xr-xBugzilla/Bug.pm15
-rw-r--r--Bugzilla/Chart.pm3
-rw-r--r--Bugzilla/DB.pm30
-rw-r--r--Bugzilla/DB/Mysql.pm11
-rw-r--r--Bugzilla/Flag.pm7
-rw-r--r--Bugzilla/FlagType.pm7
-rw-r--r--Bugzilla/Search.pm18
-rw-r--r--Bugzilla/Series.pm4
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);
generated by cgit v1.2.3-24-g4f1b (git 2.32.0) at 2024-11-22 04:48:26 +0100