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

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