Re: SQL feature requests

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Florian G. Pflug wrote:
>> knowing *why* the standard committee disallows that syntax -
>> and why everybody except oracle chose to agree with it would be quite
>> interesting.

> ...
> If you believe that, then a table source without any name at all, such
> as one produced by subqueries, should also be disallowed.

Good point. However, what I would kind of expect the standards
committee to do is to specify that each such subquery has an
automatically generated name assigned to it. Compare what SQL92 says
about assigning aliases for SELECT output columns:

a) If the i-th <derived column> in the <select list> specifies
an <as clause> that contains a <column name> C, then the
<column name> of the i-th column of the result is C.

b) If the i-th <derived column> in the <select list> does not
specify an <as clause> and the <value expression> of that
<derived column> is a single <column reference>, then the
<column name> of the i-th column of the result is C.

c) Otherwise, the <column name> of the i-th column of the <query
specification> is implementation-dependent and different
from the <column name> of any column, other than itself, of
a table referenced by any <table reference> contained in the
SQL-statement.

Implementation-dependent is a term of art here: it means that each DBMS
can do whatever the heck it wants. (But note that we fail to meet (c)
exactly, since we don't bother to generate names that are distinct ---
but in practice no one seems to care about that.)

If we could expect that any future relaxation of the spec would likewise
specify generating implementation-dependent unique table aliases for
subqueries, then we'd not be having this discussion. What troubles me
most is why the spec hasn't had identical verbiage for table aliases as
it does for column aliases, since day one. There's *something* going on
there that we don't know about.

(BTW, your argument would seem to also disallow alias-less JOIN tables,
but the spec has allowed those all along.)

regards, tom lane