Thread: 09.03.0400, wrong real values
Hi,
I am using the database 9.4.0 with the odbc driver 09.03.0400 on linux machine with unixODBC.
There are different results if I insert a float value=-0.0051 in the database with ODBC funtions SQLBindParameter(), SQLPrepare() and SQLExecute().
With the odbc version 09.03.0300 the saved value is 0.0051 and
with the odbc version 09.03.0400 the saved wrong value is -0.00510000018 (see below).
There is no difference between 32 and 64 bit.
Server Version on linux RHEL6:
psql (PostgreSQL) 9.4.0 (and 9.3.4 for the comparison)
~/.odbc.ini
[JWIE]
Driver=PostgreSQL
Port=5432
Database=jwie
FileUsage=1
UseServerSidePrepare=0
I use this table:
jwie=> \d TESTTABLE4;
Table "jwie.testtable4"
Column | Type | Modifiers
-----------------+-----------------------------+-----------
testtype | character varying(64) |
testbooleanchar | character(1) |
testinteger | bigint |
testnumber | numeric |
testchar | character(128) |
testvarchar | character varying(128) |
testraw | bytea |
testlongraw | bytea |
testblob | bytea |
testlong | text |
testdate | timestamp without time zone |
testfloat | double precision |
C Code fragment:
:
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, ID_LEN, 0, szTypeID, 0, &typeID);
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, sizeof(float), 0, &f2, 0, &charID);
retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, sizeof(float), 0, &f3, 0, &numberID);
retcode = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, sizeof(float), 0, &f4, 0, &varcharID);
retcode = SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, sizeof(float), 0, &f5, 0, &floatID);
retcode = SQLPrepare(hstmt, (SQLCHAR*)"INSERT INTO TESTTABLE4 "
"(TESTTYPE, "
"TESTCHAR, "
"TESTNUMBER, "
"TESTVARCHAR, testfloat) "
"VALUES (?, ?, ?, ?, ?)", SQL_NTS);
printf("retcode=%d\n", retcode);
strcpy((char*)szTypeID, "FLOAT1");
f2=-0.0051;
f3=-0.0051;
f4=-0.0051;
f5=-0.0051;
retcode = SQLExecute(hstmt);
:
Log files
09.03.0300 OK
========
conn = 0x198d2e0, PGAPI_Connect(DSN='JWIE', UID='jwie', PWD='xxxxx')
Driver Version='09.03.0300,201405140001'
Global Options: fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190
disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
extra_systable_prefixes='dd_;', conn_settings='(null)' conn_encoding=''
[ PostgreSQL version string = '9.3.4' ]
[ PostgreSQL version number = '9.3' ]
conn=0x198d2e0, query='select oid, typbasetype from pg_type where typname = 'lo''
[ fetched 0 rows ]
[ Large Object oid = -999 ]
[ Client encoding = 'LATIN1' (code = 8) ]
conn=0x198d2e0, query='INSERT INTO TESTTABLE4 (TESTTYPE, TESTCHAR, TESTNUMBER, TESTVARCHAR, testfloat) VALUES ('FLOAT1', '-0.0051'::float8, '-0.0051'::float8, '-0.0051'::float8, '-0.0051'::float8)'
conn=0x198d2e0, query='INSERT INTO TESTTABLE4 (TESTTYPE, TESTCHAR, TESTNUMBER, TESTVARCHAR, testfloat) VALUES ('FLOAT2', '4.5678e+12'::float8, '4.5678e+12'::float8, '4.5678e+12'::float8, '4.5678e+12'::float8)'
09.03.0400 not OK
========
conn = 0x247f2e0, PGAPI_Connect(DSN='JWIE', UID='jwie', PWD='xxxxx')
Driver Version='09.03.0400,Jan 6 2015'
Global Options: fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190
disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
extra_systable_prefixes='dd_;', conn_settings='(null)' conn_encoding=''
[ PostgreSQL version string = '9.4.0' ]
[ PostgreSQL version number = '9.4' ]
conn=0x247f2e0, query='select oid, typbasetype from pg_type where typname = 'lo''
[ fetched 0 rows ]
[ Large Object oid = -999 ]
[ Client encoding = 'LATIN1' (code = 8) ]
conn=0x247f2e0, query='INSERT INTO TESTTABLE4 (TESTTYPE, TESTCHAR, TESTNUMBER, TESTVARCHAR, testfloat) VALUES ('FLOAT1', '-0.00510000018'::float8, '-0.00510000018'::float8, '-0.00510000018'::float8, '-0.00510000018'::float8)'
conn=0x247f2e0, query='INSERT INTO TESTTABLE4 (TESTTYPE, TESTCHAR, TESTNUMBER, TESTVARCHAR, testfloat) VALUES ('FLOAT2', '4.56779996e+12'::float8, '4.56779996e+12'::float8, '4.56779996e+12'::float8, '4.56779996e+12'::float8)'
Can you please check this?
Thank you.
Best Regards
Jens Wiechmann
Jens Wiechmann | |
Redknee (Germany) GmbH Geschäftsführung / Managing Directors: David Charron, Sabine Domes / Sitz der Gesellschaft: Berlin |
Attachment
On 01/20/2015 02:41 PM, Jens Wiechmann wrote: > Hi, > I am using the database 9.4.0 with the odbc driver 09.03.0400 on linux machine with unixODBC. > > There are different results if I insert a float value=-0.0051 in the database with ODBC funtions SQLBindParameter(), SQLPrepare()and SQLExecute(). > With the odbc version 09.03.0300 the saved value is 0.0051 and > with the odbc version 09.03.0400 the saved wrong value is -0.00510000018 (see below). > There is no difference between 32 and 64 bit. > > Server Version on linux RHEL6: > psql (PostgreSQL) 9.4.0 (and 9.3.4 for the comparison) > > > ~/.odbc.ini > [JWIE] > Driver=PostgreSQL > Port=5432 > Database=jwie > FileUsage=1 > UseServerSidePrepare=0 > > > I use this table: > jwie=> \d TESTTABLE4; > Table "jwie.testtable4" > Column | Type | Modifiers > -----------------+-----------------------------+----------- > testtype | character varying(64) | > testbooleanchar | character(1) | > testinteger | bigint | > testnumber | numeric | > testchar | character(128) | > testvarchar | character varying(128) | > testraw | bytea | > testlongraw | bytea | > testblob | bytea | > testlong | text | > testdate | timestamp without time zone | > testfloat | double precision | > > > C Code fragment: > : > retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, ID_LEN, 0, szTypeID, 0, &typeID); > retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, sizeof(float), 0, &f2, 0, &charID); > retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, sizeof(float), 0, &f3, 0, &numberID); > retcode = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, sizeof(float), 0, &f4, 0, &varcharID); > retcode = SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, sizeof(float), 0, &f5, 0, &floatID); > > retcode = SQLPrepare(hstmt, (SQLCHAR*)"INSERT INTO TESTTABLE4 " > "(TESTTYPE, " > "TESTCHAR, " > "TESTNUMBER, " > "TESTVARCHAR, testfloat) " > "VALUES (?, ?, ?, ?, ?)", SQL_NTS); > > printf("retcode=%d\n", retcode); > strcpy((char*)szTypeID, "FLOAT1"); > f2=-0.0051; > f3=-0.0051; > f4=-0.0051; > f5=-0.0051; > retcode = SQLExecute(hstmt); > : > > Log files > > 09.03.0300 OK > ======== > conn = 0x198d2e0, PGAPI_Connect(DSN='JWIE', UID='jwie', PWD='xxxxx') > Driver Version='09.03.0300,201405140001' > Global Options: fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190 > disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=0 > text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64 > extra_systable_prefixes='dd_;', conn_settings='(null)' conn_encoding='' > [ PostgreSQL version string = '9.3.4' ] > [ PostgreSQL version number = '9.3' ] > conn=0x198d2e0, query='select oid, typbasetype from pg_type where typname = 'lo'' > [ fetched 0 rows ] > [ Large Object oid = -999 ] > [ Client encoding = 'LATIN1' (code = 8) ] > conn=0x198d2e0, query='INSERT INTO TESTTABLE4 (TESTTYPE, TESTCHAR, TESTNUMBER, TESTVARCHAR, testfloat) VALUES ('FLOAT1','-0.0051'::float8, '-0.0051'::float8, '-0.0051'::float8, '-0.0051'::float8)' > conn=0x198d2e0, query='INSERT INTO TESTTABLE4 (TESTTYPE, TESTCHAR, TESTNUMBER, TESTVARCHAR, testfloat) VALUES ('FLOAT2','4.5678e+12'::float8, '4.5678e+12'::float8, '4.5678e+12'::float8, '4.5678e+12'::float8)' > > > 09.03.0400 not OK > ======== > conn = 0x247f2e0, PGAPI_Connect(DSN='JWIE', UID='jwie', PWD='xxxxx') > Driver Version='09.03.0400,Jan 6 2015' > Global Options: fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190 > disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=0 > text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64 > extra_systable_prefixes='dd_;', conn_settings='(null)' conn_encoding='' > [ PostgreSQL version string = '9.4.0' ] > [ PostgreSQL version number = '9.4' ] > conn=0x247f2e0, query='select oid, typbasetype from pg_type where typname = 'lo'' > [ fetched 0 rows ] > [ Large Object oid = -999 ] > [ Client encoding = 'LATIN1' (code = 8) ] > conn=0x247f2e0, query='INSERT INTO TESTTABLE4 (TESTTYPE, TESTCHAR, TESTNUMBER, TESTVARCHAR, testfloat) VALUES ('FLOAT1','-0.00510000018'::float8, '-0.00510000018'::float8, '-0.00510000018'::float8, '-0.00510000018'::float8)' > conn=0x247f2e0, query='INSERT INTO TESTTABLE4 (TESTTYPE, TESTCHAR, TESTNUMBER, TESTVARCHAR, testfloat) VALUES ('FLOAT2','4.56779996e+12'::float8, '4.56779996e+12'::float8, '4.56779996e+12'::float8, '4.56779996e+12'::float8)' > > Can you please check this? This was changed in commit a5fed2338b59ae16a2d3a8d2744b084949684775, see discussion athttp://www.postgresql.org/message-id/DUB121-W17AD73873B390AF3AEF384D3170@phx.gbl. In a nutshell, for a float4 value, we used to only send 6 decimal digits, but now we send 9. That change was made because 6 decimal digits is not enough to distinguish between all float4 values, which meant that for some values, if you sent the value to the database and then read it back, you would not get the exact same value back. Note that in your test case, you're passing the value to SQLBindParameter as a 32-bit floating-point value (SQL_C_FLOAT, which is the same as SQL_C_REAL), but you're storing it in a 64-bit floating point column (double). I think you'll want to pass the value as a double, SQL_C_DOUBLE, instead. That'll avoid the conversion, and the extra digits that you don't want. I'm pretty sure we don't want to make further changes to this. IOW this isn't a bug. (In the server, there is an option called extra_float_digits to control the same thing, for values output from the server. The old behavior corresponds to extra_float_digits=0, while the new behavior corresponds to extra_float_digits=3. It's not directly relevant here, but you might want to read up on it in the documentation, to get a grasp of how floating-point types work) - Heikki