(my apology for trying to reply to a message in the archive which has no
reply-to feature – I hope it ends up in the right thread)
Richard, I think I know why your test of the command line shell returned no
error – an index is needed on the field with the missing collation. I am
guessing what has changed is that the query optimiser now (unnecessarily?)
selects an appropriate index, if one exists, even for a simple SELECT with
no ordering or other function desirous of an index.
Revising my example:
CREATE TABLE SourceTable (SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE
CUSTOM, RefNumber TEXT, ...);
CREATE INDEX idxSourceName ON SourceTable (Name); (sorry, I failed to
include this)
Error not thrown:
SELECT RefNumber ... or any combination of fields, INCLUDING the Name field
(the only one to have the proprietary collation), AS LONG AS one of the
fields is other than the primary key and the Name (I think this more
accurately describes the results)
“Error: no such collation sequence: CUSTOM”:
SELECT Name FROM ...
SELECT SourceID FROM ...
SELECT SourceID, Name FROM ...
-- SELECT Name and any combination of other fields FROM ... (I got that
wrong)
Further, even with ORDER BY, prior versions used to tolerate collation
override
SELECT Name COLLATE NOCASE ... FROM table ORDER BY Name
The current versions throw the error.
A new observation is that:
IF a second index exists on a field that uses a non-missing collation, e.g.,
CREATE INDEX idxRefNumber ON SourceTable(RefNumber);
THEN
Error not thrown:
SELECT SourceID FROM ...
i.e., the query optimiser cannot choose between the indexes.
I doubt that the use of an index on these simple SELECTs is of any benefit
and suspect that it may be an unintended consequence of some other
improvement to the query optimiser.
Thanks for your attention.
Tom
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
