Incorrect total circs for current/previous year in Item Status

Bug #2018534 reported by Jeff Davis
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Evergreen
Fix Released
Medium
Unassigned
3.10
Fix Released
Medium
Unassigned
3.9
Fix Released
Medium
Unassigned

Bug Description

EG 3.9+
PostgreSQL 14

In Item Status Detail View, the "Total Circs - Current Year" and "Total Circs - Prev Year" counts always show as 0, instead of showing the actual number of circs for that item.

The issue appears to be that the open-ils.pcrud.search.circbyyr.atomic API call returns the year as a string in some environments, rather than as a number. Specifically, in my experience, the year is returned as a string when using Postgres 14, but as a number when using Postgres 9.6. The year needs to be numeric in order for the staff client JS to manipulate it.

I don't know why the Postgres version matters since the year is ultimately EXTRACT'ed from a timestamp, and such values ought to be numeric in all versions of Postgres.

(This bug is not to be confused with bug 1746300.)

Changed in evergreen:
assignee: nobody → Jeff Davis (jdavis-sitka)
Revision history for this message
Jeff Davis (jdavis-sitka) wrote (last edit ):

Working branch user/jeffdavis/lp2018534-numeric-circbyyr fixes the issue by forcing the staff client to treat circbyyr years as numbers:

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/jeffdavis/lp2018534-numeric-circbyyr

Test plan:

1. On a server with a Postgres 14 database, go to Item Status and scan an item with at least one circ for the current year.
2. Click Detail View. "Total Circs - Current Year" shows 0 circs.
3. Apply the fix.
4. Repeat steps 1-2. "Total Circs - Current Year" should now show the correct circ count for this year (and the previous year).

Kudos to Jason Boyer for noticing that Postgres changed the return type for EXTRACT from double precision to numeric; this appears to be what introduced the problem. The return type was changed in PG14, so Postgres versions up to and including PG13 will be unaffected by this bug.

tags: added: pullrequest
Changed in evergreen:
assignee: Jeff Davis (jdavis-sitka) → nobody
milestone: none → 3.11-beta
Revision history for this message
Mike Rylander (mrylander) wrote :

Jeff,

Thanks, that looks like a perfectly wonderful fix for the issue. I'll commit this unless someone beats me to it.

For future-us, the background/root-cause of this is that Postgres's NUMERIC type is only limited by the general field size limitations of the backend (generally 1GB worth of ASCII characters), whereas other number-ish types (INT, BIGINT, etc) have upper and lower limits that allow us to map them to normal number-like types in the languages EG is implemented in. So, for NUMERIC, we map to the language-native string type to avoid rounding, overflow, or the like.

In this particular case, an alternate solution would be to cast the column's type to, say, INT, and then it would be number-ish in the intermediate JSON. It wouldn't be crazy to do that in addition to Jeff's fix, also, since we know the range of reasonable values for this column/field.

tags: added: itemstatus
Revision history for this message
Mike Rylander (mrylander) wrote :

As promised, I've picked this to master for 3.11 inclusion. Thanks, Jeff!

Changed in evergreen:
status: New → Fix Committed
Revision history for this message
Mike Rylander (mrylander) wrote :

Also pushed into the branches fro 3.10 and 3.9.

Changed in evergreen:
status: Fix Committed → Fix Released
Galen Charlton (gmc)
Changed in evergreen:
importance: Undecided → Medium
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.