ORDER BY 1 COLLATE

Peter Eisentraut <peter_e@gmx.net> writes:

This came from a review by Noah Misch a great while ago:
test=> SELECT b FROM foo ORDER BY 1 COLLATE "C";
ERROR: 42804: collations are not supported by type integer

According to SQL92, this should be supported. Do we want to bother? It
doesn't look hard to fix, so it's really only a question of whether this
would be useful, or its absence would be too confusing.

The ORDER BY 1 business seems to me to be legacy anyway. I'm not
inclined to put in even more hacks to make strange combinations work
there --- I think we're likely to find ourselves painted into a corner
someday as it is.

regards, tom lane

On 04/18/2011 04:20 PM, Tom Lane wrote:

Peter Eisentraut<peter_e@gmx.net> writes:

This came from a review by Noah Misch a great while ago:
test=> SELECT b FROM foo ORDER BY 1 COLLATE "C";
ERROR: 42804: collations are not supported by type integer
According to SQL92, this should be supported. Do we want to bother? It
doesn't look hard to fix, so it's really only a question of whether this
would be useful, or its absence would be too confusing.

The ORDER BY 1 business seems to me to be legacy anyway. I'm not
inclined to put in even more hacks to make strange combinations work
there --- I think we're likely to find ourselves painted into a corner
someday as it is.

It's likely to be used by SQL generators if nothing else, and I've been
known to use it as a very convenient shorthand. It would seem to me like
quite a strange inconsistency to allow order by n with some qualifiers
but not others.

cheers

andrew

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
owner@postgresql.org] On Behalf Of Andrew Dunstan
Sent: Monday, April 18, 2011 1:43 PM
To: Tom Lane
Cc: Peter Eisentraut; pgsql-hackers
Subject: Re: [HACKERS] ORDER BY 1 COLLATE

On 04/18/2011 04:20 PM, Tom Lane wrote:

Peter Eisentraut<peter_e@gmx.net> writes:

This came from a review by Noah Misch a great while ago:
test=> SELECT b FROM foo ORDER BY 1 COLLATE "C";
ERROR: 42804: collations are not supported by type integer
According to SQL92, this should be supported. Do we want to bother?

It

doesn't look hard to fix, so it's really only a question of whether

this

would be useful, or its absence would be too confusing.

The ORDER BY 1 business seems to me to be legacy anyway. I'm not
inclined to put in even more hacks to make strange combinations work
there --- I think we're likely to find ourselves painted into a

corner

someday as it is.

It's likely to be used by SQL generators if nothing else, and I've been
known to use it as a very convenient shorthand. It would seem to me
like
quite a strange inconsistency to allow order by n with some qualifiers
but not others.

I use order by <result_set_column_number> a lot, especially when result_set_column is a complicated expression.

Andrew Dunstan <andrew@dunslane.net> wrote:

It's likely to be used by SQL generators if nothing else, and I've
been known to use it as a very convenient shorthand. It would seem
to me like quite a strange inconsistency to allow order by n with
some qualifiers but not others.

That's pretty much how I feel. Like SELECT * or an INSERT without a
target column list, I wouldn't want to see it used in production,
but it saves time when hacking around in a development database or
running ad hoc queries. If we didn't support it, the inconsistency
would be odd, and we would need to document it as a deviation from
the standard.

-Kevin