Skip site navigation (1) Skip section navigation (2)

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

From: "" <myk321(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5533: PQexecParams in Binary Mode returns incorrect value for float4
Date: 2010-07-01 13:34:08
Message-ID: 201007011334.o61DY8Hd098979@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5533
Logged by:          
Email address:      myk321(at)gmail(dot)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

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2010-07-01 16:51:50
Subject: Re: BUG #5533: PQexecParams in Binary Mode returns incorrect value for float4
Previous:From: Dave PageDate: 2010-07-01 08:06:24
Subject: Re: Libpq.dll: File not recognized

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group