Re: BUG #17773: Assert triggered on analyzejoins.c

On Mon, Feb 6, 2023 at 1:41 PM PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> 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))

Thanks
Richard