Richard Guo <guofenglinux@gmail.com> writes:
> I believe what happens here is that after we pull up the LATERAL
> subquery we would have a FromExpr with only one child. Then
> remove_useless_result_rtes elides it and merges its qual, which is
> 'n.nspname = c.relname', up to its parent. This qual is then handed to
> EC machinery.
> When it comes to remove_useless_joins, we try to remove the LEFT join.
> We are supposed to remove any joinquals referencing rel 'n'. But here
> remove_rel_from_query only checks rel->joininfo, and neglects to notice
> that there is another joinqual referencing rel 'n' existing in EC. I
> think this is where the problem arises.
Actually, the problem is that we *shouldn't* try to remove the left
join, because the reference to ref_0.schemaname is logically "above"
that join. I messed up the test for that in join_is_removable().
Apparently, we had no test cases that would expose such a condition,
probably because such a reference would typically cause strength-reducing
the left join.
> Another finding is that in remove_useless_result_rtes after we elide the
> single-child FromExpr and merge its qual up to its parent, the LEFT join
> is actually can be reduced to inner join. But we fail to perform that
> since all this happens after we've done outer join reduction.
Yeah. If we *did* do that then this bug would have escaped detection
awhile longer :-(. I'm not terribly concerned about the lack of such
an optimization, but maybe we can look at it someday.
Fix pushed, thanks for investigating!
regards, tom lane
