"Chuck McDevitt" <cmcdevitt@greenplum.com> writes:
> Except "group by 1" meaning "group by column 1" is a PostgreSQL extension, not a SQL standard feature, if I recall.
Correct. Part of the reason for being conservative about changing here
is that we've got a mix of standard and nonstandard behaviors with
to-some-extent conflicting behavior. Rejecting cases that are on the
borderline between the behaviors seems like a safer course than
accepting them and maybe doing something different than the user
expects.
A lot of this is legacy behavior that would never have passed muster
if it had been newly proposed in the last few years --- we have gotten
*far* stricter about SQL compliance than we used to be. But at this
point backwards compatibility also has to weigh heavily.
> Expressions in ORDER BY are a PostgreSQL extension also...
Nyet --- they are required by SQL99 and later. SQL92 and before
had "ORDER BY output-column-name-or-number" (and nothing else).
SQL99 replaced that with ORDER BY <expression>, which they then
bastardized so that it could include output column names, allowing
them to claim that they'd only eliminated the output-column-number
variant. What we support is a rather unholy combination of the two
generations of the spec. People are quite used to ORDER BY 1 and
so I doubt we'll ever want to eliminate the special case for it.
regards, tom lane