Skip to content

Commit 055fee7

Browse files
peterepetere
committed
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". Reviewed-by: Vik Fearing <vik.fearing@2ndquadrant.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://www.postgresql.org/message-id/flat/454638cf-d563-ab76-a585-2564428062af@2ndquadrant.com
1 parent 27e1f14 commit 055fee7

22 files changed

+315
-29
lines changed

‎doc/src/sgml/ref/select.sgml

+14-2
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
5959
[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
6060
[ LATERAL ] ROWS FROM( <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] ) ] [, ...] )
6161
[ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
62-
<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> [, ...] ) ]
62+
<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> ] ]
6363

6464
<phrase>and <replaceable class="parameter">grouping_element</replaceable> can be one of:</phrase>
6565

@@ -676,7 +676,7 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
676676
</varlistentry>
677677

678678
<varlistentry>
679-
<term><literal>USING ( <replaceable class="parameter">join_column</replaceable> [, ...] )</literal></term>
679+
<term><literal>USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) [ AS <replaceable class="parameter">join_using_alias</replaceable> ]</literal></term>
680680
<listitem>
681681
<para>
682682
A clause of the form <literal>USING ( a, b, ... )</literal> is
@@ -686,6 +686,18 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
686686
equivalent columns will be included in the join output, not
687687
both.
688688
</para>
689+
690+
<para>
691+
If a <replaceable class="parameter">join_using_alias</replaceable>
692+
name is specified, it provides a table alias for the join columns.
693+
Only the join columns listed in the <literal>USING</literal> clause
694+
are addressable by this name. Unlike a regular <replaceable
695+
class="parameter">alias</replaceable>, this does not hide the names of
696+
the joined tables from the rest of the query. Also unlike a regular
697+
<replaceable class="parameter">alias</replaceable>, you cannot write a
698+
column alias list &mdash; the output names of the join columns are the
699+
same as they appear in the <literal>USING</literal> list.
700+
</para>
689701
</listitem>
690702
</varlistentry>
691703

‎src/backend/catalog/sql_features.txt

+1-1
Original file line numberDiff line numberDiff line change
@@ -264,7 +264,7 @@ F401 Extended joined table 02 FULL OUTER JOIN YES
264264
F401 Extended joined table 04 CROSS JOIN YES
265265
F402 Named column joins for LOBs, arrays, and multisets YES
266266
F403 Partitioned joined tables NO
267-
F404 Range variable for common column names NO
267+
F404 Range variable for common column names YES
268268
F411 Time zone specification YES differences regarding literal interpretation
269269
F421 National character YES
270270
F431 Read-only scrollable cursors YES

‎src/backend/nodes/copyfuncs.c

+2
Original file line numberDiff line numberDiff line change
@@ -2231,6 +2231,7 @@ _copyJoinExpr(const JoinExpr *from)
22312231
COPY_NODE_FIELD(larg);
22322232
COPY_NODE_FIELD(rarg);
22332233
COPY_NODE_FIELD(usingClause);
2234+
COPY_NODE_FIELD(join_using_alias);
22342235
COPY_NODE_FIELD(quals);
22352236
COPY_NODE_FIELD(alias);
22362237
COPY_SCALAR_FIELD(rtindex);
@@ -2442,6 +2443,7 @@ _copyRangeTblEntry(const RangeTblEntry *from)
24422443
COPY_NODE_FIELD(joinaliasvars);
24432444
COPY_NODE_FIELD(joinleftcols);
24442445
COPY_NODE_FIELD(joinrightcols);
2446+
COPY_NODE_FIELD(join_using_alias);
24452447
COPY_NODE_FIELD(functions);
24462448
COPY_SCALAR_FIELD(funcordinality);
24472449
COPY_NODE_FIELD(tablefunc);

‎src/backend/nodes/equalfuncs.c

