Perl

Consider the following program:

$x=12345678901.234567000;
$y=($x-int($x))*1000000000;
printf("%f->%f\n",$x,$y);

Here’s what it prints:

12345678901.234568->234567642.211914

I was expecting:

12345678901.234567->234567000

This appears to be some sort of rounding issue in Perl. Adding a new package seems to help:

use bignum;
$x=12345678901.234567000;
$y=($x-int($x))*1000000000;
printf("%f->%f\n",$x,$y);
print $x;

prints:

12345678901.234568->234567000
12345678901.234567

Notice how the second line is correct. The variable is fine, but printf is having some issues.

Note that if the variable is being converted from a string there are still issues. Consider the following:

use bignum;
$x="12345678901->234567000";
$y=($x-int($x))*1000000000;
printf("%f:%f\n",$x,$y);

prints:

12345678901.234568->234567642.211914

So use +0 to cast to a number:

$x="12345678901.234567000";
$x+=0;# Cast to a number
$y=($x-int($x))*1000000000;
printf("%f->%f\n",$x,$y);

prints:

12345678901.234568->234567000.000000

MySQL

The same rounding issue also appears in MySQL. Consider the following:

SELECT ('12345678901.234567'-FLOOR('12345678901.234567'))*1000000000;

prints:

234567642.211914

Take off the single quotes and all is well:

SELECT (12345678901.234567-FLOOR(12345678901.234567))*1000000000;

prints:

234567000.000000

It appears that the string to number conversion is broken in MySQL, so you have to define it explicitly. Unfortunately, you can’t just cast to a double:

SELECT (CAST('12345678901.234567' as double)-FLOOR(CAST('12345678901.234567' as double)))*1000000000;

prints:

SQL Error (1064): You have an error in your SQL syntax;

So, it’s necessary to use a decimal, but that fails too:

SELECT (CAST('12345678901.234567' as decimal)-FLOOR(CAST('12345678901.234567' as decimal)))*1000000000;

prints:

SQL Error (1064): You have an error in your SQL syntax;

Nice. Well, I guess I need to specify the width explicitly.

SELECT (CAST('12345678901.234567' as decimal(65,30))-FLOOR(CAST('12345678901.234567' as decimal(65,30))))*1000000000;

prints:

234567000.000000000000000000000000000000

PostgreSQL

The same thing happens in PostgreSQL:

SELECT ('12345678901.234567'-FLOOR('12345678901.234567'))*1000000000;

prints:

234567642.211914

And with float casts:

SELECT ('12345678901.234567'::float-FLOOR('12345678901.234567'::float))*1000000000;

prints:

234567642.211914

But, decimal works:

SELECT ('12345678901.234567'::decimal-FLOOR('12345678901.234567'::decimal))*1000000000;

234567000