Re: Making Vars outer-join aware

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

> I wrote:
> > Hans Buschmann <buschmann(at)nidsa(dot)net> writes:
> >> I just noticed your new efforts in this area.
> >> I wanted to recurr to my old thread [1] considering constant
> propagation of quals.
> >> [1]
> https://www.postgresql.org/message-id/1571413123735.26467@nidsa.net
>
> > Yeah, this patch series is not yet quite up to the point of improving
> > that. That area is indeed the very next thing I want to work on, and
> > I did spend some effort on it last month, but I ran out of time to get
> > it working. Maybe we'll have something there for v17.
>
> BTW, to clarify what's going on there: what I want to do is allow
> the regular equivalence-class machinery to handle deductions from
> equality operators appearing in LEFT JOIN ON clauses (maybe full
> joins too, but I'd be satisfied if it works for one-sided outer
> joins). I'd originally hoped that distinguishing pre-nulled from
> post-nulled variables would be enough to make that safe, but it's
> not. Here's an example:
>
> 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. Intuitively, I would expect t1.x = 1 to
be applied during the scan of t1 - it isn't like the output of the join is
allowed to include t1 rows not matching that condition anyway.

IOW, I thought the more verbose but equivalent syntax for that was:

select ... from (select * from t1 as insub where insub.x = 1) as t1 left
join t2 on (t1.x = t2.y)

Thanks!

David J.