Обсуждение: BUG #6036: why a REAL should be queried as a string?

The following bug has been logged online:

Bug reference:      6036
Logged by:          jose soares
Email address:      jose.soares@sferacarta.com
PostgreSQL version: 8.4.4
Operating system:   x86_64-pc-linux-gnu
Description:        why a REAL should be queried as a string?
Details:

I'd like to to report this strange behavior, that I think is a bug...

# \d frazione
          Table "public.frazione"
   Column   |  Type   |     Modifiers
------------+---------+--------------------
 id         | integer | not null
 importo    | real    | not null

# update  frazione set importo=0.833 where id=549;
UPDATE 1
# select * from frazione where importo=0.833;
 id | importo
----+---------
(0 rows)

# select * from frazione where importo='0.833';
 id  | importo
-----+----------
 549 |   0.833
(1 rows)

"jose soares" <jose.soares@sferacarta.com> wrote:

> Description:        why a REAL should be queried as a string?

> I'd like to to report this strange behavior, that I think is a
> bug...
>
> # \d frazione
>           Table "public.frazione"
>    Column   |  Type   |     Modifiers
> ------------+---------+--------------------
>  id         | integer | not null
>  importo    | real    | not null
>
> # update  frazione set importo=0.833 where id=549;
> UPDATE 1
> # select * from frazione where importo=0.833;
>  id | importo
> ----+---------
> (0 rows)
>
> # select * from frazione where importo='0.833';
>  id  | importo
> -----+----------
>  549 |   0.833
> (1 rows)

You're running into multiple issues here.  First off, you should
understand that "real" is an approximate data type, which is not
capable of exactly representing many decimal fractions.  "numeric"
can exactly represent decimal fractions.  So, while it's OK when you
compare real to numeric on values for which real happens to be
exact, it falls apart when its approximation doesn't exactly match
the numeric value:

test=# select '0.125'::numeric = '0.125'::real;
 ?column?
----------
 t
(1 row)

test=# select '0.833'::numeric = '0.833'::real;
 ?column?
----------
 f
(1 row)

Unadorned, that literal is taken as numeric:

test=# select pg_typeof(0.833);
 pg_typeof
-----------
 numeric
(1 row)

The other issue is that in PostgreSQL a quoted literal is not
initially a character string literal -- it starts out as type
"unknown" to make it easier to specify literals for various types.

test=# select pg_typeof('0.833');
 pg_typeof
-----------
 unknown
(1 row)

In your second example (where the number matches), the unknown value
is cast to real when you compare to the real column, so the same
approximation is generated.

In general it is a bad idea to compare for equality using
approximate values.  Perhaps you would be better off with that
column defined as numeric?

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> You're running into multiple issues here.  First off, you should
> understand that "real" is an approximate data type, which is not
> capable of exactly representing many decimal fractions.  "numeric"
> can exactly represent decimal fractions.  So, while it's OK when you
> compare real to numeric on values for which real happens to be
> exact, it falls apart when its approximation doesn't exactly match
> the numeric value:

Actually, the comparison constant is getting cast to float8, as can be
seen with "explain":

regression=# explain select * from frazione where importo='0.833';
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on frazione  (cost=0.00..36.75 rows=11 width=8)
   Filter: (importo = 0.833::real)
(2 rows)

regression=# explain select * from frazione where importo=0.833;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on frazione  (cost=0.00..36.75 rows=11 width=8)
   Filter: (importo = 0.833::double precision)
(2 rows)

But the basic point is correct: 0.833::float8 is a value that has no
exact match in float4 arithmetic.

regression=# select 0.833::float8 - 0.833::float4;
       ?column?
-----------------------
 -4.29153446113162e-09
(1 row)

In general, doing exact comparisons on float values is going to bite you
sooner or later, unless you know exactly what you're doing.

            regards, tom lane