Thread: 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