Ralf Junker wrote:
Are there any opinions on this list, or should I just post a bug ticket?
Ralf
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?
Ralf,
Issue #1 definitely looks like a bug to me. I would create a ticket to
ensure it gets addressed.
Issue #2 also looks like a bug to me. This particular query doesn't need
the collation, so it should be able to process the query without an error.
If sqlite was going to complain about any use of a table which uses an
unregistered collation, it should do so when the database is first
opened and the schema is parsed. It doesn't do this since the custom
collations can't be registered until after the database connection is
opened (it is needed as the first parameter to the create_collation call).
Given that, it can only hope to complain when compiling SQL that
requires the collation before it has been registered. Any SQL that does
not require the collation should be compiled and processed without
complaint.
I would suggest creating a bug ticket.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
