Subject: Bug? The precision from the ODBC API is not same to psql

From: "James (Ke) Wang" <[email-masked]>
Date: Mon, 30 Aug 2021 04:19:29 +0000

*   PostgreSQL ODBC driver version: 13.01.00
  *   PostgreSQL database version: PostgreSQL 10.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
  *   any error messages you saw on the screen: No. However the result from ODBC API is not expected, we expect the ODBC driver returns the result same to Psql utility. Please see the below logs.
  *   the debug log (mylog_????.log) file (you will have to enable the mylog driver option)

*) From Microsoft ODBC test program
Full Connect(Default)
Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3
Successfully connected to DSN 'pdepsql10-sbosmk'.
SQLExecDirect:
                      In:                        Statementhandle = 0x00000000007ABF30, StatementText = "create table j_name ( height double precision)", Statementlength = 46
                      Return:    SQL_SUCCESS=0
SQLExecDirect:
                      In:                        Statementhandle = 0x00000000007ABF30, StatementText = "insert into j_name values (51.3)", Statementlength = 32
                      Return:    SQL_SUCCESS=0
SQLPrepare:
                      In:                        StatementHandle = 0x00000000007ABF30, StatementText = "select height, cast (height as char(20)) as height...", TextLength = 62
                      Return:    SQL_SUCCESS=0
SQLExecute:
                      In:                        StatementHandle = 0x00000000007ABF30
                      Return:    SQL_SUCCESS=0
Get Data All:
"height", "height"
51.299999999999997, "51.299999999999997  "
1 row fetched from 2 columns.

*) From psql utility

$  psql -h pdepsql10 -p 5432 -U tktstst2 -d sbosmk -W
Password for user tktstst2:
psql (9.1.1, server 10.17)
WARNING: psql version 9.1, server version 10.17.
         Some psql features might not work.
Type "help" for help.

sbosmk=# select height, cast (height as char(20)) as height from j_name
sbosmk-# ;
height |        height
--------+----------------------
   51.3 | 51.3
(1 row)

---

From: Tom Lane <[email-masked]>
Date: Mon, 30 Aug 2021 10:02:25 -0400

>   *   any error messages you saw on the screen: No. However the result from ODBC API is not expected, we expect the ODBC driver returns the result same to Psql utility. Please see the below logs.

You can get the same result in psql, if you set extra_float_digits
correctly:

regression=# select 51.3::float8;
 float8 
--------
   51.3
(1 row)
regression=# set extra_float_digits = 2;
SET
regression=# select 51.3::float8;
       float8       
--------------------
 51.299999999999997
(1 row)

So presumably either ODBC or something in your application is
setting that parameter.

FWIW, PG version 12 and newer won't do that; they use a newer
floating-point printing algorithm with less inclination to
produce silly-looking results.

			regards, tom lane