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
