Subject: Re: Allow an alias to be attached directly to a JOIN ... USING
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>,
 Fabien COELHO <coelho@cri.ensmp.fr>,
 Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Thomas Munro <thomas.munro@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <20190917173742.GA8735@alvherre.pgsql>
 <alpine.DEB.2.21.1912241908490.27864@pseudo>
 <9a5c9ed2-1dd7-8c2a-6b90-0de54f4c13a1@2ndquadrant.com>
From: Vik Fearing <vik.fearing@2ndquadrant.com>
Message-ID: <8568b66d-405d-5d83-ac8c-e107f1b9f113@2ndquadrant.com>
Date: Tue, 31 Dec 2019 00:07:55 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.2.2
MIME-Version: 1.0
In-Reply-To: <9a5c9ed2-1dd7-8c2a-6b90-0de54f4c13a1@2ndquadrant.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

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