Fuzzing testing ran into an assertion failure with this query:
create table t (a int);
select 1 from t t1
left join t t2 on true
left join lateral (select t2.a, 1 from t t3) s(x, y) on true
left join t t4 on t4.a = s.x
join t t5 on true
where s.y is not null;
TRAP: failed Assert("list_length(root->join_rel_level[levels_needed]) == 1")
The problem is in make_outerjoininfo(). When the join clause is
strict for a lower join's RHS, we assume it also references that RHS.
But find_nonnullable_rels() looks inside PHVs, so it can report a rel
that the PHV only references laterally, which pull_varnos() does not.
Here s.x is a PHV containing t2.a, so the t4 join ends up marked as
commutable with the t1/t2 join even though its clause doesn't mention
t2.
I think we can fix it by intersecting strict_relids with
clause_relids:
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2117,6 +2117,12 @@ make_outerjoininfo(PlannerInfo *root,
*/
strict_relids = find_nonnullable_rels((Node *) clause);
+ /*
+ * find_nonnullable_rels looks inside PlaceHolderVars, so it can report
+ * rels that the clause references only laterally. Ignore those.
+ */
+ strict_relids = bms_int_members(strict_relids, clause_relids);
+
Thoughts?
- Richard
