Bug 37889

Summary: Item search: collection search doesn’t work correctly when searching 'collection is not'
Product: Koha Reporter: Kati Sillgren <kati.sillgren>
Component: SearchingAssignee: Bugs List <koha-bugs>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: andrew, emmi.takkinen, katariina.pohto, katrin.fischer
Version: unspecified   
Hardware: All   
OS: All   
GIT URL: Initiative type: ---
Sponsorship status: --- Comma delimited list of Sponsors:
Crowdfunding goal: 0 Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:

Description Kati Sillgren 2024-09-11 07:39:03 UTC
itemsearch.pl

Item search and searching 'collection is not' does not take into account items that do not have a collection code.

Not all items have a collection code.
Comment 1 Katariina Pohto 2025-03-17 11:21:50 UTC
This seems to happen with some other values that can be NULL. In the drop down menu with the barcode placeholder there can be several different options working the same way, for example age restriction or series. If you search items while selecting these as "is not", the results will not include the items that have a NULL value.

It seems to be the way SQL works. Even reports will not give you the NULL results if you search for items with (CCODE != collection) but you can circumvent it using (CCODE IS NULL OR CCODE !=CCODE). This is how our librarians think it should work in item search.

(And if we changed this logic it would still be possible to do the CCODE search the way it was, by selecting 'Collection IS' and making the multiple choice of all the collections but the ones excluded.)
Comment 2 Katrin Fischer 2026-01-30 08:26:05 UTC
*** Bug 41726 has been marked as a duplicate of this bug. ***
Comment 3 Andrew Fuerste-Henry 2026-01-30 13:39:28 UTC
I said this over on bug 41726 before it got closed as a dupe: hardcoding behavior to make item search silently differ from base SQL seems like it will create confusion in the long run. I'd prefer to see this addressed with some mechanism on the item search page to explicitly ask the search to include NULL values in the results for an IS NOT search.
Comment 4 Katrin Fischer 2026-01-30 16:23:05 UTC
Adding the step by step test plan here from bug 41726:

To test:
* Create a new location by adding a new entry to the LOC authorised value category (so it's not used otherwise)
* Add 2 items to a record
  Item A: has the location with the new value
  Item B: has no location
  Note the title of your record
* Search using the item search:
  Title: your title (don't forget to truncate with %)
  Verify your 2 items are found
* Amend the search and add to the search criteria:
  Location is not <your new location>
* Verify that you get NO results.
  Expected behavior: The item with no location should be found

In SQL we can see the difference:
select * from items where location != "something";

Results including NULL/empty locations:
select * from items where location != "something" OR location is NULL;