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

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

From
Peter Eisentraut
Date:
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

Attachment

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

From
Thomas Munro
Date:
On Tue, Jun 18, 2019 at 2:41 AM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
> 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.

Neat.  That's a refreshingly short patch to get a sql_features.txt
line bumped to YES.

> Patch attached.

It does what it says on the tin.

I see that USING is the important thing here; for (a NATURAL JOIN b)
AS ab or (a JOIN b ON ...) AS ab you still need the parentheses or
(respectively) it means something different (alias for B only) or
doesn't parse.  That makes sense.

I noticed that the HINT when you accidentally use a base table name
instead of a table alias is more helpful than the HINT you get when
you use a base table name instead of a join alias.  That seems like a
potential improvement that is independent of this syntax change.

-- 
Thomas Munro
https://enterprisedb.com



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

From
Fabien COELHO
Date:
Hello Peter,

> 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.

A few more comments.

Patch v1 applies cleanly, compiles. make check ok. Doc gen ok.

The patch allows an AS clause (alias) attached to a JOIN USING, which seems
to be SQL feature F404, which seems a new feature in SQL:2016.

The feature implementation only involves parser changes, so the underlying
infrastructure seems to be already available.

About the code:

The removal from the grammar of the dynamic type introspection to distinguish
between ON & USING is a relief in itself:-)

About the feature:

When using aliases both on tables and on the unifying using clause, the former
are hidden from view. I cannot say that I understand why, and this makes it
impossible to access some columns in some cases if there is an ambiguity, eg:

  postgres=# SELECT t.filler
             FROM pgbench_tellers AS t
         JOIN pgbench_branches AS b USING (bid) AS x;
  ERROR:  invalid reference to FROM-clause entry for table "t"
  LINE 1: SELECT t.filler FROM pgbench_tellers AS t JOIN pgbench_branc...
                 ^
  HINT:  There is an entry for table "t", but it cannot be referenced from this
         part of the query.

But then:

  postgres=# SELECT x.filler
             FROM pgbench_tellers AS t
         JOIN pgbench_branches AS b USING (bid) AS x;
  ERROR:  column reference "filler" is ambiguous
  LINE 1: SELECT x.filler FROM pgbench_tellers AS t JOIN pgbench_branc...
                 ^

Is there a good reason to forbid several aliases covering the same table?

More precisely, is this behavior expected from the spec or a side effect 
of pg implementation?

Given that the executor detects that the underlying alias exists, could it 
just let it pass instead of raising an error, and it would simply just 
work?

I'm wondering why such an alias could not be attached also to an ON 
clause. Having them in one case but not the other looks strange.

About the documentation:

The documentation changes only involves the synopsis. ISTM that maybe aliases
shadowing one another could deserve some caveat. The documentation in its
"alias" paragraph only talks about hidding table and functions names.

Also, the USING paragraph could talk about its optional alias and its 
hiding effect.

About tests:

Maybe an alias hidding case could be added.

-- 
Fabien.



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

From
Thomas Munro
Date:
On Tue, Jul 16, 2019 at 8:58 AM Fabien COELHO <coelho@cri.ensmp.fr> wrote:
> About the feature:
>
> When using aliases both on tables and on the unifying using clause, the former
> are hidden from view. I cannot say that I understand why, and this makes it
> impossible to access some columns in some cases if there is an ambiguity, eg:
>
>   postgres=# SELECT t.filler
>              FROM pgbench_tellers AS t
>             JOIN pgbench_branches AS b USING (bid) AS x;
>   ERROR:  invalid reference to FROM-clause entry for table "t"
>   LINE 1: SELECT t.filler FROM pgbench_tellers AS t JOIN pgbench_branc...
>                  ^
>   HINT:  There is an entry for table "t", but it cannot be referenced from this
>          part of the query.
>
> But then:
>
>   postgres=# SELECT x.filler
>              FROM pgbench_tellers AS t
>             JOIN pgbench_branches AS b USING (bid) AS x;
>   ERROR:  column reference "filler" is ambiguous
>   LINE 1: SELECT x.filler FROM pgbench_tellers AS t JOIN pgbench_branc...
>                  ^
>
> Is there a good reason to forbid several aliases covering the same table?
>
> More precisely, is this behavior expected from the spec or a side effect
> of pg implementation?

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 moving this to the next CF.

