Re: BUG #15613: Bug in PG Planner for Foreign Data Wrappers

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
Cc: srinivasan(dot)sa(at)zohocorp(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
Date: 2019-01-30 16:25:57
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)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

In response to


Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2019-01-30 17:48:04 Re: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
Previous Message leif 2019-01-30 15:53:51 Fwd: Re: BUG #15589: Due to missing wal, restore ends prematurely and opens database for read/write