INSERT/UPDATE with double floats lost precision

[hxzrx]

Double float may lost precision in INSERT or UPDATE queries with postmodern:query.

First, we can create a table with a double float column:

create table testfloat (
    col double precision not null default 0
);

Second, execute an INSERT query:

(postmodern:with-connection *database-config*
  (postmodern:query "insert into testfloat (col) values ($1)"
                    14.0d0))

Finally, the value 14.0d0 will be 13.999999999999998 in the database.

Thanks!

[sabracrolleton]

Can you check and see if you are seeing the same strange behavior I am using the following code (based on your example).

(defun t1-float ()
  (with-connection *database-config*
    (when (table-exists-p 'testfloat)
      (query (:drop-table 'testfloat)))
    (pomo:query "create table testfloat (col double precision not null default 0)")
   (pomo:query "insert into testfloat (col) values ($1)"
                    14.0d0)
   (pomo:query "insert into testfloat (col) values ($1)"
                    24.0d0)
   (pomo:query "insert into testfloat (col) values ($1)"
                    34.0d0)
   (pomo:query "select * from testfloat")))

(defun t1-float-no-create ()
  (with-connection *database-config*
   (pomo:query "insert into testfloat (col) values ($1)"
                    14.0d0)
   (pomo:query "insert into testfloat (col) values ($1)"
                    24.0d0)
   (pomo:query "insert into testfloat (col) values ($1)"
                    34.0d0)
   (pomo:query "select * from testfloat")))

(defun t2-float ()
  (with-connection *database-config*
    (when (table-exists-p 'testfloat)
      (query (:drop-table 'testfloat)))
    (pomo:query "create table testfloat (col double precision not null default 0)")
    (pomo:query "insert into testfloat (col) values (14.0)")
    (pomo:query "insert into testfloat (col) values ($1)"
                24.0d0)
    (pomo:query "insert into testfloat (col) values ($1)"
                34.0d0)
    (pomo:query "select * from testfloat")))

(defun t2-float-no-create ()
  (with-connection *database-config*
    (pomo:query "insert into testfloat (col) values (14.0)")
    (pomo:query "insert into testfloat (col) values ($1)"
                24.0d0)
    (pomo:query "insert into testfloat (col) values ($1)"
                34.0d0)
    (pomo:query "select * from testfloat")))

The only difference between t1-xxx and t2-xxx is that in t2-xxx the first insertion in each t2-xxx function is not a parameterized query.

Now if I use the t1-xxx versions:

(t1-float)
 ((13.999999999999998d0) (24.0d0) (34.0d0))

 (t1-float-no-create)
 ((13.999999999999998d0) (24.0d0) (34.0d0) (13.999999999999998d0) (24.0d0)
  (34.0d0))

The second and third insertions in each are fine, but the first insertion in each function has the problem you identified.

If I use the t2-xxx versions:

(t2-float)
((14.0d0) (24.0d0) (34.0d0))

(t2-float-no-create)
((14.0d0) (24.0d0) (34.0d0) (14.0d0) (24.0d0) (34.0d0))

All three insertions are as expected.

Are you seeing the same inconsistent result?

[sabracrolleton]

Also, as an additional can you add :use-binary t to your database-config parameters and run the above again? I am finding that using this parameter makes all the insertions correct.

I am still trying to track down the bad code.

[hxzrx]

Can you check and see if you are seeing the same strange behavior I am using the following code (based on your example).

(defun t1-float ()
  (with-connection *database-config*
    (when (table-exists-p 'testfloat)
      (query (:drop-table 'testfloat)))
    (pomo:query "create table testfloat (col double precision not null default 0)")
   (pomo:query "insert into testfloat (col) values ($1)"
                    14.0d0)
   (pomo:query "insert into testfloat (col) values ($1)"
                    24.0d0)
   (pomo:query "insert into testfloat (col) values ($1)"
                    34.0d0)
   (pomo:query "select * from testfloat")))

(defun t1-float-no-create ()
  (with-connection *database-config*
   (pomo:query "insert into testfloat (col) values ($1)"
                    14.0d0)
   (pomo:query "insert into testfloat (col) values ($1)"
                    24.0d0)
   (pomo:query "insert into testfloat (col) values ($1)"
                    34.0d0)
   (pomo:query "select * from testfloat")))

(defun t2-float ()
  (with-connection *database-config*
    (when (table-exists-p 'testfloat)
      (query (:drop-table 'testfloat)))
    (pomo:query "create table testfloat (col double precision not null default 0)")
    (pomo:query "insert into testfloat (col) values (14.0)")
    (pomo:query "insert into testfloat (col) values ($1)"
                24.0d0)
    (pomo:query "insert into testfloat (col) values ($1)"
                34.0d0)
    (pomo:query "select * from testfloat")))

(defun t2-float-no-create ()
  (with-connection *database-config*
    (pomo:query "insert into testfloat (col) values (14.0)")
    (pomo:query "insert into testfloat (col) values ($1)"
                24.0d0)
    (pomo:query "insert into testfloat (col) values ($1)"
                34.0d0)
    (pomo:query "select * from testfloat")))

The only difference between t1-xxx and t2-xxx is that in t2-xxx the first insertion in each t2-xxx function is not a parameterized query.

Now if I use the t1-xxx versions:

(t1-float)
 ((13.999999999999998d0) (24.0d0) (34.0d0))

 (t1-float-no-create)
 ((13.999999999999998d0) (24.0d0) (34.0d0) (13.999999999999998d0) (24.0d0)
  (34.0d0))

The second and third insertions in each are fine, but the first insertion in each function has the problem you identified.

If I use the t2-xxx versions:

(t2-float)
((14.0d0) (24.0d0) (34.0d0))

(t2-float-no-create)
((14.0d0) (24.0d0) (34.0d0) (14.0d0) (24.0d0) (34.0d0))

All three insertions are as expected.

Are you seeing the same inconsistent result?

I got the same inconsistent results as above. This kind of inconsistence is not rare, inserting 11.0d0 will get 11.000000000000002d0. As I tested , single-float did not meet this problem. I wrote a loop and inserted tens of thousands of random single-float numbers, and all tests were correct.

The PostgreSQL log showed that 13.999999999999998 was passed to the DBMS:

2024-07-10 10:31:26.853 CST,"lcmp","lcmp",23603,"127.0.0.1:43510",668deefd.5c33,21,"INSERT",2024-07-10 10:16:29 CST,8/8593,0,LOG,00000,"execute <unnamed>: insert into testfloat (col) values ($1)","parameters: $1 = '13.999999999999998'",,,,,,,,"LCMP-SERVER","client backend",,0

This issue seems quite like that of shasht. shasht uses cl:format to print float numbers to the stringified json objects and gets similar results as Postmodern.

[hxzrx]

Also, as an additional can you add :use-binary t to your database-config parameters and run the above again? I am finding that using this parameter makes all the insertions correct.

I am still trying to track down the bad code.

Yes, all cases are correct now. Thanks!
In my codes, I convert all float numbers to strings and the results are OK.

[sabracrolleton]

Now fixed. Thank you for bringing this to our attention.