Allow an alias to be attached directly to a JOIN ... USING

From e3118a93b6df9ee8144a9c0e8454860f66b8e3f5 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Mon, 30 Dec 2019 22:15:46 +0100
Subject: [PATCH v2] Allow an alias to be attached to a JOIN ... USING

This allows something like

    SELECT ... FROM t1 JOIN t2 USING (a, b, c) AS x

where x has the columns a, b, c and unlike a regular alias it does not
hide the range variables of the tables being joined t1 and t2.

Per SQL:2016 feature F404 "Range variable for common column names".
---
 doc/src/sgml/ref/select.sgml              | 11 +++-
 src/backend/catalog/sql_features.txt      |  2 +-
 src/backend/nodes/copyfuncs.c             |  2 +
 src/backend/nodes/equalfuncs.c            |  2 +
 src/backend/nodes/outfuncs.c              |  2 +
 src/backend/nodes/readfuncs.c             |  2 +
 src/backend/parser/analyze.c              |  1 +
 src/backend/parser/gram.y                 | 69 +++++++++++++++--------
 src/backend/parser/parse_clause.c         | 44 +++++++++++----
 src/backend/parser/parse_relation.c       | 42 +++++++++++++-
 src/backend/utils/adt/ruleutils.c         |  4 ++
 src/include/nodes/parsenodes.h            | 13 ++++-
 src/include/nodes/primnodes.h             |  1 +
 src/include/parser/parse_node.h           |  1 +
 src/include/parser/parse_relation.h       |  1 +
 src/test/regress/expected/create_view.out | 52 ++++++++++++++++-
 src/test/regress/expected/join.out        | 31 ++++++++++
 src/test/regress/sql/create_view.sql      | 11 ++++
 src/test/regress/sql/join.sql             |  8 +++
 19 files changed, 258 insertions(+), 41 deletions(-)

diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 691e402803..36416085c1 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -59,7 +59,7 @@
     [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
     [ LATERAL ] ROWS FROM( <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] ) ] [, ...] )
                 [ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
-    <replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) ]
+    <replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) [ AS <replaceable class="parameter">join_using_alias</replaceable> ] ]
 
 <phrase>and <replaceable class="parameter">grouping_element</replaceable> can be one of:</phrase>
 
@@ -638,6 +638,15 @@ <title id="sql-from-title"><literal>FROM</literal> Clause</title>
         equivalent columns will be included in the join output, not
         both.
        </para>
+
+       <para>
+        If a <replaceable class="parameter">join_using_alias</replaceable> is
+        specified, it gives a correlation name to the join columns.  Only the
+        join columns in the <literal>USING</literal> clause are addressable by
+        this name.  Unlike an <replaceable
+        class="parameter">alias</replaceable>, this does not hide the names of
+        the joined tables from the rest of the query.
+       </para>
       </listitem>
      </varlistentry>
 
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index ab3e381cff..cb8fd9ddb9 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -264,7 +264,7 @@ F401	Extended joined table	02	FULL OUTER JOIN	YES
 F401	Extended joined table	04	CROSS JOIN	YES	
 F402	Named column joins for LOBs, arrays, and multisets			YES	
 F403	Partitioned joined tables			NO	
-F404	Range variable for common column names			NO	
+F404	Range variable for common column names			YES	
 F411	Time zone specification			YES	differences regarding literal interpretation
 F421	National character			YES	
 F431	Read-only scrollable cursors			YES	
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index a9b8b84b8f..117079409b 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2168,6 +2168,7 @@ _copyJoinExpr(const JoinExpr *from)
 	COPY_NODE_FIELD(rarg);
 	COPY_NODE_FIELD(usingClause);
 	COPY_NODE_FIELD(quals);
+	COPY_NODE_FIELD(join_using_alias);
 	COPY_NODE_FIELD(alias);
 	COPY_SCALAR_FIELD(rtindex);
 
@@ -2374,6 +2375,7 @@ _copyRangeTblEntry(const RangeTblEntry *from)
 	COPY_SCALAR_FIELD(security_barrier);
 	COPY_SCALAR_FIELD(jointype);
 	COPY_NODE_FIELD(joinaliasvars);
+	COPY_NODE_FIELD(join_using_alias);
 	COPY_NODE_FIELD(functions);
 	COPY_SCALAR_FIELD(funcordinality);
 	COPY_NODE_FIELD(tablefunc);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 2fcd4a3467..65d29b17c9 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -784,6 +784,7 @@ _equalJoinExpr(const JoinExpr *a, const JoinExpr *b)
 	COMPARE_NODE_FIELD(rarg);
 	COMPARE_NODE_FIELD(usingClause);
 	COMPARE_NODE_FIELD(quals);
+	COMPARE_NODE_FIELD(join_using_alias);
 	COMPARE_NODE_FIELD(alias);
 	COMPARE_SCALAR_FIELD(rtindex);
 
@@ -2658,6 +2659,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
 	COMPARE_SCALAR_FIELD(security_barrier);
 	COMPARE_SCALAR_FIELD(jointype);
 	COMPARE_NODE_FIELD(joinaliasvars);
+	COMPARE_NODE_FIELD(join_using_alias);
 	COMPARE_NODE_FIELD(functions);
 	COMPARE_SCALAR_FIELD(funcordinality);
 	COMPARE_NODE_FIELD(tablefunc);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index ac02e5ec8d..02c7012380 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1659,6 +1659,7 @@ _outJoinExpr(StringInfo str, const JoinExpr *node)
 	WRITE_NODE_FIELD(rarg);
 	WRITE_NODE_FIELD(usingClause);
 	WRITE_NODE_FIELD(quals);
+	WRITE_NODE_FIELD(join_using_alias);
 	WRITE_NODE_FIELD(alias);
 	WRITE_INT_FIELD(rtindex);
 }
@@ -3072,6 +3073,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
 		case RTE_JOIN:
 			WRITE_ENUM_FIELD(jointype, JoinType);
 			WRITE_NODE_FIELD(joinaliasvars);
+			WRITE_NODE_FIELD(join_using_alias);
 			break;
 		case RTE_FUNCTION:
 			WRITE_NODE_FIELD(functions);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 3f9ebc9044..39673632a2 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1302,6 +1302,7 @@ _readJoinExpr(void)
 	READ_NODE_FIELD(rarg);
 	READ_NODE_FIELD(usingClause);
 	READ_NODE_FIELD(quals);
+	READ_NODE_FIELD(join_using_alias);
 	READ_NODE_FIELD(alias);
 	READ_INT_FIELD(rtindex);
 
@@ -1401,6 +1402,7 @@ _readRangeTblEntry(void)
 		case RTE_JOIN:
 			READ_ENUM_FIELD(jointype, JoinType);
 			READ_NODE_FIELD(joinaliasvars);
+			READ_NODE_FIELD(join_using_alias);
 			break;
 		case RTE_FUNCTION:
 			READ_NODE_FIELD(functions);
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 0656279654..d6e576f9d2 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1735,6 +1735,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
 									 JOIN_INNER,
 									 targetvars,
 									 NULL,
+									 NULL,
 									 false);
 
 	sv_namespace = pstate->p_namespace;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c5086846de..6ecba9bd6e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -437,7 +437,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	locked_rels_list
 %type <boolean>	all_or_distinct
 
-%type <node>	join_outer join_qual
+%type <node>	join_outer
 %type <jtype>	join_type
 
 %type <list>	extract_list overlay_list position_list
@@ -490,7 +490,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <ival>	sub_type opt_materialized
 %type <value>	NumericOnly
 %type <list>	NumericOnly_list
-%type <alias>	alias_clause opt_alias_clause
+%type <alias>	alias_clause opt_alias_clause opt_alias_clause_for_join_using
 %type <list>	func_alias_clause
 %type <sortby>	sortby
 %type <ielem>	index_elem
@@ -12059,20 +12059,28 @@ joined_table:
 					n->quals = NULL;
 					$$ = n;
 				}
-			| table_ref join_type JOIN table_ref join_qual
+			| table_ref join_type JOIN table_ref ON a_expr
 				{
 					JoinExpr *n = makeNode(JoinExpr);
 					n->jointype = $2;
 					n->isNatural = false;
 					n->larg = $1;
 					n->rarg = $4;
-					if ($5 != NULL && IsA($5, List))
-						n->usingClause = (List *) $5; /* USING clause */
-					else
-						n->quals = $5; /* ON clause */
+					n->quals = $6;
+					$$ = n;
+				}
+			| table_ref join_type JOIN table_ref USING '(' name_list ')' opt_alias_clause_for_join_using
+				{
+					JoinExpr *n = makeNode(JoinExpr);
+					n->jointype = $2;
+					n->isNatural = false;
+					n->larg = $1;
+					n->rarg = $4;
+					n->usingClause = $7;
+					n->join_using_alias = $9;	/* not n->alias! */
 					$$ = n;
 				}
-			| table_ref JOIN table_ref join_qual
+			| table_ref JOIN table_ref ON a_expr
 				{
 					/* letting join_type reduce to empty doesn't work */
 					JoinExpr *n = makeNode(JoinExpr);
@@ -12080,10 +12088,19 @@ joined_table:
 					n->isNatural = false;
 					n->larg = $1;
 					n->rarg = $3;
-					if ($4 != NULL && IsA($4, List))
-						n->usingClause = (List *) $4; /* USING clause */
-					else
-						n->quals = $4; /* ON clause */
+					n->quals = $5;
+					$$ = n;
+				}
+			| table_ref JOIN table_ref USING '(' name_list ')' opt_alias_clause_for_join_using
+				{
+					/* letting join_type reduce to empty doesn't work */
+					JoinExpr *n = makeNode(JoinExpr);
+					n->jointype = JOIN_INNER;
+					n->isNatural = false;
+					n->larg = $1;
+					n->rarg = $3;
+					n->usingClause = $6;
+					n->join_using_alias = $8;	/* not n->alias! */
 					$$ = n;
 				}
 			| table_ref NATURAL join_type JOIN table_ref
@@ -12140,6 +12157,21 @@ opt_alias_clause: alias_clause						{ $$ = $1; }
 			| /*EMPTY*/								{ $$ = NULL; }
 		;
 
+/*
+ * The alias clause after JOIN ... USING only accepts the AS ColId spelling,
+ * per SQL standard.  (The grammar could parse the other variants, but they
+ * don't seem to be useful, and it might lead to parser problems in the
+ * future.)
+ */
+opt_alias_clause_for_join_using:
+			AS ColId
+				{
+					$$ = makeNode(Alias);
+					$$->aliasname = $2;
+				}
+			| /*EMPTY*/								{ $$ = NULL; }
+		;
+
 /*
  * func_alias_clause can include both an Alias and a coldeflist, so we make it
  * return a 2-element list that gets disassembled by calling production.
@@ -12182,19 +12214,6 @@ join_outer: OUTER_P									{ $$ = NULL; }
 			| /*EMPTY*/								{ $$ = NULL; }
 		;
 
-/* JOIN qualification clauses
- * Possibilities are:
- *	USING ( column list ) allows only unqualified column names,
- *						  which must match between tables.
- *	ON expr allows more general qualifications.
- *
- * We return USING as a List node, while an ON-expr will not be a List.
- */
-
-join_qual:	USING '(' name_list ')'					{ $$ = (Node *) $3; }
-			| ON a_expr								{ $$ = $2; }
-		;
-
 
 relation_expr:
 			qualified_name
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index ebbba2d7b5..d7566e0741 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -53,7 +53,7 @@
 
 /* Convenience macro for the most common makeNamespaceItem() case */
 #define makeDefaultNSItem(rte, rti) \
-	makeNamespaceItem(rte, rti, true, true, false, true)
+	makeNamespaceItem(rte, rti, true, true, false, true, false)
 
 static void extractRemainingColumns(List *common_colnames,
 									List *src_colnames, List *src_colvars,
@@ -81,7 +81,8 @@ static Node *buildMergedJoinVar(ParseState *pstate, JoinType jointype,
 								Var *l_colvar, Var *r_colvar);
 static ParseNamespaceItem *makeNamespaceItem(RangeTblEntry *rte, int rtindex,
 											 bool rel_visible, bool cols_visible,
-											 bool lateral_only, bool lateral_ok);
+											 bool lateral_only, bool lateral_ok,
+											 bool join_using_alias);
 static void setNamespaceColumnVisibility(List *namespace, bool cols_visible);
 static void setNamespaceLateralState(List *namespace,
 									 bool lateral_only, bool lateral_ok);
@@ -1191,6 +1192,7 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 				   *l_colvars,
 				   *r_colvars,
 				   *res_colvars;
+		ParseNamespaceItem *my_nsitem;
 		bool		lateral_ok;
 		int			sv_namespace_length;
 		RangeTblEntry *rte;
@@ -1296,6 +1298,13 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 			j->usingClause = rlist;
 		}
 
+		/*
+		 * If a USING clause alias was specified, save the USING columns as
+		 * its column list.
+		 */
+		if (j->join_using_alias)
+			j->join_using_alias->colnames = j->usingClause;
+
 		/*
 		 * Now transform the join qualifications, if any.
 		 */
@@ -1447,6 +1456,7 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 										res_colnames,
 										j->jointype,
 										res_colvars,
+										j->join_using_alias,
 										j->alias,
 										true);
 
@@ -1483,13 +1493,25 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 		 * The join RTE itself is always made visible for unqualified column
 		 * names.  It's visible as a relation name only if it has an alias.
 		 */
-		*namespace = lappend(my_namespace,
-							 makeNamespaceItem(rte,
-											   j->rtindex,
-											   (j->alias != NULL),
-											   true,
-											   false,
-											   true));
+		my_nsitem = makeNamespaceItem(rte,
+									  j->rtindex,
+									  (j->alias != NULL || j->join_using_alias != NULL),
+									  true,
+									  false,
+									  true,
+									  j->join_using_alias != NULL);
+
+		/*
+		 * Check the JOIN/USING alias for namespace conflicts against the
+		 * subtrees (per SQL standard).
+		 */
+		if (j->join_using_alias)
+		{
+			checkNameSpaceConflicts(pstate, list_make1(my_nsitem), l_namespace);
+			checkNameSpaceConflicts(pstate, list_make1(my_nsitem), r_namespace);
+		}
+
+		*namespace = lappend(my_namespace, my_nsitem);
 
 		return (Node *) j;
 	}
