From 6ccbdce5c04e37d72f168c85d9aff05bfc95cea3 Mon Sep 17 00:00:00 2001 From: Michael Hafen Date: Fri, 14 Jan 2022 11:35:05 -0700 Subject: [PATCH] Bug 28977: Fix cat_issues_top.pl report with strict SQL modes To get the 'Most circulated items' report to run in ONLY_FULL_GROUP_BY Sql mode. Test plan: Turn on strict_sql_modes ( make sure is 1 in KOHA_CONF ) Go to Reports -> Most circulated items submit the form. Without the patch you get an error like: CGI::Compile::ROOT::usr_local_koha_master_reports_cat_issues_top_2epl::calculate(): DBI Exception: DBD::mysql::st execute failed: Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'koha_main_v4.items.itemcallnumber' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by [for Statement "SELECT DISTINCT biblio.title, COUNT(biblio.biblionumber) AS `RANK`, biblio.biblionumber AS ID, itemcallnumber as CALLNUM, ccode as CCODE, location as LOC FROM `old_issues` LEFT JOIN items USING(itemnumber) LEFT JOIN biblio USING(biblionumber) LEFT JOIN biblioitems USING(biblionumber) LEFT JOIN borrowers USING(borrowernumber) WHERE 1 AND old_issues.issuedate > '2020-08-03' AND old_issues.issuedate < '2021-05-17' AND old_issues.returndate > '2020-08-27' AND old_issues.returndate < '2021-06-01' AND old_issues.branchcode like '403' AND items.itype like 'F' AND ccode like 'FIC' AND borrowers.categorycode like 'ST' group by biblio.biblionumber order by `RANK` DESC"] at /usr/local/koha_master/reports/cat_issues_top.pl line 67 at /usr/share/perl5/DBIx/Class/Exception.pm line 77 With the patch you will see the report results view. Signed-off-by: David Nind JD Amended patch - adjust commit's title Signed-off-by: Jonathan Druart Signed-off-by: Fridolin Somers Signed-off-by: Kyle M Hall (cherry picked from commit 7745ef03ad64d95c9bdd8b2d9a7ed97f75eafd1a) Signed-off-by: Andrew Fuerste-Henry --- reports/cat_issues_top.pl | 7 ++----- 1 file changed, 2 insertions(+), 5 deletions(-) diff --git a/reports/cat_issues_top.pl b/reports/cat_issues_top.pl index a15a79bec6..113981af72 100755 --- a/reports/cat_issues_top.pl +++ b/reports/cat_issues_top.pl @@ -298,10 +298,7 @@ sub calculate { my $strcalc ; # Processing average loanperiods - $strcalc .= "SELECT DISTINCT biblio.title, COUNT(biblio.biblionumber) AS `RANK`, biblio.biblionumber AS ID"; - $strcalc .= ", itemcallnumber as CALLNUM"; - $strcalc .= ", ccode as CCODE"; - $strcalc .= ", location as LOC"; + $strcalc .= "SELECT biblio.title, COUNT(biblio.biblionumber) AS `RANK`, biblio.biblionumber AS ID"; $strcalc .= " , $colfield " if ($colfield); $strcalc .= " FROM `old_issues` LEFT JOIN items USING(itemnumber) @@ -344,7 +341,7 @@ sub calculate { @$filters[12]=~ s/\*/%/g if (@$filters[12]); $strcalc .= " AND year(old_issues.issuedate) like '" . @$filters[12] ."'" if ( @$filters[12] ); - $strcalc .= " group by biblio.biblionumber"; + $strcalc .= " group by biblio.biblionumber, biblio.title"; $strcalc .= ", $colfield" if ($column); $strcalc .= " order by `RANK` DESC"; $strcalc .= ", $colfield " if ($colfield); -- 2.39.5