+2
Original file line numberDiff line numberDiff line change
@@ -790,6 +790,7 @@ _equalJoinExpr(const JoinExpr *a, const JoinExpr *b)
790790
COMPARE_NODE_FIELD(larg);
791791
COMPARE_NODE_FIELD(rarg);
792792
COMPARE_NODE_FIELD(usingClause);
793+
COMPARE_NODE_FIELD(join_using_alias);
793794
COMPARE_NODE_FIELD(quals);
794795
COMPARE_NODE_FIELD(alias);
795796
COMPARE_SCALAR_FIELD(rtindex);
@@ -2703,6 +2704,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
27032704
COMPARE_NODE_FIELD(joinaliasvars);
27042705
COMPARE_NODE_FIELD(joinleftcols);
27052706
COMPARE_NODE_FIELD(joinrightcols);
2707+
COMPARE_NODE_FIELD(join_using_alias);
27062708
COMPARE_NODE_FIELD(functions);
27072709
COMPARE_SCALAR_FIELD(funcordinality);
27082710
COMPARE_NODE_FIELD(tablefunc);

‎src/backend/nodes/outfuncs.c

+2
Original file line numberDiff line numberDiff line change
@@ -1693,6 +1693,7 @@ _outJoinExpr(StringInfo str, const JoinExpr *node)
16931693
WRITE_NODE_FIELD(larg);
16941694
WRITE_NODE_FIELD(rarg);
16951695
WRITE_NODE_FIELD(usingClause);
1696+
WRITE_NODE_FIELD(join_using_alias);
16961697
WRITE_NODE_FIELD(quals);
16971698
WRITE_NODE_FIELD(alias);
16981699
WRITE_INT_FIELD(rtindex);
@@ -3193,6 +3194,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
31933194
WRITE_NODE_FIELD(joinaliasvars);
31943195
WRITE_NODE_FIELD(joinleftcols);
31953196
WRITE_NODE_FIELD(joinrightcols);
3197+
WRITE_NODE_FIELD(join_using_alias);
31963198
break;
31973199
case RTE_FUNCTION:
31983200
WRITE_NODE_FIELD(functions);

‎src/backend/nodes/readfuncs.c

+2
Original file line numberDiff line numberDiff line change
@@ -1346,6 +1346,7 @@ _readJoinExpr(void)
13461346
READ_NODE_FIELD(larg);
13471347
READ_NODE_FIELD(rarg);
13481348
READ_NODE_FIELD(usingClause);
1349+
READ_NODE_FIELD(join_using_alias);
13491350
READ_NODE_FIELD(quals);
13501351
READ_NODE_FIELD(alias);
13511352
READ_INT_FIELD(rtindex);
@@ -1449,6 +1450,7 @@ _readRangeTblEntry(void)
14491450
READ_NODE_FIELD(joinaliasvars);
14501451
READ_NODE_FIELD(joinleftcols);
14511452
READ_NODE_FIELD(joinrightcols);
1453+
READ_NODE_FIELD(join_using_alias);
14521454
break;
14531455
case RTE_FUNCTION:
14541456
READ_NODE_FIELD(functions);

‎src/backend/optimizer/plan/setrefs.c

+1
Original file line numberDiff line numberDiff line change
@@ -457,6 +457,7 @@ add_rte_to_flat_rtable(PlannerGlobal *glob, RangeTblEntry *rte)
457457
newrte->joinaliasvars = NIL;
458458
newrte->joinleftcols = NIL;
459459
newrte->joinrightcols = NIL;
460+
newrte->join_using_alias = NULL;
460461
newrte->functions = NIL;
461462
newrte->tablefunc = NULL;
462463
newrte->values_lists = NIL;

‎src/backend/optimizer/plan/subselect.c

+2
Original file line numberDiff line numberDiff line change
@@ -1365,6 +1365,7 @@ convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink,
13651365
result->larg = NULL; /* caller must fill this in */
13661366
result->rarg = (Node *) rtr;
13671367
result->usingClause = NIL;
1368+
result->join_using_alias = NULL;
13681369
result->quals = quals;
13691370
result->alias = NULL;
13701371
result->rtindex = 0; /* we don't need an RTE for it */
@@ -1519,6 +1520,7 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
15191520
else
15201521
result->rarg = (Node *) subselect->jointree;
15211522
result->usingClause = NIL;
1523+
result->join_using_alias = NULL;
15221524
result->quals = whereClause;
15231525
result->alias = NULL;
15241526
result->rtindex = 0; /* we don't need an RTE for it */

