Thread: numeric and float comparison oddities
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
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
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
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
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
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