Thread: numeric and float comparison oddities

numeric and float comparison oddities

From
Jeff Davis
Date:
I saw some strange results:

postgres=# select '1.1'::numeric = '1.1'::float8;?column? 
----------t
(1 row)

postgres=# select '1.1'::numeric = '1.1'::float4;?column? 
----------f
(1 row)

When I looked into it, I saw that the numeric is being cast to a float8,
making the first statement trivially true. 

Why does the cast go from numeric to float if that direction loses
precision? One reason is because float supports +/- infinity, but that
seems more like convenience than a good reason. Is there another reason?

Have we considered adding +/- infinity to numeric so that it can
represent every float value? That might make the numeric hierarchy a
little cleaner and less surprising.

Regards,Jeff Davis





Re: numeric and float comparison oddities

From
Kevin Grittner
Date:
Jeff Davis <pgsql@j-davis.com> wrote:

> I saw some strange results:
>
> postgres=# select '1.1'::numeric = '1.1'::float8;
> ?column?
> ----------
> t
> (1 row)
>
> postgres=# select '1.1'::numeric = '1.1'::float4;
> ?column?
> ----------
> f
> (1 row)

The part I find strange is that the first one evaluates to true,
since numeric can exactly represent 1.1 and float8 cannot.  It also
seems inconsistent with this:

test=# set extra_float_digits = 3;
SET
test=# select '1.1'::float4;
   float4
------------
 1.10000002
(1 row)

test=# select '1.1'::float8;
       float8
---------------------
 1.10000000000000009
(1 row)

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Re: numeric and float comparison oddities

From
Tom Lane
Date:
Kevin Grittner <kgrittn@ymail.com> writes:
> Jeff Davis <pgsql@j-davis.com> wrote:
>> I saw some strange results:

> The part I find strange is that the first one evaluates to true,
> since numeric can exactly represent 1.1 and float8 cannot.

The reason is that the numeric input is converted to float8 for
comparison:

regression=# create table ttt(f4 float4, f8 float8, fn numeric);
CREATE TABLE
regression=# explain verbose select f4=fn, f8=fn from ttt;                              QUERY PLAN
        
 
------------------------------------------------------------------------Seq Scan on public.ttt  (cost=0.00..32.00
rows=1100width=44)  Output: (f4 = (fn)::double precision), (f8 = (fn)::double precision)Planning time: 0.325 ms
 
(3 rows)

Were it not, you'd hardly ever get equality.

I think that years ago we concluded this behavior was required by
SQL spec (see the language about exact vs inexact numeric types).
        regards, tom lane



Re: numeric and float comparison oddities

From
Kevin Grittner
Date:
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Kevin Grittner <kgrittn@ymail.com> writes:
>> Jeff Davis <pgsql@j-davis.com> wrote:
>>> I saw some strange results:
>
>> The part I find strange is that the first one evaluates to true,
>> since numeric can exactly represent 1.1 and float8 cannot.
>
> The reason is that the numeric input is converted to float8 for
> comparison:
>
> regression=# create table ttt(f4 float4, f8 float8, fn numeric);
> CREATE TABLE
> regression=# explain verbose select f4=fn, f8=fn from ttt;
>                               QUERY PLAN
> ------------------------------------------------------------------------
> Seq Scan on public.ttt  (cost=0.00..32.00 rows=1100 width=44)
>   Output: (f4 = (fn)::double precision), (f8 = (fn)::double precision)
> Planning time: 0.325 ms
> (3 rows)
>
> Were it not, you'd hardly ever get equality.
>
> I think that years ago we concluded this behavior was required by
> SQL spec (see the language about exact vs inexact numeric types).

I just looked at each point in the spec where they mention
approximate numeric types, and while there was no direct mention of
this (that I could find), casting the exact number to an
approximate type would be in keeping with the spirit of other
operations involving mixed data types.  While I think what we do is
within bounds of the "implementation specific" choices we are
allowed, I think we made a bad choice on this:

test=# select '1.1'::float8 = '1.1'::float4;
 ?column?
----------
 f
(1 row)

I know that neither value is exactly 1.1 (decimal) and that they
are not the same.  In fact, while '1.1'::numeric has no exact
representation in float4 or float8, '1.1'::float4 and '1.1'::float8
both have exact representations in numeric -- at least for IEEE
format.  They are:

float4: 1.10000002384185791015625
float8: 1.100000000000000088817841970012523233890533447265625

OK, so those are not equal to each other, but neither is either of
them equal to 1.1.  It would be more consistent, ISTM, to cast
float8 to float4 when those are compared, and to cast numeric to
whichever type is on the other side of the comparison operator.

Obviously that would not be a change to back-patch; but it seems to
me to be worth considering for 9.5.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Re: numeric and float comparison oddities

From
Tom Lane
Date:
Kevin Grittner <kgrittn@ymail.com> writes:
> It would be more consistent, ISTM, to cast 
> float8 to float4 when those are compared, and to cast numeric to 
> whichever type is on the other side of the comparison operator.

I would vote against that on the grounds of greatly increased risk
of overflow failure.  Admittedly, numeric->float8 can also fail,
but float4 only goes to 1e37 or thereabouts.
        regards, tom lane



Re: numeric and float comparison oddities

From
Kevin Grittner
Date:
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Kevin Grittner <kgrittn@ymail.com> writes:
>> It would be more consistent, ISTM, to cast
>> float8 to float4 when those are compared, and to cast numeric to
>> whichever type is on the other side of the comparison operator.
>
> I would vote against that on the grounds of greatly increased risk
> of overflow failure.  Admittedly, numeric->float8 can also fail,
> but float4 only goes to 1e37 or thereabouts.

Since 1e28 is sufficient to measure the diameter of the universe in
angstroms, I'm not sure I accept the adjective "greatly" there.  I
suspect that people are getting silently burned by current behavior
more often than they would get overflow errors with the change.  At
least when you get an error it's pretty clear how to fix it with a
cast.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company