Re: BUG #14046: Bad mathematical rules for 0 cast

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(dot)stoklosa(at)nomino(dot)pl <mailto:jaroslaw(dot)stoklosa(at)nomino(dot)pl>>wrote:
>
>
> W dniu 25/03/2016 o 14:32, John McKown pisze:
>> On Fri, Mar 25, 2016 at 3:56 AM, <jaroslaw(dot)stoklosa(at)nomino(dot)pl
>> <mailto:jaroslaw(dot)stoklosa(at)nomino(dot)pl>>wrote:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference: 14046
>> Logged by: Jarosław Stokłosa
>> Email address: jaroslaw(dot)stoklosa(at)nomino(dot)pl
>> <mailto:jaroslaw(dot)stoklosa(at)nomino(dot)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