‎src/backend/parser/analyze.c

+1
Original file line numberDiff line numberDiff line change
@@ -1744,6 +1744,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
17441744
NIL,
17451745
NIL,
17461746
NULL,
1747+
NULL,
17471748
false);
17481749

17491750
sv_namespace = pstate->p_namespace;

‎src/backend/parser/gram.y

+51-9
Original file line numberDiff line numberDiff line change
@@ -509,7 +509,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
509509
%type <ival> sub_type opt_materialized
510510
%type <value> NumericOnly
511511
%type <list> NumericOnly_list
512-
%type <alias> alias_clause opt_alias_clause
512+
%type <alias> alias_clause opt_alias_clause opt_alias_clause_for_join_using
513513
%type <list> func_alias_clause
514514
%type <sortby> sortby
515515
%type <ielem> index_elem index_elem_options
@@ -12144,6 +12144,7 @@ joined_table:
1214412144
n->larg = $1;
1214512145
n->rarg = $4;
1214612146
n->usingClause = NIL;
12147+
n->join_using_alias = NULL;
1214712148
n->quals = NULL;
1214812149
$$ = n;
1214912150
}
@@ -12155,9 +12156,16 @@ joined_table:
1215512156
n->larg = $1;
1215612157
n->rarg = $4;
1215712158
if ($5 != NULL && IsA($5, List))
12158-
n->usingClause = (List *) $5; /* USING clause */
12159+
{
12160+
/* USING clause */
12161+
n->usingClause = linitial_node(List, castNode(List, $5));
12162+
n->join_using_alias = lsecond_node(Alias, castNode(List, $5));
12163+
}
1215912164
else
12160-
n->quals = $5; /* ON clause */
12165+
{
12166+
/* ON clause */
12167+
n->quals = $5;
12168+
}
1216112169
$$ = n;
1216212170
}
1216312171
| table_ref JOIN table_ref join_qual
@@ -12169,9 +12177,16 @@ joined_table:
1216912177
n->larg = $1;
1217012178
n->rarg = $3;
1217112179
if ($4 != NULL && IsA($4, List))
12172-
n->usingClause = (List *) $4; /* USING clause */
12180+
{
12181+
/* USING clause */
12182+
n->usingClause = linitial_node(List, castNode(List, $4));
12183+
n->join_using_alias = lsecond_node(Alias, castNode(List, $4));
12184+
}
1217312185
else
12174-
n->quals = $4; /* ON clause */
12186+
{
12187+
/* ON clause */
12188+
n->quals = $4;
12189+
}
1217512190
$$ = n;
1217612191
}
1217712192
| table_ref NATURAL join_type JOIN table_ref
@@ -12182,6 +12197,7 @@ joined_table:
1218212197
n->larg = $1;
1218312198
n->rarg = $5;
1218412199
n->usingClause = NIL; /* figure out which columns later... */
12200+
n->join_using_alias = NULL;
1218512201
n->quals = NULL; /* fill later */
1218612202
$$ = n;
1218712203
}
@@ -12194,6 +12210,7 @@ joined_table:
1219412210
n->larg = $1;
1219512211
n->rarg = $4;
1219612212
n->usingClause = NIL; /* figure out which columns later... */
12213+
n->join_using_alias = NULL;
1219712214
n->quals = NULL; /* fill later */
1219812215
$$ = n;
1219912216
}
@@ -12228,6 +12245,22 @@ opt_alias_clause: alias_clause { $$ = $1; }
1222812245
| /*EMPTY*/ { $$ = NULL; }
1222912246
;
1223012247

