Re: Inconsistent handling of signed zero (-0) between INNER JOIN and INTERSECT ALL

"=?utf-8?B?5byA5b+D5bCP5biC5rCR?=" <2530254482(at)qq(dot)com> writes:
> I am writing to report an inconsistency I discovered regarding the handling of floating-point signed zeros (-0) when comparing a standard INNER JOIN with its logically equivalent rewriting using INTERSECT ALL. While INNER JOIN preserves the sign of the zero (-0), the equivalent query constructed using INTERSECT ALL appears to normalize the value to positive zero (0).

This is pure chance. Because the float types regard zero and minus
zero as equal (as required by the IEEE floating-point standard),
the Postgres executor just sees those values as equal for joining
and uniqueness purposes. So it's coincidental which one of a set
of "equal" values propagates to the output of the query. With some
different arrangement of the query or input data, you could doubtless
find the opposite effect.

There are similar effects in other data types where visibly
distinguishable values compare equal, for example numeric ("42.0" and
"42.00" are equal), or text when using a non-deterministic collation.
In no case do queries do any deliberate normalization to prefer one of
such a set of values over another. The actual behavior will be more
like "which one got entered into the hash table first" when using
hashing for de-duplication, or other unspecified algorithmic behavior.

regards, tom lane