Re: BUG #14046: Bad mathematical rules for 0 cast - Mailing list pgsql-bugs

Jarosław Stokłosa <jaroslaw.stoklosa@nomino.pl> writes:
>> ​I humbly disagree with you. PostgreSQL use IEEE 754 for floating
>> point (mainly because that is what most - not all - computers today
>> use). IEEE 754 distinguishes +0 (or just 0) from -0. cast('-0' as
>> FLOAT) results in an IEEE 754 negative zero. Which is not identically
>> equal to a positive zero. The case to TEXT should, and does, preserve
>> this non-identical difference. ​

> I'm disagee with you. PostgreSQL don't distinguish +0 from -0 because
> cast('+0' as FLOAT) = cast('-0' as FLOAT) equals to TRUE. This is math
> rules. So case to float then to text shoud, and doesn't, preserve the
> equality. IEEE754 describes storage numbers in computer's memory but
> doesn't turn off math comparation rules.

Sorry, but that argument isn't terribly convincing.  The point is exactly
whether casting to another type must preserve equality, and you're just
asserting that it should be so without providing any compelling reason.

There are plenty of other counterexamples to that, though.  One that
comes to mind is that 42::numeric(6,3) will compare equal to
42::numeric(6,0), but if you cast them to text you will get '42.000'
and '42', which are not equal according to text's rules.  Another is
that 'FOO' and 'foo' compare equal according to citext, but if you
cast them to text they aren't equal anymore.

Basically, different types are allowed to have different equality
behaviors.  That's too useful to give up in the (vain) pursuit of
somebody's notion of mathematical purity.
        regards, tom lane