BUG #14046: Bad mathematical rules for 0 cast

On Fri, Mar 25, 2016 at 3:56 AM, <jaroslaw.stoklosa@nomino.pl> wrote:

The following bug has been logged on the website:

Bug reference: 14046
Logged by: Jarosław Stokłosa
Email address: jaroslaw.stoklosa@nomino.pl
PostgreSQL version: 9.4.5
Operating system: x86_64-redhat-linux-gnu
Description:

Below query should return 2 times TRUE.

SELECT cast('+0' as FLOAT) = cast('-0' as FLOAT), cast('+0' as FLOAT)::TEXT
= cast('-0' as FLOAT)::TEXT

+0 and -0 should be casted to +0 or 0.

​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. ​

Regards

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

--
How many surrealists does it take to screw in a lightbulb? One to hold the
giraffe and one to fill the bathtub with brightly colored power tools.

Maranatha! <><
John McKown

jaroslaw.stoklosa@nomino.pl writes:

+0 and -0 should be casted to +0 or 0.

On what grounds? The behavior seems sensible to me:

regression=# select cast('-0' as FLOAT);
float8
--------
-0
(1 row)

regression=# select cast('-0' as FLOAT)::text;
text
------
-0
(1 row)

If we do what you suggest, casting to text would be different from the
type's I/O conversion, which seems strange, and definitely isn't what
most other casts to text do.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

W dniu 25/03/2016 o 14:32, John McKown pisze:

On Fri, Mar 25, 2016 at 3:56 AM, <jaroslaw.stoklosa@nomino.pl
<mailto:jaroslaw.stoklosa@nomino.pl>>wrote:

The following bug has been logged on the website:

Bug reference: 14046
Logged by: Jarosław Stokłosa
Email address: jaroslaw.stoklosa@nomino.pl
<mailto:jaroslaw.stoklosa@nomino.pl>
PostgreSQL version: 9.4.5
Operating system: x86_64-redhat-linux-gnu
Description:

Below query should return 2 times TRUE.

SELECT cast('+0' as FLOAT) = cast('-0' as FLOAT), cast('+0' as
FLOAT)::TEXT
= cast('-0' as FLOAT)::TEXT

+0 and -0 should be casted to +0 or 0.

​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. ​

Hi,

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.

Regards
Jarek Stokłosa

On Tue, Mar 29, 2016 at 11:55 PM, Jarosław Stokłosa <
jaroslaw.stoklosa@nomino.pl> wrote:

W dniu 25/03/2016 o 14:32, John McKown pisze:

On Fri, Mar 25, 2016 at 3:56 AM, < <jaroslaw.stoklosa@nomino.pl>
jaroslaw.stoklosa@nomino.pl> wrote:

The following bug has been logged on the website:

Bug reference: 14046
Logged by: Jarosław Stokłosa
Email address: jaroslaw.stoklosa@nomino.pl
PostgreSQL version: 9.4.5
Operating system: x86_64-redhat-linux-gnu
Description:

Below query should return 2 times TRUE.

SELECT cast('+0' as FLOAT) = cast('-0' as FLOAT), cast('+0' as
FLOAT)::TEXT
= cast('-0' as FLOAT)::TEXT

+0 and -0 should be casted to +0 or 0.

​How would you propose to implement this "or"?​

​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.

Agreed. It is the cast to text turns off math comparison rules.

David J.

=?UTF-8?Q?Jaros=c5=82aw_Stok=c5=82osa?= <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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

W dniu 30/03/2016 o 18:32, Tom Lane pisze:

=?UTF-8?Q?Jaros=c5=82aw_Stok=c5=82osa?= <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.