12248+
/*
12249+
* The alias clause after JOIN ... USING only accepts the AS ColId spelling,
12250+
* per SQL standard. (The grammar could parse the other variants, but they
12251+
* don't seem to be useful, and it might lead to parser problems in the
12252+
* future.)
12253+
*/
12254+
opt_alias_clause_for_join_using:
12255+
AS ColId
12256+
{
12257+
$$ = makeNode(Alias);
12258+
$$->aliasname = $2;
12259+
/* the column name list will be inserted later */
12260+
}
12261+
| /*EMPTY*/ { $$ = NULL; }
12262+
;
12263+
1223112264
/*
1223212265
* func_alias_clause can include both an Alias and a coldeflist, so we make it
1223312266
* return a 2-element list that gets disassembled by calling production.
@@ -12272,15 +12305,24 @@ opt_outer: OUTER_P
1227212305

1227312306
/* JOIN qualification clauses
1227412307
* Possibilities are:
12275-
* USING ( column list ) allows only unqualified column names,
12308+
* USING ( column list ) [ AS alias ]
12309+
* allows only unqualified column names,
1227612310
* which must match between tables.
1227712311
* ON expr allows more general qualifications.
1227812312
*
12279-
* We return USING as a List node, while an ON-expr will not be a List.
12313+
* We return USING as a two-element List (the first item being a sub-List
12314+
* of the common column names, and the second either an Alias item or NULL).
12315+
* An ON-expr will not be a List, so it can be told apart that way.
1228012316
*/
1228112317

12282-
join_qual: USING '(' name_list ')' { $$ = (Node *) $3; }
12283-
| ON a_expr { $$ = $2; }
12318+
join_qual: USING '(' name_list ')' opt_alias_clause_for_join_using
12319+
{
12320+
$$ = (Node *) list_make2($3, $5);
12321+
}
12322+
| ON a_expr
12323+
{
12324+
$$ = $2;
12325+
}
1228412326
;
1228512327

1228612328

‎src/backend/parser/parse_clause.c

+32
Original file line numberDiff line numberDiff line change
@@ -1265,6 +1265,13 @@ transformFromClauseItem(ParseState *pstate, Node *n,
12651265
j->usingClause = rlist;
12661266
}
12671267

1268+
/*
1269+
* If a USING clause alias was specified, save the USING columns as
1270+
* its column list.
1271+
*/
1272+
if (j->join_using_alias)
1273+
j->join_using_alias->colnames = j->usingClause;
1274+
12681275
/*
12691276
* Now transform the join qualifications, if any.
12701277
*/
@@ -1460,6 +1467,7 @@ transformFromClauseItem(ParseState *pstate, Node *n,
14601467
res_colvars,
14611468
l_colnos,
14621469
r_colnos,
1470+
j->join_using_alias,
14631471
j->alias,
14641472
true);
14651473

