0001-Allow-an-alias-to-be-attached-directly-to-a-JOIN-.-U.patch

From e1c9e7b7a12f0f7aba8f5c88a7909a61171dee27 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Mon, 17 Jun 2019 15:35:32 +0200
Subject: [PATCH] Allow an alias to be attached directly to a JOIN ... USING

This allows something like

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

instead of requiring parentheses for the alias like

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

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

The parse analysis guts already support this, so this patch only
has to adjust the grammar a bit.
---
 doc/src/sgml/ref/select.sgml         |  2 +-
 src/backend/catalog/sql_features.txt |  2 +-
 src/backend/parser/gram.y            | 69 ++++++++++++++++++----------
 src/test/regress/expected/join.out   | 18 ++++++++
 src/test/regress/sql/join.sql        |  5 ++
 5 files changed, 69 insertions(+), 27 deletions(-)

diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 06d611b64c..628b67a11a 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">alias</replaceable> ] ]
 
 <phrase>and <replaceable class="parameter">grouping_element</replaceable> can be one of:</phrase>
 
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index ae874f38ee..15188ee970 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/parser/gram.y b/src/backend/parser/gram.y
index 8311b1dd46..844a6eab82 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -435,7 +435,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
@@ -488,7 +488,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
@@ -11967,20 +11967,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->alias = $9;
 					$$ = 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);
@@ -11988,10 +11996,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->alias = $8;
 					$$ = n;
 				}
 			| table_ref NATURAL join_type JOIN table_ref
@@ -12048,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.
@@ -12090,19 +12122,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/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 07e631d45e..425028e811 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -1621,6 +1621,24 @@ SELECT '' AS "xxx", *
      | 4 | 1 | one   | 2
 (4 rows)
 
+-- test 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';  -- error
+ERROR:  invalid reference to FROM-clause entry for table "j1_tbl"
+LINE 1: ...CT * 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.t = 'one';  -- ok
+ i | j |  t  | k  
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
 --
 -- NATURAL JOIN
 -- Inner equi-join on all columns with the same name
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index bf6d5c3ae4..7ff7f30d7f 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -126,6 +126,11 @@ 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 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';  -- error
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one';  -- ok
+
 
 --
 -- NATURAL JOIN

base-commit: 91acff7a538e6e6a8175450a38c7fa1d9a290011
-- 
2.22.0