Other places in the code where GROUP BY appears basically just branch and use a different syntax if you've said your DB is postgres. I assume this spot just got overlooked -- there's no reason the same thing can't be done here. --R Gary Whittles wrote: > Rgearding the GROUP BY clause: this is non-standard SQL, as according to > the standard every target field must either be aggregated or appear in > the GROUP BY clause. Allowing you to group on just a subset of fields > is a MySQL extension, hence the problem with Postgres and any other DB. > > Unfortunately, it's also by far the fastest way in MySQL. The only > standards-compliant way that gets close is by selecting > "pollquestions.qid, min(pollquestions.question), > min(pollquestions.whatever)". There is no need that I can see for doing > a "SELECT *" in any case, as you only get a well defined answer for the > pollquestions table anyway. "SELECT pollquestions.*" makes much more > sense. > > Anyway: the above hack is standards compliant, but the slight > performance hit might be a problem. If so then the Postgres maintainers > might like to know that Postgres has its own non-standard way of > handling this efficiently: > > SELECT DISTINCT ON (pollquestions.qid) pollquestions.* FROM .... > > Which is semantically identical to the MySQL GROUP BY. > > Can anyone think of a cross-platform way that doesn't have the > performance hit? Ideally there would be an aggregate function called > first(). > > Gaz > >> From: Rusty <rusty at kuro5hin.org> >> To: scoop-dev at lists.kuro5hin.org >> Subject: [Scoop-dev] poll bugs >> Date: Wed, 26 Jan 2005 14:04:15 -0500 > > >> Atathced is a patch for three poll bugs: >> >> * When posting a top-level comment to a poll, the poll doesn't appear >> in the "Replying to" field. To reproduce, open a non-attached poll, >> click "post a comment" and look at the "Replying to" area. It'll just >> say "0". >> >> * http://bugz.mostly-harmless.ca/show_bug.cgi?id=83 -- I don't seem to >> get this bug on my dev site, but I do elsewhere. Basically, posting a >> comment will cause a section_perm check in Elements.pm to fire when it >> shouldn't (poll perms are checked earlier). >> >> * Poll search -- go to search, choose polls, you'll get one result per >> answer for each poll. The postgres patches removed a "GROUP_BY" in the >> search query that prevented that, without replacing it with something >> that works on postgres and doesn't cause this bug. I replaced the >> group_by, because it works. If it doesn't work with postgres, then >> it's up to the postgres code to preserve mysql functionality (IMO). >> Index: lib/Scoop/Polls.pm >> =================================================================== >> RCS file: /cvs/scoop/scoop/lib/Scoop/Polls.pm,v >> retrieving revision 1.66 >> diff -u -r1.66 Polls.pm >> --- lib/Scoop/Polls.pm 5 Oct 2003 08:56:31 -0000 1.66 >> +++ lib/Scoop/Polls.pm 26 Jan 2005 18:48:24 -0000 >> @@ -71,7 +71,7 @@ >> >> my $polllist = ""; >> my ($rv, $sth) = $S->db_select({ >> - DEBUG => 0, >> + DEBUG => $DEBUG, >> WHAT => 'qid, question', >> FROM => 'pollquestions', >> }); >> @@ -216,7 +216,7 @@ >> # get and store the poll question and # of votes >> my ($question, $totalvotes, $actual_totalvotes); >> my ($rv, $sth) = $S->db_select({ >> - DEBUG => 0, >> + DEBUG => $DEBUG, >> FROM => 'pollquestions', >> WHAT => 'question, voters', >> WHERE => "qid='$qid'", >> @@ -233,7 +233,7 @@ >> $S->{UI}->{BLOCKS}->{subtitle} =~ s/<.*?>//g; >> >> ($rv, $sth) = $S->db_select({ >> - DEBUG => 0, >> + DEBUG => $DEBUG, >> FROM => 'pollanswers', >> WHAT => 'aid, answer, votes', >> WHERE => "qid='$qid'", >> Index: lib/Scoop/Search.pm >> =================================================================== >> RCS file: /cvs/scoop/scoop/lib/Scoop/Search.pm,v >> retrieving revision 1.71 >> diff -u -r1.71 Search.pm >> --- lib/Scoop/Search.pm 25 Jan 2005 17:15:24 -0000 1.71 >> +++ lib/Scoop/Search.pm 26 Jan 2005 18:48:25 -0000 >> @@ -753,6 +753,7 @@ >> $query->{WHAT} = qq|*, $date_format AS ftime|; >> $query->{FROM} = 'pollquestions,pollanswers'; >> $query->{WHERE} = qq|pollquestions.qid = pollanswers.qid AND >> ( pollquestions.question LIKE '%$args->{string}%' OR >> pollanswers.answer LIKE '%$args->{string}%' ) |; >> + $query->{GROUP_BY} = qq|pollquestions.qid|; >> $query->{ORDER_BY} = 'pollquestions.post_date desc'; >> >> } else { >> Index: lib/Scoop/Stories/Elements.pm >> =================================================================== >> RCS file: /cvs/scoop/scoop/lib/Scoop/Stories/Elements.pm,v >> retrieving revision 1.117 >> diff -u -r1.117 Elements.pm >> --- lib/Scoop/Stories/Elements.pm 25 Jan 2005 17:15:24 -0000 1.117 >> +++ lib/Scoop/Stories/Elements.pm 26 Jan 2005 18:48:25 -0000 >> @@ -25,12 +25,8 @@ >> >> my $rating_choice = $S->get_comment_option('ratingchoice'); >> >> - unless ($sid eq 'preview') { >> + unless ($sid eq 'preview' || $S->_does_poll_exist($sid)) { >> $stories = $S->story_data([$sid]); >> -# $stories = $S->getstories( >> -# {-type => 'fullstory', >> -# -sid => $sid, >> -# -perm_override => 1}); >> if ($stories) { >> $story = $stories->[0]; >> } else { >> @@ -47,7 +43,6 @@ >> my $page; >> if ( $S->_does_poll_exist($sid) == 1 ) { >> $page .= $S->display_poll($sid); >> - # warn "displaying poll $sid"; >> } else { >> # warn "getting story summary for $sid\n"; >> $page .= $S->story_summary($story); >> @@ -75,9 +70,9 @@ >> >> my $story_section = $story->{section} || >> $S->_get_story_section($sid); >> # check the section permissions >> - if ($S->have_section_perm('deny_read_stories', $story_section)) { >> + if ($S->have_section_perm('deny_read_stories', $story_section) && >> !$S->_does_poll_exist($sid)) { >> $page = qq| <b>%%norm_font%%Sorry, you don't have permission >> to read stories posted to this section.%%norm_font_end%%</b> |; >> - } elsif ($S->have_section_perm('hide_read_stories', $section)) { >> + } elsif ($S->have_section_perm('hide_read_stories', $section) && >> !$S->_does_poll_exist($sid)) { >> $page = qq| <b>%%norm_font%%Sorry, I can't seem to find that >> story.%%norm_font_end%%</b> |; >> } >> >> _______________________________________________ >> Scoop-dev mailing list >> Scoop-dev at lists.kuro5hin.org >> http://lists.kuro5hin.org/mailman/listinfo/scoop-dev > > > > _______________________________________________ > Scoop-dev mailing list > Scoop-dev at lists.kuro5hin.org > http://lists.kuro5hin.org/mailman/listinfo/scoop-dev