@@ -1493,6 +1501,30 @@ transformFromClauseItem(ParseState *pstate, Node *n,
14931501
pstate->p_joinexprs = lappend(pstate->p_joinexprs, j);
14941502
Assert(list_length(pstate->p_joinexprs) == j->rtindex);
14951503

1504+
/*
1505+
* If the join has a USING alias, build a ParseNamespaceItem for that
1506+
* and add it to the list of nsitems in the join's input.
1507+
*/
1508+
if (j->join_using_alias)
1509+
{
1510+
ParseNamespaceItem *jnsitem;
1511+
1512+
jnsitem = (ParseNamespaceItem *) palloc(sizeof(ParseNamespaceItem));
1513+
jnsitem->p_names = j->join_using_alias;
1514+
jnsitem->p_rte = nsitem->p_rte;
1515+
jnsitem->p_rtindex = nsitem->p_rtindex;
1516+
/* no need to copy the first N columns, just use res_nscolumns */
1517+
jnsitem->p_nscolumns = res_nscolumns;
1518+
/* set default visibility flags; might get changed later */
1519+
jnsitem->p_rel_visible = true;
1520+
jnsitem->p_cols_visible = true;
1521+
jnsitem->p_lateral_only = false;
1522+
jnsitem->p_lateral_ok = true;
1523+
/* Per SQL, we must check for alias conflicts */
1524+
checkNameSpaceConflicts(pstate, list_make1(jnsitem), my_namespace);
1525+
my_namespace = lappend(my_namespace, jnsitem);
1526+
}
1527+
14961528
/*
14971529
* Prepare returned namespace list. If the JOIN has an alias then it
14981530
* hides the contained RTEs completely; otherwise, the contained RTEs

‎src/backend/parser/parse_expr.c

+50-15
Original file line numberDiff line numberDiff line change
@@ -2512,26 +2512,61 @@ static Node *
25122512
transformWholeRowRef(ParseState *pstate, ParseNamespaceItem *nsitem,
25132513
int sublevels_up, int location)
25142514
{
2515-
Var *result;
2516-
25172515
/*
2518-
* Build the appropriate referencing node. Note that if the RTE is a
2519-
* function returning scalar, we create just a plain reference to the
2520-
* function value, not a composite containing a single column. This is
2521-
* pretty inconsistent at first sight, but it's what we've done
2522-
* historically. One argument for it is that "rel" and "rel.*" mean the
2523-
* same thing for composite relations, so why not for scalar functions...
2516+
* Build the appropriate referencing node. Normally this can be a
2517+
* whole-row Var, but if the nsitem is a JOIN USING alias then it contains
2518+
* only a subset of the columns of the underlying join RTE, so that will
2519+
* not work. Instead we immediately expand the reference into a RowExpr.
2520+
* Since the JOIN USING's common columns are fully determined at this
2521+
* point, there seems no harm in expanding it now rather than during
2522+
* planning.
2523+
*
2524+
* Note that if the RTE is a function returning scalar, we create just a
2525+
* plain reference to the function value, not a composite containing a
2526+
* single column. This is pretty inconsistent at first sight, but it's
2527+
* what we've done historically. One argument for it is that "rel" and
2528+
* "rel.*" mean the same thing for composite relations, so why not for
2529+
* scalar functions...
25242530
*/
2525-
result = makeWholeRowVar(nsitem->p_rte, nsitem->p_rtindex,
2526-
sublevels_up, true);
2531+
if (nsitem->p_names == nsitem->p_rte->eref)
2532+
{
2533+
Var *result;
25272534

2528-
/* location is not filled in by makeWholeRowVar */
2529-
result->location = location;
2535+
result = makeWholeRowVar(nsitem->p_rte, nsitem->p_rtindex,
2536+
sublevels_up, true);
25302537

2531-
/* mark relation as requiring whole-row SELECT access */
2532-
markVarForSelectPriv(pstate, result);
2538+
/* location is not filled in by makeWholeRowVar */
2539+
result->location = location;
25332540

2534-
return (Node *) result;
2541+
/* mark relation as requiring whole-row SELECT access */
2542+
markVarForSelectPriv(pstate, result);
2543+
2544+
return (Node *) result;
2545+
}
2546+
else
2547+
{
2548+
RowExpr *rowexpr;
2549+
List *fields;
2550+
2551+
/*
2552+
* We want only as many columns as are listed in p_names->colnames,
2553+
* and we should use those names not whatever possibly-aliased names
2554+
* are in the RTE. We needn't worry about marking the RTE for SELECT
2555+
* access, as the common columns are surely so marked already.
2556+
*/
2557+
expandRTE(nsitem->p_rte, nsitem->p_rtindex,
2558+
sublevels_up, location, false,
2559+
NULL, &fields);
2560+
rowexpr = makeNode(RowExpr);
2561+
rowexpr->args = list_truncate(fields,
2562+
list_length(nsitem->p_names->colnames));
2563+
rowexpr->row_typeid = RECORDOID;
2564+
rowexpr->row_format = COERCE_IMPLICIT_CAST;
2565+
rowexpr->colnames = copyObject(nsitem->p_names->colnames);
2566+
rowexpr->location = location;
2567+
2568+
return (Node *) rowexpr;
2569+
}
25352570
}
25362571

25372572
/*

0 commit comments

Comments
 (0)