From: Tom Lane <tgl@sss.pgh.pa.us>
To: Aleksandr Vinokurov <aleksandr.vin@gmail.com>
cc: 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>
References: <7AC1DE23-F815-421B-8F01-C49A8764354D@gmail.com>
Comments: In-reply-to Aleksandr Vinokurov <aleksandr.vin@gmail.com>
	message dated "Tue, 12 Sep 2023 18:59:43 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2820123.1694541309.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 12 Sep 2023 13:55:09 -0400
Message-ID: <2820124.1694541309@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2820124.1694541309%40sss.pgh.pa.us>
Precedence: bulk

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 =E2=80=9Cvalue=E2=80=9D for the column for which th=
e elements were requested to be named =E2=80=9Cas item=E2=80=9D,

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":

=3D# \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:

=3D# 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

=3D# 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