Inconsistent handling of signed zero (-0) between INNER JOIN and INTERSECT ALL
Hello PostgreSQL Team,
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).
Environment:
database3=# select version();
version
--------------------------------------------------------------------------------------------------------------------
PostgreSQL 18.1 (Debian 18.1-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
Reproduction Steps:
Refer to the SQL script in the attachment.
Observed Behavior:
Query 1 (JOIN) has returns:
-0
Query 2 (INTERSECT ALL) did not returns:
0
Expected Behavior:
Since INTERSECT ALL is essentially a set operation that should respect the values from the left operand (or at least maintain consistency with equivalent relational algebra operations), one would expect the output to preserve the signed zero -0, matching the INNER JOIN result.
This discrepancy suggests that the hashing or sorting mechanism used in INTERSECT ALL might be normalizing -0 to 0, whereas the JOIN operator preserves the original binary representation.
Best regards,
Ce Lyu.
开心小市民
2530254482@qq.com
Attachments:
"=?utf-8?B?5byA5b+D5bCP5biC5rCR?=" <2530254482@qq.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