Re: Making the subquery alias optional in the FROM clause

On Mon, Jun 27, 2022 at 9:49 PM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)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.