Follow-up to #2584243: Make Drupal handle path aliases in a consistent and case-insensitive fashion on all database drivers →
It is currently not possible to do a case-insensitive LIKE comparison on values with non-ASCII characters, SQLite needs to be compiled with the ICU extension for this:
(18) Case-insensitive matching of Unicode characters does not work.
The default configuration of SQLite only supports case-insensitive comparisons of ASCII characters. The reason for this is that doing full Unicode case-insensitive comparisons and case conversions requires tables and logic that would nearly double the size of the SQLite library. The SQLite developers reason that any application that needs full Unicode case support probably already has the necessary tables and functions and so SQLite should not take up space to duplicate this ability.
Instead of providing full Unicode case support by default, SQLite provides the ability to link against external Unicode comparison and conversion routines. The application can overload the built-in NOCASE collating sequence (using sqlite3_create_collation()) and the built-in like(), upper(), and lower() functions (using sqlite3_create_function()). The SQLite source code includes an "ICU" extension that does these overloads. Or, developers can write their own overloads based on their own Unicode-aware comparison routines already contained within their project.
Ref https://www.sqlite.org/faq.html#q18
TBD
One workaround may be to do UPPER(x) = UPPER(y) COLLATE NOCASE_UTF8, where NOCASE_UTF8 is the custom collation defined in the PDO driver.
Postponed: needs info
11.0 🔥
sqlite db driver
The change is currently missing an automated test that fails when run with the original code, and succeeds when the bug has been fixed.
Not all content is available!
It's likely this issue predates Contrib.social: some issue and comment data are missing.
The issue summary description is still accurate: https://www.sqlite.org/faq.html#q18 is still a thing. But per @amateescu at #3036487-3: SQLite driver should use COLLATE NOCASE instead of PDO custom collation (COLLATE NOCASE_UTF8) → , it looks like this was implemented years ago, in #2454733: Add a user-space case-insensitive collation to the SQLite driver → ? 😅