Floating Point Precision Issues on Decimal Column

[Brian H.]

Sorry that this is off M2 topic. There is still no other decent CF
forum on GG.

I just ran into a problem for the first time.

I have a banking-type application that stores "Decimal" type numbers
in MySQL with two decimal places ( Decimal(10,2) ).

I just wrote a simple script which queries the DB, pulls out some
decimal numbers, and adds them and spits out the result. I got:

2292.95 + 51.05 = 2344
2344 + 77.30 = 2421.3
2421.3 + -2421.30 = 4.54747350886E-013
4.54747350886E-013 + 99.61 = 99.61
99.61 + 99.61 = 199.22
199.22 + 48.43 = 247.65
247.65 + 77.30 = 324.95

Notice the 4.54747350886E-013. It appears that cold-fusion is doing
floating point subtraction in this case. I can't believe that in all
my years of CF, I've never ran into this issue. I tried using JavaCast
to Double and BigDouble around all my operands, but no cake. In the
end, I wrapped the operation in PrecisionEvaluate(...) and I get the
correct results. Apparently it uses the Java BigDecimal type.

2292.95 + 51.05 = 2344.00
2344.00 + 77.30 = 2421.30
2421.30 + -2421.30 = 0.00
0.00 + 99.61 = 99.61
99.61 + 99.61 = 199.22
199.22 + 48.43 = 247.65
247.65 + 77.30 = 324.95

Two things that confuse me. Why did casting as BigDouble not solve
this problem. Why did coldfusion chose "float" as the type, since the
MySQL type was NOT float, it was Decimal(10,2). I did a GetMetaData()
on the query, and the numeric columns have "TypeName" = "DECIMAL" as
they should.

Weird. Thanks for any insight!

-Brian

[Peter J. Farrell]

Brian H. said the following on 08/12/2010 10:27 PM:

> Notice the 4.54747350886E-013. It appears that cold-fusion is doing
> floating point subtraction in this case. I can't believe that in all
> my years of CF, I've never ran into this issue. I tried using JavaCast
> to Double and BigDouble around all my operands, but no cake. In the
> end, I wrapped the operation in PrecisionEvaluate(...) and I get the
> correct results. Apparently it uses the Java BigDecimal type.
>

We use Java BigInteger for calculating future timestamps (in ms -- Unix
epoch) in the caching layer because the scientific notation issue.

> Two things that confuse me. Why did casting as BigDouble not n solve
> this problem.Why did coldfusion chose "float" as the type, since the

> MySQL type was NOT float, it was Decimal(10,2). I did a GetMetaData()
> on the query, and the numeric columns have "TypeName" = "DECIMAL" as
> they should.
>

MySQL datatypes and the underlying datatype the your CFML uses may not
exactly one to one compatible. There is not "numeric" datatype in Java
(which ultimately will be the decider) and so it appears CF used the
Java primitive "float" as the Java type. This however is wrong
according to the conversion chart for the JDBC driver -- stating the
"decimal" in MySQL should be "BigDecimial" in Java:

http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-type-conversions.html

Base on a couple of comments on Ben Nadel's blog, it looks like the
behavior changed to this in CF8+. Could be that they switched the
official MySQL driver in that release. It's hard to tell what's going
on because CF is a closed source product.

.pjf

[Brian H.]

Thanks for the response and time for checking into it.

I think you are on to it saying that the JDBC driver should be
selecting BigDecimal. I am connecting using CF9's standard MySQL 4/5
driver, which I think does use JDBC, and maybe there was a
miscommunication between docs and actual programming. It seems like
this would be a very unusual oversight though since you would think
this issue would come up frequently.

-Brian

On Aug 12, 11:54 pm, "Peter J. Farrell" <pe...@mach-ii.com> wrote:
> Brian H. said the following on 08/12/2010 10:27 PM:> Notice the 4.54747350886E-013.  It appears that cold-fusion is doing
> > floating point subtraction in this case. I can't believe that in all
> > my years of CF, I've never ran into this issue. I tried using JavaCast
> > to Double and BigDouble around all my operands, but no cake. In the
> > end, I wrapped the operation in PrecisionEvaluate(...) and I get the
> > correct results. Apparently it uses the Java BigDecimal type.
>
> We use Java BigInteger for calculating future timestamps (in ms -- Unix
> epoch) in the caching layer because the scientific notation issue.
>
> > Two things that confuse me.  Why did casting as BigDouble not n solve
> > this problem.Why did coldfusion chose "float" as the type, since the
> > MySQL type was NOT float, it was Decimal(10,2). I did a GetMetaData()
> > on the query, and the numeric columns have "TypeName" = "DECIMAL" as
> > they should.
>
> MySQL datatypes and the underlying datatype the your CFML uses may not
> exactly one to one compatible.  There is not "numeric" datatype in Java
> (which ultimately will be the decider) and so it appears CF used the
> Java primitive "float" as the Java type.  This however is wrong
> according to the conversion chart for the JDBC driver -- stating the
> "decimal" in MySQL should be "BigDecimial" in Java:
>

> http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-type-con...

[Peter J. Farrell]

Actually, the JBDC driver is probably returning the correct Java
datatype for the column datatype however CF is probably casting that
into the another datatype for the underlying structure. Most CF stuff
is cast on the fly -- for some reason it's casting to float and loosing
the precision hence the need to PrecisionEvaluate().

Brian H. said the following on 08/13/2010 12:33 AM: