From: Tom Lane <tgl@sss.pgh.pa.us>
To: "=?utf-8?B?5byA5b+D5bCP5biC5rCR?=" <2530254482@qq.com>
cc: "=?utf-8?B?cGdzcWwtYnVncw==?=" <pgsql-bugs@lists.postgresql.org>
Subject: Re: Inconsistent handling of signed zero (-0) between INNER JOIN and
 INTERSECT ALL
In-reply-to: <tencent_6101D5F7106E039C8DEB50E543EF42E26309@qq.com>
References: <tencent_6101D5F7106E039C8DEB50E543EF42E26309@qq.com>
Comments: In-reply-to "=?utf-8?B?5byA5b+D5bCP5biC5rCR?=" <2530254482@qq.com>
	message dated "Tue, 13 Jan 2026 00:25:16 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3961912.1768246172.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 12 Jan 2026 14:29:32 -0500
Message-ID: <3961913.1768246172@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3961913.1768246172%40sss.pgh.pa.us>
Precedence: bulk

"=3D?utf-8?B?5byA5b+D5bCP5biC5rCR?=3D" <2530254482@qq.com> writes:
> I am writing to report an inconsistency I discovered regarding the handl=
ing of floating-point signed zeros (-0) when comparing a standard INNER JO=
IN with its logically equivalent rewriting using INTERSECT ALL. While INNE=
R JOIN preserves the sign of the zero (-0), the equivalent query construct=
ed 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