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.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Saturday, 30 June, 2018 06:06
>To: sqlite-users@mailinglists.sqlite.org
>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
>int sqlite3_table_column_metadata(
>  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
>exists */
>  int *pPrimaryKey,           /* OUTPUT: True if column part of PK */
>  int *pAutoinc               /* OUTPUT: True if column is auto-
>increment */
>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

Reply via email to