Обсуждение: 'real' strange problem in 7.1.3

I have a database in PG 7.1.3 with the following schema:

db02=# \d ellipse                 Table "ellipse"       Attribute         |     Type     | Modifier 
--------------------------+--------------+----------subject                  | text         | arm
|character(1) | rep                      | integer      | exp_date                 | date         | exp_time
    | time         | success                  | integer      | figure_radius            | integer      | tube_radius
         | integer      | cursor_radius            | integer      | direction                | integer      |
ellipse_ratio           | real         | exag_ratio               | real         | exag_start               | integer
  | exag_end                 | integer      | rotation_angle           | real         | min_inter_trial_interval |
integer     | 
 
Index: pkellipse

If I try the command:
db02=# select distinct arm from ellipse where exag_ratio = 1.0;arm 
-----LR
(2 rows)

which is correct.

Now I try the same command with a different 'real' field:
db02=# select distinct arm from ellipse where ellipse_ratio = 1.8;arm 
-----
(0 rows)

BUT, if I put the value in quotes (as if it were a string), I get:

db02=# select distinct arm from ellipse where ellipse_ratio = '1.8';arm 
-----LR
(2 rows)

which is correct.

This variable ellipse_ratio seems to be the only one of type 'real'
that requires me to use quotes (which doesn't really make sense since
it's not a character or string anyway). exag_ratio and rotation_angle
behave as I would expect a real-typed variable to behave.

db02=# select distinct exag_ratio, ellipse_ratio, rotation_angle from
ellipse;exag_ratio | ellipse_ratio | rotation_angle 
------------+---------------+----------------         1 |          0.56 |              0         1 |           1.8 |
         0
 
(2 rows)



Has anyone seen this behavior before? Perhaps, I'm doing something
wrong here or thinking of this all wrong?

Thanks.
-Tony Reina

Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms      \h for help with SQL commands      \? for help on internal slash commands
    \g or terminate with semicolon to execute query      \q to quit
 

db02=# select version();                          version                           
-------------------------------------------------------------PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC
2.96
(1 row)

PG server is RH Linux 7.1 (Seawolf), PIII 400 MHz
Vacuum verbose analyze performed just prior to the searches listed
just to be sure.

reina@nsi.edu (Tony Reina) writes:
> db02=# select distinct arm from ellipse where ellipse_ratio = 1.8;
>  arm 
> -----
> (0 rows)

You realize that floating-point values aren't exact?  Probably the "1.8"
in the database is a few bits off in the seventh decimal place, and so
it's not exactly equal to the "1.8" you've given as a constant.  In
fact, seeing that you've actually written the constant as a float8, it's
almost certain that the float4 value in the database will not promote to
exactly that float8.  On my machine I get

regression=# select (1.8::float4)::float8;     float8
------------------1.79999995231628
(1 row)

regression=# select 1.8::float4 - 1.8::float8;      ?column?
------------------------4.76837158647214e-08
(1 row)

regression=# select 1.8::float4 = 1.8::float8;?column?
----------f
(1 row)

You *might* find that writing "where ellipse_ratio = 1.8::float4"
selects your database row, or you might not --- if the 1.8 in the
database was the result of a calculation, and didn't arise directly
from input conversion of the exact string "1.8", then the odds are
it won't match.  (Your example with putting single quotes around the
1.8 is equivalent to this explicit coercion, BTW.)

In any case, any programming textbook will tell you that doing exact
comparisons on floats is folly.  Consider something like
... where abs(ellipse_ratio - 1.8) < 1.0e-6;
        regards, tom lane

>
> Now I try the same command with a different 'real' field:
> db02=# select distinct arm from ellipse where ellipse_ratio = 1.8;
>  arm
> -----
> (0 rows)
>
> BUT, if I put the value in quotes (as if it were a string), I get:
>
> db02=# select distinct arm from ellipse where ellipse_ratio = '1.8';
>  arm
> -----
>  L
>  R
> (2 rows)
>
> which is correct.

The reason is that in the first, the 1.8 is treated as double precision
which is slightly different than the 1.8 as real (you can see this with
a select 1.8::real-1.8;). I think the second postpones deciding the type
and gets converted into a 1.8 as real.

Tom Lane wrote:

> You *might* find that writing "where ellipse_ratio = 1.8::float4"
> selects your database row, or you might not --- if the 1.8 in the
> database was the result of a calculation, and didn't arise directly
> from input conversion of the exact string "1.8", then the odds are
> it won't match.  (Your example with putting single quotes around the
> 1.8 is equivalent to this explicit coercion, BTW.)
>
>

Ah, floating point precision errors! Yes, this makes sense now. Plus,
you're saying that putting the 1.8 in quotes is interpreted by the parser
as adding the ::float4 at the end. That's the bit of information that I
needed. I thought that perhaps my value was being stored as a string even
though PG was telling me that it was a float.

Thanks Tom and Stephan.

-Tony

"G. Anthony Reina" <reina@nsi.edu> writes:
> you're saying that putting the 1.8 in quotes is interpreted by the parser
> as adding the ::float4 at the end. That's the bit of information that I
> needed. I thought that perhaps my value was being stored as a string even
> though PG was telling me that it was a float.

More precisely, when you writeWHERE foo = 'const'
the constant is essentially forced to take on the datatype of foo.
(It's initially treated as a constant of type UNKNOWN, and then the
operator resolution rules will prefer to select an "=" operator with
foo's datatype on both sides, and then the unknown constant gets
coerced to that type.  Messy but it works.)

There has been some discussion about trying to handle numeric literals
in a similar fashion, wherein we don't nail down their type immediately,
but it's not been done yet.  Right now 1.8 will be taken as float8
on sight, and then you end up with a float4-vs-float8 comparison,
which is unlikely to work nicely except with values that are exactly
representable in float4 (such as small integers).
        regards, tom lane

reina@nsi.edu (Tony Reina) writes:

> db02=# select distinct arm from ellipse where exag_ratio = 1.0;
You never want to use the = test on floating point numbers.  Two
apparently equal numbers may differ in the least significant digit.
The behavior will be close to random.

When you convert the floating point to a string and round off to a
specific number of digits, you can use string compare to get more
predictable results.

Another possibility is to do something like 
  where abs(exag_ratio - 1.0) > 0.000001

(I'm not sure about the SQL function for absolute value, but you get
the idea).

-Knut

-- 
The early worm gets the bird.