Re: Postgres_fdw join pushdown - INNER - FULL OUTER join combination generating wrong result

On Tue, Mar 29, 2016 at 10:20 AM, Ashutosh Bapat
<ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>> I think the reason for that is in foreign_join_ok. This in that function:
>>
>> wrongly pulls up remote_conds from joining relations in the FULL JOIN
>> case. I think we should not pull up such conditions in the FULL JOIN case.
>>
>
> Right. For a full outer join, since each joining relation acts as outer for
> the other, we can not pull up the quals to either join clauses or other
> clauses. So, in such a case, we will need to encapsulate the joining
> relation with conditions into a subquery. Unfortunately, the current deparse
> logic does not handle this encapsulation. Adding that functionality so close
> to the feature freeze might be risky given the amount of code changes
> required.
>
> PFA patch with a quick fix. A full outer join with either of the joining
> relations having WHERE conditions (or other clauses) is not pushed down. In
> the particular case that was reported, the bug triggered because of the way
> conditions are handled for an inner join. For an inner join, all the
> conditions in ON as well as WHERE clause are treated like they are part of
> WHERE clause. This allows pushing down a join even if there are unpushable
> join clauses. But the pushable conditions can be put back into the ON
> clause. This avoids using subqueries while deparsing.

Committed.

I think we should introduce subquery-based deparsing for 9.7, but I
agree it's better not to do it now. I think we should try to handle
SEMI and ANTI joins that way, too.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company