After upgrading to 19.11, we're seeing libraries where pendingreserves.pl comes up empty when it shouldn't be. After some digging, we found it was because the library had some bad holds in their reserves table -- lines where reserves.itemnumber is null but reserves.found is not null. That runs into an issue on this line of the Holds to Pull query: AND items.itemnumber NOT IN (select itemnumber FROM reserves where found IS NOT NULL) I don't know exactly what changed between 19.05 and 19.11 to change how Koha thinks about this. I also don't know how libraries have ended up with these pathological holds. But that's a separate bug. To recreate: 1- place 2 bib-level holds on available items 2- confirm they both show on Holds to Pull 3- edit one hold from the database to set found='T' 4- reload Holds to Pull, confirm it is now empty
Created attachment 105800 [details] [review] Bug 25726: make holds to pull ignore holds where found is not null and itemnumber is null To test: 1- place 2 bib-level holds on available items 2- confirm they both show on Holds to Pull 3- edit one hold from the database to set found='T' 4- reload Holds to Pull, confirm it is now empty 5- apply patch 6- Reload Holds to Pull 7- confirm it now shows the hold you did not edit
Created attachment 105821 [details] [review] Bug 25726: make holds to pull ignore holds where found is not null and itemnumber is null To test: 1- place 2 bib-level holds on available items 2- confirm they both show on Holds to Pull 3- edit one hold from the database to set found='T' 4- reload Holds to Pull, confirm it is now empty 5- apply patch 6- Reload Holds to Pull 7- confirm it now shows the hold you did not edit Signed-off-by: donnab <donna@bywatersolutions.com>
Hi Andrew, I am a bit worried about the cause of these strange holds. You say reserves.found is not null - what was the found value for these strange holds?
Me, too! We've only seen a couple examples and haven't yet tracked down how they got in that state. In both cases the hold had a reserves.found value of T, but was not actually in transit. In one of those cases, the patron had 2 holds on the same record, which suggests maybe bug 18958 is involved? But, yes, for sure, there is a larger issue of where this bad data came from, my patch is just alleviating the major symptom caused by the data's existence. (In reply to Katrin Fischer from comment #3) > Hi Andrew, > > I am a bit worried about the cause of these strange holds. You say > reserves.found is not null - what was the found value for these strange > holds?
Hi Andrew, I am actually a bit confused by the SQL change and worried about unwanted side effects: - AND items.itemnumber NOT IN (select itemnumber FROM reserves where found IS NOT NULL) + AND items.itemnumber NOT IN (select itemnumber FROM reserves where found IS NOT NULL AND itemnumber IS NOT NULL) If I understand correctly this will make the items in 'Transit' status show up in the list. But if they really are in transit - they can't be pulled... so should they show up? I think the logic of the SQL might be right and we need to take care to fix the holds, if the state of found = "T" and itemnumber empty is unwanted. I've tried several things to get a 'pathological' hold, but haven't succeded other than in turning up more oddities (see bug 25748).
(In reply to Katrin Fischer from comment #5) > If I understand correctly this will make the items in 'Transit' status show > up in the list. But if they really are in transit - they can't be pulled... > so should they show up? I think the logic of the SQL might be right and we > need to take care to fix the holds, if the state of found = "T" and > itemnumber empty is unwanted. No the transit does not come up. Since it is in a itemnumber NOT IN ... construction. The only thing we exclude here is getting a NULL and then saying itemnumber NOT IN (NULL) making all biblio level holds disappear.
Created attachment 106493 [details] [review] Bug 25726: make holds to pull ignore holds where found is not null and itemnumber is null To test: 1- place 2 bib-level holds on available items 2- confirm they both show on Holds to Pull 3- edit one hold from the database to set found='T' 4- reload Holds to Pull, confirm it is now empty 5- apply patch 6- Reload Holds to Pull 7- confirm it now shows the hold you did not edit Signed-off-by: donnab <donna@bywatersolutions.com> Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> [EDIT] Using capitals for SQL reserved words; added a FIXME Tested with: update reserves set found='T', itemnumber=NULL where reserve_id=... This should be a workaround while we fix the underlying problem.
(In reply to Katrin Fischer from comment #5) > > If I understand correctly this will make the items in 'Transit' status show > up in the list. But if they really are in transit - they can't be pulled... > so should they show up? That doesn't seem to make the items in transit show up in Holds to pull list. However, i'm not sure of any other side effects. > I think the logic of the SQL might be right and we > need to take care to fix the holds, if the state of found = "T" and > itemnumber empty is unwanted. Agreed. Maybe we should understand why we get this pathological holds in the first place. > > I've tried several things to get a 'pathological' hold, but haven't succeded > other than in turning up more oddities (see bug 25748). Unable to recreate neither...
Thanks, all! I should add that I've only run into this on one library, so whatever got their holds into this state seems to be pretty vanishingly rare, but I'm keeping and eye out for more. If I can figure out how to reproduce it, I'll absolutely file a bug for it.
Pushed to master for 20.11, thanks to everybody involved!
backported to 20.05.x for 20.05.03
backported to 19.11.x for 19.11.09
Super I was looking for this exact fix. Looks like its the behavior of NOT IN () with NULL : I've found some explanations in : https://stackoverflow.com/questions/1001144/mysql-select-x-from-a-where-not-in-select-x-from-b-unexpected-result#1001197 .
Backported to 19.05.x branch for 19.05.15
possible cause on 27166