SQL === create table txt(); SELECT FROM pg_catalog.pg_roles AS ref_0 RIGHT JOIN txt AS ref_1 ON NULL, LATERAL (SELECT WHERE ref_0.rolpassword ~>=~ ref_0.rolpassword) AS subq_2;
Thanks for the report! I can reproduce this issue with the following query.
create table t (a int unique, b int);
select t1.a from t t1 left join (select 2 as n from t t2 left join t t3 on true) ss on true where ss.n = 2;
It seems something is wrong about the check on PlaceHolderVars in outer-join removal codes. When we want to know if a PHV actually references inner-rel's attributes, we check phinfo->ph_var->phexpr with pull_varnos. I suspect this is wrong. Shouldn't we check phinfo->ph_var?
--- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -236,7 +236,7 @@ join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo) continue; /* it definitely doesn't reference innerrel */ if (bms_is_subset(phinfo->ph_eval_at, innerrel->relids)) return false; /* there isn't any other place to eval PHV */ - if (bms_overlap(pull_varnos(root, (Node *) phinfo->ph_var->phexpr), + if (bms_overlap(pull_varnos(root, (Node *) phinfo->ph_var), innerrel->relids))
