PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15613
PG Version9.6.3
OSLinux and macOS
Opened2019-01-30 09:46:44+00
Reported bySrinivasan S A
StatusOpen

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15613
Logged by:          Srinivasan S A
Email address:      (redacted)
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
	   }

Messages

DateAuthorSubject
2019-01-30 09:46:44+00PG Bug reporting formBUG #15613: Bug in PG Planner for Foreign Data Wrappers
2019-01-30 12:31:56+00Etsuro FujitaRe: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
2019-01-30 16:25:57+00Tom LaneRe: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
2019-01-30 17:48:04+00Tom LaneRe: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
2019-01-30 20:07:38+00Tom LaneRe: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
2019-01-31 10:01:13+00Etsuro FujitaRe: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
2019-01-31 18:06:36+00Tom LaneRe: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
2019-02-06 04:15:49+00Etsuro FujitaRe: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
2019-02-06 11:50:51+00Etsuro FujitaRe: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
2019-02-06 19:15:55+00Tom LaneRe: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
2019-02-07 07:49:56+00Etsuro FujitaRe: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
2019-02-07 14:40:28+00Tom LaneRe: BUG #15613: Bug in PG Planner for Foreign Data Wrappers