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