Re: Converting NOT IN to anti-joins during planning - Mailing list pgsql-hackers

On Mon, 27 May 2019 at 20:43, Antonin Houska <ah@cybertec.at> wrote:
> I've spent some time looking into this.

Thank you for having a look at this.

> One problem I see is that SubLink can be in the JOIN/ON clause and thus it's
> not necessarily at the top of the join tree. Consider this example:
>
> CREATE TABLE a(i int);
> CREATE TABLE b(j int);
> CREATE TABLE c(k int NOT NULL);
> CREATE TABLE d(l int);
>
>   SELECT *
>     FROM
>         a
>         JOIN b ON b.j NOT IN
>                 ( SELECT
>                         c.k
>                     FROM
>                         c)
>         JOIN d ON b.j = d.l;

hmm yeah. Since the proofs that are being used in
expressions_are_not_nullable assume the join has already taken place,
then we'll either need to not use the join conditions are proofs in
that case, or just disable the optimisation instead.   I think it's
fine to just disable the optimisation since it seem rather unlikely
that someone would write a join condition like that.  Plus it seems
quite a bit more complex to validate that the optimisation would even
be correct if NULLs were not possible.

I've attached a patch which restricts the pullups to FromExpr quals.
Anything below a JoinExpr disables the optimisation now.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services