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"
Date: Tue, 12 Sep 2023 13:55:09 -0400
Message-ID: <2820124.1694541309@sss.pgh.pa.us> (raw)
In-Reply-To: <7AC1DE23-F815-421B-8F01-C49A8764354D@gmail.com>
References: <7AC1DE23-F815-421B-8F01-C49A8764354D@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, aleksandr.vin@gmail.com, 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: <2820124.1694541309@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox