Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> writes:
> (2019/01/30 18:46), PG Bug reporting form wrote:
>> Foreign scan of a table (part of join), has a column of another table
>> involved in its qual.
> I haven't looked into this in details yet, but do you have this issue in
> the latest release of 9.6 (ie 9.6.11), not in 9.6.3? I just thought
> this might have been fixed already by commits [1][2].
Nope, cause it's still there in HEAD :-(. What I'm wondering is why
the ref_0.t1_col2 IS NOT NULL condition is being pushed down to the
ref_1 scan in the first place, rather than ref_0 where you'd think it
should go. Yes, this plan shape could have been made to work if the
Var reference had been converted to a NestLoopParam reference, but
it wasn't. So there are a few interesting questions here:
* Why was the IS NOT NULL pushed down to the wrong place?
* Having made that mistake, why didn't the NLP mechanism fix it
and create a valid plan anyway?
* I find that s/RIGHT JOIN/JOIN/ makes the problem go away: the
IS NOT NULL is pushed to the right place and there's no error.
This is even weirder, because the planner successfully simplifies
the outer join to a plain join on its own, and you'd think that
that would happen before any mistake that could cause the other
two problems. Join strength reduction runs before
distribute_qual_to_rels, so how's that happening?
Probably the answers to these questions are interrelated and there's
only one bug at bottom. But maybe not. I've not really dug into
it yet.
regards, tom lane