Bug 21941

Summary: Incorrect GROUP BY in circ/reserveratios.pl
Product: Koha Reporter: Nick Clemens <nick>
Component: CirculationAssignee: Bugs List <koha-bugs>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: david, dcook, f.demians, gmcharlt, jonathan.druart, kyle.m.hall, lucas
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=15422
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27498
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23360
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on:    
Bug Blocks: 17258    

Description Nick Clemens 2018-12-04 15:01:15 UTC
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.
Comment 1 Jonathan Druart 2018-12-10 19:39:41 UTC
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 :-/
Comment 2 Katrin Fischer 2021-03-21 23:41:05 UTC
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 . . . . .
Comment 3 David Cook 2021-03-21 23:50:15 UTC
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.
Comment 4 Katrin Fischer 2021-12-12 15:15:33 UTC
*** Bug 29675 has been marked as a duplicate of this bug. ***
Comment 5 David Nind 2022-06-05 20:49:23 UTC
Can confirm that this is still occurring on master (using ku to start up koha-testing-docker, Staff interface > Circulation > Holds > Hold ratios).