@@ -1624,7 +1646,8 @@ buildMergedJoinVar(ParseState *pstate, JoinType jointype,
 static ParseNamespaceItem *
 makeNamespaceItem(RangeTblEntry *rte, int rtindex,
 				  bool rel_visible, bool cols_visible,
-				  bool lateral_only, bool lateral_ok)
+				  bool lateral_only, bool lateral_ok,
+				  bool join_using_alias)
 {
 	ParseNamespaceItem *nsitem;
 
@@ -1635,6 +1658,7 @@ makeNamespaceItem(RangeTblEntry *rte, int rtindex,
 	nsitem->p_cols_visible = cols_visible;
 	nsitem->p_lateral_only = lateral_only;
 	nsitem->p_lateral_ok = lateral_ok;
+	nsitem->p_join_using_alias = join_using_alias;
 	return nsitem;
 }
 
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 4888311f44..f8128654c2 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -673,6 +673,37 @@ scanNSItemForColumn(ParseState *pstate, ParseNamespaceItem *nsitem,
 	int32		vartypmod;
 	Oid			varcollid;
 
+	/*
+	 * If this is a JOIN/USING alias, then check that the column is part of
+	 * the USING column list.  If so, let scanRTEForColumn() below do the main
+	 * work.
+	 */
+	if (nsitem->p_join_using_alias)
+	{
+		ListCell   *c;
+		bool		found = false;
+
+		foreach(c, rte->join_using_alias->colnames)
+		{
+			const char *attcolname = strVal(lfirst(c));
+
+			if (strcmp(attcolname, colname) == 0)
+			{
+				found = true;
+				break;
+			}
+		}
+
+		if (!found)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+					 errmsg("column reference \"%s\" is invalid",
+							colname),
+					 errdetail("The range variable \"%s\" only contains columns in the USING clause.",
+							   rte->join_using_alias->aliasname),
+					 parser_errposition(pstate, location)));
+	}
+
 	/*
 	 * Scan the RTE's column names (or aliases) for a match.  Complain if
 	 * multiple matches.
@@ -1903,6 +1934,7 @@ addRangeTableEntryForJoin(ParseState *pstate,
 						  List *colnames,
 						  JoinType jointype,
 						  List *aliasvars,
+						  Alias *join_using_alias,
 						  Alias *alias,
 						  bool inFromCl)
 {
@@ -1927,9 +1959,16 @@ addRangeTableEntryForJoin(ParseState *pstate,
 	rte->subquery = NULL;
 	rte->jointype = jointype;
 	rte->joinaliasvars = aliasvars;
+	rte->join_using_alias = join_using_alias;
 	rte->alias = alias;
 
-	eref = alias ? copyObject(alias) : makeAlias("unnamed_join", NIL);
+	if (alias)
+		eref = copyObject(alias);
+	else if (join_using_alias)
+		eref = copyObject(join_using_alias);
+	else
+		eref = makeAlias("unnamed_join", NIL);
+
 	numaliases = list_length(eref->colnames);
 
 	/* fill in any unspecified alias columns */
@@ -2268,6 +2307,7 @@ addRTEtoQuery(ParseState *pstate, RangeTblEntry *rte,
 		nsitem->p_cols_visible = addToVarNameSpace;
 		nsitem->p_lateral_only = false;
 		nsitem->p_lateral_ok = true;
+		nsitem->p_join_using_alias = false;
 		pstate->p_namespace = lappend(pstate->p_namespace, nsitem);
 	}
 }
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 4af1603e7c..05049b6278 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10437,6 +10437,10 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
 				appendStringInfoString(buf, quote_identifier(colname));
 			}
 			appendStringInfoChar(buf, ')');
+
+			if (j->join_using_alias)
+				appendStringInfo(buf, " AS %s",
+								 quote_identifier(j->join_using_alias->aliasname));
 		}
 		else if (j->quals)
 		{
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ff626cbe61..5f2196deba 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1011,7 +1011,10 @@ typedef struct RangeTblEntry
 
 	/*
 	 * Fields valid for a join RTE (else NULL/zero):
-	 *
+	 */
+	JoinType	jointype;		/* type of join */
+
+	/*
 	 * joinaliasvars is a list of (usually) Vars corresponding to the columns
 	 * of the join result.  An alias Var referencing column K of the join
 	 * result can be replaced by the K'th element of joinaliasvars --- but to
@@ -1026,9 +1029,15 @@ typedef struct RangeTblEntry
 	 * Also, once planning begins, joinaliasvars items can be almost anything,
 	 * as a result of subquery-flattening substitutions.
 	 */
-	JoinType	jointype;		/* type of join */
 	List	   *joinaliasvars;	/* list of alias-var expansions */
 
+	/*
+	 * join_using_alias is an alias clause attached directly to JOIN/USING.
+	 * It is different from the alias field (below) in that it does not hide
+	 * the range variables of the tables being joined.
+	 */
+	Alias	   *join_using_alias;
+
 	/*
 	 * Fields valid for a function RTE (else NIL/zero):
 	 *
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 860a84de7c..7786294487 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1477,6 +1477,7 @@ typedef struct JoinExpr
 	Node	   *rarg;			/* right subtree */
 	List	   *usingClause;	/* USING clause, if any (list of String) */
 	Node	   *quals;			/* qualifiers on join, if any */
+	Alias	   *join_using_alias; /* alias attached to USING clause */
 	Alias	   *alias;			/* user-written alias clause, if any */
 	int			rtindex;		/* RT index assigned for join, or 0 */
 } JoinExpr;
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 674acc5d3c..635e0ecec1 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -257,6 +257,7 @@ typedef struct ParseNamespaceItem
 	bool		p_cols_visible; /* Column names visible as unqualified refs? */
 	bool		p_lateral_only; /* Is only visible to LATERAL expressions? */
 	bool		p_lateral_ok;	/* If so, does join type allow use? */
+	bool		p_join_using_alias;	/* Is it a JOIN/USING alias? */
 } ParseNamespaceItem;
 
 /* Support for parser_errposition_callback function */
diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h
index b09a71ea69..33f6499561 100644
--- a/src/include/parser/parse_relation.h
+++ b/src/include/parser/parse_relation.h
@@ -85,6 +85,7 @@ extern RangeTblEntry *addRangeTableEntryForJoin(ParseState *pstate,
 												List *colnames,
 												JoinType jointype,
 												List *aliasvars,
+												Alias *joinalias,
 												Alias *alias,
 												bool inFromCl);
 extern RangeTblEntry *addRangeTableEntryForCTE(ParseState *pstate,
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f10a3a7a12..c46a9f1bc3 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -805,6 +805,51 @@ View definition:
     (tbl3
      CROSS JOIN tbl4) same;
 
+create table tbl1a (a int, c int);
+create view view_of_joins_2a as select * from tbl1 join tbl1a using (a);
+create view view_of_joins_2b as select * from tbl1 join tbl1a using (a) as x;
+create view view_of_joins_2c as select * from (tbl1 join tbl1a using (a)) as y;
+create view view_of_joins_2d as select * from (tbl1 join tbl1a using (a) as x) as y;
+select pg_get_viewdef('view_of_joins_2a', true);
+       pg_get_viewdef       
+----------------------------
+  SELECT tbl1.a,           +
+     tbl1.b,               +
+     tbl1a.c               +
+    FROM tbl1              +
+      JOIN tbl1a USING (a);
+(1 row)
+
+select pg_get_viewdef('view_of_joins_2b', true);
+         pg_get_viewdef          
+---------------------------------
+  SELECT tbl1.a,                +
+     tbl1.b,                    +
+     tbl1a.c                    +
+    FROM tbl1                   +
+      JOIN tbl1a USING (a) AS x;
+(1 row)
+
+select pg_get_viewdef('view_of_joins_2c', true);
+        pg_get_viewdef         
+-------------------------------
+  SELECT y.a,                 +
+     y.b,                     +
+     y.c                      +
+    FROM (tbl1                +
+      JOIN tbl1a USING (a)) y;
+(1 row)
+
+select pg_get_viewdef('view_of_joins_2d', true);
+           pg_get_viewdef           
+------------------------------------
+  SELECT y.a,                      +
+     y.b,                          +
+     y.c                           +
+    FROM (tbl1                     +
+      JOIN tbl1a USING (a) AS x) y;
+(1 row)
+
 -- Test view decompilation in the face of column addition/deletion/renaming
 create table tt2 (a int, b int, c int);
 create table tt3 (ax int8, b int2, c numeric);
@@ -1904,7 +1949,7 @@ drop cascades to view aliased_view_2
 drop cascades to view aliased_view_3
 drop cascades to view aliased_view_4
 DROP SCHEMA testviewschm2 CASCADE;
-NOTICE:  drop cascades to 67 other objects
+NOTICE:  drop cascades to 72 other objects
 DETAIL:  drop cascades to table t1
 drop cascades to view temporal1
 drop cascades to view temporal2
@@ -1929,6 +1974,11 @@ drop cascades to view unspecified_types
 drop cascades to table tt1
 drop cascades to table tx1
 drop cascades to view view_of_joins
+drop cascades to table tbl1a
+drop cascades to view view_of_joins_2a
+drop cascades to view view_of_joins_2b
+drop cascades to view view_of_joins_2c
+drop cascades to view view_of_joins_2d
 drop cascades to table tt2
 drop cascades to table tt3
 drop cascades to table tt4
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 761376b007..5582961143 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -1621,6 +1621,37 @@ SELECT '' AS "xxx", *
      | 4 | 1 | one   | 2
 (4 rows)
 
+-- test join using aliases
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) WHERE J1_TBL.t = 'one';  -- ok
+ i | j |  t  | k  
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';  -- ok
+ i | j |  t  | k  
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t = 'one';  -- error
+ERROR:  invalid reference to FROM-clause entry for table "j1_tbl"
+LINE 1: ... * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t =...
+                                                             ^
+HINT:  There is an entry for table "j1_tbl", but it cannot be referenced from this part of the query.
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.i = 1;  -- ok
+ i | j |  t  | k  
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one';  -- error
+ERROR:  column reference "t" is invalid
+LINE 1: ...CT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one...
+                                                             ^
+DETAIL:  The range variable "x" only contains columns in the USING clause.
+SELECT * FROM  J1_TBL a1 JOIN J2_TBL a2 USING (i) AS a1;  -- error
+ERROR:  table name "a1" specified more than once
 --
 -- NATURAL JOIN
 -- Inner equi-join on all columns with the same name
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index e7af0bf2fa..dd5b4cd5ee 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -328,6 +328,17 @@ CREATE VIEW aliased_view_4 AS
 
 \d+ view_of_joins
 
+create table tbl1a (a int, c int);
+create view view_of_joins_2a as select * from tbl1 join tbl1a using (a);
+create view view_of_joins_2b as select * from tbl1 join tbl1a using (a) as x;
+create view view_of_joins_2c as select * from (tbl1 join tbl1a using (a)) as y;
+create view view_of_joins_2d as select * from (tbl1 join tbl1a using (a) as x) as y;
+
+select pg_get_viewdef('view_of_joins_2a', true);
+select pg_get_viewdef('view_of_joins_2b', true);
+select pg_get_viewdef('view_of_joins_2c', true);
+select pg_get_viewdef('view_of_joins_2d', true);
+
 -- Test view decompilation in the face of column addition/deletion/renaming
 
 create table tt2 (a int, b int, c int);
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 5fc6617369..a91d826a85 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -126,6 +126,14 @@ CREATE TABLE J2_TBL (
   FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b)
   ORDER BY b, t1.a;
 
+-- test join using aliases
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) WHERE J1_TBL.t = 'one';  -- ok
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';  -- ok
+SELECT * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t = 'one';  -- error
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.i = 1;  -- ok
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one';  -- error
+SELECT * FROM  J1_TBL a1 JOIN J2_TBL a2 USING (i) AS a1;  -- error
+
 
 --
 -- NATURAL JOIN
-- 
2.24.1

From 954b3c9b2a7eab3fc4c15a9777d0f829a6a0382d Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Mon, 27 Jan 2020 09:52:44 +0100
Subject: [PATCH v3] Allow an alias to be attached to a JOIN ... USING

This allows something like

    SELECT ... FROM t1 JOIN t2 USING (a, b, c) AS x

where x has the columns a, b, c and unlike a regular alias it does not
hide the range variables of the tables being joined t1 and t2.

Per SQL:2016 feature F404 "Range variable for common column names".

Discussion: https://www.postgresql.org/message-id/flat/454638cf-d563-ab76-a585-2564428062af@2ndquadrant.com
---
 doc/src/sgml/ref/select.sgml              | 11 +++-
 src/backend/catalog/sql_features.txt      |  2 +-
 src/backend/nodes/copyfuncs.c             |  2 +
 src/backend/nodes/equalfuncs.c            |  2 +
 src/backend/nodes/outfuncs.c              |  2 +
 src/backend/nodes/readfuncs.c             |  2 +
 src/backend/parser/analyze.c              |  1 +
 src/backend/parser/gram.y                 | 69 +++++++++++++++--------
 src/backend/parser/parse_clause.c         | 21 ++++++-
 src/backend/parser/parse_relation.c       | 45 ++++++++++++++-
 src/backend/utils/adt/ruleutils.c         |  4 ++
 src/include/nodes/parsenodes.h            |  7 +++
 src/include/nodes/primnodes.h             |  1 +
 src/include/parser/parse_node.h           |  1 +
 src/include/parser/parse_relation.h       |  1 +
 src/test/regress/expected/create_view.out | 52 ++++++++++++++++-
 src/test/regress/expected/join.out        | 31 ++++++++++
 src/test/regress/sql/create_view.sql      | 11 ++++
 src/test/regress/sql/join.sql             |  8 +++
 19 files changed, 243 insertions(+), 30 deletions(-)

diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 691e402803..36416085c1 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -59,7 +59,7 @@
     [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
     [ LATERAL ] ROWS FROM( <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] ) ] [, ...] )
                 [ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
-    <replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) ]
+    <replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) [ AS <replaceable class="parameter">join_using_alias</replaceable> ] ]
 
 <phrase>and <replaceable class="parameter">grouping_element</replaceable> can be one of:</phrase>
 
@@ -638,6 +638,15 @@ <title id="sql-from-title"><literal>FROM</literal> Clause</title>
         equivalent columns will be included in the join output, not
         both.
        </para>
+
+       <para>
+        If a <replaceable class="parameter">join_using_alias</replaceable> is
+        specified, it gives a correlation name to the join columns.  Only the
+        join columns in the <literal>USING</literal> clause are addressable by
+        this name.  Unlike an <replaceable
+        class="parameter">alias</replaceable>, this does not hide the names of
+        the joined tables from the rest of the query.
+       </para>
       </listitem>
      </varlistentry>
 
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 9f840ddfd2..0e0d8d0b3b 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -264,7 +264,7 @@ F401	Extended joined table	02	FULL OUTER JOIN	YES
 F401	Extended joined table	04	CROSS JOIN	YES	
 F402	Named column joins for LOBs, arrays, and multisets			YES	
 F403	Partitioned joined tables			NO	
-F404	Range variable for common column names			NO	
+F404	Range variable for common column names			YES	
 F411	Time zone specification			YES	differences regarding literal interpretation
 F421	National character			YES	
 F431	Read-only scrollable cursors			YES	
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 54ad62bb7f..6ce71fcc2d 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2168,6 +2168,7 @@ _copyJoinExpr(const JoinExpr *from)
 	COPY_NODE_FIELD(rarg);
 	COPY_NODE_FIELD(usingClause);
 	COPY_NODE_FIELD(quals);
+	COPY_NODE_FIELD(join_using_alias);
 	COPY_NODE_FIELD(alias);
 	COPY_SCALAR_FIELD(rtindex);
 
@@ -2377,6 +2378,7 @@ _copyRangeTblEntry(const RangeTblEntry *from)
 	COPY_NODE_FIELD(joinaliasvars);
 	COPY_NODE_FIELD(joinleftcols);
 	COPY_NODE_FIELD(joinrightcols);
+	COPY_NODE_FIELD(join_using_alias);
 	COPY_NODE_FIELD(functions);
 	COPY_SCALAR_FIELD(funcordinality);
 	COPY_NODE_FIELD(tablefunc);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 5b1ba143b1..8a56786538 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -788,6 +788,7 @@ _equalJoinExpr(const JoinExpr *a, const JoinExpr *b)
 	COMPARE_NODE_FIELD(rarg);
 	COMPARE_NODE_FIELD(usingClause);
 	COMPARE_NODE_FIELD(quals);
+	COMPARE_NODE_FIELD(join_using_alias);
 	COMPARE_NODE_FIELD(alias);
 	COMPARE_SCALAR_FIELD(rtindex);
 
@@ -2665,6 +2666,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
 	COMPARE_NODE_FIELD(joinaliasvars);
 	COMPARE_NODE_FIELD(joinleftcols);
 	COMPARE_NODE_FIELD(joinrightcols);
+	COMPARE_NODE_FIELD(join_using_alias);
 	COMPARE_NODE_FIELD(functions);
 	COMPARE_SCALAR_FIELD(funcordinality);
 	COMPARE_NODE_FIELD(tablefunc);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index d76fae44b8..be15d31a94 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1659,6 +1659,7 @@ _outJoinExpr(StringInfo str, const JoinExpr *node)
 	WRITE_NODE_FIELD(rarg);
 	WRITE_NODE_FIELD(usingClause);
 	WRITE_NODE_FIELD(quals);
+	WRITE_NODE_FIELD(join_using_alias);
 	WRITE_NODE_FIELD(alias);
 	WRITE_INT_FIELD(rtindex);
 }
@@ -3075,6 +3076,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
 			WRITE_NODE_FIELD(joinaliasvars);
 			WRITE_NODE_FIELD(joinleftcols);
 			WRITE_NODE_FIELD(joinrightcols);
+			WRITE_NODE_FIELD(join_using_alias);
 			break;
 		case RTE_FUNCTION:
 			WRITE_NODE_FIELD(functions);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 551ce6c41c..5852131fb8 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1302,6 +1302,7 @@ _readJoinExpr(void)
 	READ_NODE_FIELD(rarg);
 	READ_NODE_FIELD(usingClause);
 	READ_NODE_FIELD(quals);
+	READ_NODE_FIELD(join_using_alias);
 	READ_NODE_FIELD(alias);
 	READ_INT_FIELD(rtindex);
 
@@ -1404,6 +1405,7 @@ _readRangeTblEntry(void)
 			READ_NODE_FIELD(joinaliasvars);
 			READ_NODE_FIELD(joinleftcols);
 			READ_NODE_FIELD(joinrightcols);
+			READ_NODE_FIELD(join_using_alias);
 			break;
 		case RTE_FUNCTION:
 			READ_NODE_FIELD(functions);
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 748bebffc1..9bbe4632f8 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1739,6 +1739,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
 										NIL,
 										NIL,
 										NULL,
+										NULL,
 										false);
 
 	sv_namespace = pstate->p_namespace;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ba5916b4d2..50a80de856 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -437,7 +437,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	locked_rels_list
 %type <boolean>	all_or_distinct
 
-%type <node>	join_outer join_qual
+%type <node>	join_outer
 %type <jtype>	join_type
 
 %type <list>	extract_list overlay_list position_list
@@ -490,7 +490,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <ival>	sub_type opt_materialized
 %type <value>	NumericOnly
 %type <list>	NumericOnly_list
-%type <alias>	alias_clause opt_alias_clause
+%type <alias>	alias_clause opt_alias_clause opt_alias_clause_for_join_using
 %type <list>	func_alias_clause
 %type <sortby>	sortby
 %type <ielem>	index_elem
@@ -12079,20 +12079,28 @@ joined_table:
 					n->quals = NULL;
 					$$ = n;
 				}
-			| table_ref join_type JOIN table_ref join_qual
+			| table_ref join_type JOIN table_ref ON a_expr
 				{
 					JoinExpr *n = makeNode(JoinExpr);
 					n->jointype = $2;
 					n->isNatural = false;
 					n->larg = $1;
 					n->rarg = $4;
-					if ($5 != NULL && IsA($5, List))
-						n->usingClause = (List *) $5; /* USING clause */
-					else
-						n->quals = $5; /* ON clause */
+					n->quals = $6;
+					$$ = n;
+				}
+			| table_ref join_type JOIN table_ref USING '(' name_list ')' opt_alias_clause_for_join_using
+				{
+					JoinExpr *n = makeNode(JoinExpr);
+					n->jointype = $2;
+					n->isNatural = false;
+					n->larg = $1;
+					n->rarg = $4;
+					n->usingClause = $7;
+					n->join_using_alias = $9;	/* not n->alias! */
 					$$ = n;
 				}
-			| table_ref JOIN table_ref join_qual
+			| table_ref JOIN table_ref ON a_expr
 				{
 					/* letting join_type reduce to empty doesn't work */
 					JoinExpr *n = makeNode(JoinExpr);
@@ -12100,10 +12108,19 @@ joined_table:
 					n->isNatural = false;
 					n->larg = $1;
 					n->rarg = $3;
-					if ($4 != NULL && IsA($4, List))
-						n->usingClause = (List *) $4; /* USING clause */
-					else
-						n->quals = $4; /* ON clause */
+					n->quals = $5;
+					$$ = n;
+				}
+			| table_ref JOIN table_ref USING '(' name_list ')' opt_alias_clause_for_join_using
+				{
+					/* letting join_type reduce to empty doesn't work */
+					JoinExpr *n = makeNode(JoinExpr);
+					n->jointype = JOIN_INNER;
+					n->isNatural = false;
+					n->larg = $1;
+					n->rarg = $3;
+					n->usingClause = $6;
+					n->join_using_alias = $8;	/* not n->alias! */
 					$$ = n;
 				}
 			| table_ref NATURAL join_type JOIN table_ref
@@ -12160,6 +12177,21 @@ opt_alias_clause: alias_clause						{ $$ = $1; }
 			| /*EMPTY*/								{ $$ = NULL; }
 		;
 
+/*
+ * The alias clause after JOIN ... USING only accepts the AS ColId spelling,
+ * per SQL standard.  (The grammar could parse the other variants, but they
+ * don't seem to be useful, and it might lead to parser problems in the
+ * future.)
+ */
+opt_alias_clause_for_join_using:
+			AS ColId
+				{
+					$$ = makeNode(Alias);
+					$$->aliasname = $2;
+				}
+			| /*EMPTY*/								{ $$ = NULL; }
+		;
+
 /*
  * func_alias_clause can include both an Alias and a coldeflist, so we make it
  * return a 2-element list that gets disassembled by calling production.
@@ -12202,19 +12234,6 @@ join_outer: OUTER_P									{ $$ = NULL; }
 			| /*EMPTY*/								{ $$ = NULL; }
 		;
 
-/* JOIN qualification clauses
- * Possibilities are:
- *	USING ( column list ) allows only unqualified column names,
- *						  which must match between tables.
- *	ON expr allows more general qualifications.
- *
- * We return USING as a List node, while an ON-expr will not be a List.
- */
-
-join_qual:	USING '(' name_list ')'					{ $$ = (Node *) $3; }
-			| ON a_expr								{ $$ = $2; }
-		;
-
 
 relation_expr:
 			qualified_name
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 36a3efff87..6e7ac5bc3b 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1265,6 +1265,13 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 			j->usingClause = rlist;
 		}
 
+		/*
+		 * If a USING clause alias was specified, save the USING columns as
+		 * its column list.
+		 */
+		if (j->join_using_alias)
+			j->join_using_alias->colnames = j->usingClause;
+
 		/*
 		 * Now transform the join qualifications, if any.
 		 */
@@ -1462,6 +1469,7 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 										   res_colvars,
 										   l_colnos,
 										   r_colnos,
+										   j->join_using_alias,
 										   j->alias,
 										   true);
 
@@ -1515,10 +1523,21 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 		 * The join RTE itself is always made visible for unqualified column
 		 * names.  It's visible as a relation name only if it has an alias.
 		 */
-		nsitem->p_rel_visible = (j->alias != NULL);
+		nsitem->p_rel_visible = (j->alias != NULL || j->join_using_alias != NULL);
 		nsitem->p_cols_visible = true;
 		nsitem->p_lateral_only = false;
 		nsitem->p_lateral_ok = true;
+		nsitem->p_join_using_alias = (j->join_using_alias != NULL);
+
+		/*
+		 * Check the JOIN/USING alias for namespace conflicts against the
+		 * subtrees (per SQL standard).
+		 */
+		if (j->join_using_alias)
+		{
+			checkNameSpaceConflicts(pstate, list_make1(nsitem), l_namespace);
+			checkNameSpaceConflicts(pstate, list_make1(nsitem), r_namespace);
+		}
 
 		*top_nsitem = nsitem;
 		*namespace = lappend(my_namespace, nsitem);
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index b875a50646..2b32f4af99 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -671,6 +671,37 @@ scanNSItemForColumn(ParseState *pstate, ParseNamespaceItem *nsitem,
 	int			attnum;
 	Var		   *var;
 
+	/*
+	 * If this is a JOIN/USING alias, then check that the column is part of
+	 * the USING column list.  If so, let scanRTEForColumn() below do the main
+	 * work.
+	 */
+	if (nsitem->p_join_using_alias)
+	{
+		ListCell   *c;
+		bool		found = false;
+
+		foreach(c, rte->join_using_alias->colnames)
+		{
+			const char *attcolname = strVal(lfirst(c));
+
+			if (strcmp(attcolname, colname) == 0)
+			{
+				found = true;
+				break;
+			}
+		}
+
+		if (!found)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+					 errmsg("column reference \"%s\" is invalid",
+							colname),
+					 errdetail("The range variable \"%s\" only contains columns in the USING clause.",
+							   rte->join_using_alias->aliasname),
+					 parser_errposition(pstate, location)));
+	}
+
 	/*
 	 * Scan the RTE's column names (or aliases) for a match.  Complain if
 	 * multiple matches.
@@ -1265,6 +1296,7 @@ buildNSItemFromTupleDesc(RangeTblEntry *rte, Index rtindex, TupleDesc tupdesc)
 	nsitem->p_cols_visible = true;
 	nsitem->p_lateral_only = false;
 	nsitem->p_lateral_ok = true;
+	nsitem->p_join_using_alias = false;
 
 	return nsitem;
 }
@@ -1326,6 +1358,7 @@ buildNSItemFromLists(RangeTblEntry *rte, Index rtindex,
 	nsitem->p_cols_visible = true;
 	nsitem->p_lateral_only = false;
 	nsitem->p_lateral_ok = true;
+	nsitem->p_join_using_alias = false;
 
 	return nsitem;
 }
@@ -2105,6 +2138,7 @@ addRangeTableEntryForJoin(ParseState *pstate,
 						  List *aliasvars,
 						  List *leftcols,
 						  List *rightcols,
+						  Alias *join_using_alias,
 						  Alias *alias,
 						  bool inFromCl)
 {
@@ -2133,9 +2167,16 @@ addRangeTableEntryForJoin(ParseState *pstate,
 	rte->joinaliasvars = aliasvars;
 	rte->joinleftcols = leftcols;
 	rte->joinrightcols = rightcols;
+	rte->join_using_alias = join_using_alias;
 	rte->alias = alias;
 
-	eref = alias ? copyObject(alias) : makeAlias("unnamed_join", NIL);
+	if (alias)
+		eref = copyObject(alias);
+	else if (join_using_alias)
+		eref = copyObject(join_using_alias);
+	else
+		eref = makeAlias("unnamed_join", NIL);
+
 	numaliases = list_length(eref->colnames);
 
 	/* fill in any unspecified alias columns */
