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

On 2016/03/29 23:20, Ashutosh Bapat 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.

I'm not sure that is a good idea. As you mentioned, we could support
FULL JOIN fully, by encapsulating a joining relation with conditions
into a subquery. And ISTM that it is relatively easy to do that, by
borrowing some ideas from Hanada-san's original join pushdown patch. If
it's OK, I'll create a patch for that in a few days.

Sorry for speaking up late.

Best regards,
Etsuro Fujita