[Koha-patches] [PATCH] BZ6099: error in report pagination when GROUP BY

[paul.poulain at biblibre.com]

From: Paul Poulain <paul.poulain at biblibre.com>

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