subquery pullup misses lateral refs in join alias Vars

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: subquery pullup misses lateral refs in join alias Vars
Date: 2026-09-19 07:13:32
Message-ID: CAMbWs48GFZ=3Bjc1ug9JRsB0Qupg+CGcb9js5vmTM6kY2OGWOg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

is_simple_subquery() refuses to pull up a LATERAL subquery whose
targetlist or quals reference rels outside the lowest outer join above
it. But pull_up_simple_subquery() rechecks this before flattening
join alias Vars in the subquery, so a lateral reference hidden in a
join Var goes unnoticed. Both queries below hit Assert("sjinfo ==
NULL") in distribute_qual_to_rels():

create table t (a int);

-- hidden in the subquery's targetlist
select 1 from t t1,
lateral (select (j is null)::int
from ((select t1.a) s left join (select 1) v on false) j) ss(x)
left join t t2 on ss.x = t2.a;

-- hidden in the subquery's quals
select 1 from t t1,
t t2 left join
lateral (select 1 from ((select t1.a) s left join (select 1) v on false) j
where length(j::text) > 3) ss on true;

Without asserts, the second fails with "wrong phnullingrels".

I think we should flatten join alias Vars in the subquery's targetlist
and quals before the recheck. Attached is a WIP patch doing that.

- Richard

Attachment Content-Type Size
v1-0001-Flatten-join-alias-Vars-before-rechecking-subquer.patch application/octet-stream 10.8 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Ziming Zhang 2026-09-19 07:25:39 Re: [PATCH] postgres_fdw: Fix cost estimation for semi join pushdown
Previous Message Amit Langote 2026-09-19 07:05:30 Re: PG19: two RI fast-path issues found while testing the batching revert