@@ -2181,6 +2222,7 @@ addRangeTableEntryForJoin(ParseState *pstate,
 	nsitem->p_cols_visible = true;
 	nsitem->p_lateral_only = false;
 	nsitem->p_lateral_ok = true;
+	nsitem->p_join_using_alias = false;
 
 	return nsitem;
 }
@@ -2481,6 +2523,7 @@ addNSItemToQuery(ParseState *pstate, ParseNamespaceItem *nsitem,
 		nsitem->p_cols_visible = addToVarNameSpace;
 		nsitem->p_lateral_only = false;
 		nsitem->p_lateral_ok = true;
+		nsitem->p_join_using_alias = false;
 		pstate->p_namespace = lappend(pstate->p_namespace, nsitem);
 	}
 }
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 116e00bce4..98abfb9ca0 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10362,6 +10362,10 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
 				appendStringInfoString(buf, quote_identifier(colname));
 			}
 			appendStringInfoChar(buf, ')');
+
+			if (j->join_using_alias)
+				appendStringInfo(buf, " AS %s",
+								 quote_identifier(j->join_using_alias->aliasname));
 		}
 		else if (j->quals)
 		{
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index da0706add5..4b32a04bce 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1050,6 +1050,13 @@ typedef struct RangeTblEntry
 	List	   *joinleftcols;	/* left-side input column numbers */
 	List	   *joinrightcols;	/* right-side input column numbers */
 
+	/*
+	 * join_using_alias is an alias clause attached directly to JOIN/USING.
+	 * It is different from the alias field (below) in that it does not hide
+	 * the range variables of the tables being joined.
+	 */
+	Alias	   *join_using_alias;
+
 	/*
 	 * Fields valid for a function RTE (else NIL/zero):
 	 *
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index d73be2ad46..121e4b170d 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1491,6 +1491,7 @@ typedef struct JoinExpr
 	Node	   *rarg;			/* right subtree */
 	List	   *usingClause;	/* USING clause, if any (list of String) */
 	Node	   *quals;			/* qualifiers on join, if any */
+	Alias	   *join_using_alias; /* alias attached to USING clause */
 	Alias	   *alias;			/* user-written alias clause, if any */
 	int			rtindex;		/* RT index assigned for join, or 0 */
 } JoinExpr;
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index d25819aa28..7c17f3c46e 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -262,6 +262,7 @@ struct ParseNamespaceItem
 	bool		p_cols_visible; /* Column names visible as unqualified refs? */
 	bool		p_lateral_only; /* Is only visible to LATERAL expressions? */
 	bool		p_lateral_ok;	/* If so, does join type allow use? */
+	bool		p_join_using_alias;	/* Is it a JOIN/USING alias? */
 };
 
 /*
diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h
index 93f94466a0..bde59315d8 100644
--- a/src/include/parser/parse_relation.h
+++ b/src/include/parser/parse_relation.h
@@ -89,6 +89,7 @@ extern ParseNamespaceItem *addRangeTableEntryForJoin(ParseState *pstate,
 													 List *aliasvars,
 													 List *leftcols,
 													 List *rightcols,
+													 Alias *joinalias,
 													 Alias *alias,
 													 bool inFromCl);
 extern ParseNamespaceItem *addRangeTableEntryForCTE(ParseState *pstate,
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f10a3a7a12..c46a9f1bc3 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -805,6 +805,51 @@ View definition:
     (tbl3
      CROSS JOIN tbl4) same;
 
+create table tbl1a (a int, c int);
+create view view_of_joins_2a as select * from tbl1 join tbl1a using (a);
+create view view_of_joins_2b as select * from tbl1 join tbl1a using (a) as x;
+create view view_of_joins_2c as select * from (tbl1 join tbl1a using (a)) as y;
+create view view_of_joins_2d as select * from (tbl1 join tbl1a using (a) as x) as y;
+select pg_get_viewdef('view_of_joins_2a', true);
+       pg_get_viewdef       
+----------------------------
+  SELECT tbl1.a,           +
+     tbl1.b,               +
+     tbl1a.c               +
+    FROM tbl1              +
+      JOIN tbl1a USING (a);
+(1 row)
+
+select pg_get_viewdef('view_of_joins_2b', true);
+         pg_get_viewdef          
+---------------------------------
+  SELECT tbl1.a,                +
+     tbl1.b,                    +
+     tbl1a.c                    +
+    FROM tbl1                   +
+      JOIN tbl1a USING (a) AS x;
+(1 row)
+
+select pg_get_viewdef('view_of_joins_2c', true);
+        pg_get_viewdef         
+-------------------------------
+  SELECT y.a,                 +
+     y.b,                     +
+     y.c                      +
+    FROM (tbl1                +
+      JOIN tbl1a USING (a)) y;
+(1 row)
+
+select pg_get_viewdef('view_of_joins_2d', true);
+           pg_get_viewdef           
+------------------------------------
+  SELECT y.a,                      +
+     y.b,                          +
+     y.c                           +
+    FROM (tbl1                     +
+      JOIN tbl1a USING (a) AS x) y;
+(1 row)
+
 -- Test view decompilation in the face of column addition/deletion/renaming
 create table tt2 (a int, b int, c int);
 create table tt3 (ax int8, b int2, c numeric);
@@ -1904,7 +1949,7 @@ drop cascades to view aliased_view_2
 drop cascades to view aliased_view_3
 drop cascades to view aliased_view_4
 DROP SCHEMA testviewschm2 CASCADE;
-NOTICE:  drop cascades to 67 other objects
+NOTICE:  drop cascades to 72 other objects
 DETAIL:  drop cascades to table t1
 drop cascades to view temporal1
 drop cascades to view temporal2
@@ -1929,6 +1974,11 @@ drop cascades to view unspecified_types
 drop cascades to table tt1
 drop cascades to table tx1
 drop cascades to view view_of_joins
+drop cascades to table tbl1a
+drop cascades to view view_of_joins_2a
+drop cascades to view view_of_joins_2b
+drop cascades to view view_of_joins_2c
+drop cascades to view view_of_joins_2d
 drop cascades to table tt2
 drop cascades to table tt3
 drop cascades to table tt4
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 761376b007..5582961143 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -1621,6 +1621,37 @@ SELECT '' AS "xxx", *
      | 4 | 1 | one   | 2
 (4 rows)
 
+-- test join using aliases
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) WHERE J1_TBL.t = 'one';  -- ok
+ i | j |  t  | k  
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';  -- ok
+ i | j |  t  | k  
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t = 'one';  -- error
+ERROR:  invalid reference to FROM-clause entry for table "j1_tbl"
+LINE 1: ... * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t =...
+                                                             ^
+HINT:  There is an entry for table "j1_tbl", but it cannot be referenced from this part of the query.
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.i = 1;  -- ok
+ i | j |  t  | k  
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one';  -- error
+ERROR:  column reference "t" is invalid
+LINE 1: ...CT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one...
+                                                             ^
+DETAIL:  The range variable "x" only contains columns in the USING clause.
+SELECT * FROM  J1_TBL a1 JOIN J2_TBL a2 USING (i) AS a1;  -- error
+ERROR:  table name "a1" specified more than once
 --
 -- NATURAL JOIN
 -- Inner equi-join on all columns with the same name
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index e7af0bf2fa..dd5b4cd5ee 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -328,6 +328,17 @@ CREATE VIEW aliased_view_4 AS
 
 \d+ view_of_joins
 
+create table tbl1a (a int, c int);
+create view view_of_joins_2a as select * from tbl1 join tbl1a using (a);
+create view view_of_joins_2b as select * from tbl1 join tbl1a using (a) as x;
+create view view_of_joins_2c as select * from (tbl1 join tbl1a using (a)) as y;
+create view view_of_joins_2d as select * from (tbl1 join tbl1a using (a) as x) as y;
+
+select pg_get_viewdef('view_of_joins_2a', true);
+select pg_get_viewdef('view_of_joins_2b', true);
+select pg_get_viewdef('view_of_joins_2c', true);
+select pg_get_viewdef('view_of_joins_2d', true);
+
 -- Test view decompilation in the face of column addition/deletion/renaming
 
 create table tt2 (a int, b int, c int);
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 5fc6617369..a91d826a85 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -126,6 +126,14 @@ CREATE TABLE J2_TBL (
   FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b)
   ORDER BY b, t1.a;
 
+-- test join using aliases
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) WHERE J1_TBL.t = 'one';  -- ok
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';  -- ok
+SELECT * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t = 'one';  -- error
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.i = 1;  -- ok
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one';  -- error
+SELECT * FROM  J1_TBL a1 JOIN J2_TBL a2 USING (i) AS a1;  -- error
+
 
 --
 -- NATURAL JOIN

base-commit: 3e4818e9dd5be294d97ca67012528cb1c0b0ccaa
-- 
2.25.0

From 5b736c0033d4e41a4ca186101f892fbd00ff3528 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Sat, 14 Nov 2020 09:25:33 +0100
Subject: [PATCH v4] Allow an alias to be attached to a JOIN ... USING

This allows something like

    SELECT ... FROM t1 JOIN t2 USING (a, b, c) AS x

where x has the columns a, b, c and unlike a regular alias it does not
hide the range variables of the tables being joined t1 and t2.

Per SQL:2016 feature F404 "Range variable for common column names".

Discussion: https://www.postgresql.org/message-id/flat/454638cf-d563-ab76-a585-2564428062af@2ndquadrant.com
---
 doc/src/sgml/ref/select.sgml              | 11 ++++-
 src/backend/catalog/sql_features.txt      |  2 +-
 src/backend/nodes/copyfuncs.c             |  2 +
 src/backend/nodes/equalfuncs.c            |  2 +
 src/backend/nodes/outfuncs.c              |  2 +
 src/backend/nodes/readfuncs.c             |  2 +
 src/backend/parser/analyze.c              |  1 +
 src/backend/parser/gram.y                 | 48 +++++++++++++++++----
 src/backend/parser/parse_clause.c         | 21 ++++++++-
 src/backend/parser/parse_relation.c       | 45 +++++++++++++++++++-
 src/backend/utils/adt/ruleutils.c         |  4 ++
 src/include/nodes/parsenodes.h            |  7 +++
 src/include/nodes/primnodes.h             |  1 +
 src/include/parser/parse_node.h           |  1 +
 src/include/parser/parse_relation.h       |  1 +
 src/test/regress/expected/create_view.out | 52 ++++++++++++++++++++++-
 src/test/regress/expected/join.out        | 31 ++++++++++++++
 src/test/regress/sql/create_view.sql      | 11 +++++
 src/test/regress/sql/join.sql             |  8 ++++
 19 files changed, 239 insertions(+), 13 deletions(-)

diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 472b7cae81..c6e449d71e 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -59,7 +59,7 @@
     [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
     [ LATERAL ] ROWS FROM( <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] ) ] [, ...] )
                 [ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
-    <replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) ]
+    <replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) [ AS <replaceable class="parameter">join_using_alias</replaceable> ] ]
 
 <phrase>and <replaceable class="parameter">grouping_element</replaceable> can be one of:</phrase>
 
@@ -633,6 +633,15 @@ <title><literal>FROM</literal> Clause</title>
         equivalent columns will be included in the join output, not
         both.
        </para>
+
+       <para>
+        If a <replaceable class="parameter">join_using_alias</replaceable> is
+        specified, it gives a correlation name to the join columns.  Only the
+        join columns in the <literal>USING</literal> clause are addressable by
+        this name.  Unlike an <replaceable
+        class="parameter">alias</replaceable>, this does not hide the names of
+        the joined tables from the rest of the query.
+       </para>
       </listitem>
      </varlistentry>
 
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index b6e58e8493..69c24e7369 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -264,7 +264,7 @@ F401	Extended joined table	02	FULL OUTER JOIN	YES
 F401	Extended joined table	04	CROSS JOIN	YES	
 F402	Named column joins for LOBs, arrays, and multisets			YES	
 F403	Partitioned joined tables			NO	
-F404	Range variable for common column names			NO	
+F404	Range variable for common column names			YES	
 F411	Time zone specification			YES	differences regarding literal interpretation
 F421	National character			YES	
 F431	Read-only scrollable cursors			YES	
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 3031c52991..3582f48084 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2206,6 +2206,7 @@ _copyJoinExpr(const JoinExpr *from)
 	COPY_NODE_FIELD(rarg);
 	COPY_NODE_FIELD(usingClause);
 	COPY_NODE_FIELD(quals);
+	COPY_NODE_FIELD(join_using_alias);
 	COPY_NODE_FIELD(alias);
 	COPY_SCALAR_FIELD(rtindex);
 
@@ -2415,6 +2416,7 @@ _copyRangeTblEntry(const RangeTblEntry *from)
 	COPY_NODE_FIELD(joinaliasvars);
 	COPY_NODE_FIELD(joinleftcols);
 	COPY_NODE_FIELD(joinrightcols);
+	COPY_NODE_FIELD(join_using_alias);
 	COPY_NODE_FIELD(functions);
 	COPY_SCALAR_FIELD(funcordinality);
 	COPY_NODE_FIELD(tablefunc);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 9aa853748d..5cd5ec3c64 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -788,6 +788,7 @@ _equalJoinExpr(const JoinExpr *a, const JoinExpr *b)
 	COMPARE_NODE_FIELD(rarg);
 	COMPARE_NODE_FIELD(usingClause);
 	COMPARE_NODE_FIELD(quals);
+	COMPARE_NODE_FIELD(join_using_alias);
 	COMPARE_NODE_FIELD(alias);
 	COMPARE_SCALAR_FIELD(rtindex);
 
@@ -2672,6 +2673,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
 	COMPARE_NODE_FIELD(joinaliasvars);
 	COMPARE_NODE_FIELD(joinleftcols);
 	COMPARE_NODE_FIELD(joinrightcols);
+	COMPARE_NODE_FIELD(join_using_alias);
 	COMPARE_NODE_FIELD(functions);
 	COMPARE_SCALAR_FIELD(funcordinality);
 	COMPARE_NODE_FIELD(tablefunc);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 4504b1503b..a9a1f85267 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1679,6 +1679,7 @@ _outJoinExpr(StringInfo str, const JoinExpr *node)
 	WRITE_NODE_FIELD(rarg);
 	WRITE_NODE_FIELD(usingClause);
 	WRITE_NODE_FIELD(quals);
+	WRITE_NODE_FIELD(join_using_alias);
 	WRITE_NODE_FIELD(alias);
 	WRITE_INT_FIELD(rtindex);
 }
@@ -3106,6 +3107,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
 			WRITE_NODE_FIELD(joinaliasvars);
 			WRITE_NODE_FIELD(joinleftcols);
 			WRITE_NODE_FIELD(joinrightcols);
+			WRITE_NODE_FIELD(join_using_alias);
 			break;
 		case RTE_FUNCTION:
 			WRITE_NODE_FIELD(functions);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index ab7b535caa..79561c4e21 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1303,6 +1303,7 @@ _readJoinExpr(void)
 	READ_NODE_FIELD(rarg);
 	READ_NODE_FIELD(usingClause);
 	READ_NODE_FIELD(quals);
+	READ_NODE_FIELD(join_using_alias);
 	READ_NODE_FIELD(alias);
 	READ_INT_FIELD(rtindex);
 
@@ -1405,6 +1406,7 @@ _readRangeTblEntry(void)
 			READ_NODE_FIELD(joinaliasvars);
 			READ_NODE_FIELD(joinleftcols);
 			READ_NODE_FIELD(joinrightcols);
+			READ_NODE_FIELD(join_using_alias);
 			break;
 		case RTE_FUNCTION:
 			READ_NODE_FIELD(functions);
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 084e00f73d..711a1c8dba 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1733,6 +1733,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
 										NIL,
 										NIL,
 										NULL,
+										NULL,
 										false);
 
 	sv_namespace = pstate->p_namespace;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 051f1f1d49..0f947cdf87 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -498,7 +498,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <ival>	sub_type opt_materialized
 %type <value>	NumericOnly
 %type <list>	NumericOnly_list
-%type <alias>	alias_clause opt_alias_clause
+%type <alias>	alias_clause opt_alias_clause opt_alias_clause_for_join_using
 %type <list>	func_alias_clause
 %type <sortby>	sortby
 %type <ielem>	index_elem index_elem_options
@@ -11982,9 +11982,14 @@ joined_table:
 					n->larg = $1;
 					n->rarg = $4;
 					if ($5 != NULL && IsA($5, List))
-						n->usingClause = (List *) $5; /* USING clause */
+					{
+						 /* USING clause */
+						n->usingClause = linitial_node(List, castNode(List, $5));
+						n->join_using_alias = lsecond_node(Alias, castNode(List, $5));
+					}
 					else
