Determine collation associated with sort

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Determine collation associated with sort

curmudgeon
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Determine collation associated with sort

Luuk
On 30-6-2018 14:05, x wrote:

> 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?
>
>

I think it's explaned in the docs here:
https://sqlite.org/datatype3.html#collation


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Determine collation associated with sort

Keith Medcalf
In reply to this post by curmudgeon

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-
>[hidden email]] On Behalf Of x
>Sent: Saturday, 30 June, 2018 06:06
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Determine collation associated with sort

curmudgeon
In reply to this post by Luuk

>I think it's explaned in the docs here:
>https://sqlite.org/datatype3.html#collation

Thanks Luuk. That’s what I was looking for.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users