[Bug 21941] New: Incorrect GROUP BY in circ/reserveratios.pl
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21941 Bug ID: 21941 Summary: Incorrect GROUP BY in circ/reserveratios.pl Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Circulation Assignee: koha-bugs@lists.koha-community.org Reporter: nick@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org CC: gmcharlt@gmail.com, kyle.m.hall@gmail.com DBD::mysql::st execute failed: 'koha_kohadev.reserves.reservedate' isn't in GROUP BY [for Statement "SELECT reservedate, reserves.borrowernumber as borrowernumber, reserves.biblionumber, reserves.branchcode as branch, items.holdingbranch, items.itemcallnumber, items.itemnumber, GROUP_CONCAT(DISTINCT items.itemcallnumber ORDER BY items.itemnumber SEPARATOR '|') as listcall, GROUP_CONCAT(DISTINCT homebranch ORDER BY items.itemnumber SEPARATOR '|') as homebranch_list, GROUP_CONCAT(DISTINCT holdingbranch ORDER BY items.itemnumber SEPARATOR '|') as holdingbranch_list, GROUP_CONCAT(DISTINCT items.location ORDER BY items.itemnumber SEPARATOR '|') as l_location, GROUP_CONCAT(DISTINCT items.itype ORDER BY items.itemnumber SEPARATOR '|') as l_itype, reserves.found, biblio.title, biblio.author, count(DISTINCT reserves.borrowernumber) as reservecount, count(DISTINCT items.itemnumber) as itemcount FROM reserves LEFT JOIN items ON items.biblionumber=reserves.biblionumber LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber WHERE notforloan = 0 AND damaged = 0 AND itemlost = 0 AND withdrawn = 0 AND reservedate >= ? AND reservedate <= ? GROUP BY reserves.biblionumber ORDER BY reservecount DESC" with ParamValues: 0='2017-12-04 00:00', 1='2018-12-04 00:00'] at /home/vagrant/kohaclone/circ/reserveratios.pl line 150. -- 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=21941 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=15422 -- 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=21941 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |17258 CC| |jonathan.druart@bugs.koha-c | |ommunity.org Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17258 [Bug 17258] [OMNIBUS] MySQL 5.7 -- 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=21941 --- Comment #1 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- This is a hard one. I have tried several weeks ago with circ/pendingreserves.pl that has the same problem but decided to postpone it :-/ -- 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=21941 Frédéric Demians <frederic@tamil.fr> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |frederic@tamil.fr -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21941 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=27498 --- Comment #2 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- We just got new reports for this, I believe because the sandboxes are using the stricter DBMS settings: Taking this from bug 27498: I've tried testing this bug several times in a sandbox and I'm getting an error with the patch applied and even without it applied. The error starts out with the below statement and then lists 56 specific errors. I'm unable to test until something gets fixed. Plack::App::WrapCGI::call(): DBI Exception: DBD::mysql::st execute failed: 'koha_bug27498.reserves.reservedate' isn't in GROUP BY [for Statement "SELECT reservedate . . . . . -- 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=21941 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au --- Comment #3 from David Cook <dcook@prosentient.com.au> --- ONLY_FULL_GROUP_BY drives me nuts... If we're using that SQL mode, I think this query will just need to be totally rewritten. -- 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=21941 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com --- Comment #4 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- *** Bug 29675 has been marked as a duplicate of this bug. *** -- 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=21941 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=23360 -- 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=21941 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |david@davidnind.com --- Comment #5 from David Nind <david@davidnind.com> --- Can confirm that this is still occurring on master (using ku to start up koha-testing-docker, Staff interface > Circulation > Holds > Hold ratios). -- 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=21941 Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |caroline.cyr-la-rose@inlibr | |o.com --- Comment #6 from Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> --- This is still valid on ktd/main. Works fine on my local git install. -- 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=21941 --- Comment #7 from David Cook <dcook@prosentient.com.au> --- Yep still an issue in main. Unfortunately, this is an area that I don't know at all in Koha. -- 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=21941 Kevin Carnes <kevin.carnes@ub.lu.se> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Trivial patch Status|NEW |Needs Signoff -- 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=21941 --- Comment #8 from Kevin Carnes <kevin.carnes@ub.lu.se> --- Created attachment 193716 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=193716&action=edit Bug 21941: Fix GROUP BY in circ/reserveratios.pl When using strict_sql_modes, the Hold ratios page causes an Incorrect GROUP BY error and returns an Error 500. This patch removes uncessary columns from the database query and adds the biblio columns to the GROUP BY statement, so they can be selected. Since it's already grouped by biblionumber, it should not cause any additional rows to be created. Test plan: 1. Make sure that strict_sql_modes is enabled in the koha-conf.xml file. 2. Place at least one hold on a book. 3. Go to Circulation and select Hold ratios. 4. Observe that you get an Error 500. 5. Apply patch. 6. Repeat step 3. 7. Observe that you can view the page. 8. If the table is empty, change the ratio to 1. 9. Sign off. Sponsored-by: Lund University Library -- 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=21941 Kevin Carnes <kevin.carnes@ub.lu.se> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |kevin.carnes@ub.lu.se |ity.org | CC| |kevin.carnes@ub.lu.se -- 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=21941 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off Patch complexity|Trivial patch |Small patch -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21941 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #193716|0 |1 is obsolete| | --- Comment #9 from Owen Leonard <oleonard@myacpl.org> --- Created attachment 194105 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=194105&action=edit Bug 21941: Fix GROUP BY in circ/reserveratios.pl When using strict_sql_modes, the Hold ratios page causes an Incorrect GROUP BY error and returns an Error 500. This patch removes uncessary columns from the database query and adds the biblio columns to the GROUP BY statement, so they can be selected. Since it's already grouped by biblionumber, it should not cause any additional rows to be created. Test plan: 1. Make sure that strict_sql_modes is enabled in the koha-conf.xml file. 2. Place at least one hold on a book. 3. Go to Circulation and select Hold ratios. 4. Observe that you get an Error 500. 5. Apply patch. 6. Repeat step 3. 7. Observe that you can view the page. 8. If the table is empty, change the ratio to 1. 9. Sign off. Sponsored-by: Lund University Library Signed-off-by: Owen Leonard <oleonard@myacpl.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21941 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Comma delimited| |Lund University Library list of Sponsors| | Sponsorship status|--- |Sponsored -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21941 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #194105|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=21941 --- Comment #10 from David Nind <david@davidnind.com> --- Created attachment 194187 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=194187&action=edit Bug 21941: Fix GROUP BY in circ/reserveratios.pl When using strict_sql_modes, the Hold ratios page causes an Incorrect GROUP BY error and returns an Error 500. This patch removes uncessary columns from the database query and adds the biblio columns to the GROUP BY statement, so they can be selected. Since it's already grouped by biblionumber, it should not cause any additional rows to be created. Test plan: 1. Make sure that strict_sql_modes is enabled in the koha-conf.xml file. 2. Place at least one hold on a book. 3. Go to Circulation and select Hold ratios. 4. Observe that you get an Error 500. 5. Apply patch. 6. Repeat step 3. 7. Observe that you can view the page. 8. If the table is empty, change the ratio to 1. 9. Sign off. Sponsored-by: Lund University Library Signed-off-by: Owen Leonard <oleonard@myacpl.org> 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=21941 --- Comment #11 from David Nind <david@davidnind.com> --- Adding my sign-off, in case Owen can do the QA. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21941 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21941 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #194187|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=21941 --- Comment #12 from Andrew Fuerste-Henry <andrew@bywatersolutions.com> --- Created attachment 196344 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=196344&action=edit Bug 21941: Fix GROUP BY in circ/reserveratios.pl When using strict_sql_modes, the Hold ratios page causes an Incorrect GROUP BY error and returns an Error 500. This patch removes uncessary columns from the database query and adds the biblio columns to the GROUP BY statement, so they can be selected. Since it's already grouped by biblionumber, it should not cause any additional rows to be created. Test plan: 1. Make sure that strict_sql_modes is enabled in the koha-conf.xml file. 2. Place at least one hold on a book. 3. Go to Circulation and select Hold ratios. 4. Observe that you get an Error 500. 5. Apply patch. 6. Repeat step 3. 7. Observe that you can view the page. 8. If the table is empty, change the ratio to 1. 9. Sign off. Sponsored-by: Lund University Library Signed-off-by: Owen Leonard <oleonard@myacpl.org> Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Andrew Fuerste Henry <andrew@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21941 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |andrew@bywatersolutions.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=21941 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to main Version(s)| |26.05.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21941 --- Comment #13 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Nice work everyone! Pushed to main for 26.05 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21941 Jacob O'Mara <jacob.omara@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|26.05.00 |26.05.00,25.11.05 released in| | Status|Pushed to main |Pushed to stable -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21941 --- Comment #14 from Jacob O'Mara <jacob.omara@openfifth.co.uk> --- Thanks all, pushed to 25.11.x -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org