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