Re: "as name" is shadowed by name "value" when selecting with "left join jsonb_array_elements(d.items) as item on true"

Aleksandr Vinokurov <aleksandr(dot)vin(at)gmail(dot)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