From: | Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Postgres_fdw join pushdown - INNER - FULL OUTER join combination generating wrong result |
Date: | 2016-03-28 10:09:06 |
Message-ID: | 56F902C2.9000202@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 2016/03/28 18:17, Rajkumar Raghuwanshi wrote:
> I am testing postgres_fdw join pushdown feature for PostgreSQL 9.6 DB,
> and I observed below issue._
>
> 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)
I think the reason for that is in foreign_join_ok. This in that function:
switch (jointype)
{
case JOIN_INNER:
fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_i->remote_conds));
fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_o->remote_conds));
break;
case JOIN_LEFT:
fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_i->remote_conds));
fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_o->remote_conds));
break;
case JOIN_RIGHT:
fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_o->remote_conds));
fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_i->remote_conds));
break;
case JOIN_FULL:
fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_i->remote_conds));
fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_o->remote_conds));
break;
default:
/* Should not happen, we have just check this above */
elog(ERROR, "unsupported join type %d", jointype);
}
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.
Best regards,
Etsuro Fujita
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Langote | 2016-03-28 10:09:43 | Re: A question on systable_beginscan() |
Previous Message | Emre Hasegeli | 2016-03-28 09:42:54 | Re: Alter or rename enum value |