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

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.