In one of SQLite ORM's channels we are discussing about placeholders in ORDER-BY expressions, and to what extent we should prohibit placeholders just because they don't seem to make sense.

Consider e.g.

select 42 order by ?1

No matter which value will be bound to parameter ?1 the result set will always be unsorted.

Of course there are more complex and more useful ORDER-BY expressions, however the question remains why SQLite allows such parameterized statements:

1. by design.
2. oversight.
3. the principle of "be liberal in what you accept" as described in the quirks section.
4. something not worth the effort to rule out.

Thanks for any hints!

SQLite allows such because that last token is where an expression might be found in the syntax and, logically, an expression can be a single literal which may be expressed as a to-be-bound-later parameter. It would take more code and execution time to disallow the construct than to simply allow it as a legal instance of the language (which it is.) So, your alternative 4 is the answer.

This does raise an interesting issue though. It is a feature (or quirk) of SQL that an ORDER BY expression can be a simple integer greater than 0, in which case it can specify a result column rather than simply providing a value. One might imagine that by binding different integers to a parameter in the ORDER BY expression list that it would be possible to select different columns for the ordering. But it is not. Should it be?

I think it is a quirk that a natural number will be treated as a column index when used as the expression in an ORDER BY clause. Specifying a number n for a result column returns n for the value, not the value of the nth column.

To the contrary I believe that the ability to specify projection order via positional indicators in the order-by clause referencing the projection result was part of the original language specification way back in the begininning of time, and that the "design by committee" standard merely kept what was, by the time the committee did anything, already a "standard feature" of the language. Positional indication in group-by is an addition (though whether by committee or as a non-standard extension) that is uncommon.

In either case, if a parameter in the order-by clause can be used as a positional indicator in the projection result, then the same logic should also apply to parameters specified in the group-by clause in order to prevent "surprise".

One might imagine that by binding different integers to a parameter in the ORDER BY expression list that it would be possible to select different columns for the ordering. But it is not. Should it be?

That would be reasonable, but is not the standard (I do not believe). As you say, a parameter binding into the order clause is permitted, although it is not useful to do so.

Permitting that would be a non-standard extension to the language.

Quite similarly it is possible to request that the sqare root of a negative number be computed, although there is no capability to return a result (no SQL implemetations of which I am aware handle imaginary numbers -- they are confined to real numbers).

That is to say that just because something is possible, does not mean that it will work as intended. There is presently no intention anywhere in the SQL standard that a "parameter" can be used to represent anything other than a value.

I can see where it may be advantageous to permit a bound integer in the order by clause to represent a projection-position ordering, but that would not be portable anywhere else.

I can also see where it would be very useful, for example in queries used to populate a UI, so that the order-by of the projection can be altered without having to "generate" the SQL statement. (That is, the query would be unchanged except for the parameters specifying the key and ordering, whereas without such an ability the SQL query itself would have to be regenerated and prepared each time).

I think that modifying sort ordering criteria in the middle of any sorted result producing sequence is nonsensical. For that reason, I also think that using a bindable parameter as a whole expression in the comma-separated ORDER BY (or GROUP BY) list is nonsensical. IMO, such SQL should be accepted, but the term treated as a constant during query and not able to index into the result columns^a, and thereby ignored.

My question, "Should it be?", was rhetorical and intended to bring out the folly of using such constructs. Should they be "disallowed"? Maybe, if there was ever a sophisticated (non-Lite) warning system, they would draw a "Tsk, tsk", just as "SELECT NULL LIMIT 0" would.

---

a. Disallowing such a parameter to index into the columns is a practical requirement. It avoids a host of messy problems.

Probably exactly the same thing as would happen if you did that for any bound parameter: explode. The parameters are supposed to be held invariant during statement execution so this straw-man would have the same result as it presently does: the heat death of the multiverse.

Yes, I am aware that it's not a positional ordinal but a variable name.

Larry, Gunter, thanks for the plain text :)

Since it's a "fancy" way of expressing ORDER BY <constant>, but doesn't do anything useful (except perhaps play tricks with SQLite's optimizer?), it might make sense to forbid such simple "bindable" expressions at a language projection level (SQLite ORM is a C++ library). This is fairly cheap as it can be checked at compile time of the program itself rather than at SQLite parsing of the statement.

If you were using a library that is able to check expressions up front would you be happy if the library warns or disallows it? I don't want to start a discussion about library design, I just want to get some expert opinions or preferences.

To give you some context (C++ code):

int n = 5;
select(&User::name, from<User>(), order_by(n));

... is serialized to

select name from user order by ?1

Calling order_by(n) could be disallowed.