Re: Making the subquery alias optional in the FROM clause

Erwin Brandstetter <brsaweda(at)gmail(dot)com> writes:
> On Mon, 2 Oct 2023 at 00:33, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>> 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:

> I ran into one other place: error messages.
> SELECT unnamed_subquery.a
> FROM (SELECT 1 AS a)
> ERROR: There is an entry for table "unnamed_subquery", but it cannot be
> referenced from this part of the query.invalid reference to FROM-clause
> entry for table "unnamed_subquery"

Yeah, that's exposing more of the implementation than we really want.

> Notably, the same does not happen for "unnamed_subquery_1":
> SELECT unnamed_subquery_1.a
> FROM (SELECT 1 AS a), (SELECT 1 AS a)
> ERROR: missing FROM-clause entry for table "unnamed_subquery_1"

Actually, that happens because "unnamed_subquery_1" *isn't* in the
parse tree. As implemented, both RTEs are labeled "unnamed_subquery"
in the parser output, and it's ruleutils that de-duplicates them.

I'm inclined to think we should avoid letting "unnamed_subquery"
appear in the parse tree, too. It might not be a good idea to
try to leave the eref field null, but could we set it to an
empty string instead, that is

- eref = alias ? copyObject(alias) : makeAlias("unnamed_subquery", NIL);
+ eref = alias ? copyObject(alias) : makeAlias("", NIL);

and then let ruleutils replace that with "unnamed_subquery"? This
would prevent accessing the subquery name in the way Erwin shows,
because we don't let you write an empty identifier in SQL:

regression=# select "".a from (select 1 as a);
ERROR: zero-length delimited identifier at or near """"
LINE 1: select "".a from (select 1 as a);
^

However, there might then be some parser error messages that
refer to subquery "", so I'm not sure if this is totally
without surprises either.

regards, tom lane