Re: BUG #14573: lateral joins, ambuiguity

On Wed, Mar 1, 2017 at 8:22 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wednesday, March 1, 2017, <dlw405(at)gmail(dot)com> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 14573
>> Logged by: Denise Wiedl
>> Email address: dlw405(at)gmail(dot)com
>> PostgreSQL version: 9.5.3
>> Operating system: osx 10.11.5
>> Description:
>>
>> PostgreSQL 9.5.3 on x86_64-apple-darwin15.4.0, compiled by Apple LLVM
>> version 7.3.0 (clang-703.0.31), 64-bit
>>
>> The LATERAL JOIN has access to all previous columns in the join, but, it
>> doesn't give an error when there are two columns of the same name.
>> Instead,
>> it silently selects the first column.
>>
>> LEFT JOIN LATERAL (
>> SELECT "1".*, owner
>> FROM banana "1"
>> LEFT JOIN LATERAL (
>> SELECT "2".*
>> FROM users "2"
>> WHERE "1".owner_id = "2".id
>> ) owner
>>
>
> IIUC the preference exhibited is an explicit column present on the left
> side of the join over the implicit relation named column within its own
> query.
>
> ​Simpler self-contained example:
>
> select *
> from (values (1)) vals (v)
> left join lateral (
> select v
> from (values (2)) v (val)
> ) src on (true)
>
> Returns (1,1) instead of the desired (1,(2))
>
> ​Beyond my pay grade for diagnostics. I don't recall this being
> documented and I haven't looked for it yet.
>
> If I come up with a non-lateral involved example before this is answered
> I'll come back and post it.
>

​I'd say its working as designed (or, at least, its not unique to LATERAL)
- though no joy on finding where its end-user documented.​

​select v --ambigious
from (values (1)) vals (v)
cross join (
select valt as v
from (values (2)) valt (val)
) v;

select v --picks the column 1
from (values (1)) vals (v)
cross join (
select valt
from (values (2)) valt (val)
) v;

David J.
​