Bug 36317

Summary: Koha::Biblio->host_items fails with search_ordered()
Product: Koha Reporter: Fridolin Somers <fridolin.somers>
Component: Architecture, internals, and plumbingAssignee: Fridolin Somers <fridolin.somers>
Status: Failed QA --- QA Contact: Marcel de Rooy <m.de.rooy>
Severity: normal    
Priority: P5 - low CC: dcook, emmi.takkinen, m.de.rooy, nick
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24192
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34360
Change sponsored?: --- Patch complexity: Trivial patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on: 31306    
Bug Blocks:    
Attachments: Bug 36317: Koha::Biblio->host_items must use me.itemnumber
Bug 36317: Koha::Biblio->host_items must use me.itemnumber
Bug 36317: Add unit test
Bug 36317: Koha::Biblio->host_items must use me.itemnumber
Bug 36317: Koha::Biblio->host_items must use me.itemnumber
Bug 36317: Add unit test
Bug 36317: Koha::Biblio->host_items must use me.itemnumber

Description Fridolin Somers 2024-03-14 10:23:15 UTC
Koha::Biblio->host_items fails with search_ordered() because of ambiguous column 'itemnumber' in where clause.
It must use me.itemnumber like in Koha::Biblio->items
Comment 1 Fridolin Somers 2024-03-14 10:23:36 UTC
Used for example in Bug 24192
Comment 2 Fridolin Somers 2024-03-14 10:26:17 UTC
Created attachment 163131 [details] [review]
Bug 36317: Koha::Biblio->host_items must use me.itemnumber

Koha::Biblio->host_items fails with search_ordered() because of ambiguous column 'itemnumber' in where clause.
It must use me.itemnumber like in Koha::Biblio->items

Test plan :
prove t/db_dependent/Biblio.t
Comment 3 David Nind 2024-03-15 00:07:13 UTC
Created attachment 163155 [details] [review]
Bug 36317: Koha::Biblio->host_items must use me.itemnumber

Koha::Biblio->host_items fails with search_ordered() because of ambiguous column 'itemnumber' in where clause.
It must use me.itemnumber like in Koha::Biblio->items

Test plan :
prove t/db_dependent/Biblio.t

Signed-off-by: David Nind <david@davidnind.com>
Comment 4 Marcel de Rooy 2024-03-15 09:24:03 UTC
Biblio.t passes with and without this patch. Please extend the test to show that this patch resolves the issue.
Comment 5 Fridolin Somers 2024-03-18 09:50:22 UTC
Ah mistake, it is t/db_dependent/Koha/Biblio.t

I'm on adding a UT
Comment 6 Fridolin Somers 2024-03-18 14:21:27 UTC
Created attachment 163334 [details] [review]
Bug 36317: Add unit test

If fails it shows :
DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Column 'itemnumber' in where clause is ambiguous at /kohadevbox/koha/Koha/Objects.pm line 399
Comment 7 Fridolin Somers 2024-03-18 14:21:40 UTC
Created attachment 163335 [details] [review]
Bug 36317: Koha::Biblio->host_items must use me.itemnumber

Koha::Biblio->host_items fails with search_ordered() because of ambiguous column 'itemnumber' in where clause.
It must use me.itemnumber like in Koha::Biblio->items

Test plan :
prove t/db_dependent/Biblio.t

Signed-off-by: David Nind <david@davidnind.com>
Comment 8 Fridolin Somers 2024-03-18 14:27:14 UTC
(In reply to Fridolin Somers from comment #6)
> Created attachment 163334 [details] [review] [review]
> Bug 36317: Add unit test
> 
> If fails it shows :
> DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st
> execute failed: Column 'itemnumber' in where clause is ambiguous at
> /kohadevbox/koha/Koha/Objects.pm line 399

Combining Koha::Biblio->host_items with Koha::Items->search_ordered in test suite was too hard.
I've done a simpler join on an item transfer which fails on current master.

Note that I test with MariaDB 10.6. Looks like it is more severe on join than lower versions.
Comment 9 Fridolin Somers 2024-03-18 14:28:34 UTC
Created attachment 163338 [details] [review]
Bug 36317: Koha::Biblio->host_items must use me.itemnumber

Koha::Biblio->host_items fails with search_ordered() because of ambiguous column 'itemnumber' in where clause.
It must use me.itemnumber like in Koha::Biblio->items

Test plan :
prove t/db_dependent/Koha/Biblio.t

Signed-off-by: David Nind <david@davidnind.com>
Comment 10 Fridolin Somers 2024-03-18 14:33:50 UTC
Working on this I learned that DBIx combines several searches into one :
  Koha::Items->search( { itemnumber => 123 } )->search( {}, { join => 'branchtransfers' } );
Will give only one SQL query : 
  SELECT * FROM items JOIN branchtransfers USING itemnumber WHERE itemnumber='123'

So we should always use explicit "me" :
  Koha::Items->search( { "me.itemnumber" => 123 } )->search( {}, { join => 'branchtransfers' } );
Comment 11 Emmi Takkinen 2024-04-02 08:34:58 UTC
Created attachment 164238 [details] [review]
Bug 36317: Add unit test

If fails it shows :
DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Column 'itemnumber' in where clause is ambiguous at /kohadevbox/koha/Koha/Objects.pm line 399

Signed-off-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi>
Comment 12 Emmi Takkinen 2024-04-02 08:35:23 UTC
Created attachment 164239 [details] [review]
Bug 36317: Koha::Biblio->host_items must use me.itemnumber

Koha::Biblio->host_items fails with search_ordered() because of ambiguous column 'itemnumber' in where clause.
It must use me.itemnumber like in Koha::Biblio->items

Test plan :
prove t/db_dependent/Koha/Biblio.t

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi>
Comment 13 Nick Clemens (kidclamp) 2024-04-02 11:35:24 UTC
How is this failure encountered? Or is this the base for another bug?

1 - Enable EasyAnalyticalRecords
2 - Go to a records details page and copy a barcode
3 - Go to another record
4 - Edit -> Link to host record
5 - Enter the barcode
6 - Records are now linked, and display with no error
Comment 14 Fridolin Somers 2024-04-03 14:48:38 UTC
(In reply to Nick Clemens from comment #13)
> How is this failure encountered? Or is this the base for another bug?
> 
> 1 - Enable EasyAnalyticalRecords
> 2 - Go to a records details page and copy a barcode
> 3 - Go to another record
> 4 - Edit -> Link to host record
> 5 - Enter the barcode
> 6 - Records are now linked, and display with no error

Currently there is no failure but there is a good chance in the future.
For example Bug 24192 wants to add :
  $dat->{ITEM_RESULTS} = $biblio->host_items->search_ordered;