Re: Wrong results from an antijoin

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