[BUGS] BUG #5533: PQexecParams in Binary Mode returns incorrect value for float4

The following bug has been logged online:

Bug reference:      5533
Logged by:          
Email address:      myk...@gmail.com
PostgreSQL version: 8.4.3
Operating system:   Ubuntu 10.04
Description:        PQexecParams in Binary Mode returns incorrect value for
float4
Details: 

Experience: PQexecParams (pqlib) in binary mode returns incorrect value for
float4 data type.

Example: Code below extracts a 0.75 float4 value from PostgreSQL, but
pqlib's PQexecParams returns 1.812500.  Same query in text mode returns 0.75
correctly.  Expected 0.75 return value in Binary mode.

Machine:
Dell Precision - Core 2 Duo
Ubuntu 10.04 LTS (Lucid Lynx)
PostgresQL 8.4.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6
20060404 (Red Hat 3.4.6-10), 32-bit

Demo.c starts here
// C Prototypes and Std Include headers
#include <ftw.h>
#include <stdio.h>
#include <stdlib.h>
#include <sys/stat.h>
#include "libpq-fe.h"

// Some silly parameters for handling postgreql
#define oidINT4 23;
#define oidFLOAT4 700;

#define TextFormat 0;
#define BinaryFormat 1;

void main()
{
        PGconn* conn;
    PGresult   *res;

    //Parameters for the insert
    int inParams = 2;
    Oid iParamTypes[2];
        char* iParamValues[2];
        int iParamLengths[2];
        int iParamFormats[2];
        int iResultFormat = BinaryFormat;

    //Parameters for the select
    int snParams = 1;
    Oid sParamTypes[1];
        char* sParamValues[1];
        int sParamLengths[1];
        int sParamFormats[1];
        int sResultFormat = BinaryFormat;

        //Index Value
        int Index;

        //Variables to handle the float4
    union
    {
      float             f;
      unsigned int      i;
    }   Swap;
        char* ptrFltValue;

        // Connect to the default database and create a test table consisting 
of a
column of int4 and float4
        conn = PQconnectdb("dbname=postgres user=postgres password=<insert 
password
here>");
        res = PQexec(conn, "CREATE TABLE testtbl( Intgr int4, Flt float8, 
PRIMARY
KEY ( Intgr ));");
        PQclear(res);

        //Insert 1 rows, 1 containing 0.75 the float4 field (100 in the int4
field)
        iParamTypes[0] = oidINT4;
        iParamTypes[1] = oidFLOAT4;
        iParamLengths[0] = sizeof(unsigned int);
        iParamLengths[1] = sizeof(float);
        iParamFormats[0] = BinaryFormat;
        iParamFormats[1] = BinaryFormat;
        Index = htonl(100);
        iParamValues[0] = (char*) &Index;
    Swap.f = 0.75;
    Swap.i = htonl(Swap.i);
    iParamValues[1] = (char*) &Swap;
    res = PQexecParams(conn, "Insert into testtbl(Intgr, Flt) Values ($1,
$2);", inParams, &iParamTypes,
                                iParamValues, &iParamLengths, &iParamFormats, 
iResultFormat);
        PQclear(res);

    //Retrieve the row in Binary mode
        sParamTypes[0] = oidINT4;
        sParamLengths[0] = sizeof(unsigned int);
        sParamFormats[0] = BinaryFormat;
        sParamValues[0] = (char*) &Index;
    res = PQexecParams(conn, "SELECT * FROM testtbl where (Intgr = $1);",
snParams, &sParamTypes,
                                        sParamValues, &sParamLengths, 
&sParamFormats, sResultFormat);
        ptrFltValue = PQgetvalue(res,0,1);
        Swap.i = ntohl(*((int *) ptrFltValue));

    //Print the Binary mode result
    printf("Flt retrieved in Binary mode is = %f.\n", Swap.f);

    //Retrieve the row in Text mode
        PQclear(res);
        sResultFormat = TextFormat;
    res = PQexecParams(conn, "SELECT * FROM testtbl where (Intgr = $1);",
snParams, &sParamTypes,
                                        sParamValues, &sParamLengths, 
&sParamFormats, sResultFormat);

    //Print the Text mode results
    printf("Flt retrieved in Binary mode is = %s.\n", PQgetvalue(res,0,1));

    //Clean-up
        PQclear(res);
        PQfinish(conn);

        return;
}

Demo.c ends here

Makefile starts here
#
# Makefile for Demo application
# Use by invoking 'make' on the command line
#
# Specify the compiler
CC = gcc

# Specify the pre-processor flags
CPPFLAGS += -I/opt/PostgreSQL/8.4/include
CPPFLAGS += -I${HOME}

# Specify the compiler flags
CFLAGS += -c
CFLAGS += -g

# Specify the linker flags
LDFLAGS += -g

# Specify the linker libraries
LDLIBS += -L/opt/PostgreSQL/8.4/lib -lpq
LDLIBS += /opt/PostgreSQL/8.4/lib/libssl.so.4 
LDLIBS += /opt/PostgreSQL/8.4/lib/libcrypto.so.4

# Specify the files making up the application
SOURCES = Demo.c
EXECUTABLE = Demo

all: $(EXECUTABLE)

$(EXECUTABLE): $(OBJECTS)
        $(CC) $(LDFLAGS) $(LDLIBS) $(OBJECTS) -o $@

.c.o:
        $(CC) $(CPPFLAGS) $(CFLAGS) $< -o $@

install:
        @echo "Build complete!"

Makefile ends here

Some needless speculation:
0.75 = 3F400000 (as IEEE 32-bit float)
0.75 = 3FE8000000000000 (as IEEE 64-bit float)
1.812500 = 3FE80000 (as returned by PQexecParams)
i.e. the return value is the first 32-bits of the 64-bit representation of
the correct value.

Same result apparent for 1.22 test value
Returns: 1.902500.
1.22 = 3F9C28F6 (as IEEE 32-bit float)
1.22 = 3FF3851EB851EB85 (as 64-bit IEEE 64-bit float)
1.9025 = 3FF3851E (as returned by PQexecParams)
Ref: http://babbage.cs.qc.cuny.edu/IEEE-754/Decimal.html

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs