From: Tom Lane <tgl@sss.pgh.pa.us>
To: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
cc: srinivasan.sa@zohocorp.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
In-reply-to: <5C51993C.2070606@lab.ntt.co.jp>
References: <15613-092be1be9576c728@postgresql.org>
 <5C51993C.2070606@lab.ntt.co.jp>
Comments: In-reply-to Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
	message dated "Wed, 30 Jan 2019 21:31:56 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <19831.1548865557.1@sss.pgh.pa.us>
Date: Wed, 30 Jan 2019 11:25:57 -0500
Message-ID: <19832.1548865557@sss.pgh.pa.us>
Precedence: bulk

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