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 >OrdCol1,OrdCol2,...,OrdColn’ > >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 >sort? > >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@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
