[Koha-bugs] [Bug 28977] Most-circulated items (cat_issues_top.pl) is failing with SQL Mode ONLY_FULL_GROUP_BY

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977

Jonathan Druart <jonathan.druart+koha at gmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
 Attachment #129982|0                           |1
        is obsolete|                            |

--- Comment #19 from Jonathan Druart <jonathan.druart+koha at gmail.com> ---
Created attachment 130081
  -->
https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=130081&action=edit
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 at davidnind.com>

JD Amended patch - adjust commit's title

Signed-off-by: Jonathan Druart <jonathan.druart at bugs.koha-community.org>

-- 
You are receiving this mail because:
You are watching all bug changes.