-- 
Thomas Munro
https://enterprisedb.com



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

From
Alvaro Herrera
Date:
On 2019-Aug-01, Thomas Munro 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.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



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

From
Peter Eisentraut
Date:
On 2019-09-17 19:37, Alvaro Herrera wrote:
> On 2019-Aug-01, Thomas Munro 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.

I tried to analyze the spec for what the behavior should be here, but I
got totally lost.  I'll give it another look.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



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

From
Fabien COELHO
Date:
On Tue, 17 Sep 2019, Alvaro Herrera 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-fa592d565dda@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.

ISTM that this patch could be "returned with feedback".

-- 
Fabien.



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

From
Peter Eisentraut
Date:
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-fa592d565dda@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

Attachment

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

From
Vik Fearing
Date:
On 30/12/2019 22:25, Peter Eisentraut wrote:
> 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-fa592d565dda@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.


Excellent!  Thank you for working on this, Peter.


One thing I notice is that the joined columns are still accessible from
their respective table names when they should not be per spec.  That
might be one of those "silly restrictions" that we choose to ignore, but
it should probably be noted somewhere, at the very least in a code
comment if not in user documentation. (This is my reading of SQL:2016 SR
11.a.i)

-- 

Vik Fearing




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

From
Fabien COELHO
Date:
Hello Peter,

> 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.

It seems that this patch does not apply anymore after Tom's 5815696.

-- 
Fabien.



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

From
Peter Eisentraut
Date:
On 2019-12-31 00:07, Vik Fearing wrote:
> One thing I notice is that the joined columns are still accessible from
> their respective table names when they should not be per spec.  That
> might be one of those "silly restrictions" that we choose to ignore, but
> it should probably be noted somewhere, at the very least in a code
> comment if not in user documentation. (This is my reading of SQL:2016 SR
> 11.a.i)

Here is a rebased patch.

The above comment is valid.  One reason I didn't implement it is that it 
would create inconsistencies with existing behavior, which is already 
nonstandard.

For example,

create table a (id int, a1 int, a2 int);
create table b (id int, b2 int, b3 int);

makes

select a.id from a join b using (id);

invalid.  Adding an explicit alias for the common column names doesn't 
change that semantically, because an implicit alias also exists if an 
explicit one isn't specified.

I agree that some documentation would be in order if we decide to leave 
it like this.

Another reason was that it seemed "impossible" to implement it before 
Tom's recent refactoring of the parse namespace handling.  Now we also 
have parse namespace columns tracked separately from range table 
entries, so it appears that this would be possible.  If we want to do it.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment

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

From
Daniel Gustafsson
Date:
> On 27 Jan 2020, at 10:19, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
>
> On 2019-12-31 00:07, Vik Fearing wrote:
>> One thing I notice is that the joined columns are still accessible from
>> their respective table names when they should not be per spec.  That
>> might be one of those "silly restrictions" that we choose to ignore, but
>> it should probably be noted somewhere, at the very least in a code
>> comment if not in user documentation. (This is my reading of SQL:2016 SR
>> 11.a.i)
>
> Here is a rebased patch.

This thread has stalled for a bit, let's try to bring it to an end.

Vik: having shown interest in, and been actively reviewing, this patch; do you
have time to review this latest version from Peter during this commitfest?

cheers ./daniel


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

From
Wolfgang Walther
Date:
Peter Eisentraut:
> On 2019-12-31 00:07, Vik Fearing wrote:
>> One thing I notice is that the joined columns are still accessible from
>> their respective table names when they should not be per spec.  That
>> might be one of those "silly restrictions" that we choose to ignore, but
>> it should probably be noted somewhere, at the very least in a code
>> comment if not in user documentation. (This is my reading of SQL:2016 SR
>> 11.a.i)
> 
> Here is a rebased patch.
> 
> The above comment is valid.  One reason I didn't implement it is that it 
> would create inconsistencies with existing behavior, which is already 
> nonstandard.
> 
> For example,
> 
> create table a (id int, a1 int, a2 int);
> create table b (id int, b2 int, b3 int);
> 
> makes
> 
> select a.id from a join b using (id);
> 
> invalid.  Adding an explicit alias for the common column names doesn't 
> change that semantically, because an implicit alias also exists if an 
> explicit one isn't specified.
I just looked through the patch without applying or testing it - but I 
couldn't find anything that would indicate that this is not going to 
work for e.g. a LEFT JOIN as well. First PG patch I looked at, so tell 
me if I missed something there.

