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.ba...@enterprisedb.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 >
