Bug 33514

Summary: SearchWithIS[B|S]NVariations breaks ISBN search in item search
Product: Koha Reporter: Katrin Fischer <katrin.fischer>
Component: SearchingAssignee: Bugs List <koha-bugs>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: david, emily.lamancusa, jonathan.druart, nick, patrick.robitaille, tomascohen
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33303
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:
Bug Depends on: 23919    
Bug Blocks:    

Description Katrin Fischer 2023-04-13 07:57:11 UTC 
Item search is SQL based, not search engine based. When bug 23919 added SearchWithISSNVariations to item search, it broke all searches, but for one use case (that was tested): single ISBN, no ISBD punctuation. 

We are exploding the search now with up to 4 forms of ISBN into something like:

isbn like 'isbn1' or isbn like 'isbn2' ...

But: We are not truncating. 

As soon as there is more than one ISBN (this is a repeatable field) or the library actually follows MARC21 standard adding ISBD punctuation, this fails. The data in biblioitems.isbn looks like this:

isbn1 ; | isbn2 ; | isbn3.
isbn1.

This also seems to create quite a performance issue on big databases and it can't be turned off separately from the normal search.

I am not sure what is best, we could do something like this, which will make the search work again, but I think we also should make it a separate switch:

@@ -1047,7 +1047,12 @@ sub _SearchItems_build_where_fragment {
                  if ( 
C4::Context->preference("SearchWithISBNVariations") and $query ) {
                      my @isbns = C4::Koha::GetVariationsOfISBN( $query );
                      $query = [];
+                    foreach my $isbn(@isbns) {
+                        $isbn = "%" . $isbn . "%";
+                    }
Comment 1 Katrin Fischer 2023-04-13 07:58:54 UTC 
I should also mention that any truncation entered in the item search is lost when it's run through the ISBN conversion. So there is no workaround.
Comment 2 Katrin Fischer 2023-05-14 15:38:29 UTC 
I am not sure what the best solution here is:

1) Remove the ISBN handling from item search.
2) Fix it to search for ISBN left and right truncated (%isbn%) 
   a) create a separate system preference to turn it on/off 
   b) leave it at one system preference as it is now that controls catalog and     
      item search

Note: the performance issues I mentioned earlier have been fixed by giving the databaes more resources.

2a?
Comment 3 Tomás Cohen Arazi (tcohen) 2023-05-24 11:38:18 UTC 
(In reply to Katrin Fischer from comment #2)
> I am not sure what the best solution here is:

What about making sure that normalized strings are always stored on the DB? (i.e. no spaces, no symbols). It feels like GetVariationsOfISBN might be too much in my opinion.
Comment 4 Katrin Fischer 2023-05-26 07:48:36 UTC 
(In reply to Tomás Cohen Arazi from comment #3)
> (In reply to Katrin Fischer from comment #2)
> > I am not sure what the best solution here is:
> 
> What about making sure that normalized strings are always stored on the DB?
> (i.e. no spaces, no symbols). It feels like GetVariationsOfISBN might be too
> much in my opinion.

I think that's not the main issue here, sorting differently would not resolve hte issue. We need to fix the search query. The problem is that we store a string of space separated ISBN and you can't search that with SQL without truncating/using LIKE. And that's what happens now and only works when there is only one ISBN on the record (which often is not the case at least for us)
Comment 5 Katrin Fischer 2023-05-27 21:54:48 UTC 
sorting => storing

> I think that's not the main issue here, storing differently would not
> resolve hte issue. We need to fix the search query. The problem is that we
> store a string of space separated ISBN and you can't search that with SQL
> without truncating/using LIKE. And that's what happens now and only works
> when there is only one ISBN on the record (which often is not the case at
> least for us)