*** a/contrib/postgres_fdw/deparse.c --- b/contrib/postgres_fdw/deparse.c *************** *** 88,93 **** typedef struct foreign_loc_cxt --- 88,105 ---- } foreign_loc_cxt; /* + * Structure for information on subqueries' column aliases + */ + typedef struct ColumnAliases + { + List *exprs; /* subqueries' columns */ + int max_exprs; /* maximum number of columns stored */ + int *ssno; /* table alias numbers of columns */ + int *sscolno; /* column alias numbers of columns */ + int next_ssno; /* next subquery's table alias number */ + } ColumnAliases; + + /* * Context for deparseExpr */ typedef struct deparse_expr_cxt *************** *** 96,101 **** typedef struct deparse_expr_cxt --- 108,114 ---- RelOptInfo *foreignrel; /* the foreign relation we are planning for */ StringInfo buf; /* output buffer to append to */ List **params_list; /* exprs that will become remote Params */ + ColumnAliases *colaliases; /* subqueries' column aliases */ } deparse_expr_cxt; #define REL_ALIAS_PREFIX "r" *************** *** 103,108 **** typedef struct deparse_expr_cxt --- 116,124 ---- #define ADD_REL_QUALIFIER(buf, varno) \ appendStringInfo((buf), "%s%d.", REL_ALIAS_PREFIX, (varno)) + #define SS_ALIAS_PREFIX "ss" + #define SSCOL_ALIAS_PREFIX "c" + /* * Functions to determine whether an expression can be evaluated safely on * remote server. *************** *** 152,164 **** static void printRemoteParam(int paramindex, Oid paramtype, int32 paramtypmod, deparse_expr_cxt *context); static void printRemotePlaceholder(Oid paramtype, int32 paramtypmod, deparse_expr_cxt *context); ! static void deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context); static void deparseLockingClause(deparse_expr_cxt *context); static void appendOrderByClause(List *pathkeys, deparse_expr_cxt *context); static void appendConditions(List *exprs, deparse_expr_cxt *context); static void deparseFromExprForRel(StringInfo buf, PlannerInfo *root, ! RelOptInfo *joinrel, bool use_alias, List **params_list); /* --- 168,188 ---- deparse_expr_cxt *context); static void printRemotePlaceholder(Oid paramtype, int32 paramtypmod, deparse_expr_cxt *context); ! static void deparseSelectSql(List *tlist, ! List *remote_conds, ! List **retrieved_attrs, deparse_expr_cxt *context); static void deparseLockingClause(deparse_expr_cxt *context); static void appendOrderByClause(List *pathkeys, deparse_expr_cxt *context); static void appendConditions(List *exprs, deparse_expr_cxt *context); static void deparseFromExprForRel(StringInfo buf, PlannerInfo *root, ! RelOptInfo *joinrel, bool use_alias, ! List **params_list, ColumnAliases *colaliases); ! static void appendSubselectAlias(List *exprs, deparse_expr_cxt *context); ! static void updateColumnAliases(ColumnAliases *colaliases, Expr *expr, ! int ssno, int sscolno); ! static bool hasColumnAlias(ColumnAliases *colaliases, Expr *node, ! int *ssno, int *sscolno); /* *************** *** 764,769 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel, --- 788,794 ---- List *tlist, List *remote_conds, List *pathkeys, List **retrieved_attrs, List **params_list) { + ColumnAliases colaliases; deparse_expr_cxt context; /* We handle relations for foreign tables and joins between those */ *************** *** 771,793 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel, rel->reloptkind == RELOPT_BASEREL || rel->reloptkind == RELOPT_OTHER_MEMBER_REL); /* Fill portions of context common to join and base relation */ context.buf = buf; context.root = root; context.foreignrel = rel; context.params_list = params_list; ! /* Construct SELECT clause and FROM clause */ ! deparseSelectSql(tlist, retrieved_attrs, &context); ! ! /* ! * Construct WHERE clause ! */ ! if (remote_conds) ! { ! appendStringInfo(buf, " WHERE "); ! appendConditions(remote_conds, &context); ! } /* Add ORDER BY clause if we found any useful pathkeys */ if (pathkeys) --- 796,817 ---- rel->reloptkind == RELOPT_BASEREL || rel->reloptkind == RELOPT_OTHER_MEMBER_REL); + /* Initialize subqueries' column aliases */ + colaliases.exprs = NIL; + colaliases.max_exprs = 32; + colaliases.ssno = (int *) palloc(colaliases.max_exprs * sizeof(int)); + colaliases.sscolno = (int *) palloc(colaliases.max_exprs * sizeof(int)); + colaliases.next_ssno = 1; + /* Fill portions of context common to join and base relation */ context.buf = buf; context.root = root; context.foreignrel = rel; context.params_list = params_list; + context.colaliases = &colaliases; ! /* Construct SELECT clause and FROM clause and WHERE clause */ ! deparseSelectSql(tlist, remote_conds, retrieved_attrs, &context); /* Add ORDER BY clause if we found any useful pathkeys */ if (pathkeys) *************** *** 800,806 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel, /* * Construct a simple SELECT statement that retrieves desired columns * of the specified foreign table, and append it to "buf". The output ! * contains just "SELECT ... FROM ....". * * We also create an integer List of the columns being retrieved, which is * returned to *retrieved_attrs. --- 824,830 ---- /* * Construct a simple SELECT statement that retrieves desired columns * of the specified foreign table, and append it to "buf". The output ! * contains just "SELECT ... FROM ... WHERE ...". * * We also create an integer List of the columns being retrieved, which is * returned to *retrieved_attrs. *************** *** 809,820 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel, * deparseSelectStmtForRel() for details. */ static void ! deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context) { StringInfo buf = context->buf; RelOptInfo *foreignrel = context->foreignrel; PlannerInfo *root = context->root; PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private; /* * Construct SELECT list --- 833,856 ---- * deparseSelectStmtForRel() for details. */ static void ! deparseSelectSql(List *tlist, ! List *remote_conds, ! List **retrieved_attrs, ! deparse_expr_cxt *context) { StringInfo buf = context->buf; RelOptInfo *foreignrel = context->foreignrel; PlannerInfo *root = context->root; PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private; + StringInfoData jointree; + + /* + * Deparse a join tree expression in FROM clause first. + */ + initStringInfo(&jointree); + deparseFromExprForRel(&jointree, root, foreignrel, + (foreignrel->reloptkind == RELOPT_JOINREL), + context->params_list, context->colaliases); /* * Construct SELECT list *************** *** 848,857 **** deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context) /* * Construct FROM clause */ ! appendStringInfoString(buf, " FROM "); ! deparseFromExprForRel(buf, root, foreignrel, ! (foreignrel->reloptkind == RELOPT_JOINREL), ! context->params_list); } /* --- 884,899 ---- /* * Construct FROM clause */ ! appendStringInfo(buf, " FROM %s", jointree.data); ! ! /* ! * Construct WHERE clause ! */ ! if (remote_conds) ! { ! appendStringInfoString(buf, " WHERE "); ! appendConditions(remote_conds, context); ! } } /* *************** *** 1117,1123 **** deparseExplicitTargetList(List *tlist, List **retrieved_attrs, if (i > 0) appendStringInfoString(buf, ", "); ! deparseVar(var, context); *retrieved_attrs = lappend_int(*retrieved_attrs, i + 1); --- 1159,1165 ---- if (i > 0) appendStringInfoString(buf, ", "); ! deparseExpr((Expr *) var, context); *retrieved_attrs = lappend_int(*retrieved_attrs, i + 1); *************** *** 1137,1143 **** deparseExplicitTargetList(List *tlist, List **retrieved_attrs, */ static void deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel, ! bool use_alias, List **params_list) { PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private; --- 1179,1185 ---- */ static void deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel, ! bool use_alias, List **params_list, ColumnAliases *colaliases) { PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private; *************** *** 1145,1160 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel, { RelOptInfo *rel_o = fpinfo->outerrel; RelOptInfo *rel_i = fpinfo->innerrel; StringInfoData join_sql_o; StringInfoData join_sql_i; /* Deparse outer relation */ initStringInfo(&join_sql_o); ! deparseFromExprForRel(&join_sql_o, root, rel_o, true, params_list); /* Deparse inner relation */ initStringInfo(&join_sql_i); ! deparseFromExprForRel(&join_sql_i, root, rel_i, true, params_list); /* * For a join relation FROM clause entry is deparsed as --- 1187,1250 ---- { RelOptInfo *rel_o = fpinfo->outerrel; RelOptInfo *rel_i = fpinfo->innerrel; + PgFdwRelationInfo *fpinfo_o = (PgFdwRelationInfo *) rel_o->fdw_private; + PgFdwRelationInfo *fpinfo_i = (PgFdwRelationInfo *) rel_i->fdw_private; StringInfoData join_sql_o; StringInfoData join_sql_i; /* Deparse outer relation */ initStringInfo(&join_sql_o); ! if (fpinfo->jointype == JOIN_FULL && fpinfo_o->remote_conds) ! { ! List *tlist = NIL; ! List *retrieved_attrs = NIL; ! deparse_expr_cxt context; ! ! context.buf = &join_sql_o; ! context.root = root; ! context.foreignrel = rel_o; ! context.params_list = params_list; ! context.colaliases = colaliases; ! ! tlist = add_to_flat_tlist(tlist, rel_o->reltarget->exprs); ! appendStringInfoChar(&join_sql_o, '('); ! deparseSelectSql(tlist, ! fpinfo_o->remote_conds, ! &retrieved_attrs, ! &context); ! appendStringInfoChar(&join_sql_o, ')'); ! appendSubselectAlias(rel_o->reltarget->exprs, &context); ! } ! else ! deparseFromExprForRel(&join_sql_o, root, rel_o, true, params_list, ! colaliases); /* Deparse inner relation */ initStringInfo(&join_sql_i); ! if (fpinfo->jointype == JOIN_FULL && fpinfo_i->remote_conds) ! { ! List *tlist = NIL; ! List *retrieved_attrs = NIL; ! deparse_expr_cxt context; ! ! context.buf = &join_sql_i; ! context.root = root; ! context.foreignrel = rel_i; ! context.params_list = params_list; ! context.colaliases = colaliases; ! ! tlist = add_to_flat_tlist(tlist, rel_o->reltarget->exprs); ! appendStringInfoChar(&join_sql_i, '('); ! deparseSelectSql(tlist, ! fpinfo_i->remote_conds, ! &retrieved_attrs, ! &context); ! appendStringInfoChar(&join_sql_i, ')'); ! appendSubselectAlias(rel_i->reltarget->exprs, &context); ! } ! else ! deparseFromExprForRel(&join_sql_i, root, rel_i, true, params_list, ! colaliases); /* * For a join relation FROM clause entry is deparsed as *************** *** 1173,1178 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel, --- 1263,1269 ---- context.foreignrel = foreignrel; context.root = root; context.params_list = params_list; + context.colaliases = colaliases; appendStringInfo(buf, "("); appendConditions(fpinfo->joinclauses, &context); *************** *** 1206,1212 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel, heap_close(rel, NoLock); } ! return; } /* --- 1297,1396 ---- heap_close(rel, NoLock); } ! } ! ! static void ! appendSubselectAlias(List *exprs, deparse_expr_cxt *context) ! { ! StringInfo buf = context->buf; ! ColumnAliases *colaliases = context->colaliases; ! int ssno; ! int sscolno; ! bool first; ! ListCell *lc; ! ! /* Append a table alias */ ! ssno = colaliases->next_ssno; ! appendStringInfo(buf, " %s%d", SS_ALIAS_PREFIX, ssno); ! ! /* Append column aliases */ ! sscolno = 1; ! first = true; ! appendStringInfoChar(buf, '('); ! foreach(lc, exprs) ! { ! Expr *expr = (Expr *) lfirst(lc); ! ! if (!first) ! appendStringInfoString(buf, ", "); ! first = false; ! ! appendStringInfo(buf, "%s%d", SSCOL_ALIAS_PREFIX, sscolno); ! ! updateColumnAliases(colaliases, expr, ssno, sscolno); ! ! sscolno++; ! } ! appendStringInfoChar(buf, ')'); ! ! colaliases->next_ssno++; ! } ! ! static void ! updateColumnAliases(ColumnAliases *colaliases, Expr *expr, ! int ssno, int sscolno) ! { ! int num_exprs = list_length(colaliases->exprs); ! int max_exprs = colaliases->max_exprs; ! int i; ! ListCell *lc; ! ! i = 0; ! foreach(lc, colaliases->exprs) ! { ! if (equal(lfirst(lc), (Node *) expr)) ! { ! colaliases->ssno[i] = ssno; ! colaliases->sscolno[i] = sscolno; ! return; ! } ! i++; ! } ! Assert(i == num_exprs); ! ! colaliases->exprs = lappend(colaliases->exprs, expr); ! if (num_exprs + 1 >= max_exprs) ! { ! max_exprs *= 2; ! colaliases->ssno = (int *) repalloc(colaliases->ssno, ! max_exprs * sizeof(int)); ! colaliases->sscolno = (int *) repalloc(colaliases->sscolno, ! max_exprs * sizeof(int)); ! colaliases->max_exprs = max_exprs; ! } ! colaliases->ssno[num_exprs] = ssno; ! colaliases->sscolno[num_exprs] = sscolno; ! } ! ! static bool ! hasColumnAlias(ColumnAliases *colaliases, Expr *node, ! int *ssno, int *sscolno) ! { ! int i; ! ListCell *lc; ! ! i = 0; ! foreach(lc, colaliases->exprs) ! { ! if (equal(lfirst(lc), (Node *) node)) ! { ! *ssno = colaliases->ssno[i]; ! *sscolno = colaliases->sscolno[i]; ! return true; ! } ! i++; ! } ! return false; } /* *************** *** 1342,1347 **** deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root, --- 1526,1532 ---- context.foreignrel = baserel; context.buf = buf; context.params_list = params_list; + context.colaliases = NULL; appendStringInfoString(buf, "UPDATE "); deparseRelation(buf, rel); *************** *** 1426,1431 **** deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root, --- 1611,1617 ---- context.foreignrel = baserel; context.buf = buf; context.params_list = params_list; + context.colaliases = NULL; appendStringInfoString(buf, "DELETE FROM "); deparseRelation(buf, rel); *************** *** 1741,1746 **** deparseExpr(Expr *node, deparse_expr_cxt *context) --- 1927,1946 ---- if (node == NULL) return; + if (context->colaliases) + { + int ssno; + int sscolno; + + if (hasColumnAlias(context->colaliases, node, &ssno, &sscolno)) + { + appendStringInfo(context->buf, "%s%d.%s%d", + SS_ALIAS_PREFIX, ssno, + SSCOL_ALIAS_PREFIX, sscolno); + return; + } + } + switch (nodeTag(node)) { case T_Var: *** a/contrib/postgres_fdw/expected/postgres_fdw.out --- b/contrib/postgres_fdw/expected/postgres_fdw.out *************** *** 1144,1149 **** SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1. --- 1144,1201 ---- | 27 (10 rows) + -- full outer join with restrictions on the joining relations + EXPLAIN (COSTS false, VERBOSE) + SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 BETWEEN 50 AND 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 BETWEEN 50 AND 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1; + QUERY PLAN + --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan + Output: ft4.c1, ft5.c1 + Relations: (public.ft4) FULL JOIN (public.ft5) + Remote SQL: SELECT ss1.c1, ss2.c1 FROM ((SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 50)) AND ((c1 <= 60))) ss1(c1) FULL JOIN (SELECT c1 FROM "S 1"."T 4" WHERE ((c1 >= 50)) AND ((c1 <= 60))) ss2(c1) ON (((ss1.c1 = ss2.c1)))) ORDER BY ss1.c1 ASC NULLS LAST, ss2.c1 ASC NULLS LAST + (4 rows) + + SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 BETWEEN 50 AND 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 BETWEEN 50 AND 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1; + c1 | c1 + ----+---- + 50 | + 52 | + 54 | 54 + 56 | + 58 | + 60 | 60 + | 51 + | 57 + (8 rows) + + -- full outer join + inner join + EXPLAIN (COSTS false, VERBOSE) + SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 AND t1.c1 BETWEEN 50 AND 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10; + QUERY PLAN + -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Limit + Output: t1.c1, t2.c1, t3.c1 + -> Foreign Scan + Output: t1.c1, t2.c1, t3.c1 + Relations: ((public.ft4 t1) INNER JOIN (public.ft5 t2)) FULL JOIN (public.ft4 t3) + Remote SQL: SELECT ss1.c1, ss1.c2, r4.c1 FROM ((SELECT r1.c1, r2.c1 FROM ("S 1"."T 3" r1 INNER JOIN "S 1"."T 4" r2 ON (TRUE)) WHERE ((r1.c1 = (r2.c1 + 1))) AND ((r1.c1 >= 50)) AND ((r1.c1 <= 60))) ss1(c1, c2) FULL JOIN "S 1"."T 3" r4 ON (((ss1.c2 = r4.c1)))) ORDER BY ss1.c1 ASC NULLS LAST, ss1.c2 ASC NULLS LAST, r4.c1 ASC NULLS LAST + (6 rows) + + SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 AND t1.c1 BETWEEN 50 AND 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10; + c1 | c1 | c1 + ----+----+---- + 52 | 51 | + 58 | 57 | + | | 2 + | | 4 + | | 6 + | | 8 + | | 10 + | | 12 + | | 14 + | | 16 + (10 rows) + -- full outer join three tables EXPLAIN (COSTS false, VERBOSE) SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; *** a/contrib/postgres_fdw/postgres_fdw.c --- b/contrib/postgres_fdw/postgres_fdw.c *************** *** 4073,4082 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype, 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: --- 4073,4078 ---- *** a/contrib/postgres_fdw/sql/postgres_fdw.sql --- b/contrib/postgres_fdw/sql/postgres_fdw.sql *************** *** 372,377 **** SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGH --- 372,385 ---- EXPLAIN (COSTS false, VERBOSE) SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10; SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10; + -- full outer join with restrictions on the joining relations + EXPLAIN (COSTS false, VERBOSE) + SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 BETWEEN 50 AND 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 BETWEEN 50 AND 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1; + SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 BETWEEN 50 AND 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 BETWEEN 50 AND 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1; + -- full outer join + inner join + EXPLAIN (COSTS false, VERBOSE) + SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 AND t1.c1 BETWEEN 50 AND 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10; + SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 AND t1.c1 BETWEEN 50 AND 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10; -- full outer join three tables EXPLAIN (COSTS false, VERBOSE) SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;