Message-ID: <5C51993C.2070606@lab.ntt.co.jp>
Date: Wed, 30 Jan 2019 21:31:56 +0900
From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
Subject: Re: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
References: <15613-092be1be9576c728@postgresql.org>
In-Reply-To: <15613-092be1be9576c728@postgresql.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
To: srinivasan.sa@zohocorp.com, pgsql-bugs@lists.postgresql.org
Cc: PG Bug reporting form <noreply@postgresql.org>
Precedence: bulk

(2019/01/30 18:46), PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference:      15613
> Logged by:          Srinivasan S A
> Email address:      srinivasan.sa@zohocorp.com
> PostgreSQL version: 9.6.3
> Operating system:   Linux and macOS
> Description:
>
> Foreign scan of a table (part of join), has a column of another table
> involved in its qual. This column is of type T_Var (instead of T_Param)
> which could lead to errors or wrong results. This issue is reproducible in
> all foreign data wrappers, including file_fdw. The same query runs fine for
> PG Tables.
>
> Steps to reproduce in file_fdw:
>
> 1. Create 3 tables and analyze it:
>
> CREATE FOREIGN TABLE table1 (
>      t1_col1 bigint,
>      t1_col2 bigint)
> SERVER pglog options (
>      filename 'table1.csv',
>      format 'csv',
>      DELIMITER '|'
> );
>
> CREATE FOREIGN TABLE table2 (
>      t2_col1 bigint)
> SERVER pglog options (
>      filename 'table2.csv',
>      format 'csv',
>      DELIMITER '|'
> );
>
> CREATE FOREIGN TABLE table3 (
>      t3_col1 bigint)
> SERVER pglog options (
>      filename 'table3.csv',
>      format 'csv',
>      DELIMITER '|'
> );
>
> 2. Run the query:
>
> SELECT
>      subq_1.c1 AS c1
> FROM
>      table1 AS ref_0,
>      LATERAL (
>          SELECT
>              ref_0.t1_col1 AS c1,
>              subq_0.c1 AS c2,
>              subq_0.c2 AS c3
>          FROM (
>              SELECT
>                  ref_1.t2_col1 AS c1,
>                  ref_0.t1_col2 AS c2
>              FROM
>                  table2 AS ref_1
>              WHERE
>                  TRUE) AS subq_0
>          RIGHT JOIN table3 AS ref_3 ON (subq_0.c1 = ref_3.t3_col1)
>      WHERE
>          pg_catalog.inet_client_port()<  subq_0.c1) AS subq_1
> WHERE
>      subq_1.c3 IS NOT NULL
> LIMIT 108;
>
> This throws an error:
>
> ERROR:  attribute number 2 exceeds number of columns 1
>
> Explain gives
>
>                                      QUERY PLAN
>
> ----------------------------------------------------------------------------------
>   Hash Join  (cost=10000000001.11..10000000003.34 rows=1 width=8)
>     Hash Cond: (ref_1.t2_col1 = ref_3.t3_col1)
>     ->   Nested Loop  (cost=10000000000.00..10000000002.22 rows=1 width=16)
>           ->   Foreign Scan on table1 ref_0  (cost=0.00..1.10 rows=1
> width=16)
>                 Foreign File:
> /Volumes/Official/workspace/Datasets/table1.csv
>                 Foreign File Size: 4
>           ->   Foreign Scan on table2 ref_1  (cost=0.00..1.10 rows=1
> width=8)
>                 Filter: ((t1_col2 IS NOT NULL) AND (inet_client_port()<
> t2_col1))
>                 Foreign File:
> /Volumes/Official/workspace/Datasets/table2.csv
>                 Foreign File Size: 2
>     ->   Hash  (cost=1.10..1.10 rows=1 width=8)
>           ->   Foreign Scan on table3 ref_3  (cost=0.00..1.10 rows=1
> width=8)
>                 Foreign File:
> /Volumes/Official/workspace/Datasets/table2.csv
>                 Foreign File Size: 2
>
> Here, table2's qual involves table1's column t1_col2. In the plan, this
> column should be of type T_Param instead of T_Var. Query plan below:

I haven't looked into this in details yet, but do you have this issue in 
the latest release of 9.6 (ie 9.6.11), not in 9.6.3?  I just thought 
this might have been fixed already by commits [1][2].

Best regards,
Etsuro Fujita

[1] 
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e5d83995e;hp=79b2e52615faa768d8436c1795e445541460e9d2
[2] 
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c792c7db41466ff02107e3233ec9d92d8e3df866