Remember that comparing two strings ignoring differences in case (or accents, etc.) is a fairly locale-specific operation. The default Unicode collation algorithm tries to be as agnostic as it can of locale rules, but it falls down spectacularly for a few languages.

For example, in T1191, you mentioned that you don't case if "Diffusion" and "DIFFUSION" collide. In English, that's OK, but in Turkish, "i" and "I" are totally different letters. ("i" capitalizes to 'İ' in Turkish, and 'I' lower-cases to 'ı'. They are not related letters any more than 'i' and 'j' are in English.)

In addition, the Unicode collation algorithm doesn't deal with differences in accents or special cases like German ß mapping to SS in upper-case, or position-sensitive letters like Greek sigma σ, which is ς at the end of a word.

Because of this, it's usually a good idea to apply collation rules only when you know the locale of the viewer. If you want the database to cache locale-specific collation rules for performance, you can use functionality like ICU's collation keys, which are available to PHP via the Collator class. (Just be sure that the keys are discarded between PHP or ICU version upgrades.)

http://php.net/manual/en/class.collator.php

Yeah -- we'll probably pursue that eventually, but I think the cost-to-benefit isn't worthwhile today. In particular, users can't even select Turkish or German locales yet, we've never received user reports about collation issues, occasional incorrect collisions / sub-optimal sorts have reasonable workarounds (rename projects/documents; just scroll through the list), and it won't get much harder to fix over time if we put it off for now.

(In contrast, the meat of T1191 has no workarounds, affects an increasing number of users, and gets harder to fix over time a lot faster.)

Sure—as long as the original data is present and we can rebuild collation indices later, it probably doesn't matter too much. (I'm not sure how the migration would work, but hopefully it's not lossy.)

Yeah, there's no data loss here. To migrate, we'd:

• Create some new <objectPHID, field, locale, collationKey> table in databases which need it.
• We join that table to express ORDER BY, using the viewer's locale to select which locale we examine.
• When objects get their search index updated, publish into the applicable tables (we already publish similar data for other reasons).
• Historic data can be rebuilt online by the daemons with bin/search index --all --background.
• Convert the sort (_unicode_ci) columns to text (_bin) columns, basically undoing this diff.
• This would fix all collation issues and make sorting locale-dependent, but allow "Diffusion" and "DIFFUSION" again without collision. We can either accept that, or find some acceptable normalization function which collides only "bad" duplicates, but this has to be locale-independent.

Makes sense. Could just do that now, of course..

Oh, and:

This would fix all collation issues and make sorting locale-dependent, but allow "Diffusion" and "DIFFUSION" again without collision.

Well, they would have the same sort keys, so you could use that to collapse them if you'd like.

Makes sense. Could just do that now, of course..

Sure, but it would be a bunch of work and not actually make anything better for anyone since it's not possible to select any locale except English.

Well, they would have the same sort keys, so you could use that to collapse them if you'd like.

We'd have to pick a locale for the keys, though. We could say "project names must be unique when collated in en_US", but I don't know enough about locale issues to know how reasonable that is. If we choose "project names must be unique when collated in utf8mb4_unicode_ci" instead, which is probably about as good as en_US, we can just skip undoing the column collations.