BUG #5533: PQexecParams in Binary Mode returns incorrect value for float4 - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5533
Logged by:
Email address:      myk321@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