Обсуждение: 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