Re: 09.03.0400, wrong real values - Mailing list pgsql-odbc

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