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

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!