From: Aleksandr Vinokurov <aleksandr.vin@gmail.com>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.400.51.1.1\))
Subject: "as name" is shadowed by name "value" when selecting with "left join
 jsonb_array_elements(d.items) as item on true"
Message-Id: <7AC1DE23-F815-421B-8F01-C49A8764354D@gmail.com>
Date: Tue, 12 Sep 2023 18:59:43 +0200
To: pgsql-bugs@lists.postgresql.org
Archived-At: 
 <https://www.postgresql.org/message-id/7AC1DE23-F815-421B-8F01-C49A8764354D%40gmail.com>
Precedence: bulk

Hello good people,

I=E2=80=99ve stepped into a bug today, where the requested name is =
strangely shadowed.

Consider three following examples (in short =E2=80=94 check the =
=E2=80=9Cvalue=E2=80=9D 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 =E2=80=9Cas item=E2=80=9D, and for last two =
everything is as expected. But the first one is strange:
1. It shows the name =E2=80=9Cvalue=E2=80=9D for the column for which =
the elements were requested to be named =E2=80=9Cas item=E2=80=9D,
2. but on also it does not fail on making a copy of the column =
(referenced by name =E2=80=9Citem=E2=80=9D as =E2=80=9Citem=E2=80=9D).

Actually you don=E2=80=99t need to make it =E2=80=9Citem as item=E2=80=9D,=
 it can be =E2=80=9Cbuggy_name as item=E2=80=9D if you make it "left =
join jsonb_array_elements(d.items) as buggy_name on true=E2=80=9D =E2=80=94=
 the way =E2=80=9Citem as item=E2=80=9D is just a workaround for now to =
have a column with 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=20

 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=