Re: Making the subquery alias optional in the FROM clause - Mailing list pgsql-hackers

On Mon, Jun 27, 2022 at 9:49 PM Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> This was discussed previously in [1], and there seemed to be general
> consensus in favour of it, but no new patch emerged.
>
> Attached is a patch that takes the approach of not generating an alias
> at all, which seems to be neater and simpler, and less code than
> trying to generate a unique alias.
>
> It still generates an eref for the subquery RTE, which has a made-up
> relation name, but that is marked as not visible on the
> ParseNamespaceItem, so it doesn't conflict with anything else, need
> not be unique, and cannot be used for qualified references to the
> subquery's columns.
>
> The only place that exposes the eref's made-up relation name is the
> existing query deparsing code in ruleutils.c, which uniquifies it and
> generates SQL spec-compliant output. For example:
>
> CREATE OR REPLACE VIEW test_view AS
>   SELECT *
>     FROM (SELECT a, b FROM foo),
>          (SELECT c, d FROM bar)
>    WHERE a = c;
>
> \sv test_view
>
> CREATE OR REPLACE VIEW public.test_view AS
>  SELECT subquery.a,
>     subquery.b,
>     subquery_1.c,
>     subquery_1.d
>    FROM ( SELECT foo.a,
>             foo.b
>            FROM foo) subquery,
>     ( SELECT bar.c,
>             bar.d
>            FROM bar) subquery_1
>   WHERE subquery.a = subquery_1.c

It doesn't play that well if you have something called subquery though:

CREATE OR REPLACE VIEW test_view AS
  SELECT *
    FROM (SELECT a, b FROM foo),
         (SELECT c, d FROM bar), (select relname from pg_class limit
1)  as subquery
   WHERE a = c;

\sv test_view
CREATE OR REPLACE VIEW public.test_view AS
 SELECT subquery.a,
    subquery.b,
    subquery_1.c,
    subquery_1.d,
    subquery_2.relname
   FROM ( SELECT foo.a,
            foo.b
           FROM foo) subquery,
    ( SELECT bar.c,
            bar.d
           FROM bar) subquery_1,
    ( SELECT pg_class.relname
           FROM pg_class
         LIMIT 1) subquery_2
  WHERE subquery.a = subquery_1.c

While the output is a valid query, it's not nice that it's replacing a
user provided alias with another one (or force an alias if you have a
relation called subquery).  More generally, I'm -0.5 on the feature.
I prefer to force using SQL-compliant queries, and also not take bad
habits.