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

real or float4 '=' operator is broken in 7.1.2

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: real or float4 '=' operator is broken in 7.1.2
Date: 2001-07-24 16:30:13
Message-ID: 200107241630.f6OGUD128762@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
David Dodsworth (ddodsworth(at)navtechinc(dot)com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
real or float4 '=' operator is broken in 7.1.2

Long Description
As the example shows, a select for on a 'real' or 'float4' value 
value will not return any rows when matched for equality.

This is only broken when using a numeric literal (i.e. without
quotes) compared with a string literal (with single quotes)

This works fine on Postgres 6.5 (on a different Linux/gcc combo)

Also note that select * from "test_r" where "myfloat" > 29.92 does
return records.  

WORKAROUND: Don't use float4/real use float8 instead, or always
enclose real numbers in single quotes.

Version: Postgres 7.1.2 rebuilt from source using 
gcc 2.7.2.3, Linux 2.0.34 and RedHat 5.1 

Sample Code
/* Input */

drop table "test_r";
drop table "test_f";
drop table "test_f4";
drop sequence "test_r_key_seq";
drop sequence "test_f_key_seq";
drop sequence "test_f4_key_seq";
CREATE TABLE "test_r" ("key" serial, "myfloat" real);
CREATE TABLE "test_f" ("key" serial, "myfloat" float8);
CREATE TABLE "test_f4" ("key" serial, "myfloat" float4);
INSERT INTO "test_r" ("myfloat") values ('29.92');
INSERT INTO "test_r" ("myfloat") values (29.92);
INSERT INTO "test_f" ("myfloat") values ('29.92');
INSERT INTO "test_f" ("myfloat") values (29.92);
INSERT INTO "test_f4" ("myfloat") values ('29.92');
INSERT INTO "test_f4" ("myfloat") values (29.92);
select * from "test_r" where "myfloat" = 29.92;
select * from "test_r" where "myfloat" = '29.92';
select * from "test_f" where "myfloat" = 29.92;
select * from "test_f" where "myfloat" = '29.92';
select * from "test_f4" where "myfloat" = 29.92;
select * from "test_f4" where "myfloat" = '29.92';      

/* Output */
DROP
DROP
DROP
DROP
DROP
DROP
CREATE
CREATE
CREATE
INSERT 1580890 1
INSERT 1580891 1
INSERT 1580892 1
INSERT 1580893 1
INSERT 1580894 1
INSERT 1580895 1
 key | myfloat
-----+---------
(0 rows)

 key | myfloat
-----+---------
   1 |   29.92
   2 |   29.92   

 key | myfloat
-----+---------
   1 |   29.92
   2 |   29.92
(2 rows)

 key | myfloat
-----+---------
   1 |   29.92
   2 |   29.92
(2 rows)

 key | myfloat
-----+---------
(0 rows)

 key | myfloat
-----+---------
   1 |   29.92
   2 |   29.92
(2 rows)
    

No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2001-07-24 17:43:27
Subject: Re: real or float4 '=' operator is broken in 7.1.2
Previous:From: Felipe Alvarez HarneckerDate: 2001-07-23 22:00:43
Subject: Re: Documentation Bug related to Inheritance

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