Обсуждение: BUG #14046: Bad mathematical rules for 0 cast
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.
Regards
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=C5=82aw Stok=C5=82osa
> 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) =3D cast('-0' as FLOAT), cast('+0' as FLOAT)::=
TEXT
> =3D cast('-0' as FLOAT)::TEXT
>
> +0 and -0 should be casted to +0 or 0.
>
=E2=80=8BI humbly disagree with you. PostgreSQL use IEEE 754 for floating p=
oint
(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. =
=E2=80=8B
>
> Regards
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
--=20
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
W dniu 25/03/2016 o 14:32, John McKown pisze:
Hi,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.
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=C5=82aw Stok=C5=82osa <
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=C5=82aw Stok=C5=82osa
>> 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) =3D cast('-0' as FLOAT), cast('+0' as
>> FLOAT)::TEXT
>> =3D cast('-0' as FLOAT)::TEXT
>>
>> +0 and -0 should be casted to +0 or 0.
>>
>
=E2=80=8BHow would you propose to implement this "or"?=E2=80=8B
> =E2=80=8BI 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=
. =E2=80=8B
>
>
> I'm disagee with you. PostgreSQL don't distinguish +0 from -0 because
> cast('+0' as FLOAT) =3D 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.
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
W dniu 30/03/2016 o 18:03, David G. Johnston pisze:
I propose for TEXT cast '-0'::FLOAT and '+0'::FLOAT to '0'::TEXT - like '-0'::NUMERIC::TEXT, '+0'::NUMERIC::TEXT
Regards
Jarek Stokłosa
W dniu 25/03/2016 o 14:32, John McKown pisze: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 propose for TEXT cast '-0'::FLOAT and '+0'::FLOAT to '0'::TEXT - like '-0'::NUMERIC::TEXT, '+0'::NUMERIC::TEXT
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. 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.
Regards
Jarek Stokłosa
W dniu 30/03/2016 o 18:03, David G. Johnston pisze:
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
W dniu 25/03/2016 o 14:32, John McKown pisze: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.
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
W dniu 30/03/2016 o 18:32, Tom Lane pisze:
> 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.
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
On Thu, Mar 31, 2016 at 12:49 AM, Jaros=C5=82aw Stok=C5=82osa < 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 t= o > turn off equality, in my opinion. > =E2=80=8B-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.