From: | Aleksandr Vinokurov <aleksandr(dot)vin(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | "as name" is shadowed by name "value" when selecting with "left join jsonb_array_elements(d.items) as item on true" |
Date: | 2023-09-12 16:59:43 |
Message-ID: | 7AC1DE23-F815-421B-8F01-C49A8764354D@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2023-09-12 17:55:09 | Re: "as name" is shadowed by name "value" when selecting with "left join jsonb_array_elements(d.items) as item on true" |
Previous Message | Alexander Kukushkin | 2023-09-12 13:29:46 | Re: pg_rewind WAL segments deletion pitfall |