Re: Floating point error

Here is a smaller test case that does not involve Java. I guess this probably is just due to floating point error when the initial value is inserted that is too large for the field but it's still a surprise.

Create a test table, insert a couple values and view the results:

CREATE TABLE test (
id INTEGER PRIMARY KEY,
value REAL NOT NULL
);

INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885);
SELECT * FROM test;

id | value
----+---------
1 | 10.3885
2 | 10.3885
(2 rows)

At this point you would think you have two equal values. Now change the type:

ALTER TABLE test ALTER COLUMN value TYPE DOUBLE PRECISION;
SELECT * FROM test;

id | value
----+------------------
1 | 10.3884572982788
2 | 10.388500213623
(2 rows)

Values no longer equal and the first one is in fact closer to what as originally inserted. Why is this? Is this simply caused by how the initially inserted value is stored as floating point?

If you create a "copy" of the database before changing the field type then both values get inserted as "10.3885." Changing the type then results in two equal values. Maybe this is just another pitfall of using floating point numbers and at this point I am just trying to identify exactly where our errors are being introduced so can anyone confirm the above behavior is correct? In our real world example we are not changing the type but are instead getting the second value w/id = 1 above when using JDBC to retrieve values into a Java double field.

I ran the above on PostgreSQL 9.1.2 and 9.2.2 with the same results.

Tom

On Feb 24, 2013, at 9:17 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:

> On 02/24/2013 06:58 PM, Tom Duffey wrote:
>>
>> On Feb 24, 2013, at 8:44 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
>>
>>> On 02/24/2013 06:13 PM, Tom Duffey wrote:
>>>> Hi Everyone,
>>>>
>>>> Riddle me this. I have a database column of type "real" that gets mapped to a Java field of type double via JDBC. We have two databases, test and production, and the test database is periodically blown away and reloaded from a copy of production. We recently noticed that some values do not match when viewed within our application on test vs. production. More specifically:
>>>>
>>>> - Selecting values from both test and production DBs using psql shows "10.3885" as the value
>>>> - The Java app on production shows "10.3884573" while the test app shows "10.3885"
>>>>
>>>> I have a hunch that when the value was originally inserted into the production DB it probably contained more than the 6 digits supported by the real data type. It may have even been exactly the "10.3884573" value we see when retrieving via JDBC on production. What I don't understand is why when the value gets mapped back to Java via JDBC those extra digits are coming back. Can anyone explain this or do you think I'm on the wrong track? I stepped through code and it sure seems like the extra information is coming back from the JDBC driver.
>>>
>>> Are the production and test apps running on the same platform i.e. OS, bitness, etc.
>>
>> Yes, the production and test apps are running on the same platform. The Java apps themselves are physically on the same Linux server. The production and test databases reside within the same instance of PostgreSQL.
>>
>> Also, I should have mentioned up front that I am well aware of the pitfalls of using floating point values and also the fact that PostgreSQL's "real" data type supports 6 digits of precision. What I do not understand is why my JDBC driver is returning more information than what I receive in psql or if I operate on a copy of the database. This leads me to believe that more information was available at insertion time and is somehow being made available to my application even though the data type should only store 6 digits. Let me see if I can write a quick little test case.
>>
>
> Well I guess you could look in the dump file and see what is recorded there.
>
>> Tom
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com

--
Tom Duffey
tduffey(at)trillitech(dot)com
414-751-0600 x102