Inconsistent handling of signed zero (-0) between INNER JOIN and INTERSECT ALL - Mailing list pgsql-bugs

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)
• 
  

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