Tender, sorry, you patch is perfect. I badly tested it this morning. My fault. There is just the case with the constant that is not handled (select * from a left join (select 1 const1 from b) x where x.const1 is null) but i don't think it worth handling it. I added 2 regresssion tests in case you want to add them to your patch. The first one is important, the second one is just to confirm that subqueries are working. I've added 3 new tables because in test_setup.sql and join.sql there were no table with not null constraints.
Again, thanks a lot.
From 130800e76af9e98c024b76d85fd167857e6549d4 Mon Sep 17 00:00:00 2001 From: Nicolas Adenis-Lamarre <[email protected]> Date: Thu, 1 Jan 2026 23:09:54 +0100 Subject: [PATCH] Reduce JOIN_LEFT TO JOIN_ANTI : regression tests --- src/test/regress/expected/join.out | 37 ++++++++++++++++++++++++++++++ src/test/regress/sql/join.sql | 18 +++++++++++++++ 2 files changed, 55 insertions(+) diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index d05a0ca0373..9698c239974 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -3194,6 +3194,15 @@ reset enable_nestloop; -- -- basic semijoin and antijoin recognition tests -- +CREATE TABLE aj1 (id integer primary key); +CREATE TABLE aj2 (id integer primary key, aj1_id integer); +CREATE TABLE aj3 (id integer primary key, aj2_id integer); +INSERT INTO aj1 VALUES (1); +INSERT INTO aj1 VALUES (2); +INSERT INTO aj2 VALUES (1, 1); +INSERT INTO aj2 VALUES (2, 1); +INSERT INTO aj3 VALUES (1, 2); +INSERT INTO aj3 VALUES (2, 2); explain (costs off) select a.* from tenk1 a where unique1 in (select unique2 from tenk1 b); @@ -3254,6 +3263,34 @@ where b.unique2 is null; -> Index Only Scan using tenk1_unique2 on tenk1 b (5 rows) +explain (costs off) +select aj1.* from aj1 left join aj2 on aj1.id = aj2.aj1_id +where aj2.id is null; + QUERY PLAN +------------------------------------ + Hash Right Anti Join + Hash Cond: (aj2.aj1_id = aj1.id) + -> Seq Scan on aj2 + -> Hash + -> Seq Scan on aj1 +(5 rows) + +explain (costs off) +select aj1.* from aj1 left join (select aj2.id aj2_id, aj2.aj1_id, aj3.id aj3_id from aj2 left join aj3 on aj2.id = aj3.aj2_id) x on aj1.id = x.aj1_id +where x.aj2_id is null; + QUERY PLAN +------------------------------------------ + Hash Right Anti Join + Hash Cond: (aj2.aj1_id = aj1.id) + -> Hash Right Join + Hash Cond: (aj3.aj2_id = aj2.id) + -> Seq Scan on aj3 + -> Hash + -> Seq Scan on aj2 + -> Hash + -> Seq Scan on aj1 +(9 rows) + -- check that we avoid de-duplicating columns redundantly set enable_memoize to off; explain (costs off) diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index b91fb7574df..acab11e20bb 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -837,6 +837,16 @@ reset enable_nestloop; -- -- basic semijoin and antijoin recognition tests -- +CREATE TABLE aj1 (id integer primary key); +CREATE TABLE aj2 (id integer primary key, aj1_id integer); +CREATE TABLE aj3 (id integer primary key, aj2_id integer); + +INSERT INTO aj1 VALUES (1); +INSERT INTO aj1 VALUES (2); +INSERT INTO aj2 VALUES (1, 1); +INSERT INTO aj2 VALUES (2, 1); +INSERT INTO aj3 VALUES (1, 2); +INSERT INTO aj3 VALUES (2, 2); explain (costs off) select a.* from tenk1 a @@ -859,6 +869,14 @@ explain (costs off) select a.* from tenk1 a left join tenk1 b on a.unique1 = b.unique2 where b.unique2 is null; +explain (costs off) +select aj1.* from aj1 left join aj2 on aj1.id = aj2.aj1_id +where aj2.id is null; + +explain (costs off) +select aj1.* from aj1 left join (select aj2.id aj2_id, aj2.aj1_id, aj3.id aj3_id from aj2 left join aj3 on aj2.id = aj3.aj2_id) x on aj1.id = x.aj1_id +where x.aj2_id is null; + -- check that we avoid de-duplicating columns redundantly set enable_memoize to off; explain (costs off) -- 2.34.1
