Обсуждение: "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

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

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