Tom Lane <> wrote:
> Kevin Grittner <> writes:
>> Jeff Davis <> 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);
> 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;
(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
The Enterprise PostgreSQL Company

Sent via pgsql-hackers mailing list (
To make changes to your subscription:

Reply via email to