So given this:

SELECT x.id FROM a LEFT JOIN b USING (id) AS x

will this return NULL or a.id for rows that don't match in b? This 
should definitely be mentioned in the docs and I guess a test wouldn't 
be too bad as well?

In any case: If a.id and b.id would not be available anymore, but just 
x.id, either the id value itself or the NULL value (indicating the 
missing row in b) are lost. So this seems like a no-go.

 > I agree that some documentation would be in order if we decide to leave
 > it like this.

Keep it like that!



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

From
Georgios Kokolatos
Date:
Hi,

I noticed that this patch fails on the cfbot.
For this, I changed the status to: 'Waiting on Author'.

Cheers,
//Georgios

The new status of this patch is: Waiting on Author

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

From
Peter Eisentraut
Date:
On 2020-11-10 16:15, Georgios Kokolatos wrote:
> I noticed that this patch fails on the cfbot.
> For this, I changed the status to: 'Waiting on Author'.
> 
> Cheers,
> //Georgios
> 
> The new status of this patch is: Waiting on Author

Here is a rebased and lightly retouched patch.

-- 
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/

Attachment

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

From
Peter Eisentraut
Date:
On 2020-08-03 19:44, Wolfgang Walther wrote:
> So given this:
> 
> SELECT x.id FROM a LEFT JOIN b USING (id) AS x
> 
> will this return NULL or a.id for rows that don't match in b? This
> should definitely be mentioned in the docs and I guess a test wouldn't
> be too bad as well?

This issue is independent of the presence of the alias "x", so I don't 
think it has to do with this patch.

There is a fair amount of documentation on outer joins, so I expect that 
this is discussed there.

-- 
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/



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

From
Peter Eisentraut
Date:
On 05.03.21 18:00, David Steele wrote:
> On 11/14/20 3:49 AM, Peter Eisentraut wrote:
>> On 2020-11-10 16:15, Georgios Kokolatos wrote:
>>> I noticed that this patch fails on the cfbot.
>>> For this, I changed the status to: 'Waiting on Author'.
>>>
>>> Cheers,
>>> //Georgios
>>>
>>> The new status of this patch is: Waiting on Author
>>
>> Here is a rebased and lightly retouched patch.
> 
> There don't seem to be any objections to just documenting the slight 
> divergence from the spec.
> 
> So, does it make sense to just document that and proceed?

Yeah, I think that is not a problem.

I think Tom's input on the guts of this patch would be most valuable, 
since it intersects a lot with the parse namespace refactoring he did.



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

From
Tom Lane
Date:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> I think Tom's input on the guts of this patch would be most valuable, 
> since it intersects a lot with the parse namespace refactoring he did.

Yeah, I've been meaning to take a look.  I'll try to get it done in
the next couple of days.

            regards, tom lane



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

From
Tom Lane
Date:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> I think Tom's input on the guts of this patch would be most valuable,
> since it intersects a lot with the parse namespace refactoring he did.

