"as name" is shadowed by name "value" when selecting with "left join jsonb_array_elements(d.items) as item on true"

Hello good people,

I’ve stepped into a bug today, where the requested name is strangely shadowed.

Consider three following examples (in short — check the “value” column name in the first result):

-----------------

select *, item as item
from (select '[1]'::jsonb as items) as d
left join jsonb_array_elements(d.items) as item on true;

 items | value | item
-------+-------+------
 [1]   | 1     | 1

select *, item as item
from (select *, jsonb_array_elements(d.items) as item
from (select '[1]'::jsonb as items) as d) as f;

 items | item | item
-------+------+------
 [1]   | 1    | 1

select *, item as item
from (select '{1}'::text[] as items) as d
left join unnest(d.items) as item on true;

 items | item | item
-------+------+------
 {1}   | 1    | 1

-----------------

All three request name to be “as item”, and for last two everything is as expected. But the first one is strange:
1. It shows the name “value” for the column for which the elements were requested to be named “as item”,
2. but on also it does not fail on making a copy of the column (referenced by name “item” as “item”).

Actually you don’t need to make it “item as item”, it can be “buggy_name as item” if you make it "left join
jsonb_array_elements(d.items)as buggy_name on true” — the way “item as item” is just a workaround for now to have a
columnwith the desired name and data. 

That behaviour was observed first on

PostgreSQL 12.16 (Debian 12.16-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0,
64-bit

and then

 PostgreSQL 15.4 (Debian 15.4-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0,
64-bit


If I can help with something else, please let me know

With best regards,
Aleksandr Vinokurov