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

Thanks Ashutosh for the patch. I have applied and tested it. Now getting
proper result for reported issue.

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation

On Tue, Mar 29, 2016 at 7:50 PM, Ashutosh Bapat <
ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:

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