Thread: real or float4 '=' operator is broken in 7.1.2

David Dodsworth (ddodsworth@navtechinc.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

More information to add to this bug.
I get the same behavior as the original poster, but the following on these
queries:

sszabo=> select myfloat-29.92 from "test_f4";
       ?column?
----------------------
 7.62939436071974e-08
 7.62939436071974e-08
(2 rows)
sszabo=> select myfloat-'29.92' from "test_f4";
 ?column?
----------
        0
        0
(2 rows)

This is probably due to the float constant being made
a float8 since I also see the following:
sszabo=> select 29.92::float4-29.92::float8;
       ?column?
----------------------
 7.62939436071974e-08
(1 row)


On Tue, 24 Jul 2001 pgsql-bugs@postgresql.org wrote:

> 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);

pgsql-bugs@postgresql.org writes:
> real or float4 '=' operator is broken in 7.1.2

No, it isn't --- try coercing the values you are comparing against
to be float4.  Your error is in assuming that 29.92::float8 should
equal 29.92::float4 coerced back to float8.  Which might be a nice
property to have, but it's not the real behavior of real floating-
point arithmetics.

            regards, tom lane