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

> Observation:_ Inner join and full outer join combination on a table
>>
>> generating wrong result.
>>
>> SELECT * FROM lt;
>> c1
>> ----
>> 1
>> 2
>> (2 rows)
>>
>> SELECT * FROM ft;
>> c1
>> ----
>> 1
>> 2
>> (2 rows)
>>
>> \d+ ft
>> Foreign table "public.ft"
>> Column | Type | Modifiers | FDW Options | Storage | Stats target |
>> Description
>>
>> --------+---------+-----------+-------------+---------+--------------+-------------
>> c1 | integer | | | plain | |
>> Server: link_server
>> FDW Options: (table_name 'lt')
>>
>> --inner join and full outer join on local tables
>> SELECT t1.c1,t2.c1,t3.c1 FROM lt t1 INNER JOIN lt t2 ON (t1.c1 = t2.c1)
>> FULL JOIN lt t3 ON (t2.c1 = t3.c1);
>> c1 | c1 | c1
>> ----+----+----
>> 1 | 1 | 1
>> 2 | 2 | 2
>> (2 rows)
>>
>> --inner join and full outer join on corresponding foreign tables
>> SELECT t1.c1,t2.c1,t3.c1 FROM ft t1 INNER JOIN ft t2 ON (t1.c1 = t2.c1)
>> FULL JOIN ft t3 ON (t2.c1 = t3.c1);
>> c1 | c1 | c1
>> ----+----+----
>> 1 | 1 | 1
>> 1 | 2 |
>> 2 | 1 |
>> 2 | 2 | 2
>> (4 rows)
>>
>
Thanks Rajkumar for the detailed report.

>
> 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.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company