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 -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
