Re: BUG #2204: Feature Req: Unique output column names

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> Or show us where the standard mandates name munging and we
> will implement it.

SQL92 says (in 7.9 <query specification>):

9) Case:

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.

Rule (c) is a bit interesting since it does NOT say that a generated
name has to be different from the generated names of other columns of
the same SELECT; rather it has to be different from names of columns
in base tables used by the SELECT. Not sure why they did that. We
don't really honor this rule at present.

In any case, rules (a) and (b) *directly* contradict the notion that
output column names can be guaranteed unique.

Also notice that generated names are "implementation-dependent" not
"implementation-defined" --- that means that we do not have to document
what the generation process is, nor promise that it won't change.
This means that any application that depends on generated names to be
particular things is violating the spec, and has only itself to blame
when it breaks. You should be using AS if you want stable names to
refer to these columns with.

I haven't looked at SQL99 or SQL2003, but most likely they say the same
thing in two or three times as many words ;-)

regards, tom lane