From af6131f5c4f5c6a2817a0b6ec1412868540aec44 Mon Sep 17 00:00:00 2001 From: Paul Poulain Date: Wed, 6 Apr 2011 18:21:27 +0200 Subject: [PATCH] BZ6099: error in report pagination when GROUP BY the calculation of the size of a query was wrong when GROUP BY was used. This patch update the calculation of the size. Performance is not good, but calculation is correct ! --- C4/Reports/Guided.pm | 34 ++++++++++------------------------ reports/guided_reports.pl | 2 +- 2 files changed, 11 insertions(+), 25 deletions(-) diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm index 7231457..3ee3626 100644 --- a/C4/Reports/Guided.pm +++ b/C4/Reports/Guided.pm @@ -43,7 +43,7 @@ BEGIN { save_report get_saved_reports execute_query get_saved_report create_compound run_compound get_column_type get_distinct_values save_dictionary get_from_dictionary delete_definition delete_report format_results get_sql - select_2_select_count_value update_sql + nb_rows update_sql ); } @@ -373,6 +373,15 @@ sub get_criteria { return ( \@criteria_array ); } +sub nb_rows($) { + my $sql = shift or return; + my $sth = C4::Context->dbh->prepare($sql); + $sth->execute(); + while ($sth->fetchrow()) { + } + return $sth->rows; +} + =item execute_query ($results, $total, $error) = execute_query($sql, $offset, $limit) @@ -399,29 +408,6 @@ the user in a user-supplied SQL query WILL apply in any case. # ~ remove any LIMIT clause # ~ repace SELECT clause w/ SELECT count(*) -sub select_2_select_count_value ($) { - my $sql = shift or return; - my $countsql = select_2_select_count($sql); - $debug and warn "original query: $sql\ncount query: $countsql\n"; - my $sth1 = C4::Context->dbh->prepare($countsql); - $sth1->execute(); - my $total = $sth1->fetchrow(); - $debug and warn "total records for this query: $total\n"; - return $total; -} -sub select_2_select_count ($) { - # Modify the query passed in to create a count query... (I think this covers all cases -crn) - my ($sql) = strip_limit(shift) or return; - $sql =~ s/\bSELECT\W+(?:\w+\W+){1,}?FROM\b|\bSELECT\W\*\WFROM\b/SELECT count(*) FROM /ig; - return $sql; -} -sub strip_limit ($) { - my $sql = shift or return; - ($sql =~ /\bLIMIT\b/i) or return ($sql, 0, undef); - $sql =~ s/\bLIMIT\b\s*(\d+)(\s*\,\s*(\d+))?\s*/ /ig; - return ($sql, (defined $2 ? $1 : 0), (defined $3 ? $3 : $1)); # offset can default to 0, LIMIT cannot! -} - sub execute_query ($;$$$) { my ( $sql, $offset, $limit, $no_count ) = @_; diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index a53712e..746174c 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -484,7 +484,7 @@ elsif ($phase eq 'Run this report'){ $sql =~ s/<<$split[$i*2+1]>>/$quoted/; } my ($sth, $errors) = execute_query($sql, $offset, $limit); - my $total = select_2_select_count_value($sql) || 0; + my $total = nb_rows($sql) || 0; unless ($sth) { die "execute_query failed to return sth for report $report: $sql"; } else { -- 1.7.1