-						n->quals = $5; /* ON clause */
+						/* ON clause */
+						n->quals = $5;
 					$$ = n;
 				}
 			| table_ref JOIN table_ref join_qual
@@ -11996,9 +12001,14 @@ joined_table:
 					n->larg = $1;
 					n->rarg = $3;
 					if ($4 != NULL && IsA($4, List))
-						n->usingClause = (List *) $4; /* USING clause */
+					{
+						/* USING clause */
+						n->usingClause = linitial_node(List, castNode(List, $4));
+						n->join_using_alias = lsecond_node(Alias, castNode(List, $4));
+					}
 					else
-						n->quals = $4; /* ON clause */
+						/* ON clause */
+						n->quals = $4;
 					$$ = n;
 				}
 			| table_ref NATURAL join_type JOIN table_ref
@@ -12055,6 +12065,21 @@ opt_alias_clause: alias_clause						{ $$ = $1; }
 			| /*EMPTY*/								{ $$ = NULL; }
 		;
 
+/*
+ * The alias clause after JOIN ... USING only accepts the AS ColId spelling,
+ * per SQL standard.  (The grammar could parse the other variants, but they
+ * don't seem to be useful, and it might lead to parser problems in the
+ * future.)
+ */
+opt_alias_clause_for_join_using:
+			AS ColId
+				{
+					$$ = makeNode(Alias);
+					$$->aliasname = $2;
+				}
+			| /*EMPTY*/								{ $$ = NULL; }
+		;
+
 /*
  * func_alias_clause can include both an Alias and a coldeflist, so we make it
  * return a 2-element list that gets disassembled by calling production.
@@ -12099,15 +12124,22 @@ opt_outer: OUTER_P
 
 /* JOIN qualification clauses
  * Possibilities are:
- *	USING ( column list ) allows only unqualified column names,
+ *	USING ( column list ) [ AS alias ]
+ *						  allows only unqualified column names,
  *						  which must match between tables.
  *	ON expr allows more general qualifications.
  *
  * We return USING as a List node, while an ON-expr will not be a List.
  */
 
-join_qual:	USING '(' name_list ')'					{ $$ = (Node *) $3; }
-			| ON a_expr								{ $$ = $2; }
+join_qual: USING '(' name_list ')' opt_alias_clause_for_join_using
+				{
+					$$ = (Node *) list_make2($3, $5);
+				}
+			| ON a_expr
+				{
+					$$ = $2;
+				}
 		;
 
 
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index ea4a1f5aeb..cc87f93894 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1267,6 +1267,13 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 			j->usingClause = rlist;
 		}
 
+		/*
+		 * If a USING clause alias was specified, save the USING columns as
+		 * its column list.
+		 */
+		if (j->join_using_alias)
+			j->join_using_alias->colnames = j->usingClause;
+
 		/*
 		 * Now transform the join qualifications, if any.
 		 */
@@ -1464,6 +1471,7 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 										   res_colvars,
 										   l_colnos,
 										   r_colnos,
+										   j->join_using_alias,
 										   j->alias,
 										   true);
 
@@ -1517,10 +1525,21 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 		 * The join RTE itself is always made visible for unqualified column
 		 * names.  It's visible as a relation name only if it has an alias.
 		 */
-		nsitem->p_rel_visible = (j->alias != NULL);
+		nsitem->p_rel_visible = (j->alias != NULL || j->join_using_alias != NULL);
 		nsitem->p_cols_visible = true;
 		nsitem->p_lateral_only = false;
 		nsitem->p_lateral_ok = true;
+		nsitem->p_join_using_alias = (j->join_using_alias != NULL);
+
+		/*
+		 * Check the JOIN/USING alias for namespace conflicts against the
+		 * subtrees (per SQL standard).
+		 */
+		if (j->join_using_alias)
+		{
+			checkNameSpaceConflicts(pstate, list_make1(nsitem), l_namespace);
+			checkNameSpaceConflicts(pstate, list_make1(nsitem), r_namespace);
+		}
 
 		*top_nsitem = nsitem;
 		*namespace = lappend(my_namespace, nsitem);
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index a56bd86181..614cef5bed 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -671,6 +671,37 @@ scanNSItemForColumn(ParseState *pstate, ParseNamespaceItem *nsitem,
 	int			attnum;
 	Var		   *var;
 
+	/*
+	 * If this is a JOIN/USING alias, then check that the column is part of
+	 * the USING column list.  If so, let scanRTEForColumn() below do the main
+	 * work.
+	 */
+	if (nsitem->p_join_using_alias)
+	{
+		ListCell   *c;
+		bool		found = false;
+
+		foreach(c, rte->join_using_alias->colnames)
+		{
+			const char *attcolname = strVal(lfirst(c));
+
+			if (strcmp(attcolname, colname) == 0)
+			{
+				found = true;
+				break;
+			}
+		}
+
+		if (!found)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+					 errmsg("column reference \"%s\" is invalid",
+							colname),
+					 errdetail("The range variable \"%s\" only contains columns in the USING clause.",
+							   rte->join_using_alias->aliasname),
+					 parser_errposition(pstate, location)));
+	}
+
 	/*
 	 * Scan the RTE's column names (or aliases) for a match.  Complain if
 	 * multiple matches.
@@ -1265,6 +1296,7 @@ buildNSItemFromTupleDesc(RangeTblEntry *rte, Index rtindex, TupleDesc tupdesc)
 	nsitem->p_cols_visible = true;
 	nsitem->p_lateral_only = false;
 	nsitem->p_lateral_ok = true;
+	nsitem->p_join_using_alias = false;
 
 	return nsitem;
 }
@@ -1326,6 +1358,7 @@ buildNSItemFromLists(RangeTblEntry *rte, Index rtindex,
 	nsitem->p_cols_visible = true;
 	nsitem->p_lateral_only = false;
 	nsitem->p_lateral_ok = true;
+	nsitem->p_join_using_alias = false;
 
 	return nsitem;
 }
@@ -2135,6 +2168,7 @@ addRangeTableEntryForJoin(ParseState *pstate,
 						  List *aliasvars,
 						  List *leftcols,
 						  List *rightcols,
+						  Alias *join_using_alias,
 						  Alias *alias,
 						  bool inFromCl)
 {
@@ -2163,9 +2197,16 @@ addRangeTableEntryForJoin(ParseState *pstate,
 	rte->joinaliasvars = aliasvars;
 	rte->joinleftcols = leftcols;
 	rte->joinrightcols = rightcols;
+	rte->join_using_alias = join_using_alias;
 	rte->alias = alias;
 
-	eref = alias ? copyObject(alias) : makeAlias("unnamed_join", NIL);
+	if (alias)
+		eref = copyObject(alias);
+	else if (join_using_alias)
+		eref = copyObject(join_using_alias);
+	else
+		eref = makeAlias("unnamed_join", NIL);
+
 	numaliases = list_length(eref->colnames);
 
 	/* fill in any unspecified alias columns */
@@ -2211,6 +2252,7 @@ addRangeTableEntryForJoin(ParseState *pstate,
 	nsitem->p_cols_visible = true;
 	nsitem->p_lateral_only = false;
 	nsitem->p_lateral_ok = true;
+	nsitem->p_join_using_alias = false;
 
 	return nsitem;
 }
@@ -2511,6 +2553,7 @@ addNSItemToQuery(ParseState *pstate, ParseNamespaceItem *nsitem,
 		nsitem->p_cols_visible = addToVarNameSpace;
 		nsitem->p_lateral_only = false;
 		nsitem->p_lateral_ok = true;
+		nsitem->p_join_using_alias = false;
 		pstate->p_namespace = lappend(pstate->p_namespace, nsitem);
 	}
 }
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index c2c6df2a4f..db5e9d294c 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10582,6 +10582,10 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
 				appendStringInfoString(buf, quote_identifier(colname));
 			}
 			appendStringInfoChar(buf, ')');
+
+			if (j->join_using_alias)
+				appendStringInfo(buf, " AS %s",
+								 quote_identifier(j->join_using_alias->aliasname));
 		}
 		else if (j->quals)
 		{
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 7ef9b0eac0..52494d37f9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1058,6 +1058,13 @@ typedef struct RangeTblEntry
 	List	   *joinleftcols;	/* left-side input column numbers */
 	List	   *joinrightcols;	/* right-side input column numbers */
 
+	/*
+	 * join_using_alias is an alias clause attached directly to JOIN/USING.
+	 * It is different from the alias field (below) in that it does not hide
+	 * the range variables of the tables being joined.
+	 */
+	Alias	   *join_using_alias;
+
 	/*
 	 * Fields valid for a function RTE (else NIL/zero):
 	 *
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 5b190bb99b..a87f239f3d 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1498,6 +1498,7 @@ typedef struct JoinExpr
 	Node	   *rarg;			/* right subtree */
 	List	   *usingClause;	/* USING clause, if any (list of String) */
 	Node	   *quals;			/* qualifiers on join, if any */
+	Alias	   *join_using_alias; /* alias attached to USING clause */
 	Alias	   *alias;			/* user-written alias clause, if any */
 	int			rtindex;		/* RT index assigned for join, or 0 */
 } JoinExpr;
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index d25819aa28..7c17f3c46e 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -262,6 +262,7 @@ struct ParseNamespaceItem
 	bool		p_cols_visible; /* Column names visible as unqualified refs? */
 	bool		p_lateral_only; /* Is only visible to LATERAL expressions? */
 	bool		p_lateral_ok;	/* If so, does join type allow use? */
