Thread: non-integer constant in ORDER BY: why exactly, and documentation?
SELECT * FROM generate_series(1,10) ORDER BY 'foo';
ERROR: non-integer constant in ORDER BY
LINE 1: SELECT * FROM generate_series(1,10) ORDER BY 'foo';
The query was generated by an app (and the result somewhat inadvertent), so it was easy enough to change and I'm not asking here about a practical problem.
I am curious though about why this "limitation" exists. I get that integer constants are reserved for sorting by column numbers. But if Postgres already knows that it's a non-integer constant, why not let it go through with the (admittedly pointless) ordering?
Also, I couldn't see that this was explictly mentioned in the documentation. The relevant pieces seemed to be:
Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.
followed closely by:
It is also possible to use arbitrary expressions in the ORDER BY clause, including columns that do not appear in the SELECT output list.
(http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-ORDERBY)
And looking at the expressions page (http://www.postgresql.org/docs/8.4/static/sql-expressions.html), the first type of value expression is a "constant or literal expression." So nothing seems to explicitly rule out a literal ORDER BY.
I'm not sure if it would do violence to something I'm missing, but would the following combined statement work for the documentation?
"Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression. The expression can include column values--whether they appear in the SELECT output list or not. An expression may not, however, consist solely of a non-integer constant. And an integer constant will be interpreted as the ordinal number of an output column "
Thanks in advance.
Ken
--
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ken Tanzer
Sent: Thursday, October 11, 2012 4:49 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] non-integer constant in ORDER BY: why exactly, and documentation?
Hi. I recently ran a query that generate the same error as this:
SELECT * FROM generate_series(1,10) ORDER BY 'foo';
ERROR: non-integer constant in ORDER BY
LINE 1: SELECT * FROM generate_series(1,10) ORDER BY 'foo';
The query was generated by an app (and the result somewhat inadvertent), so it was easy enough to change and I'm not asking here about a practical problem.
I am curious though about why this "limitation" exists. I get that integer constants are reserved for sorting by column numbers. But if Postgres already knows that it's a non-integer constant, why not let it go through with the (admittedly pointless) ordering?
Also, I couldn't see that this was explictly mentioned in the documentation. The relevant pieces seemed to be:
Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.
followed closely by:
It is also possible to use arbitrary expressions in the ORDER BY clause, including columns that do not appear in the SELECT output list.
(http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-ORDERBY)
And looking at the expressions page (http://www.postgresql.org/docs/8.4/static/sql-expressions.html), the first type of value expression is a "constant or literal expression." So nothing seems to explicitly rule out a literal ORDER BY.
I'm not sure if it would do violence to something I'm missing, but would the following combined statement work for the documentation?
"Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression. The expression can include column values--whether they appear in the SELECT output list or not. An expression may not, however, consist solely of a non-integer constant. And an integer constant will be interpreted as the ordinal number of an output column "
I would categorize this under “help people avoid shooting themselves in the foot”. A possible situation is that the user meant to use double-quotes to specify an identifier but instead used single quotes. Since a literal constant would not impact the sort order the planner should either discard it silently or throw an exception. The exception is preferred since the presence of a constant literal likely means whatever generated the query is broken and should be fixed.
The documentation tweak probably is overkill given the rarity of the issue and the fact the system generates an appropriate error message when it does occur.
David J.
Ken Tanzer <ken.tanzer@gmail.com> writes: > Hi. I recently ran a query that generate the same error as this: > SELECT * FROM generate_series(1,10) ORDER BY 'foo'; > ERROR: non-integer constant in ORDER BY > I am curious though about why this "limitation" exists. I get that integer > constants are reserved for sorting by column numbers. But if Postgres > already knows that it's a non-integer constant, why not let it go through > with the (admittedly pointless) ordering? I think the argument was that it's almost certainly a mistake, so we're more helpful by throwing an error than by silently executing a query that probably won't do what the user was expecting. In this particular example, it seems quite likely that the programmer meant "foo" (ie a quoted column reference) and got the quote style wrong ... regards, tom lane
On Oct 11, 2012, at 4:48 PM, Ken Tanzer wrote: > Hi. I recently ran a query that generate the same error as this: > > SELECT * FROM generate_series(1,10) ORDER BY 'foo'; > ERROR: non-integer constant in ORDER BY > LINE 1: SELECT * FROM generate_series(1,10) ORDER BY 'foo'; > > The query was generated by an app (and the result somewhat inadvertent), so it was easy enough to change and I'm not askinghere about a practical problem. > > I am curious though about why this "limitation" exists. I get that integer constants are reserved for sorting by columnnumbers. But if Postgres already knows that it's a non-integer constant, why not let it go through with the (admittedlypointless) ordering? > > Also, I couldn't see that this was explictly mentioned in the documentation. The relevant pieces seemed to be: > > Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expressionformed from input-column values. > > followed closely by: > > It is also possible to use arbitrary expressions in the ORDER BY clause, including columns that do not appear in the SELECToutput list. > (http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-ORDERBY) > > And looking at the expressions page (http://www.postgresql.org/docs/8.4/static/sql-expressions.html), the first type ofvalue expression is a "constant or literal expression." So nothing seems to explicitly rule out a literal ORDER BY. > > I'm not sure if it would do violence to something I'm missing, but would the following combined statement work for thedocumentation? > > "Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression. The expression can include column values--whether they appear in the SELECT output list or not. An expressionmay not, however, consist solely of a non-integer constant. And an integer constant will be interpreted as theordinal number of an output column " Apparently, the parser tries to pull an column index out of any constant appearing in that position. It can be triviallyworked around: select * from generate_series(1,10) order by coalesce('foo'); but that doesn't help if your query is automatically generated. Cheers, M
On Thu, Oct 11, 2012 at 2:07 PM, A.M. <agentm@themactionfaction.com> wrote: > On Oct 11, 2012, at 4:48 PM, Ken Tanzer wrote: > select * from generate_series(1,10) order by coalesce('foo'); Another workaround is select bar.* from generate_series(1,3) as bar, (values ('foo')) as foo order by foo; or even simpler select * from generate_series(1,3) order by (values ('foo')); or my favorite select * from generate_series(1,3) order by 'foo'::text; -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +14158679984
I think the argument was that it's almost certainly a mistake, so we're
more helpful by throwing an error than by silently executing a query
that probably won't do what the user was expecting. In this particular
example, it seems quite likely that the programmer meant "foo" (ie a
quoted column reference) and got the quote style wrong ...
I guess it depends what you mean by mistake. In this case, here was the actual code involved:
if ($GLOBALS['AG_DEMO_MODE']) {
$label_field="'XXXXXX, XXX'";
} else {
$label_field= $object . '_name(' . $id_field . ')'; // e.g., client_name(client_id)
}
$op .= selectto('objectPickerPickList',$obj_opt )
. do_pick_sql("SELECT $id_field AS value,$label_field AS label FROM " . $def['table'] . " ORDER BY $label_field")
...
So yes there are lots of workarounds (and thanks all for the suggestions), including for this case just "ORDER BY 2". And there surely are better ways to code this, but finding areas for potential improvement is a target-rich environment, and one usually in need of prioritization. In this case, there's no reason the code above _couldn't_ have been adequately functional, had not some well-meaning software gotten in the way by trying to watch out for me... ;)
Then again, my personal Postgres score of times it has helped me versus times it has not is probably about 1.5 million to 7, so don't hear this as a giant grumble or complaint. I really was more curious than anything...
Cheers,
Ken
Ken Tanzer <ken.tanzer@gmail.com> writes:
> Hi. I recently ran a query that generate the same error as this:
> SELECT * FROM generate_series(1,10) ORDER BY 'foo';
> ERROR: non-integer constant in ORDER BY> I am curious though about why this "limitation" exists. I get that integerI think the argument was that it's almost certainly a mistake, so we're
> constants are reserved for sorting by column numbers. But if Postgres
> already knows that it's a non-integer constant, why not let it go through
> with the (admittedly pointless) ordering?
more helpful by throwing an error than by silently executing a query
that probably won't do what the user was expecting. In this particular
example, it seems quite likely that the programmer meant "foo" (ie a
quoted column reference) and got the quote style wrong ...
regards, tom lane
--
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
On 2012-10-11, David Johnston <polobo@yahoo.com> wrote: > This is a multipart message in MIME format. > > ------=_NextPart_000_0400_01CDA7D1.CAF1CC60 > Content-Type: text/plain; > charset="us-ascii" > Content-Transfer-Encoding: 7bit > > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ken Tanzer > Sent: Thursday, October 11, 2012 4:49 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] non-integer constant in ORDER BY: why exactly, and > documentation? > > > > Hi. I recently ran a query that generate the same error as this: > > SELECT * FROM generate_series(1,10) ORDER BY 'foo'; > ERROR: non-integer constant in ORDER BY > LINE 1: SELECT * FROM generate_series(1,10) ORDER BY 'foo'; yeah, it seems there's a difference between a constant and a constant-valued expression SELECT * FROM generate_series(1,10) ORDER BY 'foo'::text; SELECT * FROM generate_series(1,10) ORDER BY 1::int desc; SELECT * FROM generate_series(1,10) ORDER BY 1 desc; -- ⚂⚃ 100% natural