That's great! I'm an end-user and cannot compile it to check myself. Thanks to shane, drh, Hub Dog for the rapid fix. I will have to dig around to figure out how/when that shows up in a release.
That leaves the question about the change of syntax between 3.5.4 and 3.6.17. In the earlier it was sufficient to override the unavailable collation with a COLLATE clause at the first instance of the field if it is to be later compared in a WHERE clause, e.g.: SELECT Name COLLATE NOCASE FROM AddressTable WHERE Name LIKE '%_'; By release 3.6.17, it was necessary to put the COLLATE clause where the comparison is made, e.g.: SELECT Name FROM AddressTable WHERE Name COLLATE NOCASE LIKE '%_'; 3.5.4 was happy either way. However, if this latter SELECT is a virtual table the subject of another SELECT stmt, then both 3.5.4 and 3.6.17+ throw up an error: "No such collation sequence: xxxxx" whereas 3.5.4 is happy if the first statement is the virtual table. For both 3.5.4 and 3.6.17 to be happy, COLLATE NOCASE must be used twice: SELECT * FROM (SELECT Name COLLATE NOCASE FROM AddressTable WHERE Name COLLATE NOCASE LIKE '%_'); OR the collated field must be renamed in the form of the first statement: SELECT * FROM (SELECT Name COLLATE NOCASE AS Nuts FROM AddressTable WHERE Nuts LIKE '%_'); The ORDER BY clause operated differently in 3.5.4, requiring the COLLATE clause within, despite its earlier use in the SELECT expr. Leading to SELECT Name COLLATE NOCASE FROM AddressTable WHERE Name LIKE '%_' ORDER BY Name COLLATE NOCASE; as sufficient for 3.5.4 SELECT Name FROM AddressTable WHERE Name COLLATE NOCASE LIKE '%_' ORDER BY Name COLLATE NOCASE; as necessary for 3.6.17+ and acceptable to 3.5.4 and these, as necessary for both: SELECT * FROM (SELECT Name COLLATE NOCASE FROM AddressTable WHERE Name COLLATE NOCASE LIKE '%_' ORDER BY Name COLLATE NOCASE); -- OR -- SELECT * FROM (SELECT Name COLLATE NOCASE AS Nuts FROM AddressTable WHERE Nuts LIKE '%_' ORDER BY Nuts); So it appears that the later versions are more rigorous than 3.5.4, having a consistent requirement for the collation override in any clause where comparison is made on a field with a missing, application dependent collation sequence, rather than an inferred override from a previous declaration. The trap then is that 'lazy' overrides allowed in 3.5.4 will trigger a "No such collation sequence" in later versions. I think I prefer the 'lazy' override for both WHERE and ORDER BY but perhaps there is good reason to require more explicit COLLATION declarations. Tom "Hub Dog" <hub...@gmail.com> wrote in message news:4de0b9581001220719u72a3857eq88b674a14d265...@mail.gmail.com... >I can confirm that the crash problem has been fixed by > http://www.sqlite.org/src/info/1258875e07 checked-in . > Now executing following sql will report no such collation sequence: > RMNOCASE > instead of crash. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
