BUG #18522: Wrong results with Merge Right Anti Join, inconsistent with Merge Anti Join - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18522
Logged by:          Antti Lampinen
Email address:      antti@lampinen.eu
PostgreSQL version: 16.3
Operating system:   AWS RDS + Macos
Description:

The following two queries result in different query plans and different
results, even though there is only a dummy condition change between them.
The
latter results are correct, there are two rows that match the conditions.

The following was run both in AWS RDS and after restoring database from
plain
SQL dump to local Macos system.


First query.

EXPLAIN (ANALYZE, SETTINGS, VERBOSE )
SELECT id, market_id
FROM "order"
WHERE "order"."deleted_at" IS NULL AND
      "order"."archived_at" IS NULL AND
      NOT EXISTS (SELECT
                  FROM "matchmaking_request"
                  WHERE ("matchmaking_request"."order_id" = "order"."id"
AND
                         "matchmaking_request"."started_at" IS NOT NULL
AND
                         "matchmaking_request"."cancelled_at" IS NULL))
AND
      "order"."market_id" IN (1);

QUERY PLAN
Merge Right Anti Join  (cost=415.85..527.39 rows=350 width=8) (actual
time=6.015..6.018 rows=1 loops=1)
  Output: "order".id, "order".market_id
  Inner Unique: true
  Merge Cond: (matchmaking_request.order_id = "order".id)
  ->  Index Scan using matchmaking_request_order_id_idx on
public.matchmaking_request  (cost=0.28..834.07 rows=5840 width=4) (actual
time=0.032..2.089 rows=702 loops=1)
        Output: <retracted>
        Filter: ((matchmaking_request.started_at IS NOT NULL) AND
(matchmaking_request.cancelled_at IS NULL))
        Rows Removed by Filter: 301
  ->  Sort  (cost=415.56..416.82 rows=503 width=8) (actual time=3.695..3.730
rows=493 loops=1)
        Output: "order".id, "order".market_id
        Sort Key: "order".id
        Sort Method: quicksort  Memory: 40kB
        ->  Index Scan using order_market_id_idx on public."order"
(cost=0.28..392.99 rows=503 width=8) (actual time=0.035..3.555 rows=493
loops=1)
              Output: "order".id, "order".market_id
              Index Cond: ("order".market_id = 1)
              Filter: (("order".deleted_at IS NULL) AND ("order".archived_at
IS NULL))
              Rows Removed by Filter: 871
Settings: random_page_cost = '1'
Planning Time: 0.643 ms
Execution Time: 6.301 ms

Second query. Note the dummy condition at the end.

EXPLAIN (ANALYZE, SETTINGS, VERBOSE )
SELECT id, market_id
FROM "order"
WHERE "order"."deleted_at" IS NULL AND
      "order"."archived_at" IS NULL AND
      NOT EXISTS (SELECT
                  FROM "matchmaking_request"
                  WHERE ("matchmaking_request"."order_id" = "order"."id"
AND
                         "matchmaking_request"."started_at" IS NOT NULL
AND
                         "matchmaking_request"."cancelled_at" IS NULL))
AND
      "order"."market_id" IN (1, 1); -- dummy condition

QUERY PLAN
Merge Anti Join  (cost=0.56..579.56 rows=503 width=8) (actual
time=6.622..6.773 rows=2 loops=1)
  Output: "order".id, "order".market_id
  Merge Cond: ("order".id = matchmaking_request.order_id)
  ->  Index Scan using order_pkey on public."order"  (cost=0.28..467.77
rows=723 width=8) (actual time=0.078..4.435 rows=493 loops=1)
        Output: <retracted>
        Filter: (("order".deleted_at IS NULL) AND ("order".archived_at IS
NULL) AND ("order".market_id = ANY ('{1,1}'::integer[])))
        Rows Removed by Filter: 1935
  ->  Index Scan using matchmaking_request_order_id_idx on
public.matchmaking_request  (cost=0.28..834.07 rows=5840 width=4) (actual
time=0.017..1.996 rows=702 loops=1)
        Output: <retracted>
        Filter: ((matchmaking_request.started_at IS NOT NULL) AND
(matchmaking_request.cancelled_at IS NULL))
        Rows Removed by Filter: 301
Settings: random_page_cost = '1'
Planning Time: 0.629 ms
Execution Time: 6.853 ms