Bug 27964

Summary: Link to subscription in serial collections view is broken - the field subscriptionid is undefined in the subscription object.
Product: Koha Reporter: Andreas Jonsson <andreas.jonsson>
Component: SerialsAssignee: Bugs List <koha-bugs>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: enhancement    
Priority: P5 - low CC: janet.mcgowan, jean-manuel.broust
Version: 20.05   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:
Attachments: Bug 27964: Don't use wildcards for subscriptionhistory table in subscription queries

Description Andreas Jonsson 2021-03-16 10:16:20 UTC
The functions C4::Serials::GetSubscription and C4::Serials::GetSubscriptionsFromBiblionumber return subscriptions where subscriptionid is undefined when the subscription have no subscription history.

The database queries use wildcards to include fields from a left joined table. subscriptionhistory.subscriptionid will be NULL, which might be the value choosen as the field subscriptionid.

How the field is chosen may depend on the version of DBI as this problem was discovered after an upgrade to Ubuntu Focal Fossa from Bionic Beaver.
Comment 1 Andreas Jonsson 2021-03-16 10:22:51 UTC
Created attachment 118291 [details] [review]
Bug 27964: Don't use wildcards for subscriptionhistory table in subscription queries
Comment 2 Katrin Fischer 2021-06-04 00:21:46 UTC
Hi Andreas, could you please include a test plan and problem description in your commit message? Should this be Needs Signoff?
Comment 3 Katrin Fischer 2022-02-19 23:38:40 UTC
Can confirm this for 20.11, have to retest on master still.
Comment 4 Owen Leonard 2022-05-12 15:24:27 UTC
(In reply to Katrin Fischer from comment #2)
> Hi Andreas, could you please include a test plan and problem description in
> your commit message? Should this be Needs Signoff?

Katrin's questions still await a response!
Comment 5 Andreas Jonsson 2022-05-13 07:32:18 UTC
I think it is hard to come up with a test plan for this issue.  But it is obviously a problem that if several columns with the same name is included the resulting rows will not be well defined when fetched as hashes.

I know that there is code that crashes when the subscriptionhistory entry is missing.  This patch do not fix all problems, however.

The real problem is that the database schema is flawed.  The history table should, if designed according to normal database principles, contain one entry per historic number.  But it seem to be designed to always contain exactly one entry that that enumerates the historic numbers in one attribute.

However, deletes in the biblio table cascades to the subscriptionhistory table, so the subscriptionhistory entry can be missing.  Note that the biblionumber in the history entry may be different from the biblionumber in the subscription.