Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: Aleksandr Vinokurov <aleksandr.vin@gmail.com>
Mime-Version: 1.0 (1.0)
Subject: Re: "as name" is shadowed by name "value" when selecting with "left
 join jsonb_array_elements(d.items) as item on true"
Date: Tue, 12 Sep 2023 21:03:20 +0200
Message-Id: <286F430B-6939-4C07-BF5B-DFD2BBE94E7A@gmail.com>
References: <2820124.1694541309@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
In-Reply-To: <2820124.1694541309@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/286F430B-6939-4C07-BF5B-DFD2BBE94E7A%40gmail.com>
Precedence: bulk

Oh,

That's very interesting, thanks a lot for quick response. And have a nice ev=
ening.

With best regards,
Aleksandr Vinokourov

> On 12 Sep 2023, at 19:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
> =EF=BB=BFAleksandr 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;
>=20
>> items | value | item
>> -------+-------+------
>> [1]   | 1     | 1
>=20
>> 1. It shows the name =E2=80=9Cvalue=E2=80=9D for the column for which the=
 elements were requested to be named =E2=80=9Cas item=E2=80=9D,
>=20
> 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":
>=20
> =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$
>=20
> 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.
>=20
> To clarify what's happening, you could specify the column alias
> explicitly:
>=20
> =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=20
> -------+-----+------
> [1]   | 1   | 1
> (1 row)
>=20
> or even
>=20
> =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=20
> -------+-----+------
> [1]   | 1   | 1
> (1 row)
>=20
> 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.
>=20
>            regards, tom lane