money.transaction_billing_summary view displays incorrect billing_type and billing_note for the actual last transaction
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Evergreen |
Fix Released
|
High
|
Unassigned | ||
2.8 |
Fix Released
|
Undecided
|
Unassigned | ||
2.9 |
Fix Released
|
Undecided
|
Unassigned |
Bug Description
Evergreen 2.3.6
OpenSRF 2.1.2
PostgreSQL 9.1.9
Ubuntu 12.04
The money.transcati
View definition:
CREATE OR REPLACE VIEW money.transacti
SELECT billing.xact, last(billing.
FROM money.billing
WHERE billing.voided IS FALSE
GROUP BY billing.xact
ORDER BY max(billing.
Running that query with a specific transaction:
SELECT billing.xact, last(billing.
FROM money.billing
WHERE billing.voided IS FALSE
AND billing.xact = 51632427
GROUP BY billing.xact
ORDER BY max(billing.
xact | last_billing_type | last_billing_note | last_billing_ts | total_owed
-------
51632427 | Overdue materials | System Generated Overdue Fine | 2010-08-31 16:10:55.392861-04 | 64.95
(1 row)
but, selecting all billings from that transaction shows this:
id | xact | billing_ts | voided | voider | void_time | amount | billing_type | note | btype
-------
112214932 | 51632427 | 2010-08-31 16:10:55.392861-04 | f | | | 59.95 | Lost Materials | SYSTEM GENERATED | 3
91178266 | 51632427 | 2009-11-15 23:00:00-05 | f | | | 0.10 | Overdue materials | System Generated Overdue Fine | 1
91115555 | 51632427 | 2009-11-13 23:00:00-05 | f | | | 0.10 | Overdue materials | System Generated Overdue Fine | 1
90955863 | 51632427 | 2009-11-12 23:00:00-05 | f | | | 0.10 | Overdue materials | System Generated Overdue Fine | 1
90955862 | 51632427 | 2009-11-11 23:00:00-05 | f | | | 0.10 | Overdue materials | System Generated Overdue Fine | 1
90777700 | 51632427 | 2009-11-10 23:00:00-05 | f | | | 0.10 | Overdue materials | System Generated Overdue Fine | 1
The billing_ts matches the view, but the note and billing_type do not.
Changed in evergreen: | |
milestone: | 2.6.0-rc1 → 2.next |
no longer affects: | evergreen/2.4 |
tags: | removed: pullrequest |
no longer affects: | evergreen/2.5 |
Changed in evergreen: | |
assignee: | nobody → Dan Wells (dbw2) |
Changed in evergreen: | |
status: | Fix Committed → Fix Released |
Thank you to Mike Rylander who proposed this solution to the problem with that particular view:
CREATE OR REPLACE VIEW money.transacti on_billing_ summary AS
LAST_VALUE( billing_ type) OVER w AS last_billing_type,
LAST_VALUE( note) OVER w AS last_billing_note,
MAX(billing_ ts) OVER w AS last_billing_ts,
SUM(COALESCE( amount, 0)) OVER w AS total_owed
SELECT DISTINCT xact,
FROM money.billing
WHERE voided IS FALSE
WINDOW w AS (PARTITION BY xact ORDER BY billing_ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
As he mentioned, there are many others to convert to using window functions. I have identified a list:
-- money.billable_ xact_summary_ new xact_with_ void_summary transaction_ billing_ summary transaction_ billing_ type_summary transaction_ payment_ summary on_billing_ type_summary on_billing_ with_void_ summary on_payment_ summary on_payment_ with_void_ summary
-- money.billable_
-- money.open_
-- money.open_
-- money.open_
-- money.transacti
-- money.transacti
-- money.transacti
-- money.transacti
I'm not sure how involved changing all of those would be, but it sounds like project.