You don't understand me. You give the examples differ than I - I've
compared numbers with the same type, which are equal (IEEE 754, sign
doesn't matter in this case for math equality). Cast to TEXT isn't able
to turn off equality, in my opinion.
Regards,
Jarek Stokłosa

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

W dniu 30/03/2016 o 18:03, David G. Johnston pisze:

On Tue, Mar 29, 2016 at 11:55 PM, Jarosław Stokłosa
<jaroslaw.stoklosa@nomino.pl <mailto:jaroslaw.stoklosa@nomino.pl>>wrote:

W dniu 25/03/2016 o 14:32, John McKown pisze:

On Fri, Mar 25, 2016 at 3:56 AM, <jaroslaw.stoklosa@nomino.pl
<mailto:jaroslaw.stoklosa@nomino.pl>>wrote:

The following bug has been logged on the website:

Bug reference: 14046
Logged by: Jarosław Stokłosa
Email address: jaroslaw.stoklosa@nomino.pl
<mailto:jaroslaw.stoklosa@nomino.pl>
PostgreSQL version: 9.4.5
Operating system: x86_64-redhat-linux-gnu
Description:

Below query should return 2 times TRUE.

SELECT cast('+0' as FLOAT) = cast('-0' as FLOAT), cast('+0'
as FLOAT)::TEXT
= cast('-0' as FLOAT)::TEXT

+0 and -0 should be casted to +0 or 0.

​How would you propose to implement this "or"?​

I propose for TEXT cast '-0'::FLOAT and '+0'::FLOAT to '0'::TEXT - like
'-0'::NUMERIC::TEXT, '+0'::NUMERIC::TEXT

​ 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.

Agreed. It is the cast to text turns off math comparison rules.

I understand, that convert to text can produce trouble with comparation
- Tom Lane give example in this thread, but in this case IEEE 754
doesn't prohibit to show zero without sign. NUMERIC is an example.

Regards
Jarek Stokłosa

W dniu 30/03/2016 o 18:03, David G. Johnston pisze:

On Tue, Mar 29, 2016 at 11:55 PM, Jarosław Stokłosa
<jaroslaw.stoklosa@nomino.pl <mailto:jaroslaw.stoklosa@nomino.pl>>wrote:

W dniu 25/03/2016 o 14:32, John McKown pisze:

On Fri, Mar 25, 2016 at 3:56 AM, <jaroslaw.stoklosa@nomino.pl
<mailto:jaroslaw.stoklosa@nomino.pl>>wrote:

The following bug has been logged on the website:

Bug reference: 14046
Logged by: Jarosław Stokłosa
Email address: jaroslaw.stoklosa@nomino.pl
<mailto:jaroslaw.stoklosa@nomino.pl>
PostgreSQL version: 9.4.5
Operating system: x86_64-redhat-linux-gnu
Description:

Below query should return 2 times TRUE.

SELECT cast('+0' as FLOAT) = cast('-0' as FLOAT), cast('+0'
as FLOAT)::TEXT
= cast('-0' as FLOAT)::TEXT

+0 and -0 should be casted to +0 or 0.

​How would you propose to implement this "or"?​

​ 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.

Agreed. It is the cast to text turns off math comparison rules.

David J.

Hi David, Tom, John,

Solution for me:
SELECT cast('+0' as FLOAT) = cast('-0' as FLOAT), cast('+0' as
FLOAT)::NUMERIC::FLOAT::TEXT= cast('-0' as FLOAT)::NUMERIC::FLOAT::TEXT

There are 2 times TRUE.

Thank you for your opinion and objective discussion.

Regards
Jarek

On Thu, Mar 31, 2016 at 12:49 AM, Jarosław Stokłosa <
jaroslaw.stoklosa@nomino.pl> wrote:

You don't understand me. You give the examples differ than I - I've
compared numbers with the same type, which are equal (IEEE 754, sign
doesn't matter in this case for math equality). Cast to TEXT isn't able to
turn off equality, in my opinion.

​-0 and +0 have distinct identities that are defined to compare as equal
when both values are represented as a floating point number. While it may
be your opinion that said equality should hold after converting -0 and +0
to textual representations it is impossible to simultaneously maintain
their distinct identities post-text conversion and have their text
representations compare as equal. PostgreSQL has chosen to treat their
identity characteristic as being primary and thus retains the + and - signs
when representing these values as text.

If there is anything more than your opinion of mathematical correctness
involved here it would be nice if you could share why it is you need the
float equality rules to continue to hold when two distinct floats are
represented as text.

In any case you can write a custom float-to-text function and use that
instead of "cast(float as text)"

David J.