On Sun, Nov 13, 2022 at 6:45 AM Tom Lane <t...@sss.pgh.pa.us> wrote:
> Looking again at that contain_vars_of_level restriction, I think the
> reason for it was just to avoid making a FROM subquery that has outer
> references, and the reason we needed to avoid that was merely that we
> didn't have LATERAL at the time. So I experimented with the attached.
> It seems to work, in that we don't get wrong answers from any of the
> small number of places that are affected. (I wonder though whether
> those test cases still test what they were intended to, particularly
> the postgres_fdw one. We might have to try to hack them some more
> to not get affected by this optimization.) Could do with more test
> cases, no doubt.
Hmm, it seems there were discussions about this change before, such as
in [1].
> One thing I'm not at all clear about is whether we need to restrict
> the optimization so that it doesn't occur if the subquery contains
> outer references falling outside available_rels. I think that that
> case is covered by is_simple_subquery() deciding later to not pull up
> the subquery based on LATERAL restrictions, but maybe that misses
> something.
I think we need to do this, otherwise we'd encounter the problem
described in [2]. In short, the problem is that the constraints imposed
by LATERAL references may make us fail to find any legal join order. As
an example, consider
explain select * from A where exists
(select * from B where A.i in (select C.i from C where C.j = B.j));
ERROR: failed to build any 3-way joins
[1]
https://www.postgresql.org/message-id/flat/CAN_9JTx7N%2BCxEQLnu_uHxx%2BEscSgxLLuNgaZT6Sjvdpt7toy3w%40mail.gmail.com
[2]
https://www.postgresql.org/message-id/CAMbWs49cvkF9akbomz_fCCKS=D5TY=4kgheqcfhpzcxs1gv...@mail.gmail.com
Thanks
Richard
