SQLite will not select the collation based on the index -- it is exactly the
opposite -- the collation requested is used to find an appropriate index.
So if you do an order by that needs BINARY collation, and the only index
available is a NOCASE collation index, that index cannot be used (for the
purpose of ordering) and the rows must be sorted in a separate step. Ascending
and Descending is different. You can use and index of a different order if the
collation matches, you just have to do it backwards (ie, from end to start).
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Saturday, 30 June, 2018 06:06
>Subject: [sqlite] Determine collation associated with sort
>Suppose I have a select such as
>‘select * from TblsAndJoins where Condns order by
>I want to know the collation associated with the sort. I know a
>COLLATE condition could be attached to the order by BUT suppose
>there’s none. How would I determine the collation associated with the
>I’m familiar with https://sqlite.org/c3ref/table_column_metadata.html
> sqlite3 *db, /* Connection handle */
> const char *zDbName, /* Database name or NULLtion sequence
> const char *zTableName, /* Table name */
> const char *zColumnName, /* Column name */
> char const **pzDataType, /* OUTPUT: Declared data type */
> char const **pzCollSeq, /* OUTPUT: Collation sequence name */
> int *pNotNull, /* OUTPUT: True if NOT NULL constraint
> int *pPrimaryKey, /* OUTPUT: True if column part of PK */
> int *pAutoinc /* OUTPUT: True if column is auto-
>which would tell me if a particular column of the sort has a
>collation sequence but what if it’s the index itself that has a
>collation attached? How would I cover all possible angles?
>sqlite-users mailing list
sqlite-users mailing list