[Bug 42995] New: Error in table of pending orders prevents receiving orders
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 Bug ID: 42995 Summary: Error in table of pending orders prevents receiving orders Initiative type: --- Sponsorship --- status: Product: Koha Version: 24.11 Hardware: All OS: All Status: NEW Severity: blocker Priority: P5 - low Component: Acquisitions Assignee: koha-bugs@lists.koha-community.org Reporter: katrin.fischer@bsz-bw.de QA Contact: testopia@bugs.koha-community.org Target Milestone: --- Found while testing bug 40575 and confirmed by David on main: [ERROR] GET /api/v1/acquisitions/orders: unhandled exception (DBIx::Class::Exception)<<DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Unknown column 'biblio.biblionumber' in 'WHERE' at /kohadevbox/koha/Koha/Objects.pm line 401>> 1. Add an order to the bask for My Vendor. 2. Close the basket 3. Go the vendor page 4. Click 'Receive shipments' 5. Enter an invoice number 6. Click 'Next' 7. You get a "Something went wrong when loading the table. 500: Internal Server Error." Click OK 8. The 'Receive orders from My Vendor" page sits on "Processing ....". 9. There is the error above in the plack-api-error.log -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Version|24.11 |Main CC| |pedro.amorim@openfifth.co.u | |k -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |david@davidnind.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@gmail.com Depends on| |41950 --- Comment #1 from Jonathan Druart <jonathan.druart@gmail.com> --- Caused by commit e51334604fc61ecdf259686e778ba31a94697fe2 Bug 41950: Make +count embeds sortable via SQL COUNT subqueries Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41950 [Bug 41950] Make +count embeds sortable by using SQL-level COUNT subqueries -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff Patch complexity|--- |Small patch Assignee|koha-bugs@lists.koha-commun |tomascohen@gmail.com |ity.org | -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 --- Comment #2 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- Created attachment 201566 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=201566&action=edit Bug 42995: (regression tests) Nested +count embeds with has_many prefetch This patch adds regression tests for the 500 Internal Server Error triggered when GET /api/v1/acquisitions/orders is called with nested +count embeds (e.g. biblio.items+count) combined with a has_many prefetch (e.g. biblio.suggestions.suggester) and pagination. The root cause is that DBIC's limiting subquery optimization places +select scalar refs in the inner subquery where the parent join alias (biblio) is not yet available, resulting in: DBD::mysql: Unknown column 'biblio.biblionumber' in 'WHERE' The regression was introduced in: commit e51334604fc61ecdf259686e778ba31a94697fe2 Bug 41950: Make +count embeds sortable via SQL COUNT subqueries Test plan: 1. Apply this patch 2. Run: $ ktd --shell k$ prove t/db_dependent/api/v1/acquisitions_orders.t => FAIL: The new subtest fails with 500 errors instead of 200 3. Sign off :-D -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 --- Comment #3 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- Created attachment 201567 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=201567&action=edit Bug 42995: Fix nested +count subqueries with pagination and has_many When nested +count embeds (e.g. biblio.items+count) are used alongside a has_many prefetch (e.g. biblio.suggestions or the implicitly added biblio.biblioitem) and pagination, DBIC's limiting subquery optimization places +select scalar refs in the inner subquery where the parent join alias (e.g. 'biblio') is not yet available. This patch fixes _build_count_subquery to resolve the correlation back to the main table (me) via the FK chain. Instead of generating: (SELECT COUNT(*) FROM items WHERE items.biblionumber = biblio.biblionumber) It now generates: (SELECT COUNT(*) FROM items WHERE items.biblionumber = me.biblionumber) This works because me.biblionumber IS the FK to biblio, holding the same value. The main table columns are always available regardless of DBIC's query structure. Changes: - Add $main_source parameter to _build_count_subquery for FK resolution - Thread main_source through _parse_prefetch and dbic_merge_prefetch - When parent_alias != 'me', resolve through parent relationship condition - Update Query.t assertion to match the new correlation expression Test plan: 1. Apply the regression tests from the previous commit 2. Run: $ ktd --shell k$ prove t/db_dependent/api/v1/acquisitions_orders.t => FAIL: The Bug 42995 subtest fails with 500 errors 3. Apply this patch, repeat from step 2 k$ prove t/Koha/REST/Plugin/Query.t \ t/db_dependent/Koha/Object.t \ t/db_dependent/Koha/REST/Plugin/Objects.t \ t/db_dependent/api/v1/acquisitions_orders.t \ t/db_dependent/api/v1/acquisitions_vendors.t \ t/db_dependent/api/v1/ill_requests.t \ t/db_dependent/api/v1/tickets.t \ t/db_dependent/api/v1/patrons.t => SUCCESS: Tests pass! 4. Sign off :-D -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |tomascohen@gmail.com --- Comment #4 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- Hi folks. Sorry I missed this situation when doing bug 41950. Here's the full picture. When the page requests orders with biblio.items+count and biblio.suggestions.suggester: What the code builds (attributes passed to DBIC): ```perl { rows => 20, page => 1, prefetch => { biblio => ['suggestions', 'biblioitem'] }, '+select' => [ \"(SELECT COUNT(*) FROM items WHERE items.biblionumber = ???)" ], '+as' => ['biblio.items_count'], } ``` Without has_many in prefetch (e.g. only biblio which is belongs_to), DBIC generates a single flat query: ```sql SELECT me.*, (SELECT COUNT(*) FROM items WHERE items.biblionumber = biblio.biblionumber), biblio.* FROM aqorders me LEFT JOIN biblio ON biblio.biblionumber = me.biblionumber LIMIT 20 ``` Here `biblio.biblionumber` works fine because biblio is joined in the same query. With has_many in prefetch (suggestions or biblioitem are has_many from Biblio), DBIC uses a limiting subquery to avoid row multiplication from the has_many join: ```sql SELECT subq.*, biblio.*, suggestions.*, biblioitem.* FROM ( -- Inner query: pagination happens here, NO joins to biblio SELECT me.*, (SELECT COUNT(*) FROM items WHERE items.biblionumber = biblio.biblionumber) -- BOOM! FROM aqorders me LIMIT 20 ) subq LEFT JOIN biblio ON biblio.biblionumber = subq.biblionumber LEFT JOIN suggestions ON suggestions.biblionumber = biblio.biblionumber LEFT JOIN biblioitem ON biblioitem.biblionumber = biblio.biblionumber ``` DBIC moves `+select` into the inner query but puts the joins in the outer query. The subquery references `biblio.biblionumber` but `biblio` isn't joined there. MySQL says: `Unknown column 'biblio.biblionumber' in WHERE`. The fix is to use `me.biblionumber` instead: ```sql SELECT subq.*, biblio.*, suggestions.*, biblioitem.* FROM ( SELECT me.*, (SELECT COUNT(*) FROM items WHERE items.biblionumber = me.biblionumber) -- OK! FROM aqorders me LIMIT 20 ) subq LEFT JOIN biblio ON biblio.biblionumber = subq.biblionumber LEFT JOIN suggestions ON suggestions.biblionumber = biblio.biblionumber LEFT JOIN biblioitem ON biblioitem.biblionumber = biblio.biblionumber ``` `me.biblionumber` is always available in the inner query because me IS the inner query's table. And since `me.biblionumber` is the FK that points to `biblio.biblionumber`, it holds the same value. The correlation is semantically identical. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #201566|0 |1 is obsolete| | Attachment #201567|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 --- Comment #5 from David Nind <david@davidnind.com> --- Created attachment 201619 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=201619&action=edit Bug 42995: (regression tests) Nested +count embeds with has_many prefetch This patch adds regression tests for the 500 Internal Server Error triggered when GET /api/v1/acquisitions/orders is called with nested +count embeds (e.g. biblio.items+count) combined with a has_many prefetch (e.g. biblio.suggestions.suggester) and pagination. The root cause is that DBIC's limiting subquery optimization places +select scalar refs in the inner subquery where the parent join alias (biblio) is not yet available, resulting in: DBD::mysql: Unknown column 'biblio.biblionumber' in 'WHERE' The regression was introduced in: commit e51334604fc61ecdf259686e778ba31a94697fe2 Bug 41950: Make +count embeds sortable via SQL COUNT subqueries Test plan: 1. Apply this patch 2. Run: $ ktd --shell k$ prove t/db_dependent/api/v1/acquisitions_orders.t => FAIL: The new subtest fails with 500 errors instead of 200 3. Sign off :-D Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 --- Comment #6 from David Nind <david@davidnind.com> --- Created attachment 201620 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=201620&action=edit Bug 42995: Fix nested +count subqueries with pagination and has_many When nested +count embeds (e.g. biblio.items+count) are used alongside a has_many prefetch (e.g. biblio.suggestions or the implicitly added biblio.biblioitem) and pagination, DBIC's limiting subquery optimization places +select scalar refs in the inner subquery where the parent join alias (e.g. 'biblio') is not yet available. This patch fixes _build_count_subquery to resolve the correlation back to the main table (me) via the FK chain. Instead of generating: (SELECT COUNT(*) FROM items WHERE items.biblionumber = biblio.biblionumber) It now generates: (SELECT COUNT(*) FROM items WHERE items.biblionumber = me.biblionumber) This works because me.biblionumber IS the FK to biblio, holding the same value. The main table columns are always available regardless of DBIC's query structure. Changes: - Add $main_source parameter to _build_count_subquery for FK resolution - Thread main_source through _parse_prefetch and dbic_merge_prefetch - When parent_alias != 'me', resolve through parent relationship condition - Update Query.t assertion to match the new correlation expression Test plan: 1. Apply the regression tests from the previous commit 2. Run: $ ktd --shell k$ prove t/db_dependent/api/v1/acquisitions_orders.t => FAIL: The Bug 42995 subtest fails with 500 errors 3. Apply this patch, repeat from step 2 k$ prove t/Koha/REST/Plugin/Query.t \ t/db_dependent/Koha/Object.t \ t/db_dependent/Koha/REST/Plugin/Objects.t \ t/db_dependent/api/v1/acquisitions_orders.t \ t/db_dependent/api/v1/acquisitions_vendors.t \ t/db_dependent/api/v1/ill_requests.t \ t/db_dependent/api/v1/tickets.t \ t/db_dependent/api/v1/patrons.t => SUCCESS: Tests pass! 4. Sign off :-D Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |40575 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40575 [Bug 40575] Transferring order will reload the page and its form -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 Bug 42995 depends on bug 41950, which changed state. Bug 41950 Summary: Make +count embeds sortable by using SQL-level COUNT subqueries https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41950 What |Removed |Added ---------------------------------------------------------------------------- Status|Needs documenting |RESOLVED Resolution|--- |FIXED -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com --- Comment #10 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- *** Bug 43042 has been marked as a duplicate of this bug. *** -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 Michaela Sieber <michaela.sieber@kit.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |clemens.tubach@kit.edu, | |michaela.sieber@kit.edu, | |raphael.straub@kit.edu -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 Pedro Amorim (ammopt) <pedro.amorim@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to main Version(s)| |26.11.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 --- Comment #11 from Pedro Amorim (ammopt) <pedro.amorim@openfifth.co.uk> --- Thanks everyone! Pushed to main for 26.11! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 --- Comment #12 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Please rebase for 26.05 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Resolution|--- |FIXED Status|Pushed to main |RESOLVED --- Comment #13 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Missing 26.05.x dependency, no backport. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org