order by k-th column

[trueqbit]

In the CTE branch I introduced the ability to sort via column number by passing compile-time integral constants to order_by like order_by(1_nth_col). Yevgeniy thought that this is most likely over-engineering for something that is already working, like order_by(1).

However, sqlite_orm currently always treats the thing passed to order_by as a regular sub expression, and replaces the integer by a question mark, because it is 'bindable'.

Preparing a parameterized statement

select id from object order by ?

is not the same as an unparameterized

select id from object order by 1

.
Executing those two statements yield different results; the parameterized is not sorted at all. SQlite wouldn't even compile such a compound statement and would immediately tell that there's no such column alias.

I am not saying that sqlite_orm's current implementation is wrong.
I merely set out to escape the bindable mechanism, utilizing std::integral_constant for that purpose.

I think that std::integral_constant, or better yet a struct derived from it, serves the following 3 purposes well:

• Escape sqlite_orm's binding mechanism
• Constant literal value (for serialization to SQL, as well as in C++)
• sqlite_orm is able to check the syntax, i.e. whether the ORDER BY column number is within K columns of the result set.

Also see The ORDER BY clause, stating "If the ORDER BY expression is a constant integer K then the expression is considered an alias for the K-th column of the result set".

At the same time I would also set out to reject passing arithmetic types and strings to order_by, because it doesn't make sense and leads to errors when preparing statements.
The only thing that baffles me is that statements like select 1 order by 1+0 or select 1 order by 'abc' is accepted by SQlite, just not bringing about anything useful; which leads to "doesn't make sense" and sqlite_orm should reject it.

[fnc12]

If ORDER BY ? is a no-op then we can disable bindings for order_by_t so it will be serialized as ORDER BY 1 always when user writes order_by(1). But I'd like to get some proves that it is a no-op

[trueqbit]

Proof is here: storage.select(&Object::n, order_by(1)) == std::vector<int>{1, 0}.

I wouldn't go this far as to completely disable bindings for order_by_t, because you would block out any other potentially valid ORDER BY expression, including placeholders;
e.g.

select id from object order by case when id > 2 then 0 else 1 end

is perfectly valid and can involve placeholders (bindables).

I understand that you are reluctant to introduce yet another thing, which is good. However I want to convince you that user-defined numeric literals are a good thing here as per the points mentioned above 😃
Until C++ gets P1045 "constexpr Function Parameters" one day, a user-defined literal is the closest as we can get to having compile-time values in a kind of natural syntax.

What's speaking against capturing a value into a compile-time constant value? I find the ability to check the syntax at compile-time very compelling.

[fnc12]

I like compile-time constant value but I am not sure that ORDER BY 1 will be expected to be translated to C++ as order_by(1_nth_col) but order_by(1) cause it is intuitive. The better solution is to provide support for order_by(1)

[trueqbit]

Then what do you do about int x = 5; order_by(x);? It's not a constant literal value expression.
Also, many other SQL-syntax constructs need to be circumvented in order to be usable in C++.

[fnc12]

We will find the answer if we'll understand what is the purpose of the same code with raw libsqlite when user writes a query like SELECT * FROM users ORDER BY ? and binds int at runtime. I'd test it. Probably SQLite doesn't allow binding such things just like LIKELYHOOD function. It doesn't allow to bind the second argument.

[trueqbit]

Did you see the link to my branch above? SQLite allows binding such things: it's a complete expr.

[fnc12]

If SQLite allows binding such things then I believe it has some purpose. Probably ORDER BY ? changes order term. Did you test it?

[trueqbit]

I asked on the SQLite forum about single parameters as ORDER-BY expressions.
Larry and Gunter replied that it's just one of many syntactically valid expressions, and not worth to rule it out at the SQLite parser level.

It's like it's upon the user to decide not to use it, and we can decide whether we simply allow or disallow such a construct.

Well, whatever we do, what I wanted to accomplish is forming a positional ordinal.
However I twist and turn it in my head, a literal user-defined suffix is the C++ way of capturing a true literal to form such a positional ordinal in the resulting SQL statement. It has the precise properties of never being alterable, not being "aliasable". This is simply not possible with built-in literals. Therefore, it seems that custom literal suffixes were invented precisely for these purposes. It might seem a bit impractical at first, but it has the benefit of adding contextual meaning as well.

See PR #966 showcasing how I imagine the implementation.

[fnc12]

It is a very nice discussion thanks Klaus. Looks like you broke these guys right after your post with C++ code with templates 😀. So it looks like we can just disable binding for order_by to make expressions like order_by(1) work.
About literals: please note that literals in SQLite is not the same that literals in C++. When you run a query like this:

auto a = 5;
auto b = 5;
auto rows = storage.select(c(a) + b);  // line 3

the line 3 doesn't has any C++ literal but does have 2 SQLite literals. Why so? Because SQLite expression's compilation happens inside storage.prepare call and literal is a value known at compilation time. That is why both

storage.select(c(5) + 5);

and

storage.select(c(a) + b);

contain literals. So I don't see any reason of prohibiting passing C++ variables/constants as SQLite literals. That is I don't see any reason (yet) of adding 1_nth_col literal feature. Let's disable bindings for order_by and that will be enough. If some expressions will add more functionality inside ORDER BY in future then we'll add a dedicated support for that.

[trueqbit]

I think we're talking past each other.
I am thinking about a positional ordinal.
What is a literal for you? How exactly is it a literal and at what point? Neither c(5) nor 5 results in a literal known at compilation time, neither in C++ nor in the resulting SQL statement.

[fnc12]

Literal is a constant with no name known at compilation time. E.g. SELECT id, name FROM users ORDER BY 1 has 1 as literal. If we disable bindings for order_by when writing order_by(2) will also produce such literals for SQLite no matter how 2 is stored in C++: literal, named constant or variable.