Strange float behavior
Hi,
I have strange behavior with floats.
When i insert specific floats to table in specific order and
then make query. I should get 0 but i get very small number.
DROP TABLE test;
create table test
(
col1 float,
col2 float
);
INSERT INTO test(col1,col2) VALUES(20,47);
INSERT INTO test(col1,col2) VALUES(34.4,6);
INSERT INTO test(col1,col2) VALUES(20,-47);
INSERT INTO test(col1,col2) VALUES(34.4,-6);
SELECT
SUM(col1*col2)
FROM
test;
(sum)
1,13686838e-13
When i change the order of inserts, i get 0.
DROP TABLE test;
create table test
(
col1 float,
col2 float
);
INSERT INTO test(col1,col2) VALUES(20,47);
INSERT INTO test(col1,col2) VALUES(20,-47);
INSERT INTO test(col1,col2) VALUES(34.4,6);
INSERT INTO test(col1,col2) VALUES(34.4,-6);
SELECT
SUM(col1*col2)
FROM
test;
(sum)
0,00
System this is tested:
IBM Informix Dynamic Server Version 12.10.FC2IE
IBM Informix Dynamic Server Version 12.10.FC3E
On Wed, Mar 22, 2017 at 06:22 MATTI JAATINEN <matti.jaatinen@norelco.fi>
wrote:
> I have strange behavior with floats.
> When i insert specific floats to table in specific order and
> then make query. I should get 0 but i get very small number.
>
> DROP TABLE test;
> create table test
> (>
> col1 float,
>
> col2 float
> );
> INSERT INTO test(col1,col2) VALUES(20,47);
> INSERT INTO test(col1,col2) VALUES(34.4,6);
> INSERT INTO test(col1,col2) VALUES(20,-47);
> INSERT INTO test(col1,col2) VALUES(34.4,-6);>
> SELECT
> SUM(col1*col2)
> FROM
> test;
>
> (sum)
>
> 1,13686838e-13
>
> When i change the order of inserts, i get 0.
>
> DROP TABLE test;
> create table test
> (>
> col1 float,
>
> col2 float
> );
> INSERT INTO test(col1,col2) VALUES(20,47);
> INSERT INTO test(col1,col2) VALUES(20,-47);
> INSERT INTO test(col1,col2) VALUES(34.4,6);
> INSERT INTO test(col1,col2) VALUES(34.4,-6);>
> SELECT
> SUM(col1*col2)
> FROM
> test;
>
> (sum)
>
> 0,00
>
> System this is tested:
> IBM Informix Dynamic Server Version 12.10.FC2IE
This is fairly ordinary behaviour for binary floating point values. They
cannot represent all decimal values exactly, so there are minor
discrepancies between what you type and what is stored, and these can
create anomalies like what you show. If accuracy is crucial in the decimal
arithmetic, use the DECIMAL type. If you use FLOAT (C double type), you
have decided that such discrepancies are not a problem.
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2015.1101 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--001a1140331ef28fc3054b528aa4