Bug 23081 - Make items.issues and deleteditems.issues default to 0 instead of null
Summary: Make items.issues and deleteditems.issues default to 0 instead of null
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Searching (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Andrew Fuerste-Henry
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks: 29902
  Show dependency treegraph
 
Reported: 2019-06-07 18:18 UTC by John Yorio
Modified: 2022-03-30 08:24 UTC (History)
9 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
20.05.00, 19.11.06, 19.05.11


Attachments
Bug 23081: Change issues fields in items and deleteditems to default to zero (4.81 KB, patch)
2020-04-23 18:16 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review
Bug 23081: Change issues fields in items and deleteditems to default to zero (4.81 KB, patch)
2020-04-23 19:41 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review
Bug 23081: Change issues fields in items and deleteditems to default to zero (5.00 KB, patch)
2020-04-23 19:48 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review
Bug 23081: Change issues fields in items and deleteditems to default to zero (5.05 KB, patch)
2020-04-23 21:54 UTC, Lucas Gass
Details | Diff | Splinter Review
Bug 23081: set default to 0 for items.issues and deleteditems.issues (4.11 KB, patch)
2020-04-26 23:54 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review
Bug 23081: atomicupdate for change to existing installs (1.46 KB, patch)
2020-04-26 23:55 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review
Bug 23081: set default to 0 for items.issues and deleteditems.issues (4.19 KB, patch)
2020-04-27 09:57 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 23081: atomicupdate for change to existing installs (1.52 KB, patch)
2020-04-27 09:57 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 23081: Revert "Bug 24443: Consider NULL as 0 for issues in items search" (976 bytes, patch)
2020-04-27 09:57 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 23081: Adjust tests (1.28 KB, patch)
2020-04-27 09:57 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 23081: [19.11.x] Set items.issues to 0 if not provided (AddItem) (873 bytes, patch)
2020-05-11 14:05 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description John Yorio 2019-06-07 18:18:24 UTC
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.
Comment 1 Azucena Aguayo 2019-10-15 17:48:48 UTC
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.
Comment 2 Katrin Fischer 2019-11-02 20:36:35 UTC
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.
Comment 3 Andrew Fuerste-Henry 2020-04-23 17:30:46 UTC
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.
Comment 4 Andrew Fuerste-Henry 2020-04-23 18:16:25 UTC
Created attachment 103601 [details] [review]
Bug 23081: Change issues fields in items and deleteditems to default to zero
Comment 5 Andrew Fuerste-Henry 2020-04-23 18:23:18 UTC
Not sure I did this correctly, gonna get some eyes on it before I set it to Needs Signoff :)
Comment 6 Andrew Fuerste-Henry 2020-04-23 19:41:06 UTC
Created attachment 103605 [details] [review]
Bug 23081: Change issues fields in items and deleteditems to default to zero
Comment 7 Andrew Fuerste-Henry 2020-04-23 19:48:02 UTC
Created attachment 103606 [details] [review]
Bug 23081: Change issues fields in items and deleteditems to default to zero
Comment 8 Andrew Fuerste-Henry 2020-04-23 20:13:02 UTC
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
Comment 9 Lucas Gass 2020-04-23 21:54:29 UTC
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>
Comment 10 Jonathan Druart 2020-04-24 13:02:28 UTC
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.
Comment 11 Andrew Fuerste-Henry 2020-04-26 23:54:59 UTC
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
Comment 12 Andrew Fuerste-Henry 2020-04-26 23:55:02 UTC
Created attachment 103726 [details] [review]
Bug 23081: atomicupdate for change to existing installs
Comment 13 Andrew Fuerste-Henry 2020-04-26 23:57:23 UTC
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.
Comment 14 Jonathan Druart 2020-04-27 08:56:08 UTC
(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.
Comment 15 Jonathan Druart 2020-04-27 09:57:25 UTC
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>
Comment 16 Jonathan Druart 2020-04-27 09:57:29 UTC
Created attachment 103742 [details] [review]
Bug 23081: atomicupdate for change to existing installs

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 17 Jonathan Druart 2020-04-27 09:57:34 UTC
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>
Comment 18 Jonathan Druart 2020-04-27 09:57:37 UTC
Created attachment 103744 [details] [review]
Bug 23081: Adjust tests

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 19 Martin Renvoize 2020-04-27 10:45:10 UTC
Nice work everyone!

Pushed to master for 20.05
Comment 20 Andrew Fuerste-Henry 2020-04-27 11:55:58 UTC
Thanks, Jonathan, for adding that!
Comment 21 Joy Nelson 2020-05-08 21:34:27 UTC
Backported to 19.11.x for 19.11.06
Comment 22 Jonathan Druart 2020-05-11 14:05:42 UTC
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.
Comment 23 Joy Nelson 2020-05-11 19:29:14 UTC
pushed 19.11.x patch to 19.11.x for 19.11.06
Comment 24 Lucas Gass 2020-05-14 21:57:02 UTC
backported to 19.05.x for 19.05.11
Comment 25 Fridolin Somers 2020-07-21 10:10:54 UTC
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.
Comment 26 Katrin Fischer 2022-03-29 09:38:47 UTC
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");
}