Re: BUG #14573: lateral joins, ambuiguity

Also, I would note that the preference of inner vs. outer for lateral joins
are the opposite for tables vs columns. Rather confusing.

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

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