Tom Lane <t...@sss.pgh.pa.us> wrote: > Kevin Grittner <kgri...@ymail.com> writes: >> Jeff Davis <pg...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
