Bugzilla – Attachment 130081 Details for
Bug 28977
Most-circulated items (cat_issues_top.pl) is failing with SQL Mode ONLY_FULL_GROUP_BY
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 28977: Fix cat_issues_top.pl report with strict SQL modes
Bug-28977-Fix-catissuestoppl-report-with-strict-SQ.patch (text/plain), 3.20 KB, created by
Jonathan Druart
on 2022-02-02 10:47:46 UTC
(
hide
)
Description:
Bug 28977: Fix cat_issues_top.pl report with strict SQL modes
Filename:
MIME Type:
Creator:
Jonathan Druart
Created:
2022-02-02 10:47:46 UTC
Size:
3.20 KB
patch
obsolete
>From 982a9aaed426b951e7a456d4418a22f8e1a19797 Mon Sep 17 00:00:00 2001 >From: Michael Hafen <michael.hafen@washk12.org> >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 <strict_sql_modes> 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 <david@davidnind.com> > >JD Amended patch - adjust commit's title > >Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> >--- > 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 886fecfa6f3..23666f314ca 100755 >--- a/reports/cat_issues_top.pl >+++ b/reports/cat_issues_top.pl >@@ -295,10 +295,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) >@@ -341,7 +338,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.25.1
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 28977
:
124664
|
125124
|
125208
|
128684
|
129510
|
129982
| 130081