pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18990: The results of equivalent queries are inconsistent (one returns 0, another returns -0)
3+ messages / 2 participants
[nested] [flat]
* BUG #18990: The results of equivalent queries are inconsistent (one returns 0, another returns -0)
@ 2025-07-17 11:30 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2025-07-17 11:30 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jinhui-lai@foxmail.com
The following bug has been logged on the website:
Bug reference: 18990
Logged by: Jinhui
Email address: jinhui-lai@foxmail.com
PostgreSQL version: 17.5
Operating system: ubuntu 22.04
Description:
Hi, PG developers
Thanks for reading my report!
You can reproduce it as follows:
SELECT DISTINCT t1.c0 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t0 WHERE t1.c0
> t0.c0)
UNION
SELECT DISTINCT t1.c0 FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE t1.c0 >
t0.c0);
c0
----
-4
-3
-2
-1
-0
1
SELECT DISTINCT t1.c0 FROM t1;
c0
----
0
-2
-1
-4
-3
1
Thanks once again for your precious time.
Best regard,
Jinhui
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18990: The results of equivalent queries are inconsistent (one returns 0, another returns -0)
@ 2025-07-17 12:06 Dilip Kumar <dilipbalaut@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Dilip Kumar @ 2025-07-17 12:06 UTC (permalink / raw)
To: jinhui-lai@foxmail.com; pgsql-bugs@lists.postgresql.org
On Thu, Jul 17, 2025 at 5:03 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 18990
> Logged by: Jinhui
> Email address: jinhui-lai@foxmail.com
> PostgreSQL version: 17.5
> Operating system: ubuntu 22.04
> Description:
>
> Hi, PG developers
>
> Thanks for reading my report!
>
> You can reproduce it as follows:
>
> SELECT DISTINCT t1.c0 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t0 WHERE t1.c0
> > t0.c0)
> UNION
> SELECT DISTINCT t1.c0 FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE t1.c0 >
> t0.c0);
> c0
> ----
> -4
> -3
> -2
> -1
> -0
> 1
>
> SELECT DISTINCT t1.c0 FROM t1;
> c0
> ----
> 0
> -2
> -1
> -4
> -3
> 1
>
> Thanks once again for your precious time.
There is no explanation about what the problem is, can you please
explain what problem you see?
--
Regards,
Dilip Kumar
Google
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18990: The results of equivalent queries are inconsistent (one returns 0, another returns -0)
@ 2025-07-17 13:09 Dilip Kumar <dilipbalaut@gmail.com>
parent: Dilip Kumar <dilipbalaut@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Dilip Kumar @ 2025-07-17 13:09 UTC (permalink / raw)
To: jinhui-lai@foxmail.com; pgsql-bugs@lists.postgresql.org
On Thu, Jul 17, 2025 at 5:36 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Thu, Jul 17, 2025 at 5:03 PM PG Bug reporting form
> <noreply@postgresql.org> wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference: 18990
> > Logged by: Jinhui
> > Email address: jinhui-lai@foxmail.com
> > PostgreSQL version: 17.5
> > Operating system: ubuntu 22.04
> > Description:
> >
> > Hi, PG developers
> >
> > Thanks for reading my report!
> >
> > You can reproduce it as follows:
> >
> > SELECT DISTINCT t1.c0 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t0 WHERE t1.c0
> > > t0.c0)
> > UNION
> > SELECT DISTINCT t1.c0 FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE t1.c0 >
> > t0.c0);
> > c0
> > ----
> > -4
> > -3
> > -2
> > -1
> > -0
> > 1
> >
> > SELECT DISTINCT t1.c0 FROM t1;
> > c0
> > ----
> > 0
> > -2
> > -1
> > -4
> > -3
> > 1
> >
> > Thanks once again for your precious time.
>
> There is no explanation about what the problem is, can you please
> explain what problem you see?
So I think the next email you sent where you directly sent to me and
forgot to copy the pgsql-bugs, you have explained the case and your
concern is about the varying output. Actually you are seeing this
output difference because logically 0 and -0 are the same, and you are
seeing different results because both queries were using different
plans for execution.
postgres[3245960]=# explain SELECT DISTINCT t1.c0 FROM t1;
QUERY PLAN
------------------------------------------------------------
HashAggregate (cost=38.25..40.25 rows=200 width=8)
Group Key: c0
-> Seq Scan on t1 (cost=0.00..32.60 rows=2260 width=8)
(3 rows)
postgres[3245960]=# explain SELECT DISTINCT t1.c0 FROM t1 WHERE EXISTS
(SELECT 1 FROM t0 WHERE t1.c0 > t0.c0);
QUERY PLAN
------------------------------------------------------------------------------
Unique (cost=51216.71..51220.48 rows=200 width=8)
-> Sort (cost=51216.71..51218.59 rows=753 width=8)
Sort Key: t1.c0
-> Nested Loop Semi Join (cost=0.00..51180.73 rows=753 width=8)
Join Filter: (t1.c0 > t0.c0)
-> Seq Scan on t1 (cost=0.00..32.60 rows=2260 width=8)
-> Materialize (cost=0.00..43.90 rows=2260 width=8)
-> Seq Scan on t0 (cost=0.00..32.60 rows=2260 width=8)
(8 rows)
Then just for showing the example I turn off the hash aggregate and
executed again, then we can see the results are same, reason is that
both 0 and -0 are same so with distinct whichever is selected first
the next will not be selected, and this will depends upon the strategy
that which will be selected first.
So IMHO this is not an issue.
postgres[3245960]=# set enable_hashagg =off;
SET
postgres[3245960]=# explain SELECT DISTINCT t1.c0 FROM t1;
QUERY PLAN
------------------------------------------------------------------
Unique (cost=158.51..169.81 rows=200 width=8)
-> Sort (cost=158.51..164.16 rows=2260 width=8)
Sort Key: c0
-> Seq Scan on t1 (cost=0.00..32.60 rows=2260 width=8)
(4 rows)
postgres[3245960]=# SELECT DISTINCT t1.c0 FROM t1;
c0
----
-4
-3
-2
-1
-0
1
(6 rows)
postgres[3245960]=# select 1 where +0=-0;^C
postgres[3245960]=# SELECT DISTINCT t1.c0 FROM t1 WHERE NOT EXISTS
(SELECT 1 FROM t0 WHERE t1.c0 > t0.c0)
UNION
SELECT DISTINCT t1.c0 FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE
t1.c0 > t0.c0);
c0
----
-4
-3
-2
-1
-0
1
(6 rows)
--
Regards,
Dilip Kumar
Google
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-07-17 13:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-17 11:30 BUG #18990: The results of equivalent queries are inconsistent (one returns 0, another returns -0) PG Bug reporting form <noreply@postgresql.org>
2025-07-17 12:06 ` Dilip Kumar <dilipbalaut@gmail.com>
2025-07-17 13:09 ` Dilip Kumar <dilipbalaut@gmail.com>
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