Обсуждение: BUG #6096: ECPG set precision of double value to 14 digits

The following bug has been logged online:

Bug reference:      6096
Logged by:          Akira Kurosawa
Email address:      kurosawa-akira@mxc.nes.nec.co.jp
PostgreSQL version: 8.4.5
Operating system:   Red Hat Enterprise Linux 5.5
Description:        ECPG set precision of double value to 14 digits
Details:

When I used host variable of double type in ECPG code,
the precision of the data was treated as 14 digits.

I executed following command.

--------------------------------------------------
EXEC SQL BEGIN DECLARE SECTION;

double dbl;

EXEC SQL END DECLARE SECTION;

EXEC SQL CONNECT TO testdb@localhost:5432 USER postgres;

dbl = 12345678901234;  -- 14 digits
EXEC SQL INSERT INTO double_table (c1) VALUES (:dbl);
dbl = 1234567890.1234;  -- 14 digits
EXEC SQL INSERT INTO double_table (c1) VALUES (:dbl);
dbl = 0.12345678901234;  -- 14 digits
EXEC SQL INSERT INTO double_table (c1) VALUES (:dbl);
dbl = 123456789012345;  -- 15 digits
EXEC SQL INSERT INTO double_table (c1) VALUES (:dbl);
dbl = 1234567890.12345;  -- 15 digits
EXEC SQL INSERT INTO double_table (c1) VALUES (:dbl);
dbl = 0.123456789012345;  -- 15 digits
EXEC SQL INSERT INTO double_table (c1) VALUES (:dbl);

EXEC SQL COMMIT;

EXEC SQL DISCONNECT;
--------------------------------------------------

Then I checked inserted data, but all data was stored by 14 digits
precision.

--------------------------------------------------
testdb=# \d double_table
      Table "public.double_table"
 Column |       Type       | Modifiers
--------+------------------+-----------
 c1     | double precision |

testdb=# SELECT * FROM double_table;
        c1
------------------
   12345678901234
  1234567890.1234
 0.12345678901234
  123456789012340
  1234567890.1235
 0.12345678901234
(6 rows)
--------------------------------------------------

The precision of double type should be 15 digits,
and I think this result is wrong.
PostgreSQL 9.0.4 also shows the same result.

In function "ecpg_store_input:execute.c",
the value of double type is changed by sprintf to a character string
at 14 digits precision. I'm thinking this point is a problem.

  sprintf(mallocedval, "%.14g", *((double *) var->value));


Following log is result of executing same query by psql.

--------------------------------------------------
testdb=# INSERT INTO double_table VALUES ('12345678901234');
INSERT 0 1
testdb=# INSERT INTO double_table VALUES ('1234567890.1234');
INSERT 0 1
testdb=# INSERT INTO double_table VALUES ('0.12345678901234');
INSERT 0 1
testdb=# INSERT INTO double_table VALUES ('123456789012345');
INSERT 0 1
testdb=# INSERT INTO double_table VALUES ('1234567890.12345');
INSERT 0 1
testdb=# INSERT INTO double_table VALUES ('0.123456789012345');
INSERT 0 1
testdb=# SELECT * FROM double_table;
        c1
-------------------
    12345678901234
   1234567890.1234
  0.12345678901234
   123456789012345
  1234567890.12345
 0.123456789012345
(6 rows)
--------------------------------------------------

Hi,

I built PostgreSQL 8.4.8 with the following patch,
then executed same program.

This result is same as psql's one.

--------------------------------------------------
testdb=# SELECT * FROM double_table;
        c1
-------------------
    12345678901234
   1234567890.1234
  0.12345678901234
   123456789012345
  1234567890.12345
 0.123456789012345
(6 rows)
--------------------------------------------------


*** execute.c.org       2011-04-15 12:17:14.000000000 +0900
--- execute.c   2011-07-16 19:46:05.000000000 +0900
***************
*** 709,720 ****
                                        strcpy(mallocedval, "array [");

                                        for (element = 0; element < asize; element++)
!                                               sprintf(mallocedval + strlen(mallocedval), "%.14g,", ((double *)
var->value)[element]);

                                        strcpy(mallocedval + strlen(mallocedval) - 1, "]");
                                }
                                else
!                                       sprintf(mallocedval, "%.14g", *((double *) var->value));

                                *tobeinserted_p = mallocedval;
                                break;
--- 709,720 ----
                                        strcpy(mallocedval, "array [");

                                        for (element = 0; element < asize; element++)
!                                               sprintf(mallocedval + strlen(mallocedval), "%.15g,", ((double *)
var->value)[element]);

                                        strcpy(mallocedval + strlen(mallocedval) - 1, "]");
                                }
                                else
!                                       sprintf(mallocedval, "%.15g", *((double *) var->value));

                                *tobeinserted_p = mallocedval;
                                break;

On Sat, Jul 16, 2011 at 09:21:04PM +0900, Akira Kurosawa wrote:
> I built PostgreSQL 8.4.8 with the following patch,
> then executed same program.

Thanks for the patch, applied.

Michael
--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
Jabber: michael.meskes at googlemail dot com
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL