Horton
Menu

Thread

  1. BUG #18990: The results of equivalent queries are inconsistent (one returns 0, another returns -0)

    PG Bug reporting form <noreply@postgresql.org> — 2025-07-17T11:30:40Z

    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

  2. Re: BUG #18990: The results of equivalent queries are inconsistent (one returns 0, another returns -0)

    Dilip Kumar <dilipbalaut@gmail.com> — 2025-07-17T12:06:55Z

    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

  3. Re: BUG #18990: The results of equivalent queries are inconsistent (one returns 0, another returns -0)

    Dilip Kumar <dilipbalaut@gmail.com> — 2025-07-17T13:09:30Z

    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