Re: numeric and float comparison oddities

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
>> Jeff Davis <pgsql(at)j-davis(dot)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