Not your daddy's floating point error

[Torben Frandsen]

Hi

I'm porting an old Access/VB application to .Net, and I've encountered a
small problem.

The VB application takes a value from a query and stores it in a Double.
This value is the result of a calculation involving three values all stored
in Access as Single. This is how the values bubble up:

Bottom query
(Foo*Bar) as Baz
Middle query
Sum(Baz) as Quux
Top query
(Quux * Gazonk) as Gloop

Let's put some real world numbers in there:
Foo = 100.2
Bar = .210666
(Only one row for the middle query)
Gazonk = .001

Access produces the result 0.021108731318773.

Before you warm up to lecture me on floating point arithmetics, let's see
what C# makes of it:

First, let's just use float/Single:

?.210666f*100.2f*.001f
0.0211087335

I get an error, but not the error I was looking for. Perhaps Access casts to
a double?

?(double).210666f*100.2f*.001f
0.021108733644182805

Nope, the error is moving in the wrong direction. Does it cast everything to
double and then do the math?

?.210666d*100.2d*.001d
0.0211087332

Whaddaya know! This is the mathematically correct result. Unfortunately, I'd
like the old well-known error. So does it cast everything to double and then
jam the result back into a float/Single?

?(float).210666d*100.2d*.001d
0.021108733284473422

Guess not. So, what is going on? Is there any way for me in C# to reproduce
the results from Access?

(And yes, it does matter. When you pour numbers like these into a 95x95
linear equation system, the errors scale up, and it becomes close to
impossible to compare the results.)

Thanks,
Torben

 

[Jon Skeet [C# MVP]]

On Nov 2, 12:15 pm, "Torben Frandsen" <[email protected]å.privat.tele.dk>
wrote:

Guess not. So, what is going on? Is there any way for me in C# to reproduce
the results from Access?

(And yes, it does matter. When you pour numbers like these into a 95x95
linear equation system, the errors scale up, and it becomes close to
impossible to compare the results.)

Are you actually trying to get the closest results to "completely
accurate" or are you trying to emulate the Access behaviour? There's a
massive difference.

Note also that even though the debugger may *display* the
mathematically correct answer in some cases, the double that is
closest to that answer is actually:

0.021108733200000000251872478429504553787410259246826171875

Now, for simple multiplications you may find that decimal is what you
want - but it's a complicated decision to make, and depends on what
the figures actually represent etc.

Jon

 

[Torben Frandsen]

Are you actually trying to get the closest results to "completely
accurate" or are you trying to emulate the Access behaviour? There's a
massive difference.

I'm trying to emulate the Access behavior. Sorry if I didn't make that
completely clear.

Note also that even though the debugger may *display* the
mathematically correct answer in some cases, the double that is
closest to that answer is actually:

0.021108733200000000251872478429504553787410259246826171875

Oops

Now, for simple multiplications you may find that decimal is what you
want - but it's a complicated decision to make, and depends on what
the figures actually represent etc.

I agree, and I'm sure I would have done a few things differently if only ...


Thanks,
Torben

 

[Jon Skeet [C# MVP]]

I'm trying to emulate the Access behavior. Sorry if I didn't make that
completely clear.

Hmm. In that case it's going to be pretty tricky, I suspect. I
wouldn't be surprised if it behaved in subtly different ways depending
on exactly what you're doing. Be *very* careful around conversions to
strings - just remember that they're pretty much all going to be lies.

Oh, and the cake is a lie too.

I agree, and I'm sure I would have done a few things differently if only ....

Out of interest, why is compatibility with Access more important
than "true" accuracy? Just wondering.

Jon

 

[Torben Frandsen]

Hmm. In that case it's going to be pretty tricky, I suspect. I
wouldn't be surprised if it behaved in subtly different ways depending
on exactly what you're doing.

I think it does. I'm just hoping someone could tell me the secret settings
on the transmogrifier.

Be *very* careful around conversions to
strings - just remember that they're pretty much all going to be lies.

I know. But the differences become very real after solving 98 equations with
98 variables 14 times.

Oh, and the cake is a lie too.

So they say. But the recipe is real, isn't it?

Out of interest, why is compatibility with Access more important
than "true" accuracy? Just wondering.

For debugging reasons, at least for now. When things start slipping in a big
nasty algorithm, it can be very hard to see if I'm doing the wrong thing
with the right numbers, the right thing with the wrong numbers or even the
wrong thing with the wrong numbers. Later when I have proven to the customer
that their old solution is producing wrong results, it will be because they
don't want another invoice.

I'm afraid my only option is to get my hands dirty in the old code base and
see what happens

Thanks,
Torben

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Bottom query
(Foo*Bar) as Baz
Middle query
Sum(Baz) as Quux
Top query
(Quux * Gazonk) as Gloop

Let's put some real world numbers in there:
Foo = 100.2
Bar = .210666
(Only one row for the middle query)
Gazonk = .001

Access produces the result 0.021108731318773.

I think we will need more info about the Access part. Column
types, SQL statements, VBA code etc..

Because when I tried recreating in Access I got 0.0211087332 !

Arne

 

[Torben Frandsen]

I think we will need more info about the Access part. Column
types, SQL statements, VBA code etc..

Okay, the involved values are all defined as Single. There are two queries
and not three as first mentioned. The bottom one says

SELECT Sum(t1.Foo*t2.Bar) AS Quux
FROM t1 INNER JOIN t2 -- etc.

The top query says

SELECT BottomQuery.Quux * Parameters.FooFactor AS Gazonk
FROM BottomQuery.

There is no code involved at this stage.

Because when I tried recreating in Access I got 0.0211087332 !

That's odd. I just tried to reproduce the problem and I got an entirely new
result: 0.211087317516938.