From b99cbd1d893ff0a730ab7187f409bcdf3c6f4aeb Mon Sep 17 00:00:00 2001 From: "mkanat%kerio.com" <> Date: Wed, 16 Mar 2005 08:27:14 +0000 Subject: Bug 174295: ANSI SQL requires all columns in SELECT to be in GROUP BY, unless they are in "aggregate" functions Patch By Tomas Kopal r=joel, a=myk --- Bugzilla/Bug.pm | 15 +++++++++++---- Bugzilla/Chart.pm | 3 ++- Bugzilla/DB.pm | 30 +++++++++++++++++++++++++++--- Bugzilla/DB/Mysql.pm | 11 +++++++++++ Bugzilla/Flag.pm | 7 +++++-- Bugzilla/FlagType.pm | 7 +++++-- Bugzilla/Search.pm | 18 +++++++++++++++--- Bugzilla/Series.pm | 4 +++- buglist.cgi | 10 +++++----- describekeywords.cgi | 7 +++++-- editclassifications.cgi | 11 +++++++---- editcomponents.cgi | 21 ++++++++++++--------- editkeywords.cgi | 7 +++++-- editmilestones.cgi | 5 +++-- editproducts.cgi | 47 ++++++++++++++++++++++++++++------------------- editusers.cgi | 7 +++---- request.cgi | 11 +++++++++-- sanitycheck.cgi | 6 ++++-- summarize_time.cgi | 10 ++++++---- 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. All other methods have prefix C. $text = the text to search (scalar) Returns: formatted SQL for substring search (scalar) +=item C + + 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 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 "\n"; print " \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 "\n\n"; print " \n"; print " \n\n"; print " \n"; print "
Edit products of ...
Bugs:"; - 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 "
Bugs:"; - 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}); -- cgit v1.2.3-24-g4f1b