Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table:
CREATE TABLE a (b COLLATE unknown); Now open this table in the default SQLite3 CLI. Up to here, everything works as expected. Now some peculiar observations: 1. Issue "PRAGMA collation_list;" and notice that "unknow" lists next to the other registered collations, even though "unknown" is not registered with the default SQLite3 CLI: sqlite> PRAGMA collation_list; 0|unknown 1|NOCASE 2|BINARY Question 1: Is this the expected behaviour, or should not "PRAGMA collation_list;" rather list registered collations only? 2. Running simple queries like "SELECT * FROM a;" work fine. But subselects, in their most basic form and with no sorting or comparisons, do not: sqlite> SELECT * FROM a, (SELECT * FROM a); SQL error: no such collation sequence: unknown This is surprising to me because I do not see where the collation sequence should matter to this query. To demonstrate, here is the explain output of a table with a registered collation sequence. No mention of the collation name here: sqlite> CREATE TABLE b (b collate nocase); sqlite> EXPLAIN SELECT * FROM b, (SELECT * FROM b); 0|Goto|0|17| 1|Integer|0|0| 2|OpenRead|0|3| 3|SetNumColumns|0|1| 4|Integer|0|0| 5|OpenRead|2|3| 6|SetNumColumns|2|1| 7|Rewind|0|14| 8|Rewind|2|13| 9|Column|0|0| 10|Column|2|0| 11|Callback|2|0| 12|Next|2|9| 13|Next|0|8| 14|Close|0|0| 15|Close|2|0| 16|Halt|0|0| 17|Transaction|0|0| 18|VerifyCookie|0|4| 19|TableLock|0|3|b 20|Goto|0|1| 21|Noop|0|0| Question 2: Why does this happen, and is there a way to work around the problem by issuing explicit collation sequences? Thanks, Ralf ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