+	bool		p_join_using_alias;	/* Is it a JOIN/USING alias? */
 };
 
 /*
diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h
index 93f94466a0..bde59315d8 100644
--- a/src/include/parser/parse_relation.h
+++ b/src/include/parser/parse_relation.h
@@ -89,6 +89,7 @@ extern ParseNamespaceItem *addRangeTableEntryForJoin(ParseState *pstate,
 													 List *aliasvars,
 													 List *leftcols,
 													 List *rightcols,
+													 Alias *joinalias,
 													 Alias *alias,
 													 bool inFromCl);
 extern ParseNamespaceItem *addRangeTableEntryForCTE(ParseState *pstate,
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index b234d2d4f9..33e6c04066 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -805,6 +805,51 @@ View definition:
     (tbl3
      CROSS JOIN tbl4) same;
 
+create table tbl1a (a int, c int);
+create view view_of_joins_2a as select * from tbl1 join tbl1a using (a);
+create view view_of_joins_2b as select * from tbl1 join tbl1a using (a) as x;
+create view view_of_joins_2c as select * from (tbl1 join tbl1a using (a)) as y;
+create view view_of_joins_2d as select * from (tbl1 join tbl1a using (a) as x) as y;
+select pg_get_viewdef('view_of_joins_2a', true);
+       pg_get_viewdef       
+----------------------------
+  SELECT tbl1.a,           +
+     tbl1.b,               +
+     tbl1a.c               +
+    FROM tbl1              +
+      JOIN tbl1a USING (a);
+(1 row)
+
+select pg_get_viewdef('view_of_joins_2b', true);
+         pg_get_viewdef          
+---------------------------------
+  SELECT tbl1.a,                +
+     tbl1.b,                    +
+     tbl1a.c                    +
+    FROM tbl1                   +
+      JOIN tbl1a USING (a) AS x;
+(1 row)
+
+select pg_get_viewdef('view_of_joins_2c', true);
+        pg_get_viewdef         
+-------------------------------
+  SELECT y.a,                 +
+     y.b,                     +
+     y.c                      +
+    FROM (tbl1                +
+      JOIN tbl1a USING (a)) y;
+(1 row)
+
+select pg_get_viewdef('view_of_joins_2d', true);
+           pg_get_viewdef           
+------------------------------------
+  SELECT y.a,                      +
+     y.b,                          +
+     y.c                           +
+    FROM (tbl1                     +
+      JOIN tbl1a USING (a) AS x) y;
+(1 row)
+
 -- Test view decompilation in the face of column addition/deletion/renaming
 create table tt2 (a int, b int, c int);
 create table tt3 (ax int8, b int2, c numeric);
@@ -1943,7 +1988,7 @@ drop cascades to view aliased_view_2
 drop cascades to view aliased_view_3
 drop cascades to view aliased_view_4
 DROP SCHEMA testviewschm2 CASCADE;
-NOTICE:  drop cascades to 68 other objects
+NOTICE:  drop cascades to 73 other objects
 DETAIL:  drop cascades to table t1
 drop cascades to view temporal1
 drop cascades to view temporal2
@@ -1968,6 +2013,11 @@ drop cascades to view unspecified_types
 drop cascades to table tt1
 drop cascades to table tx1
 drop cascades to view view_of_joins
+drop cascades to table tbl1a
+drop cascades to view view_of_joins_2a
+drop cascades to view view_of_joins_2b
+drop cascades to view view_of_joins_2c
+drop cascades to view view_of_joins_2d
 drop cascades to table tt2
 drop cascades to table tt3
 drop cascades to table tt4
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 6c9a5e26dd..4b14fa32c5 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -1621,6 +1621,37 @@ SELECT '' AS "xxx", *
      | 4 | 1 | one   | 2
 (4 rows)
 
+-- test join using aliases
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) WHERE J1_TBL.t = 'one';  -- ok
+ i | j |  t  | k  
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';  -- ok
+ i | j |  t  | k  
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t = 'one';  -- error
+ERROR:  invalid reference to FROM-clause entry for table "j1_tbl"
+LINE 1: ... * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t =...
+                                                             ^
+HINT:  There is an entry for table "j1_tbl", but it cannot be referenced from this part of the query.
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.i = 1;  -- ok
+ i | j |  t  | k  
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one';  -- error
+ERROR:  column reference "t" is invalid
+LINE 1: ...CT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one...
+                                                             ^
+DETAIL:  The range variable "x" only contains columns in the USING clause.
+SELECT * FROM  J1_TBL a1 JOIN J2_TBL a2 USING (i) AS a1;  -- error
+ERROR:  table name "a1" specified more than once
 --
 -- NATURAL JOIN
 -- Inner equi-join on all columns with the same name
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index 6d4dd53965..c9a9c31278 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -328,6 +328,17 @@ CREATE VIEW aliased_view_4 AS
 
 \d+ view_of_joins
 
+create table tbl1a (a int, c int);
+create view view_of_joins_2a as select * from tbl1 join tbl1a using (a);
+create view view_of_joins_2b as select * from tbl1 join tbl1a using (a) as x;
+create view view_of_joins_2c as select * from (tbl1 join tbl1a using (a)) as y;
+create view view_of_joins_2d as select * from (tbl1 join tbl1a using (a) as x) as y;
+
+select pg_get_viewdef('view_of_joins_2a', true);
+select pg_get_viewdef('view_of_joins_2b', true);
+select pg_get_viewdef('view_of_joins_2c', true);
+select pg_get_viewdef('view_of_joins_2d', true);
+
 -- Test view decompilation in the face of column addition/deletion/renaming
 
 create table tt2 (a int, b int, c int);
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index dd60d6a1f3..0c73ce8728 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -126,6 +126,14 @@ CREATE TABLE J2_TBL (
   FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b)
   ORDER BY b, t1.a;
 
+-- test join using aliases
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) WHERE J1_TBL.t = 'one';  -- ok
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';  -- ok
+SELECT * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t = 'one';  -- error
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.i = 1;  -- ok
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one';  -- error
+SELECT * FROM  J1_TBL a1 JOIN J2_TBL a2 USING (i) AS a1;  -- error
+
 
 --
 -- NATURAL JOIN

base-commit: 788dd0b839fc9f2c85caf86014105afdb60ed5e3
-- 
2.29.2

diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index bdf8ec46e2..5dfea46021 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1217,9 +1217,9 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 		 * input column numbers more easily.
 		 */
 		l_nscolumns = l_nsitem->p_nscolumns;
-		l_colnames = l_nsitem->p_rte->eref->colnames;
+		l_colnames = l_nsitem->p_names->colnames;
 		r_nscolumns = r_nsitem->p_nscolumns;
-		r_colnames = r_nsitem->p_rte->eref->colnames;
+		r_colnames = r_nsitem->p_names->colnames;
 
 		/*
 		 * Natural join does not explicitly specify columns; must generate
@@ -1469,7 +1469,7 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 		 * Now that we know the join RTE's rangetable index, we can fix up the
 		 * res_nscolumns data in places where it should contain that.
 		 */
