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
---------------------
Result is
-$0.01 -$11,250,546.74 $11,250,546.75 -$0.02 -$0.01
This be wrong.
Figuring it must be Money type dropped and recreated the view without
the money casting.
------------The Fixed 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) 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) 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) 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) AS difference, sum(
CASE
WHEN g.gltrans_date <= p.period_end THEN g.gltrans_amount
ELSE 0.0
END) 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
GROUP BY p.period_id, a.accnt_number, p.period_start, p.period_end,
a.accnt_id, a.accnt_descrip, p.period_yearperiod_id, a.accnt_type
ORDER BY p.period_id, a.accnt_number;
---------------End Select --------------------
The above query results in this which is what i would expect.
0.00000000 -11250546.74375232 11250546.74375232
0.00000000 0.00000000
Now knowing for sure its in Money type casting i do this select statement
----------------------
Select
'2',
sum( beginBalance )::text::money as beginbalance,
sum( negative )::text::money as debit,
sum( positive )::text::money as credit,
sum( difference )::text::money as difference,
sum( endbalance)::text::money as endbalance
from trailbalance
union
Select
'1',
sum( beginBalance::text::money) as beginbalance,
sum( negative::text::money) as debit,
sum( positive::text::money) as credit,
sum( difference::text::money) as difference,
sum( endbalance::text::money) as endbalance
from trailbalance
-------------------------
The results I think very interesting
"1" -$0.01 -$11,250,546.74 $11,250,546.75 -$0.02; -$0.01
"2" $0.00 -$11,250,546.74 $11,250,546.74 $0.00 $0.00
As you can see casting to money before sum() is called are incorrect
Can anyone else confirm this odd behavior when casting to Money type.
Thank you for your time and patience reading this long post....
