Bug 3315

Summary: Aggregate SQL clauses break pagination in guided reports
Product: Koha Reporter: Chris Cormack <chris>
Component: ReportsAssignee: Galen Charlton <gmcharlt>
Status: CLOSED DUPLICATE QA Contact: Bugs List <koha-bugs>
Severity: major    
Priority: P5 - low CC: jwagner, katrin.fischer
Version: master   
Hardware: PC   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:

Description Chris Cormack 2010-05-21 01:09:02 UTC

---- Reported by rch@liblime.com 2009-06-08 21:46:13 ----

guided reports tries to do a count(*) on sql results to determine if it needs to paginate the output for the screen.
If the saved query includes an aggregate clause, most notably GROUP BY, it counts incorrectly.

See Garry Collum's comment on http://bugs.koha.org/cgi-bin/bugzilla3/show_bug.cgi?id=2982 .

I suggest a short term fix would be to test for aggregate clause in the sql, and if there is one, report to the template that we don't know how many results there are, so only show the first page.
Force the user to download the csv if they want the complete results set until a more robust solution is found.

---- Additional Comments From kdr@ckls.org 2009-07-01 16:38:30 ----

Although the work-around is valid and is a down and dirty solution this is something that NEEDS to be addressed. As it is it is defintely NOT librarian-friendly, especially in libraries where the staff (often one person) are technologically-challenged.

Although the idea of merely using .csv sounds easy to many of us, there are many librarians that do not know what the heck .csv is, let alone how to work with it in this context.

The goal is to make Koha more user-friendly. Group by, etc., are important to some of the reports needed, so the results need to be displayed correctly to the user.

--- Bug imported by chris@bigballofwax.co.nz 2010-05-21 01:09 UTC  ---

This bug was previously known as _bug_ 3315 at http://bugs.koha.org/cgi-bin/bugzilla3/show_bug.cgi?id=3315
This bug blocked bug(s) 2982.

Actual time not defined. Setting to 0.0
The original reporter of this bug does not have
   an account here. Reassigning to the person who moved
   it here: chris@bigballofwax.co.nz.
   Previous reporter was rch@liblime.com.
CC member kdr@ckls.org does not have an account here

Comment 1 Katrin Fischer 2011-03-02 11:33:54 UTC
There is no pagination. There is no message showing that there are more results than showing on the screen. 

The csv export is not comfortable and missing the links to borrower accounts and other Koha pages used in the report.
Comment 2 Katrin Fischer 2011-03-28 10:22:47 UTC
I think I found the place in the code, but I get lost trying to understand it:

 487         my $total = select_2_select_count_value($sql) || 0;

 402 sub select_2_select_count_value ($) {
 403     my $sql = shift or return;
 404     my $countsql = select_2_select_count($sql);
 405     $debug and warn "original query: $sql\ncount query: $countsql\n";
 406     my $sth1 = C4::Context->dbh->prepare($countsql);
 407     $sth1->execute();
 408     my $total = $sth1->fetchrow();
 409     $debug and warn "total records for this query: $total\n";
 410     return $total;
 411 }
 412 sub select_2_select_count ($) {
 413     # Modify the query passed in to create a count query... (I think this covers all cases -crn)
 414     my ($sql) = strip_limit(shift) or return;
 415     $sql =~ s/\bSELECT\W+(?:\w+\W+){1,}?FROM\b|\bSELECT\W\*\WFROM\b/SELECT count(*) FROM /ig;
 416     return $sql;
 417 }

Is there perhaps a better way to find out the number of results of a sql query?
Comment 3 Katrin Fischer 2011-04-07 15:13:17 UTC

*** This bug has been marked as a duplicate of bug 6099 ***