https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 --- Comment #1 from Michael Hafen <michael.hafen@washk12.org> --- Created attachment 124664 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=124664&action=edit Proposed patch 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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 Michael Hafen <michael.hafen@washk12.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |In Discussion --- Comment #3 from Michael Hafen <michael.hafen@washk12.org> --- (In reply to David Nind from comment #2)

I had a go at testing with koha-testing-docker (KTD), but was unable to get this to work.

- KTD = started with ku - Strict mode already set in /etc/koha/sites/kohadev/koha-conf.xml

Error before patch applied, no change after (with a flush_memcached and restart_all):

CGI::Compile::ROOT::kohadevbox_koha_reports_cat_issues_top_2epl::calculate(): DBI Exception: DBD::mysql::st execute failed: 'koha_kohadev.biblio.title' isn't in GROUP BY at /kohadevbox/koha/reports/cat_issues_top.pl line 64 at /usr/share/perl5/DBIx/Class/Exception.pm line 77

I think I will leave the testing to someone who understands how this all works a bit better than I do!

What options did you use to generate that error? I'm not able to reproduce it. I didn't test this report thoroughly, so I'm sure there is more work to be done here. Now that I'm playing with it a bit more the "distinct" is causing trouble too with Mysql 8. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 Michael Hafen <michael.hafen@washk12.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #124664|0 |1 is obsolete| | --- Comment #5 from Michael Hafen <michael.hafen@washk12.org> --- Created attachment 125124 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=125124&action=edit Proposed patch Fresh patch with fixes for MariaDB in Debian 9. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 --- Comment #7 from David Nind <david@davidnind.com> --- Testing notes (koha-testing-docker): - tested on environments available by starting KTD with ku, ku-mdb, ku-md9, ku-md10, ky-my8 - check out and check in an item (so that there was some data for the report) -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |michael.hafen@washk12.org |ity.org | CC| |m.de.rooy@rijksmuseum.nl -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #125124|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #125208|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |tomascohen@gmail.com |y.org | -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Text to go in the|This fixes an error that |This fixes an error that release notes|causes the most circulated |causes the most circulated |items report to fail when |items report to fail when |run on a database with SQL |run on a database with SQL |mode ONLY_FULL_GROUP_BY and |mode ONLY_FULL_GROUP_BY and |in struct SQL mode. |in strict SQL mode. CC| |fridolin.somers@biblibre.co | |m -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 --- Comment #10 from Michael Hafen <michael.hafen@washk12.org> --- (In reply to Fridolin Somers from comment #9)

Do we still need "DISTINCT" on biblio.title ?

Maybe so; depends on how likely it is that separate biblios will have the same title, and whether the library would want those separate records grouped together. In my case, we'd probably want the records grouped together. I tried the query in MySQL 8.0 with and without the 'DISTINCT', and there was 1 row difference. Though my case is probably not typical, being a school district with a bunch of schools that migrated from separate databases into one instance of Koha. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 --- Comment #12 from Fridolin Somers <fridolin.somers@biblibre.com> --- Ah that is strange. Looks like callnumber is not displayed in result table. This has been added by Bug 10154 as a filter, but looks there there is no need to add in the SELECT. Can someone confirm ? -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 --- Comment #14 from Fridolin Somers <fridolin.somers@biblibre.com> --- (In reply to Michael Hafen from comment #13)

(In reply to Fridolin Somers from comment #12)

...

Ah that is strange. Looks like callnumber is not displayed in result table. This has been added by Bug 10154 as a filter, but looks there there is no need to add in the SELECT. Can someone confirm ?

I would guess that that is intended. The call number shows in the filters, and probably isn't necessary in the results table.

If someone wants it there though, it can be added. Here or in an enhancement bug.

I propose removing from select here, instead of group_concat -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 Michael Hafen <michael.hafen@washk12.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #128684|0 |1 is obsolete| | --- Comment #16 from Michael Hafen <michael.hafen@washk12.org> --- Created attachment 129510 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=129510&action=edit Bug 28977: Add GROUP_CONCAT to some of the selected columns -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Needs Signoff CC| |jonathan.druart+koha@gmail. | |com --- Comment #17 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Asking for a signoff. Could you adjust the patch's title? -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #129510|0 |1 is obsolete| | --- Comment #18 from David Nind <david@davidnind.com> --- Created attachment 129982 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=129982&action=edit Bug 28977: Add GROUP_CONCAT to some of the selected columns 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> -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #129982|0 |1 is obsolete| | --- Comment #19 from Jonathan Druart <jonathan.druart+koha@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@davidnind.com> JD Amended patch - adjust commit's title Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 --- Comment #20 from Fridolin Somers <fridolin.somers@biblibre.com> --- Pushed to master for 22.05, thanks to everybody involved 🦄 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28977 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |andrew@bywatersolutions.com Status|Pushed to stable |Pushed to oldstable Version(s)|22.05.00,21.11.03 |22.05.00,21.11.03,21.05.11 released in| | --- Comment #22 from Andrew Fuerste-Henry <andrew@bywatersolutions.com> --- Pushed to 21.05.x for 21.05.11 -- You are receiving this mail because: You are watching all bug changes.