On 2019-12-24 19:13, Fabien COELHO wrote:
Indeed, that seems like a problem, and it's a good question. You can
see this on unpatched master with SELECT x.filler FROM
(pgbench_tellers AS t JOIN b USING (bid)) AS x.
I'm not sure I understand why that problem is a blocker for this patch.
As discussed on another thread,
https://www.postgresql.org/message-id/flat/2aa57950-b1d7-e9b6-0770-fa592d565...@2ndquadrant.com
the patch does not conform to spec
SQL:2016 Part 2 Foundation Section 7.10 <joined table>
Basically "x" is expected to include *ONLY* joined attributes with USING,
i.e. above only x.bid should exists, and per-table aliases are expected to
still work for other attributes.
I took another crack at this. Attached is a new patch that addresses
the semantic comments from this and the other thread. It's all a bit
tricky, comments welcome.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From e3118a93b6df9ee8144a9c0e8454860f66b8e3f5 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@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
