Re: [BUGS] BUG #14573: lateral joins, ambuiguity

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

On Wednesday, March 1, 2017, <dlw405@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference:      14573
Logged by:          Denise Wiedl
Email address:      dlw405@gmail.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.

​