From: Tom Lane <tgl@sss.pgh.pa.us>
To: 开心小市民 <2530254482@qq.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: Inconsistent handling of signed zero (-0) between INNER JOIN and INTERSECT ALL
Date: Mon, 12 Jan 2026 14:29:32 -0500
Message-ID: <3961913.1768246172@sss.pgh.pa.us> (raw)
In-Reply-To: <tencent_6101D5F7106E039C8DEB50E543EF42E26309@qq.com>
References: <tencent_6101D5F7106E039C8DEB50E543EF42E26309@qq.com>
"=?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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, 2530254482@qq.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Inconsistent handling of signed zero (-0) between INNER JOIN and INTERSECT ALL
In-Reply-To: <3961913.1768246172@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox