Re: [HACKERS] math error or rounding problem Money type
On 6/8/08, Gregory Stark [EMAIL PROTECTED] wrote: I don't think as late as possible applies with money. If you were dealing with approximate measurements you want to round as late as possible because rounding is throwing away precision. But if you're dealing with money you're dealing with exact quantities. There is only going to be one correct time to round and that's whenever you're creating an actual ledger item or order line item or whatever. Once you've calculated how much interest to credit or whatever you have to make that credit an exact number of cents and the $0.004 you lost or gained in rounding never comes up again. Completely correct. In a proper accounting system you can only pull from a very limited subset of arithmetic operations. 'rounding' is not one of them except in the special case you mention above. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] math error or rounding problem Money type
Gregory Stark wrote: Mark Kirkwood [EMAIL PROTECTED] writes: IFAIK (dimly recalling numerical analysis courses at university) SUM and ROUND can *never* be commuted. In general the recommended approach is to round as late as possible and as few times are possible - so your 1st query is the correct or best way to go. Justin, isn't your problem related precisely to what Tom said? Now, when you're casting to Money, you're doing a cast like that original_type - text - money (that's from your trailbalance view). I suspect the original_type is NUMERIC (and I think it's a very good type to keep your monetary data in). My guess: what happens is that you have numbers with more that 6 fractional digits in your original table, and they're kept as NUMERIC values. If you round them to the 6th fractional digit *before* summing them up, you can indeed get different results from what you'd get if you'd rounded them *after* doign the sum. Compare: =# select round(0.004 + 0.004, 6) ; round -- 0.01 (1 row) =# select round(0.004, 6) + round(0.004) ; ?column? -- 0.00 Do you see what (could've) happened? The first query is computed like this: round(0.004 + 0.004, 0) = round(0.008, 6) = 0.01 whereas the second one is more like: round(0.004, 6) + round(0.004, 6) = 0.00 + 0.00 = 0.00 Fractional parts that have been thrown away by the rounding may, when added up, become fractional parts that get significant when you're calculating the rounded value of the sum. So yes, probably the way to go is do *all* computations in NUMERIC and only cast when you're about to generate a report or present the data to the end user. Otherwise you risk losing some cents like that (and you need to be aware that a cast to MONEY *is* in fact a truncation, and you will not get mathematically correct results). Cheers, Jan -- Jan Urbanski GPG key ID: E583D7D2 ouden estin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] math error or rounding problem Money type
Mark Kirkwood [EMAIL PROTECTED] writes: IFAIK (dimly recalling numerical analysis courses at university) SUM and ROUND can *never* be commuted. In general the recommended approach is to round as late as possible and as few times are possible - so your 1st query is the correct or best way to go. I don't think as late as possible applies with money. If you were dealing with approximate measurements you want to round as late as possible because rounding is throwing away precision. But if you're dealing with money you're dealing with exact quantities. There is only going to be one correct time to round and that's whenever you're creating an actual ledger item or order line item or whatever. Once you've calculated how much interest to credit or whatever you have to make that credit an exact number of cents and the $0.004 you lost or gained in rounding never comes up again. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] math error or rounding problem Money type
I believe i have found a math bug/rounding problem with Money type when its used with SUM()... Postgresql 8.3.1 --- Background on the Problem We have gl_trans table with 92,000 rows with one column containing the positive and negative entries. In order to make this gl_trans table make more sense and to group the accounts in correct debits and credits along with type of accounts, A view was created that does grouping and sorting. To further make things easier the view casted the results into the Money Type just to make the select statements that call the view shorter. All looked great for several weeks till all of sudden the sumed values for all accounts goes out by 0.01. I needed to confirm this was a rounding problem and not a GL entry that was bad. ( if we had a bad entry this would scream we have a far bigger problem where the application allowed an GL entry to be committed that was out of balance) To confirm that all entries made have equal and opposite entry below select statement was created. The gltrans_sequence column is integer key that groups General Ledger entries together so all the sides of a specific entry can be found. select * from (select gltrans_sequence, sum(gltrans_amount) as Neg from gltrans where gltrans_amount 0 group by gltrans_sequence) as neg, (select gltrans_sequence, sum(gltrans_amount) as pos from gltrans where gltrans_amount 0 group by gltrans_sequence) as pos where neg.gltrans_sequence = pos.gltrans_sequence and neg.neg pos.pos*-1 This returns no records as expected... Now armed with that no entry was bad I suspected it had to be with the money data type. So I added explicit castings select * from (select gltrans_sequence, sum(gltrans_amount::text::money) as Neg from gltrans where gltrans_amount 0 group by gltrans_sequence) as neg, (select gltrans_sequence, sum(gltrans_amount::text::money) as pos from gltrans where gltrans_amount 0 group by gltrans_sequence) as pos where neg.gltrans_sequence = pos.gltrans_sequence and neg.neg pos.pos*-1 select * from (select gltrans_sequence, sum(gltrans_amount::text::money) as Neg from gltrans where gltrans_amount 0 group by gltrans_sequence) as neg, (select gltrans_sequence, sum(gltrans_amount::text::money) as pos from gltrans where gltrans_amount 0 group by gltrans_sequence) as pos where neg.gltrans_sequence = pos.gltrans_sequence and neg.neg::text::money pos.pos::text::money*-1 - select * from (select gltrans_sequence, sum(gltrans_amount) as Neg from gltrans where gltrans_amount 0 group by gltrans_sequence) as neg, (select gltrans_sequence, sum(gltrans_amount) as pos from gltrans where gltrans_amount 0 group by gltrans_sequence) as pos where neg.gltrans_sequence = pos.gltrans_sequence and neg.neg::text::money pos.pos::text::money*-1 --- Nothing resulted in showing a entry that was out of balance. --Identifying the problem --- So i turned my attention to the view which casted numeric type to Money. View is called trailbalance The Bad Select Statement that creates the View -- SELECT p.period_id, p.period_start, p.period_end, a.accnt_id, a.accnt_number, a.accnt_descrip, p.period_yearperiod_id, a.accnt_type, SUM(CASE WHEN g.gltrans_date p.period_start THEN g.gltrans_amount ELSE 0.0 END)::text::money AS beginbalance, SUM(CASE WHEN g.gltrans_date = p.period_end AND g.gltrans_date = p.period_start AND g.gltrans_amount = 0::numeric THEN g.gltrans_amount ELSE 0.0 END)::text::money AS negative, SUM(CASE WHEN g.gltrans_date = p.period_end AND g.gltrans_date = p.period_start AND g.gltrans_amount = 0::numeric THEN g.gltrans_amount ELSE 0.0 END)::text::money AS positive, SUM(CASE WHEN g.gltrans_date = p.period_end AND g.gltrans_date = p.period_start THEN g.gltrans_amount ELSE 0.0 END)::text::money AS difference, SUM(CASE WHEN g.gltrans_date = p.period_end THEN g.gltrans_amount ELSE 0.0 END)::text::money AS endbalance FROM period p CROSS JOIN accnt a LEFT JOIN gltrans g ON (g.gltrans_accnt_id = a.accnt_id AND g.gltrans_posted = true) where p.period_id = 58 group by p.period_id, p.period_start, p.period_end, a.accnt_id, a.accnt_number, a.accnt_descrip, p.period_yearperiod_id, a.accnt_type ORDER BY p.period_id, a.accnt_number; ---End Select The query that calls this View -- Select sum( beginBalance ) as beginbalance, sum( negative ) as debit, sum( positive ) as credit, sum( difference ) as difference, sum( endbalance) as endbalance from trailbalance -
Re: [HACKERS] math error or rounding problem Money type
Justin [EMAIL PROTECTED] writes: I believe i have found a math bug/rounding problem with Money type when its used with SUM()... Postgresql 8.3.1 You do know that money only stores two fractional digits? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] math error or rounding problem Money type
Tom Lane wrote: Justin [EMAIL PROTECTED] writes: I believe i have found a math bug/rounding problem with Money type when its used with SUM()... Postgresql 8.3.1 You do know that money only stores two fractional digits? regards, tom lane yes. The question is why are the to sides not equal anymore Take this Select '2', round(sum( beginBalance ),6) as beginbalance, round(sum( negative ),6) as debit, round(sum( positive ),6) as credit, round(sum( difference ),6) as difference, round(sum( endbalance),6) as endbalance from trailbalance union Select '1', sum( round(beginBalance,6)) as beginbalance, sum( round(negative,6)) as debit, sum( round(positive,6)) as credit, sum( round(difference,6)) as difference, sum( round(endbalance,6)) as endbalance from trailbalance 1 -0.06 -11250546.74375211250546.743752 0.00 -0.07 2 0.00-11250546.743752 11250546.743752 0.000.00 At the very least this show a clear warning when rounding do it after all the sum function is called not before.
Re: [HACKERS] math error or rounding problem Money type
Justin wrote: yes. The question is why are the to sides not equal anymore Take this Select '2', round(sum( beginBalance ),6) as beginbalance, round(sum( negative ),6) as debit, round(sum( positive ),6) as credit, round(sum( difference ),6) as difference, round(sum( endbalance),6) as endbalance from trailbalance union Select '1', sum( round(beginBalance,6)) as beginbalance, sum( round(negative,6)) as debit, sum( round(positive,6)) as credit, sum( round(difference,6)) as difference, sum( round(endbalance,6)) as endbalance from trailbalance 1 -0.06 -11250546.74375211250546.743752 0.00 -0.07 2 0.00-11250546.743752 11250546.743752 0.00 0.00 At the very least this show a clear warning when rounding do it after all the sum function is called not before. IFAIK (dimly recalling numerical analysis courses at university) SUM and ROUND can *never* be commuted. In general the recommended approach is to round as late as possible and as few times are possible - so your 1st query is the correct or best way to go. Cheers Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
