<quote> but if ORDER BY is relying on an index for ordering, then flip() can have negative effects.</quote>
Substr() could have negative effects on ordering too. That is a red herring. Flip() is merely a function that reverses the order of codepoints "as found" without knowing anything about what those codepoints, individually or in combination, might signify in a writing system. If I want to write those codepoints to a column that's my concern. What if I wanted to have a column that consisted of codepoints from all over the Unicode range: a codepoint from Greek next to a codepoint from Swahili next to a codepoint from Hungarian? Shouldn't I be able to say to a database: this column contains codepoints (characters) and collation is not relevant, sort the column using the numeric value of the codepoints? Tim Romano Nicolas Williams wrote: > On Tue, Nov 17, 2009 at 05:15:16PM -0500, Igor Tandetnik wrote: > >> Nicolas Williams <nicolas.willi...@sun.com> wrote: >> >>> This is no longer true, either of 'ch' nor 'll'. >>> >> There is a number of contractions in Hungarian that are still very >> much in use, but I can't recall them off the top of my head the way I >> can 'ch' (it's something like 'dzs'). There are also contractions in >> German Phonebook sort (e.g. 'oe' should sort between 'o with umlaut' >> and 'p', if I recall correctly). There are likely other cases. >> > > I'm not surprised :( > > >>> The principle you >>> state is correct, of course, but really, this is a collation problem, >>> and affects SQLite3 apps regardless of "flip()". >>> >> My point is, it's difficult to even define what the correct behavior >> of flip() should be, let alone implement one. And so the safest course >> of action is to leave it out of core SQLite: a developer in need of >> such a function would presumably know the nature of their data and >> precisely what they want the function to achieve, and can always >> implement it as a custom function. >> > > Maybe. For indexing, I don't see the harm as long as an index built > with this function isn't used for ORDER BY when you care about > collations (ah! SQLite3 couldn't tell this is happening without knowing > the semantics of the function). > > >>> The collation is >>> per-column, and the run-time should make functions aware of the >>> collation (if any) of a column when an argument. >>> >> What about >> >> select flip(EnglishText || GermanText || SpanishText) >> from MyMultilingualTable; >> > > No different than: > > select EnglishText || GermanText || SpanishText from MyMultilingualTable; > > the concatenation can create 'oe' and all those other whatever they are > called's. > > This is OK until you ORDER BY, and _then_ the collation requested or > inferred needs to apply. Ah, there should be no inference of collation > from function names, and functions shouldn't have to care about > collations "in effect" -- only ORDER BY should care, but if ORDER BY is > relying on an index for ordering, then flip() can have negative effects. > > Nico > > ------------------------------------------------------------------------ > > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 8.5.425 / Virus Database: 270.14.71/2510 - Release Date: 11/17/09 > 19:26:00 > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
