When conducting an item search, if I set the checkout count to = 0, I get "No data available in table" (and there are most definitely items without any checkouts). If I conduct a search that returns results (without filtering by checkout count), and then enter 0 into the box on the search results screen, I get results with 10 checkouts. I expected to get the items with 0 checkouts.
My library has been using the Item Search and also noticed the same problem with the checkout count. I think the problem is related to the "issues" field in the "items" table. The "issues" field is of type SMALLINT with a default value of "null" Using a report, I can illustrate the behavior. Example 1: select barcode, issues from items where issues <= <<equal or less than checkouts>> Result 1: Items with checkouts less or equal to the number you enter are listed but NOT items with 0 checkouts. Same as the Item Search. Example 2: select barcode, issues from items where issues is null OR issues <= <<equal or less than checkouts>> Result 2: Items with checkouts less or equal to the number you enter are listed AND any items with no checkouts. What we would expect to get if looking for check count = 0 or < # My assumption is that Koha displays a '0' when viewing an item on the staff client, however the "items" table stores a "null" value in the "issues" field. Limiting the checkout count with a '0' gives no results because there is no '0' in the "issues" field. If possible the "issues" field needs to have the default value set to '0', or the Item Search code needs to account for a "null" value.
I've confirmed, issues = 0 doesn't work in item search. Looking for items that haven't been issued is a common use case for weeding, marking this as a bug.
I'm inclined to agree with Azucena that changing the items.issues default to zero rather than null seems like a good answer here. Changing item search functionality to accommodate those nulls seems like addressing a symptom rather than a root cause.
Created attachment 103601 [details] [review] Bug 23081: Change issues fields in items and deleteditems to default to zero
Not sure I did this correctly, gonna get some eyes on it before I set it to Needs Signoff :)
Created attachment 103605 [details] [review] Bug 23081: Change issues fields in items and deleteditems to default to zero
Created attachment 103606 [details] [review] Bug 23081: Change issues fields in items and deleteditems to default to zero
Confirmed that item search gives no results for number of checkouts=0 in 19.11. However, in master I *do* get items with issues=null in item search results for checkouts=0. So that search does work in master and I'm not sure what changed, but this schema change seems worthwhile anyway. To test: - save and run this sql query in reports: select sum(if(issues is null,1,0)),sum(if(issues=0,1,0)) from items - you should see a lot of nulls and no zeros - apply patch - updatedatabase - re-run your query and see that your nulls have changed to zeros - create a new item - rerun your query and see your new item is counted in the zeros, not the nulls
Created attachment 103608 [details] [review] Bug 23081: Change issues fields in items and deleteditems to default to zero Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
The original issue report on this bug report has been fixed by bug 24443. Please edit the bug report title and remove the modifications made by bug 24443.
Created attachment 103725 [details] [review] Bug 23081: set default to 0 for items.issues and deleteditems.issues To test: - save and run this sql query in reports: select sum(if(issues is null,1,0)),sum(if(issues=0,1,0)) from items - you should see a lot of nulls and no zeros - apply patch - updatedatabase - re-run your query and see that your nulls have changed to zeros - create a new item - rerun your query and see your new item is counted in the zeros, not the nulls
Created attachment 103726 [details] [review] Bug 23081: atomicupdate for change to existing installs
Renamed bug and patch and moved the atomicupdate bit into its own patch. Nothing here actually duplicates what was done in 24443. It does make the change in 24443 sort of irrelevant, but I think this schema change is still worth making, as it will make reports easier and cleaner to write.
(In reply to Andrew Fuerste-Henry from comment #13) > Renamed bug and patch and moved the atomicupdate bit into its own patch. > Nothing here actually duplicates what was done in 24443. It does make the > change in 24443 sort of irrelevant, but I think this schema change is still > worth making, as it will make reports easier and cleaner to write. Andrew, yes that is what I was trying to explain. That's why we should remove the changes made by bug 24443, it's no longer relevant.
Created attachment 103741 [details] [review] Bug 23081: set default to 0 for items.issues and deleteditems.issues To test: - save and run this sql query in reports: select sum(if(issues is null,1,0)),sum(if(issues=0,1,0)) from items - you should see a lot of nulls and no zeros - apply patch - updatedatabase - re-run your query and see that your nulls have changed to zeros - create a new item - rerun your query and see your new item is counted in the zeros, not the nulls Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Created attachment 103742 [details] [review] Bug 23081: atomicupdate for change to existing installs Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Created attachment 103743 [details] [review] Bug 23081: Revert "Bug 24443: Consider NULL as 0 for issues in items search" This reverts commit 80f1374f262544a750b5d81a7d9605c8708c53b1. Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Created attachment 103744 [details] [review] Bug 23081: Adjust tests Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Nice work everyone! Pushed to master for 20.05
Thanks, Jonathan, for adding that!
Backported to 19.11.x for 19.11.06
Created attachment 104706 [details] [review] Bug 23081: [19.11.x] Set items.issues to 0 if not provided (AddItem) In master it has been replaced by Koha::Object->store and we deal with the default value.
pushed 19.11.x patch to 19.11.x for 19.11.06
backported to 19.05.x for 19.05.11
Shouldn't we specify 'NOT NULL', like for itemlost for example : `itemlost` tinyint(1) NOT NULL default 0 I'm using MariaDB 10.4.13, colum issues is created nullable.
It looks like this database update might have created quite an issue. Some libraries were looking at deleteditems.timestamp as deletion date, but the database update here altered it, so now it's all update day... :( $DBversion = '19.12.00.080'; if( CheckVersion( $DBversion ) ) { $dbh->do( "UPDATE items set issues=0 where issues is null" ); $dbh->do( "UPDATE deleteditems set issues=0 where issues is null" ); $dbh->do( "ALTER TABLE items ALTER issues set default 0" ); $dbh->do( "ALTER TABLE deleteditems ALTER issues set default 0" ); NewVersion( $DBversion, 23081, "Set default to 0 for items.issues"); }