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.
Example:
The related_to_everyone table has FK to both the aaardvark table, and the
banana table.
In turn, each aardvark entry and each banana entry has a FK to it's owner.
I'm trying to figure out for a given row in related_to_everyone, what is the
aardvark_owner, and the banana_owner.
In a dynamically generated SQL query, we had created 2 columns named
"owner". The subselect statement within the 2nd lateral join, ends up
grabbing the 1st owner column.
```
SELECT
(related_to_everybody_aardy.owner).last_name,
(related_to_everybody_banana.owner).last_name
FROM related_to_everybody "0"
-- get ardy owner
LEFT JOIN LATERAL (
SELECT "1".*, owner
FROM aaardvark "1"
LEFT JOIN LATERAL (
SELECT "2".*
FROM users "2"
WHERE "1".owner_id = "2".id
) owner ON true
WHERE "0".aardy = "1".id
) related_to_everybody_aardy ON true
-- get banana owner
LEFT JOIN LATERAL (
SELECT "1".*, owner
FROM banana "1"
LEFT JOIN LATERAL (
SELECT "2".*
FROM users "2"
WHERE "1".owner_id = "2".id
) owner ON true
WHERE "0".banana = "1".id
) related_to_everybody_banana ON true
WHERE <select 1 row in related_to_everybody>
;
```
^^ The `SELECT owner` in the 2nd lateral join grabs the value from the 1st
owner column.
Such that (related_to_everybody_banana.owner).last_name now refers to the
aardvark_owner.
We could solve this by aliasing each column within the subquery:
```
LEFT JOIN LATERAL (
SELECT "1".*, banana_owner as owner
FROM banana "1"
LEFT JOIN LATERAL (
SELECT "2".*
FROM users "2"
WHERE "1".owner_id = "2".id
) banana_owner ON true
WHERE "0".banana = "1".id
) related_to_everybody_banana ON true
```
Then the (related_to_everybody_banana.owner).last_name will correctly refer
to the banana owner.
We are confused on why there was not an ambiguity error thrown on the
property 'owner' during the 2nd lateral join's SELECT statement. Should
there be?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs