2

I have a class representing specific database table. Some the properties in this class as well as some of the fields in database table are type double.

Here, in the class, you see some properties have 1 significant digit eg. T12, T13, T18, T19 (0.8, 12.1 etc).

Class view

However when I execute insertion of this class into database table, Linq2Db produces such a query:

Query view

When I look into database table, fields contains proper one-significant-digit values:

Database table view

I know perfectly how floating point numbers are represented in binary format. I've already read article Why Are Floating Point Numbers Inaccurate?.

My questions:

  1. Why double type repesentation in class (T12 = -0.8) differs from representation after 'Linq2Db' manipulates it (T12 = -0.800...004)?

  2. Is it safe to save such double values in database? (I would like to prevent from having inaccurate values)

  3. Why is there a difference between values in query and values in database table?

Edit 1:

This is a result of query on database. 15 significant digits (all 0's) vs 17 significant digits (screen above). Access can't show more than 15 digits.

Query on database

Edit 2:

Decimal               0.8
Class Double          0.8
Floating-Point Double 0.8000000000000000444089209850062616169452667236328125
Floating-Point Single 0.800000011920928955078125 (for comparison)
SQL Query             0.80000000000000004 (!)  => 17 digits after point (why?)
MS Access             0.800000000000000 = 0.8  => 15 digits after point

Why double type in class can be 0.8 but the same value without any manipulations or arithmetic operations is inserted into database as 0.80000000000000004? What 'Linq2Db' does with it?

yarecky
  • 97
  • 8
  • Probably, the full values are stored in the database. While one significant digit is _displayed_, the full value is stored. Try querying back the value, you'll probably get the same value Linq2Db inserted. If you want to store specific values with a certain accuracy, I recommend the `Decimal` data type, or the `Currency` data type when working with Access and an mdb file. – Erik A May 03 '18 at 12:58
  • What's the definition of "safe" here? If you don't want inaccuracies then don't choose float. Use a `decimal` type, but understand that there is a slight performance difference and that is what you are sacrificing for data accuracy. – Jacob H May 03 '18 at 12:59

2 Answers2

2

linq2db use 17 significant digits to represent double value as decimal SQL literal, because it is minimal number of digits that allow you to roundtrip double value to decimal representation and back safely without precision lost.

In your case it is not necessary, because Access (and Excel BTW) doesn't support more that 15 significant digits for double type.

1

If you want an accurate representation of decimal values, then switch to decimal rather than floating point representations.

A number like 0.8 cannot be accurately represented using a fixed number of binary decimal digits (however, 0.5 and 0.75 can be). What you actually see depends on the application displaying the result, not the way that it is stored.

So, just use the data type that corresponds to what you really want, a fixed point decimal.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • After quering data I see perfectly rounded values with 0's to the end. The only difference is that Ms Access can show maximum 15 digits after dot, where screen from Visual Studio shows 17 digits and last one is different than 0. See here: [link](https://imgur.com/a/tbazS5H). – yarecky May 03 '18 at 13:13