Be Very Afraid Of Floating Point Numbers

What does this statement in the Help mean, exactly?

Values held as DOUBLE are accurate to 15 significant digits, but may be subject to rounding error beyond the fifteenth digit.

In the classroom or laboratory, it probably means this:

Values held as DOUBLE are accurate to 15 significant digits, but may be subject to rounding error beyond the fifteenth digit.

In the real world, where people write programs to do real things with real data, it means this:

Values held as floating point cause all sorts of problems and should be avoided at all costs. When you can't avoid them, convert them. When you can't convert them, don't trust them: Test them, check them, and fix them when you find errors in the data. Do it in your code, don't wait for your users to notice drifting data six months down the road.

Here's an example that doesn't involve any arithmetic, so we can't blame the algorithms:

• An application written using Microsoft Access has a large number of columns with the "Data Type" defined as "Number" and the "Field Size" set to "Double" instead of "Long Integer" or "Decimal" or something safe and manageable.

• One of these columns is the primary key for a table with over 3 million rows.

• The SQL Anywhere 12 migration wizard is used to convert the Access database into a SQL Anywhere database, and it dutifully defines the primary key as DOUBLE... after all, what else can it do? That's what Access says it is.

• After migrating, most of the primary key values look like integers; they have values like 11, 21 and 3208547315.

• However, over a million rows have values that look like this: 1020661200.9999999, 1020661224.0000001, 3108547316.0000005 and 3108547313.9999995.

Yes, those are all way out past the fifteenth digit.

I don't care where the problem comes from, Access, SQL Anywhere or the migration process. This kind of crap always happens when you mess with floating point, and in this case I just want it expunged, excised, gone, done with.

I'm not going to take a chance with a situation like this...

SELECT ( SELECT COUNT(*) FROM t WHERE pkey - TRUNCNUM ( pkey, 0 ) = 0 )  AS "Integers", 
       ( SELECT COUNT(*) FROM t WHERE pkey - TRUNCNUM ( pkey, 0 ) <> 0 ) AS "Not Integers";

Integers   Not Integers
1,917,541  1,252,134

I'm going to bite the bullet, do this...

UPDATE t
   SET pkey = ROUND ( pkey, 0 )
 WHERE pkey - TRUNCNUM ( pkey, 0 ) <> 0;

1252134 row(s) updated
Execution time: 272.574 seconds

Then I'm going to UNLOAD that table, change the data type from DOUBLE to BIGINT, and reload.

Or do an ALTER TABLE, that might be fun.

Either way, no more DOUBLE.

No more fear.