-		Assert(res_colindex == list_length(nsitem->p_rte->eref->colnames));
+		Assert(res_colindex == list_length(nsitem->p_names->colnames));
 		for (k = 0; k < res_colindex; k++)
 		{
 			ParseNamespaceColumn *nscol = res_nscolumns + k;
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index f869e159d6..b38d919621 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -2518,6 +2518,7 @@ transformWholeRowRef(ParseState *pstate, ParseNamespaceItem *nsitem,
 	 * historically.  One argument for it is that "rel" and "rel.*" mean the
 	 * same thing for composite relations, so why not for scalar functions...
 	 */
+	/* XXX wrong for join alias case! */
 	result = makeWholeRowVar(nsitem->p_rte, nsitem->p_rtindex,
 							 sublevels_up, true);
 
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index ca02982e0b..17232f027e 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -65,6 +65,7 @@ static ParseNamespaceItem *scanNameSpaceForRelid(ParseState *pstate, Oid relid,
 static void check_lateral_ref_ok(ParseState *pstate, ParseNamespaceItem *nsitem,
 								 int location);
 static int	scanRTEForColumn(ParseState *pstate, RangeTblEntry *rte,
+							 Alias *eref,
 							 const char *colname, int location,
 							 int fuzzy_rte_penalty,
 							 FuzzyAttrMatchState *fuzzystate);
@@ -184,7 +185,6 @@ scanNameSpaceForRefname(ParseState *pstate, const char *refname, int location)
 	foreach(l, pstate->p_namespace)
 	{
 		ParseNamespaceItem *nsitem = (ParseNamespaceItem *) lfirst(l);
-		RangeTblEntry *rte = nsitem->p_rte;
 
 		/* Ignore columns-only items */
 		if (!nsitem->p_rel_visible)
@@ -193,7 +193,7 @@ scanNameSpaceForRefname(ParseState *pstate, const char *refname, int location)
 		if (nsitem->p_lateral_only && !pstate->p_lateral_active)
 			continue;
 
-		if (strcmp(rte->eref->aliasname, refname) == 0)
+		if (strcmp(nsitem->p_names->aliasname, refname) == 0)
 		{
 			if (result)
 				ereport(ERROR,
@@ -420,7 +420,7 @@ checkNameSpaceConflicts(ParseState *pstate, List *namespace1,
 	{
 		ParseNamespaceItem *nsitem1 = (ParseNamespaceItem *) lfirst(l1);
 		RangeTblEntry *rte1 = nsitem1->p_rte;
-		const char *aliasname1 = rte1->eref->aliasname;
+		const char *aliasname1 = nsitem1->p_names->aliasname;
 		ListCell   *l2;
 
 		if (!nsitem1->p_rel_visible)
@@ -430,10 +430,11 @@ checkNameSpaceConflicts(ParseState *pstate, List *namespace1,
 		{
 			ParseNamespaceItem *nsitem2 = (ParseNamespaceItem *) lfirst(l2);
 			RangeTblEntry *rte2 = nsitem2->p_rte;
+			const char *aliasname2 = nsitem2->p_names->aliasname;
 
 			if (!nsitem2->p_rel_visible)
 				continue;
-			if (strcmp(rte2->eref->aliasname, aliasname1) != 0)
+			if (strcmp(aliasname2, aliasname1) != 0)
 				continue;		/* definitely no conflict */
 			if (rte1->rtekind == RTE_RELATION && rte1->alias == NULL &&
 				rte2->rtekind == RTE_RELATION && rte2->alias == NULL &&
@@ -466,7 +467,7 @@ check_lateral_ref_ok(ParseState *pstate, ParseNamespaceItem *nsitem,
 	{
 		/* SQL:2008 demands this be an error, not an invisible item */
 		RangeTblEntry *rte = nsitem->p_rte;
-		char	   *refname = rte->eref->aliasname;
+		char	   *refname = nsitem->p_names->aliasname;
 
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
@@ -672,10 +673,10 @@ scanNSItemForColumn(ParseState *pstate, ParseNamespaceItem *nsitem,
 	Var		   *var;
 
 	/*
-	 * Scan the RTE's column names (or aliases) for a match.  Complain if
+	 * Scan the nsitem's column names (or aliases) for a match.  Complain if
 	 * multiple matches.
 	 */
-	attnum = scanRTEForColumn(pstate, rte,
+	attnum = scanRTEForColumn(pstate, rte, nsitem->p_names,
 							  colname, location,
 							  0, NULL);
 
@@ -712,7 +713,7 @@ scanNSItemForColumn(ParseState *pstate, ParseNamespaceItem *nsitem,
 					(errcode(ERRCODE_UNDEFINED_COLUMN),
 					 errmsg("column \"%s\" of relation \"%s\" does not exist",
 							colname,
-							rte->eref->aliasname)));
+							nsitem->p_names->aliasname)));
 
 		var = makeVar(nscol->p_varno,
 					  nscol->p_varattno,
@@ -752,6 +753,12 @@ scanNSItemForColumn(ParseState *pstate, ParseNamespaceItem *nsitem,
  *	  else return InvalidAttrNumber.
  *	  If the name proves ambiguous within this RTE, raise error.
  *
+ * Actually, we only search the names listed in "eref".  This can be either
+ * rte->eref, in which case we are indeed searching all the column names,
+ * or for a join it can be rte->join_using_alias, in which case we are only
+ * considering the common column names (which are the first N columns of the
+ * join, so everything works).
+ *
  * pstate and location are passed only for error-reporting purposes.
  *
  * Side effect: if fuzzystate is non-NULL, check non-system columns
@@ -765,6 +772,7 @@ scanNSItemForColumn(ParseState *pstate, ParseNamespaceItem *nsitem,
  */
 static int
 scanRTEForColumn(ParseState *pstate, RangeTblEntry *rte,
+				 Alias *eref,
 				 const char *colname, int location,
 				 int fuzzy_rte_penalty,
 				 FuzzyAttrMatchState *fuzzystate)
@@ -786,7 +794,7 @@ scanRTEForColumn(ParseState *pstate, RangeTblEntry *rte,
 	 * Callers interested in finding match with shortest distance need to
 	 * defend against this directly, though.
 	 */
-	foreach(c, rte->eref->colnames)
+	foreach(c, eref->colnames)
 	{
 		const char *attcolname = strVal(lfirst(c));
 
@@ -970,7 +978,7 @@ searchRangeTableForCol(ParseState *pstate, const char *alias, const char *colnam
 			 * Scan for a matching column; if we find an exact match, we're
 			 * done.  Otherwise, update fuzzystate.
 			 */
-			if (scanRTEForColumn(orig_pstate, rte, colname, location,
+			if (scanRTEForColumn(orig_pstate, rte, rte->eref, colname, location,
 								 fuzzy_rte_penalty, fuzzystate)
 				&& fuzzy_rte_penalty == 0)
 			{
@@ -1252,6 +1260,7 @@ buildNSItemFromTupleDesc(RangeTblEntry *rte, Index rtindex, TupleDesc tupdesc)
 
 	/* ... and build the nsitem */
 	nsitem = (ParseNamespaceItem *) palloc(sizeof(ParseNamespaceItem));
+	nsitem->p_names = rte->eref;
 	nsitem->p_rte = rte;
 	nsitem->p_rtindex = rtindex;
 	nsitem->p_nscolumns = nscolumns;
@@ -1313,6 +1322,7 @@ buildNSItemFromLists(RangeTblEntry *rte, Index rtindex,
 
 	/* ... and build the nsitem */
 	nsitem = (ParseNamespaceItem *) palloc(sizeof(ParseNamespaceItem));
+	nsitem->p_names = rte->eref;
 	nsitem->p_rte = rte;
 	nsitem->p_rtindex = rtindex;
 	nsitem->p_nscolumns = nscolumns;
@@ -2198,6 +2208,7 @@ addRangeTableEntryForJoin(ParseState *pstate,
 	 * list --- caller must do that if appropriate.
 	 */
 	nsitem = (ParseNamespaceItem *) palloc(sizeof(ParseNamespaceItem));
+	nsitem->p_names = rte->eref;
 	nsitem->p_rte = rte;
 	nsitem->p_rtindex = list_length(pstate->p_rtable);
 	nsitem->p_nscolumns = nscolumns;
@@ -2356,7 +2367,7 @@ addRangeTableEntryForCTE(ParseState *pstate,
 	 */
 	if (rte->ctelevelsup > 0)
 		for (int i = 0; i < n_dontexpand_columns; i++)
-			psi->p_nscolumns[list_length(psi->p_rte->eref->colnames) - 1 - i].p_dontexpand = true;
+			psi->p_nscolumns[list_length(psi->p_names->colnames) - 1 - i].p_dontexpand = true;
 
 	return psi;
 }
@@ -3037,7 +3048,7 @@ expandNSItemVars(ParseNamespaceItem *nsitem,
 	if (colnames)
 		*colnames = NIL;
 	colindex = 0;
-	foreach(lc, nsitem->p_rte->eref->colnames)
+	foreach(lc, nsitem->p_names->colnames)
 	{
 		Value	   *colnameval = (Value *) lfirst(lc);
 		const char *colname = strVal(colnameval);
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 176b9f37c1..03a7f5bb1a 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -226,8 +226,16 @@ struct ParseState
 /*
  * An element of a namespace list.
  *
+ * p_names contains the table name and column names exposed by this nsitem.
+ * (Typically it's equal to p_rte->eref, but for a JOIN USING alias it's
+ * equal to p_rte->join_using_alias.  Since the USING columns will be the
+ * join's first N columns, the net effect is just that we expose only those
+ * join columns via this nsitem.)
+ *
+ * p_rte and p_rtindex link to the underlying rangetable entry.
+ *
  * The p_nscolumns array contains info showing how to construct Vars
- * referencing corresponding elements of the RTE's colnames list.
+ * referencing the names appearing in the p_names->colnames list.
  *
  * Namespace items with p_rel_visible set define which RTEs are accessible by
  * qualified names, while those with p_cols_visible set define which RTEs are
@@ -255,9 +263,10 @@ struct ParseState
  */
 struct ParseNamespaceItem
 {
+	Alias	   *p_names;		/* Table and column names */
 	RangeTblEntry *p_rte;		/* The relation's rangetable entry */
 	int			p_rtindex;		/* The relation's index in the rangetable */
-	/* array of same length as p_rte->eref->colnames: */
+	/* array of same length as p_names->colnames: */
 	ParseNamespaceColumn *p_nscolumns;	/* per-column data */
 	bool		p_rel_visible;	/* Relation name is visible? */
 	bool		p_cols_visible; /* Column names visible as unqualified refs? */

diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 9c5cf50ef0..869941bc22 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -59,7 +59,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
     [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
     [ LATERAL ] ROWS FROM( <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] ) ] [, ...] )
                 [ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
-    <replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) ]
+    <replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) [ AS <replaceable class="parameter">join_using_alias</replaceable> ] ]
 
 <phrase>and <replaceable class="parameter">grouping_element</replaceable> can be one of:</phrase>
 
@@ -676,7 +676,7 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
      </varlistentry>
 
      <varlistentry>
-      <term><literal>USING ( <replaceable class="parameter">join_column</replaceable> [, ...] )</literal></term>
+      <term><literal>USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) [ AS <replaceable class="parameter">join_using_alias</replaceable> ]</literal></term>
       <listitem>
        <para>
         A clause of the form <literal>USING ( a, b, ... )</literal> is
@@ -686,6 +686,20 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
         equivalent columns will be included in the join output, not
         both.
        </para>
+
+       <para>
+        If a <replaceable class="parameter">join_using_alias</replaceable>
+        name is specified, it provides a table alias for the join columns.
+        Only the join columns listed in the <literal>USING</literal> clause
+        are addressable by this name.  Unlike a
+        regular <replaceable class="parameter">alias</replaceable>, this does
+        not hide the names of the joined tables from the rest of the query.
+        Also unlike a
+        regular <replaceable class="parameter">alias</replaceable>, you
+        cannot write a column alias list &mdash; the output names of the join
+        columns are the same as they appear in the <literal>USING</literal>
+        list.
+       </para>
       </listitem>
      </varlistentry>
 
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index b7165404cd..657e6c734b 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -264,7 +264,7 @@ F401	Extended joined table	02	FULL OUTER JOIN	YES
 F401	Extended joined table	04	CROSS JOIN	YES	
 F402	Named column joins for LOBs, arrays, and multisets			YES	
 F403	Partitioned joined tables			NO	
-F404	Range variable for common column names			NO	
+F404	Range variable for common column names			YES	
 F411	Time zone specification			YES	differences regarding literal interpretation
 F421	National character			YES	
 F431	Read-only scrollable cursors			YES	
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 2c20541e92..732c262265 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2230,6 +2230,7 @@ _copyJoinExpr(const JoinExpr *from)
 	COPY_NODE_FIELD(larg);
 	COPY_NODE_FIELD(rarg);
 	COPY_NODE_FIELD(usingClause);
+	COPY_NODE_FIELD(join_using_alias);
 	COPY_NODE_FIELD(quals);
 	COPY_NODE_FIELD(alias);
 	COPY_SCALAR_FIELD(rtindex);
@@ -2440,6 +2441,7 @@ _copyRangeTblEntry(const RangeTblEntry *from)
 	COPY_NODE_FIELD(joinaliasvars);
 	COPY_NODE_FIELD(joinleftcols);
 	COPY_NODE_FIELD(joinrightcols);
+	COPY_NODE_FIELD(join_using_alias);
 	COPY_NODE_FIELD(functions);
 	COPY_SCALAR_FIELD(funcordinality);
 	COPY_NODE_FIELD(tablefunc);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 3e980c457c..75b7592cb4 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -790,6 +790,7 @@ _equalJoinExpr(const JoinExpr *a, const JoinExpr *b)
 	COMPARE_NODE_FIELD(larg);
 	COMPARE_NODE_FIELD(rarg);
 	COMPARE_NODE_FIELD(usingClause);
+	COMPARE_NODE_FIELD(join_using_alias);
 	COMPARE_NODE_FIELD(quals);
 	COMPARE_NODE_FIELD(alias);
 	COMPARE_SCALAR_FIELD(rtindex);
@@ -2693,6 +2694,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
 	COMPARE_NODE_FIELD(joinaliasvars);
 	COMPARE_NODE_FIELD(joinleftcols);
 	COMPARE_NODE_FIELD(joinrightcols);
+	COMPARE_NODE_FIELD(join_using_alias);
 	COMPARE_NODE_FIELD(functions);
 	COMPARE_SCALAR_FIELD(funcordinality);
 	COMPARE_NODE_FIELD(tablefunc);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 305311d4a7..edc569297f 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1692,6 +1692,7 @@ _outJoinExpr(StringInfo str, const JoinExpr *node)
 	WRITE_NODE_FIELD(larg);
 	WRITE_NODE_FIELD(rarg);
 	WRITE_NODE_FIELD(usingClause);
+	WRITE_NODE_FIELD(join_using_alias);
 	WRITE_NODE_FIELD(quals);
 	WRITE_NODE_FIELD(alias);
 	WRITE_INT_FIELD(rtindex);
@@ -3182,6 +3183,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
 			WRITE_NODE_FIELD(joinaliasvars);
 			WRITE_NODE_FIELD(joinleftcols);
 			WRITE_NODE_FIELD(joinrightcols);
+			WRITE_NODE_FIELD(join_using_alias);
 			break;
 		case RTE_FUNCTION:
 			WRITE_NODE_FIELD(functions);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 9b8f81c523..5e1274b872 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1346,6 +1346,7 @@ _readJoinExpr(void)
 	READ_NODE_FIELD(larg);
 	READ_NODE_FIELD(rarg);
 	READ_NODE_FIELD(usingClause);
+	READ_NODE_FIELD(join_using_alias);
 	READ_NODE_FIELD(quals);
 	READ_NODE_FIELD(alias);
 	READ_INT_FIELD(rtindex);
@@ -1449,6 +1450,7 @@ _readRangeTblEntry(void)
 			READ_NODE_FIELD(joinaliasvars);
 			READ_NODE_FIELD(joinleftcols);
 			READ_NODE_FIELD(joinrightcols);
+			READ_NODE_FIELD(join_using_alias);
 			break;
 		case RTE_FUNCTION:
 			READ_NODE_FIELD(functions);
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 42f088ad71..018af8f1eb 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -457,6 +457,7 @@ add_rte_to_flat_rtable(PlannerGlobal *glob, RangeTblEntry *rte)
 	newrte->joinaliasvars = NIL;
 	newrte->joinleftcols = NIL;
 	newrte->joinrightcols = NIL;
+	newrte->join_using_alias = NULL;
 	newrte->functions = NIL;
 	newrte->tablefunc = NULL;
 	newrte->values_lists = NIL;
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index f3e46e0959..9bb84c4c30 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -1365,6 +1365,7 @@ convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink,
 	result->larg = NULL;		/* caller must fill this in */
 	result->rarg = (Node *) rtr;
 	result->usingClause = NIL;
+	result->join_using_alias = NULL;
 	result->quals = quals;
 	result->alias = NULL;
 	result->rtindex = 0;		/* we don't need an RTE for it */
@@ -1519,6 +1520,7 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
 	else
 		result->rarg = (Node *) subselect->jointree;
 	result->usingClause = NIL;
+	result->join_using_alias = NULL;
 	result->quals = whereClause;
 	result->alias = NULL;
 	result->rtindex = 0;		/* we don't need an RTE for it */
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 7149724953..5de1307570 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1744,6 +1744,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
 										NIL,
 										NIL,
 										NULL,
+										NULL,
 										false);
 
 	sv_namespace = pstate->p_namespace;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index bc43641ffe..815ba470e4 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -508,7 +508,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <ival>	sub_type opt_materialized
 %type <value>	NumericOnly
 %type <list>	NumericOnly_list
-%type <alias>	alias_clause opt_alias_clause
+%type <alias>	alias_clause opt_alias_clause opt_alias_clause_for_join_using
 %type <list>	func_alias_clause
 %type <sortby>	sortby
 %type <ielem>	index_elem index_elem_options
@@ -12097,6 +12097,7 @@ joined_table:
 					n->larg = $1;
 					n->rarg = $4;
 					n->usingClause = NIL;
+					n->join_using_alias = NULL;
 					n->quals = NULL;
 					$$ = n;
 				}
@@ -12108,9 +12109,16 @@ joined_table:
 					n->larg = $1;
 					n->rarg = $4;
 					if ($5 != NULL && IsA($5, List))
-						n->usingClause = (List *) $5; /* USING clause */
+					{
+						 /* USING clause */
+						n->usingClause = linitial_node(List, castNode(List, $5));
+						n->join_using_alias = lsecond_node(Alias, castNode(List, $5));
+					}
 					else
-						n->quals = $5; /* ON clause */
+					{
+						/* ON clause */
+						n->quals = $5;
+					}
 					$$ = n;
 				}
 			| table_ref JOIN table_ref join_qual
@@ -12122,9 +12130,16 @@ joined_table:
 					n->larg = $1;
 					n->rarg = $3;
 					if ($4 != NULL && IsA($4, List))
-						n->usingClause = (List *) $4; /* USING clause */
+					{
+						/* USING clause */
+						n->usingClause = linitial_node(List, castNode(List, $4));
+						n->join_using_alias = lsecond_node(Alias, castNode(List, $4));
+					}
 					else
-						n->quals = $4; /* ON clause */
+					{
+						/* ON clause */
+						n->quals = $4;
+					}
 					$$ = n;
 				}
 			| table_ref NATURAL join_type JOIN table_ref
@@ -12135,6 +12150,7 @@ joined_table:
 					n->larg = $1;
 					n->rarg = $5;
 					n->usingClause = NIL; /* figure out which columns later... */
+					n->join_using_alias = NULL;
 					n->quals = NULL; /* fill later */
 					$$ = n;
 				}
@@ -12147,6 +12163,7 @@ joined_table:
 					n->larg = $1;
 					n->rarg = $4;
 					n->usingClause = NIL; /* figure out which columns later... */
+					n->join_using_alias = NULL;
 					n->quals = NULL; /* fill later */
 					$$ = n;
 				}
@@ -12181,6 +12198,22 @@ opt_alias_clause: alias_clause						{ $$ = $1; }
 			| /*EMPTY*/								{ $$ = NULL; }
 		;
 
+/*
+ * The alias clause after JOIN ... USING only accepts the AS ColId spelling,
+ * per SQL standard.  (The grammar could parse the other variants, but they
+ * don't seem to be useful, and it might lead to parser problems in the
+ * future.)
+ */
+opt_alias_clause_for_join_using:
+			AS ColId
+				{
+					$$ = makeNode(Alias);
+					$$->aliasname = $2;
+					/* the column name list will be inserted later */
+				}
+			| /*EMPTY*/								{ $$ = NULL; }
+		;
+
 /*
  * func_alias_clause can include both an Alias and a coldeflist, so we make it
  * return a 2-element list that gets disassembled by calling production.
@@ -12225,15 +12258,24 @@ opt_outer: OUTER_P
 
 /* JOIN qualification clauses
  * Possibilities are:
- *	USING ( column list ) allows only unqualified column names,
+ *	USING ( column list ) [ AS alias ]
+ *						  allows only unqualified column names,
  *						  which must match between tables.
  *	ON expr allows more general qualifications.
  *
- * We return USING as a List node, while an ON-expr will not be a List.
+ * We return USING as a two-element List (the first item being a sub-List
+ * of the common column names, and the second either an Alias item or NULL).
+ * An ON-expr will not be a List, so it can be told apart that way.
  */
 
-join_qual:	USING '(' name_list ')'					{ $$ = (Node *) $3; }
-			| ON a_expr								{ $$ = $2; }
+join_qual: USING '(' name_list ')' opt_alias_clause_for_join_using
+				{
+					$$ = (Node *) list_make2($3, $5);
+				}
+			| ON a_expr
+				{
+					$$ = $2;
+				}
 		;
 
 
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 5dfea46021..af80aa4593 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1265,6 +1265,13 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 			j->usingClause = rlist;
 		}
 
+		/*
+		 * If a USING clause alias was specified, save the USING columns as
+		 * its column list.
+		 */
+		if (j->join_using_alias)
+			j->join_using_alias->colnames = j->usingClause;
+
 		/*
 		 * Now transform the join qualifications, if any.
 		 */
@@ -1460,6 +1467,7 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 										   res_colvars,
 										   l_colnos,
 										   r_colnos,
+										   j->join_using_alias,
 										   j->alias,
 										   true);
 
@@ -1493,6 +1501,30 @@ transformFromClauseItem(ParseState *pstate, Node *n,
 		pstate->p_joinexprs = lappend(pstate->p_joinexprs, j);
 		Assert(list_length(pstate->p_joinexprs) == j->rtindex);
 
+		/*
+		 * If the join has a USING alias, build a ParseNamespaceItem for that
+		 * and add it to the list of nsitems in the join's input.
+		 */
+		if (j->join_using_alias)
+		{
+			ParseNamespaceItem *jnsitem;
+
+			jnsitem = (ParseNamespaceItem *) palloc(sizeof(ParseNamespaceItem));
+			jnsitem->p_names = j->join_using_alias;
+			jnsitem->p_rte = nsitem->p_rte;
+			jnsitem->p_rtindex = nsitem->p_rtindex;
+			/* no need to copy the first N columns, just use res_nscolumns */
+			jnsitem->p_nscolumns = res_nscolumns;
+			/* set default visibility flags; might get changed later */
+			jnsitem->p_rel_visible = true;
+			jnsitem->p_cols_visible = true;
+			jnsitem->p_lateral_only = false;
+			jnsitem->p_lateral_ok = true;
+			/* Per SQL, we must check for alias conflicts */
+			checkNameSpaceConflicts(pstate, list_make1(jnsitem), my_namespace);
+			my_namespace = lappend(my_namespace, jnsitem);
+		}
+
 		/*
 		 * Prepare returned namespace list.  If the JOIN has an alias then it
 		 * hides the contained RTEs completely; otherwise, the contained RTEs
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index b38d919621..982244ee30 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -2508,27 +2508,61 @@ static Node *
 transformWholeRowRef(ParseState *pstate, ParseNamespaceItem *nsitem,
 					 int sublevels_up, int location)
 {
-	Var		   *result;
-
 	/*
-	 * Build the appropriate referencing node.  Note that if the RTE is a
-	 * function returning scalar, we create just a plain reference to the
-	 * function value, not a composite containing a single column.  This is
-	 * pretty inconsistent at first sight, but it's what we've done
-	 * historically.  One argument for it is that "rel" and "rel.*" mean the
-	 * same thing for composite relations, so why not for scalar functions...
+	 * Build the appropriate referencing node.  Normally this can be a
+	 * whole-row Var, but if the nsitem is a JOIN USING alias then it contains
+	 * only a subset of the columns of the underlying join RTE, so that will
+	 * not work.  Instead we immediately expand the reference into a RowExpr.
+	 * Since the JOIN USING's common columns are fully determined at this
+	 * point, there seems no harm in expanding it now rather than during
+	 * planning.
+	 *
+	 * Note that if the RTE is a function returning scalar, we create just a
+	 * plain reference to the function value, not a composite containing a
+	 * single column.  This is pretty inconsistent at first sight, but it's
+	 * what we've done historically.  One argument for it is that "rel" and
+	 * "rel.*" mean the same thing for composite relations, so why not for
+	 * scalar functions...
 	 */
-	/* XXX wrong for join alias case! */
-	result = makeWholeRowVar(nsitem->p_rte, nsitem->p_rtindex,
-							 sublevels_up, true);
+	if (nsitem->p_names == nsitem->p_rte->eref)
+	{
+		Var		   *result;
 
-	/* location is not filled in by makeWholeRowVar */
-	result->location = location;
+		result = makeWholeRowVar(nsitem->p_rte, nsitem->p_rtindex,
+								 sublevels_up, true);
 
-	/* mark relation as requiring whole-row SELECT access */
-	markVarForSelectPriv(pstate, result);
+		/* location is not filled in by makeWholeRowVar */
+		result->location = location;
 
-	return (Node *) result;
+		/* mark relation as requiring whole-row SELECT access */
+		markVarForSelectPriv(pstate, result);
+
+		return (Node *) result;
+	}
+	else
+	{
+		RowExpr    *rowexpr;
+		List	   *fields;
+
+		/*
+		 * We want only as many columns as are listed in p_names->colnames,
+		 * and we should use those names not whatever possibly-aliased names
+		 * are in the RTE.  We needn't worry about marking the RTE for SELECT
+		 * access, as the common columns are surely so marked already.
+		 */
+		expandRTE(nsitem->p_rte, nsitem->p_rtindex,
+				  sublevels_up, location, false,
+				  NULL, &fields);
+		rowexpr = makeNode(RowExpr);
+		rowexpr->args = list_truncate(fields,
+									  list_length(nsitem->p_names->colnames));
+		rowexpr->row_typeid = RECORDOID;
+		rowexpr->row_format = COERCE_IMPLICIT_CAST;
+		rowexpr->colnames = copyObject(nsitem->p_names->colnames);
+		rowexpr->location = location;
+
+		return (Node *) rowexpr;
+	}
 }
 
 /*
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 17232f027e..d451f055f7 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -2140,6 +2140,7 @@ addRangeTableEntryForJoin(ParseState *pstate,
 						  List *aliasvars,
 						  List *leftcols,
 						  List *rightcols,
+						  Alias *join_using_alias,
 						  Alias *alias,
 						  bool inFromCl)
 {
@@ -2168,6 +2169,7 @@ addRangeTableEntryForJoin(ParseState *pstate,
 	rte->joinaliasvars = aliasvars;
 	rte->joinleftcols = leftcols;
 	rte->joinrightcols = rightcols;
+	rte->join_using_alias = join_using_alias;
 	rte->alias = alias;
 
 	eref = alias ? copyObject(alias) : makeAlias("unnamed_join", NIL);
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index f0de2a25c9..25db612c75 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10644,6 +10644,10 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
 				appendStringInfoString(buf, quote_identifier(colname));
 			}
 			appendStringInfoChar(buf, ')');
+
+			if (j->join_using_alias)
+				appendStringInfo(buf, " AS %s",
+								 quote_identifier(j->join_using_alias->aliasname));
 		}
 		else if (j->quals)
 		{
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 68425eb2c0..2364acd270 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1068,6 +1068,13 @@ typedef struct RangeTblEntry
 	List	   *joinleftcols;	/* left-side input column numbers */
 	List	   *joinrightcols;	/* right-side input column numbers */
 
+	/*
+	 * join_using_alias is an alias clause attached directly to JOIN/USING. It
+	 * is different from the alias field (below) in that it does not hide the
+	 * range variables of the tables being joined.
+	 */
+	Alias	   *join_using_alias;
+
 	/*
 	 * Fields valid for a function RTE (else NIL/zero):
 	 *
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index d4ce037088..f66e1449d8 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1499,6 +1499,11 @@ typedef struct RangeTblRef
  * alias has a critical impact on semantics, because a join with an alias
  * restricts visibility of the tables/columns inside it.
  *
+ * join_using_alias is an Alias node representing the join correlation
+ * name that SQL:2016 and later allow to be attached to JOIN/USING.
+ * Its column alias list includes only the common column names from USING,
+ * and it does not restrict visibility of the join's input tables.
+ *
  * During parse analysis, an RTE is created for the Join, and its index
  * is filled into rtindex.  This RTE is present mainly so that Vars can
  * be created that refer to the outputs of the join.  The planner sometimes
@@ -1514,6 +1519,7 @@ typedef struct JoinExpr
 	Node	   *larg;			/* left subtree */
 	Node	   *rarg;			/* right subtree */
 	List	   *usingClause;	/* USING clause, if any (list of String) */
+	Alias	   *join_using_alias;	/* alias attached to USING clause, if any */
 	Node	   *quals;			/* qualifiers on join, if any */
 	Alias	   *alias;			/* user-written alias clause, if any */
 	int			rtindex;		/* RT index assigned for join, or 0 */
diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h
index 5dbe5ba2e2..8336c2c5a2 100644
--- a/src/include/parser/parse_relation.h
+++ b/src/include/parser/parse_relation.h
@@ -88,6 +88,7 @@ extern ParseNamespaceItem *addRangeTableEntryForJoin(ParseState *pstate,
 													 List *aliasvars,
 													 List *leftcols,
 													 List *rightcols,
+													 Alias *joinalias,
 													 Alias *alias,
 													 bool inFromCl);
 extern ParseNamespaceItem *addRangeTableEntryForCTE(ParseState *pstate,
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index bd5fe60450..87fd2fbfd0 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -805,6 +805,51 @@ View definition:
     (tbl3
      CROSS JOIN tbl4) same;
 
+create table tbl1a (a int, c int);
+create view view_of_joins_2a as select * from tbl1 join tbl1a using (a);
+create view view_of_joins_2b as select * from tbl1 join tbl1a using (a) as x;
+create view view_of_joins_2c as select * from (tbl1 join tbl1a using (a)) as y;
+create view view_of_joins_2d as select * from (tbl1 join tbl1a using (a) as x) as y;
+select pg_get_viewdef('view_of_joins_2a', true);
+       pg_get_viewdef       
+----------------------------
+  SELECT tbl1.a,           +
+     tbl1.b,               +
+     tbl1a.c               +
+    FROM tbl1              +
+      JOIN tbl1a USING (a);
+(1 row)
+
+select pg_get_viewdef('view_of_joins_2b', true);
+         pg_get_viewdef          
+---------------------------------
+  SELECT tbl1.a,                +
+     tbl1.b,                    +
+     tbl1a.c                    +
+    FROM tbl1                   +
+      JOIN tbl1a USING (a) AS x;
+(1 row)
+
+select pg_get_viewdef('view_of_joins_2c', true);
+        pg_get_viewdef         
+-------------------------------
+  SELECT y.a,                 +
+     y.b,                     +
+     y.c                      +
+    FROM (tbl1                +
+      JOIN tbl1a USING (a)) y;
+(1 row)
+
+select pg_get_viewdef('view_of_joins_2d', true);
+           pg_get_viewdef           
+------------------------------------
+  SELECT y.a,                      +
+     y.b,                          +
+     y.c                           +
+    FROM (tbl1                     +
+      JOIN tbl1a USING (a) AS x) y;
+(1 row)
+
 -- Test view decompilation in the face of column addition/deletion/renaming
 create table tt2 (a int, b int, c int);
 create table tt3 (ax int8, b int2, c numeric);
@@ -1949,7 +1994,7 @@ drop cascades to view aliased_view_2
 drop cascades to view aliased_view_3
 drop cascades to view aliased_view_4
 DROP SCHEMA testviewschm2 CASCADE;
-NOTICE:  drop cascades to 68 other objects
+NOTICE:  drop cascades to 73 other objects
 DETAIL:  drop cascades to table t1
 drop cascades to view temporal1
 drop cascades to view temporal2
@@ -1974,6 +2019,11 @@ drop cascades to view unspecified_types
 drop cascades to table tt1
 drop cascades to table tx1
 drop cascades to view view_of_joins
+drop cascades to table tbl1a
+drop cascades to view view_of_joins_2a
+drop cascades to view view_of_joins_2b
+drop cascades to view view_of_joins_2c
+drop cascades to view view_of_joins_2d
 drop cascades to table tt2
 drop cascades to table tt3
 drop cascades to table tt4
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 5c7528c029..8480e69204 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -1621,6 +1621,54 @@ SELECT *
  4 | 1 | one   | 2
 (4 rows)
 
+-- test join using aliases
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) WHERE J1_TBL.t = 'one';  -- ok
+ i | j |  t  | k  
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';  -- ok
+ i | j |  t  | k  
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t = 'one';  -- error
+ERROR:  invalid reference to FROM-clause entry for table "j1_tbl"
+LINE 1: ... * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t =...
+                                                             ^
+HINT:  There is an entry for table "j1_tbl", but it cannot be referenced from this part of the query.
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.i = 1;  -- ok
+ i | j |  t  | k  
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one';  -- error
+ERROR:  column x.t does not exist
+LINE 1: ...CT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one...
+                                                             ^
+SELECT * FROM J1_TBL a1 JOIN J2_TBL a2 USING (i) AS a1;  -- error
+ERROR:  table name "a1" specified more than once
+SELECT x.* FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';
+ i 
+---
+ 1
+(1 row)
+
+SELECT ROW(x.*) FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';
+ row 
+-----
+ (1)
+(1 row)
+
+SELECT row_to_json(x.*) FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';
+ row_to_json 
+-------------
+ {"i":1}
+(1 row)
+
 --
 -- NATURAL JOIN
 -- Inner equi-join on all columns with the same name
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index fbd1313b9c..bdda56e8de 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -328,6 +328,17 @@ select * from
 
 \d+ view_of_joins
 
+create table tbl1a (a int, c int);
+create view view_of_joins_2a as select * from tbl1 join tbl1a using (a);
+create view view_of_joins_2b as select * from tbl1 join tbl1a using (a) as x;
+create view view_of_joins_2c as select * from (tbl1 join tbl1a using (a)) as y;
+create view view_of_joins_2d as select * from (tbl1 join tbl1a using (a) as x) as y;
+
+select pg_get_viewdef('view_of_joins_2a', true);
+select pg_get_viewdef('view_of_joins_2b', true);
+select pg_get_viewdef('view_of_joins_2c', true);
+select pg_get_viewdef('view_of_joins_2d', true);
+
 -- Test view decompilation in the face of column addition/deletion/renaming
 
 create table tt2 (a int, b int, c int);
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 6a209a27aa..5e7cc814f1 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -126,6 +126,16 @@ SELECT *
   FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b)
   ORDER BY b, t1.a;
 
+-- test join using aliases
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) WHERE J1_TBL.t = 'one';  -- ok
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';  -- ok
+SELECT * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t = 'one';  -- error
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.i = 1;  -- ok
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one';  -- error
+SELECT * FROM J1_TBL a1 JOIN J2_TBL a2 USING (i) AS a1;  -- error
+SELECT x.* FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';
+SELECT ROW(x.*) FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';
+SELECT row_to_json(x.*) FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';
 
 --
 -- NATURAL JOIN