I really didn't like the way you'd done that :-(.  My primary complaint
is that any one ParseNamespaceItem can describe only one table alias,
but here we have the potential for two aliases associated with the same
join:

    select * from (t1 join t2 using(a) as tu) tx;

Admittedly that's not hugely useful since tx hides the tu alias, but
it should behave in a sane fashion.  (BTW, after reading the SQL spec
again along the way to reviewing this, I am wondering if hiding the
lower aliases is really what we want; though it may be decades too late
to change that.)

However, ParseNamespaceItem as it stands needs some help for this.
It has a wired-in assumption that p_rte->eref describes the table
and column aliases exposed by the nsitem.  0001 below fixes this by
creating a separate p_names field in an nsitem.  (There are some
comments in 0001 referencing JOIN USING aliases, but no actual code
for the feature.)  That saves one indirection in common code paths,
so it's possibly a win on its own.  Then 0002 is your patch rebased
onto that infrastructure, and with some cleanup of my own.

One thing I ran into is that a whole-row Var for the JOIN USING
alias did the wrong thing.  It should have only the common columns,
but we were getting all the join columns in examples such as the
row_to_json() test case I added.  This is difficult to fix given
the existing whole-row Var infrastructure, unless we want to make a
separate RTE for the JOIN USING alias, which I think is overkill.
What I did about this was to make transformWholeRowRef produce a
ROW() construct --- which is something that a whole-row Var for a
join would be turned into by the planner anyway.  I think this is
semantically OK since the USING construct has already nailed down
the number and types of the join's common columns; there's no
prospect of those changing underneath a stored view query.  It's
slightly ugly because the ROW() construct will be visible in a
decompiled view instead of "tu.*" like you wrote originally,
but I'm willing to live with that.

Speaking of decompiled views, I feel like ruleutils.c could do with
a little more work to teach it that these aliases are available.
Right now, it resorts to ugly workarounds:

regression=# create table t1 (a int, b int, c int);
CREATE TABLE
regression=# create table t2 (a int, x int, y int);
CREATE TABLE
regression=# create view vvv as select tj.a, t1.b from t1 full join t2 using(a) as tj, t1 as tx;
CREATE VIEW
regression=# \d+ vvv
                             View "public.vvv"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 a      | integer |           |          |         | plain   |
 b      | integer |           |          |         | plain   |
View definition:
 SELECT a,
    t1.b
   FROM t1
     FULL JOIN t2 USING (a) AS tj,
    t1 tx(a_1, b, c);

That's not wrong, but it could likely be done better if ruleutils
realized it could use the tj alias to reference the column, instead
of having to force unqualified "a" to be a globally unique name.

I ran out of steam to look into that, though, and it's probably
something that could be improved later.

One other cosmetic thing is that this:

regression=# select tu.* from (t1 join t2 using(a) as tu) tx;
ERROR:  missing FROM-clause entry for table "tu"
LINE 1: select tu.* from (t1 join t2 using(a) as tu) tx;
               ^

is a relatively dumb error message, compared to

regression=# select t1.* from (t1 join t2 using(a) as tu) tx;
ERROR:  invalid reference to FROM-clause entry for table "t1"
LINE 1: select t1.* from (t1 join t2 using(a) as tu) tx;
               ^
HINT:  There is an entry for table "t1", but it cannot be referenced from this part of the query.

I didn't look into why that isn't working, but maybe errorMissingRTE
needs to trawl all of the ParseNamespaceItems not just the RTEs.

Anyway, since these remaining gripes are cosmetic, I'll mark this RFC.

            regards, tom lane

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 — 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

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

From
Peter Eisentraut
Date:
On 23.03.21 00:18, Tom Lane wrote:
> However, ParseNamespaceItem as it stands needs some help for this.
> It has a wired-in assumption that p_rte->eref describes the table
> and column aliases exposed by the nsitem.  0001 below fixes this by
> creating a separate p_names field in an nsitem.  (There are some
> comments in 0001 referencing JOIN USING aliases, but no actual code
> for the feature.)  That saves one indirection in common code paths,
> so it's possibly a win on its own.  Then 0002 is your patch rebased
> onto that infrastructure, and with some cleanup of my own.

Makes sense.  I've committed it based on that.

> Speaking of decompiled views, I feel like ruleutils.c could do with
> a little more work to teach it that these aliases are available.
> Right now, it resorts to ugly workarounds:

Yeah, the whole has_dangerous_join_using() can probably be unwound and 
removed with this.  But it's a bit of work.

> One other cosmetic thing is that this:
> 
> regression=# select tu.* from (t1 join t2 using(a) as tu) tx;
> ERROR:  missing FROM-clause entry for table "tu"
> LINE 1: select tu.* from (t1 join t2 using(a) as tu) tx;
>                 ^
> 
> is a relatively dumb error message, compared to
> 
> regression=# select t1.* from (t1 join t2 using(a) as tu) tx;
> ERROR:  invalid reference to FROM-clause entry for table "t1"
> LINE 1: select t1.* from (t1 join t2 using(a) as tu) tx;
>                 ^
> HINT:  There is an entry for table "t1", but it cannot be referenced from this part of the query.
> 
> I didn't look into why that isn't working, but maybe errorMissingRTE
> needs to trawl all of the ParseNamespaceItems not just the RTEs.

Yes, I've prototyped that and it would have the desired effect.  Might 
need some code rearranging, like either change searchRangeTableForRel() 
to not return an RTE or make a similar function for ParseNamespaceItem 
search.  Needs some more thought.  I have left a test case in that would 
show any changes here.