Hi Richard, Thanks for the patch. I agree that giving sublink-derived antijoins an RTE and relid is the right and most principled way to prevent improper qual pushdown into the RHS.
I also agree with Tom's point regarding add_antijoin_rte(): building a dummy ParseState just to call addRangeTableEntryForJoin() feels like an abuse of the parser machinery. It would be much cleaner and less fragile to construct the RangeTblEntry directly via makeNode(RangeTblEntry) in the planner (or share a helper). In addition, looking at the regression tests: the patch updates both convert_EXISTS_sublink_to_join() and convert_ANY_sublink_to_join(), but the added tests only cover the NOT EXISTS case. It would be great to also add a test case for NOT IN (which exercises the convert_ANY_sublink_to_join() code path with under_not = true). Something like this in src/test/regress/sql/join.sql: sql -- Also test NOT IN (convert_ANY_sublink_to_join) EXPLAIN (COSTS OFF) SELECT s.* FROM (VALUES (1)) AS v(z) LEFT JOIN (SELECT 123456 AS c, t1.f1 AS x FROM int4_tbl t1 WHERE t1.f1 NOT IN (SELECT t2.unique1 FROM tenk1 t2)) s ON true WHERE s.x = s.c; SELECT s.* FROM (VALUES (1)) AS v(z) LEFT JOIN (SELECT 123456 AS c, t1.f1 AS x FROM int4_tbl t1 WHERE t1.f1 NOT IN (SELECT t2.unique1 FROM tenk1 t2)) s ON true WHERE s.x = s.c; Thanks
