Thread: "as name" is shadowed by name "value" when selecting with "left join jsonb_array_elements(d.items) as item on true"
"as name" is shadowed by name "value" when selecting with "left join jsonb_array_elements(d.items) as item on true"
From
Aleksandr Vinokurov
Date:
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
Re: "as name" is shadowed by name "value" when selecting with "left join jsonb_array_elements(d.items) as item on true"
From
Tom Lane
Date:
Aleksandr Vinokurov <aleksandr.vin@gmail.com> writes: > 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 > 1. It shows the name “value” for the column for which the elements were requested to be named “as item”, I see no bug here. In the FROM entry "jsonb_array_elements(d.items) as item", you've declared the *table* alias to be "item", but you left the column name(s) of the table unspecified --- and jsonb_array_elements declares its output argument to be named "value": =# \sf jsonb_array_elements CREATE OR REPLACE FUNCTION pg_catalog.jsonb_array_elements(from_json jsonb, OUT value jsonb) RETURNS SETOF jsonb LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT ROWS 100 AS $function$jsonb_array_elements$function$ So "select *" expands the available columns as "items" from table "d" and "value" from table "item". Referencing "item" in the SELECT list is really a whole-table reference, although this isn't too obvious because we hack that to act identical to a column reference if the reference is to a scalar-producing function. To clarify what's happening, you could specify the column alias explicitly: =# select *, item as item from (select '[1]'::jsonb as items) as d left join jsonb_array_elements(d.items) as item(zed) on true; items | zed | item -------+-----+------ [1] | 1 | 1 (1 row) or even =# select *, item.zed as item from (select '[1]'::jsonb as items) as d left join jsonb_array_elements(d.items) as item(zed) on true; items | zed | item -------+-----+------ [1] | 1 | 1 (1 row) Most scalar-producing functions don't declare an output argument name, and in that case "as foo" works effectively like "as foo(foo)" to set both the table and column alias. I'm not sure why jsonb_array_elements goes out of its way to do this differently. regards, tom lane
Re: "as name" is shadowed by name "value" when selecting with "left join jsonb_array_elements(d.items) as item on true"
From
Aleksandr Vinokurov
Date:
Oh, That's very interesting, thanks a lot for quick response. And have a nice evening. With best regards, Aleksandr Vinokourov > On 12 Sep 2023, at 19:55, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Aleksandr Vinokurov <aleksandr.vin@gmail.com> writes: >> 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 > >> 1. It shows the name “value” for the column for which the elements were requested to be named “as item”, > > I see no bug here. In the FROM entry "jsonb_array_elements(d.items) as > item", you've declared the *table* alias to be "item", but you left > the column name(s) of the table unspecified --- and jsonb_array_elements > declares its output argument to be named "value": > > =# \sf jsonb_array_elements > CREATE OR REPLACE FUNCTION pg_catalog.jsonb_array_elements(from_json jsonb, OUT value jsonb) > RETURNS SETOF jsonb > LANGUAGE internal > IMMUTABLE PARALLEL SAFE STRICT ROWS 100 > AS $function$jsonb_array_elements$function$ > > So "select *" expands the available columns as "items" from table "d" > and "value" from table "item". Referencing "item" in the SELECT list > is really a whole-table reference, although this isn't too obvious > because we hack that to act identical to a column reference if the > reference is to a scalar-producing function. > > To clarify what's happening, you could specify the column alias > explicitly: > > =# select *, item as item > from (select '[1]'::jsonb as items) as d > left join jsonb_array_elements(d.items) as item(zed) on true; > items | zed | item > -------+-----+------ > [1] | 1 | 1 > (1 row) > > or even > > =# select *, item.zed as item > from (select '[1]'::jsonb as items) as d > left join jsonb_array_elements(d.items) as item(zed) on true; > items | zed | item > -------+-----+------ > [1] | 1 | 1 > (1 row) > > Most scalar-producing functions don't declare an output argument > name, and in that case "as foo" works effectively like "as foo(foo)" > to set both the table and column alias. I'm not sure why > jsonb_array_elements goes out of its way to do this differently. > > regards, tom lane