From: Aleksandr Vinokurov <aleksandr.vin@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Subject: "as name" is shadowed by name "value" when selecting with "left join jsonb_array_elements(d.items) as item on true"
Date: Tue, 12 Sep 2023 18:59:43 +0200
Message-ID: <7AC1DE23-F815-421B-8F01-C49A8764354D@gmail.com> (raw)
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 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
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: aleksandr.vin@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: "as name" is shadowed by name "value" when selecting with "left join jsonb_array_elements(d.items) as item on true"
In-Reply-To: <7AC1DE23-F815-421B-8F01-C49A8764354D@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox