Re: [Chicken-hackers] Collating prepared statements in sqlite3: bug?

[alaric]

> Quoth Thomas Chust on Setting Orange, the 62nd of Discord:
>> If you actually intended to make the column by which the query
>> results are ordered variable, the statement you used is not suitable
>> for that purpose, neither is any prepared statement.
>
> Thanks, Thomas; I wasn't aware of that limitation. Hard-coding the
> sorting column works, in any case; even if it's slightly less
> convenient.

Although some SQL engines will just interpolate strings in where ?s appear
(with some quoting to make them valid literals, as you discovered to your
horror), the general idea of a prepared statement is that the parsing of
the SQL and the generation of a query plan can be performed once, at
prepare time, and then the desired literal values interpolated directly
into the query plan when the prepared statement is used. This means that
wherever a ? appears in the statement to be prepared, the SQL parser will
parse that as a literal value (although the value is not yet known), so
that a query plan to order by a literal value (which is a no-op, I guess)
will be generated! Which field you order by would affect the optimal
structure of the query plan (ordering by an indexed field can be done by
just scanning that index in order, rather than needing to perform an
actual sort), so what we're ordering by really needs to be known at query
plan time.