Re: BUG #15613: Bug in PG Planner for Foreign Data Wrappers

(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(dot)sa(at)zohocorp(dot)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