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

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:

DETAIL: {PLANNEDSTMT
:commandType 1
:queryId 2207256765
:hasReturning false
:hasModifyingCTE false
:canSetTag true
:transientPlan false
:dependsOnRole false
:parallelModeNeeded false
:planTree
{HASHJOIN
:startup_cost 10000000001.11
:total_cost 10000000003.34
:plan_rows 1
:plan_width 8
:parallel_aware false
:plan_node_id 0
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 94
}
:resno 1
:resname c1
:ressortgroupref 0
:resorigtbl 4626056
:resorigcol 1
:resjunk false
}
)
:qual <>
:lefttree
{NESTLOOP
:startup_cost 10000000000.00
:total_cost 10000000002.22
:plan_rows 1
:plan_width 16
:parallel_aware false
:plan_node_id 1
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 94
}
:resno 1
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65000
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 6
:varoattno 1
:location 222
}
:resno 2
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:qual <>
:lefttree
{FOREIGNSCAN
:startup_cost 0.00
:total_cost 1.10
:plan_rows 1
:plan_width 16
:parallel_aware false
:plan_node_id 2
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 1
:location -1
}
:resno 1
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 2
:location -1
}
:resno 2
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:qual <>
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:scanrelid 1
:operation 1
:fs_server 4625991
:fdw_exprs <>
:fdw_private <>
:fdw_scan_tlist <>
:fdw_recheck_quals <>
:fs_relids (b 1)
:fsSystemCol false
}
:righttree
{FOREIGNSCAN
:startup_cost 0.00
:total_cost 1.10
:plan_rows 1
:plan_width 8
:parallel_aware false
:plan_node_id 3
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 6
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 6
:varoattno 1
:location -1
}
:resno 1
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:qual (
{NULLTEST
:arg
{VAR
:varno 1
:varattno 2
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 2
:location 259
}
:nulltesttype 1
:argisrow false
:location 535
}
{OPEXPR
:opno 37
:opfuncid 854
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 0
:args (
{FUNCEXPR
:funcid 2197
:funcresulttype 23
:funcretset false
:funcvariadic false
:funcformat 0
:funccollid 0
:inputcollid 0
:args <>
:location 462
}
{VAR
:varno 6
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 6
:varoattno 1
:location 222
}
)
:location 492
}
)
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:scanrelid 6
:operation 1
:fs_server 4625991
:fdw_exprs <>
:fdw_private <>
:fdw_scan_tlist <>
:fdw_recheck_quals <>
:fs_relids (b 6)
:fsSystemCol false
}
:initPlan <>
:extParam (b)
:allParam (b)
:jointype 0
:joinqual <>
:nestParams <>
}
:righttree
{HASH
:startup_cost 1.10
:total_cost 1.10
:plan_rows 1
:plan_width 8
:parallel_aware false
:plan_node_id 4
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 4
:varoattno 1
:location -1
}
:resno 1
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:qual <>
:lefttree
{FOREIGNSCAN
:startup_cost 0.00
:total_cost 1.10
:plan_rows 1
:plan_width 8
:parallel_aware false
:plan_node_id 5
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 4
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 4
:varoattno 1
:location 429
}
:resno 1
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:qual <>
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:scanrelid 4
:operation 1
:fs_server 4625991
:fdw_exprs <>
:fdw_private <>
:fdw_scan_tlist <>
:fdw_recheck_quals <>
:fs_relids (b 4)
:fsSystemCol false
}
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:skewTable 4626059
:skewColumn 1
:skewInherit false
:skewColType 20
:skewColTypmod -1
}
:initPlan <>
:extParam (b)
:allParam (b)
:jointype 0
:joinqual <>
:hashclauses (
{OPEXPR
:opno 410
:opfuncid 467
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 0
:args (
{VAR
:varno 65001
:varattno 2
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 6
:varoattno 1
:location 222
}
{VAR
:varno 65000
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 4
:varoattno 1
:location 429
}
)
:location -1
}
)
}
:rtable (
{RTE
:alias
{ALIAS
:aliasname ref_0
:colnames <>
}
:eref
{ALIAS
:aliasname ref_0
:colnames ("t1_col1" "t1_col2")
}
:rtekind 0
:relid 4626056
:relkind f
:tablesample <>
:lateral false
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 9 10)
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
{RTE
:alias
{ALIAS
:aliasname subq_1
:colnames <>
}
:eref
{ALIAS
:aliasname subq_1
:colnames ("c1" "c2" "c3")
}
:rtekind 1
:subquery <>
:security_barrier false
:lateral true
:inh false
:inFromCl true
:requiredPerms 0
:checkAsUser 0
:selectedCols (b)
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
{RTE
:alias
{ALIAS
:aliasname subq_0
:colnames <>
}
:eref
{ALIAS
:aliasname subq_0
:colnames ("c1" "c2")
}
:rtekind 1
:subquery <>
:security_barrier false
:lateral true
:inh false
:inFromCl true
:requiredPerms 0
:checkAsUser 0
:selectedCols (b)
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
{RTE
:alias
{ALIAS
:aliasname ref_3
:colnames <>
}
:eref
{ALIAS
:aliasname ref_3
:colnames ("t3_col1")
}
:rtekind 0
:relid 4626062
:relkind f
:tablesample <>
:lateral false
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 9)
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
{RTE
:alias <>
:eref
{ALIAS
:aliasname unnamed_join
:colnames ("c1" "c2" "t3_col1")
}
:rtekind 2
:jointype 0
:joinaliasvars <>
:lateral false
:inh false
:inFromCl true
:requiredPerms 0
:checkAsUser 0
:selectedCols (b)
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
{RTE
:alias
{ALIAS
:aliasname ref_1
:colnames <>
}
:eref
{ALIAS
:aliasname ref_1
:colnames ("t2_col1")
}
:rtekind 0
:relid 4626059
:relkind f
:tablesample <>
:lateral false
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 9)
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
)
:resultRelations <>
:utilityStmt <>
:subplans <>
:rewindPlanIDs (b)
:rowMarks <>
:relationOids (o 4626056 4626062 4626059)
:invalItems <>
:nParamExec 0
}