Re: [sqlite] Undefined collation: Peculiar observations ...
Hello Trevor Talbot, >> Trevor, I am not sure what you mean by "subselect on its own". Is this what >> you are looking for? > >> sqlite> SELECT * FROM (SELECT * FROM a); >> SQL error: no such collation sequence: unknown > >Yes, exactly. I was curious to see if it made any kind of difference. >Unfortunately I don't have an explanation/fix for you though. I created two tickets about these collation peculiarities yesterday. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Undefined collation: Peculiar observations ...
On 11/28/07, Ralf Junker <[EMAIL PROTECTED]> wrote: > >> 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); > Trevor, I am not sure what you mean by "subselect on its own". Is this what > you are looking for? > sqlite> SELECT * FROM (SELECT * FROM a); > SQL error: no such collation sequence: unknown Yes, exactly. I was curious to see if it made any kind of difference. Unfortunately I don't have an explanation/fix for you though. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Undefined collation: Peculiar observations ...
>> 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:
>
>> 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);
>
>That's not just a subselect, it's also a join. Does a subselect on
>its own have the same behavior?
Thanks all for the feedback.
Trevor, I am not sure what you mean by "subselect on its own". Is this what you
are looking for?
sqlite> INSERT INTO a VALUES ('one');
sqlite> SELECT * FROM (SELECT * FROM a);
SQL error: no such collation sequence: unknown
sqlite> SELECT *, * FROM a;
one|one
Ralf
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Undefined collation: Peculiar observations ...
On 11/25/07, Ralf Junker <[EMAIL PROTECTED]> wrote: > 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: > 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); That's not just a subselect, it's also a join. Does a subselect on its own have the same behavior? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Undefined collation: Peculiar observations ...
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] -
