On 2016/03/29 23:20, Ashutosh Bapat wrote:
I think the reason for that is in foreign_join_ok. This in that
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.
Sent via pgsql-hackers mailing list (email@example.com)
To make changes to your subscription: