A small new feature in SQL:2016 allows attaching a table alias to a
JOIN/USING construct:
<named columns join> ::=
USING <left paren> <join column list> <right paren>
[ AS <join correlation name> ]
(The part in brackets is new.)
This seems quite useful, and it seems the code would already support
this if we allow the grammar to accept this syntax.
Patch attached.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From e1c9e7b7a12f0f7aba8f5c88a7909a61171dee27 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@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
