Re: Making Vars outer-join aware

On Tue, Jan 24, 2023 at 1:25 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Tue, Jan 24, 2023 at 12:31 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> select ... from t1 left join t2 on (t1.x = t2.y and t1.x = 1);
> >>
> >> If we turn the generic equivclass.c logic loose on these clauses,
> >> it will deduce t2.y = 1, which is good, and then apply t2.y = 1 at
> >> the scan of t2, which is even better (since we might be able to turn
> >> that into an indexscan qual). However, it will also try to apply
> >> t1.x = 1 at the scan of t1, and that's just wrong, because that
> >> will eliminate t1 rows that should come through with null extension.
>
> > Is there a particular comment or README where that last conclusion is
> > explained so that it makes sense.
>
> Hm? It's a LEFT JOIN, so it must not eliminate any rows from t1.
> A row that doesn't have t1.x = 1 will appear in the output with
> null columns for t2 ... but it must still appear, so we cannot
> filter on t1.x = 1 in the scan of t1.
>
>
Ran some queries, figured it out. Sorry for the noise. I had turned the
behavior of the RHS side appearing in the ON clause into a personal general
rule then tried to apply it to the LHS (left join mental model) without
working through the rules from first principles.

David J.