Обсуждение: ORDER BY 1 COLLATE

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.

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