I was bit by this today while busily engaged in some related work on my web app project. What the heck?!
"The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension."
FAQ item (18) Case-insensitive matching of Unicode characters does not work, at https://sqlite.org/faq.html :
"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."
If I am distributing an app that uses the installed SQLite, you expect me to build a special version of SQLite and bundle it with my app?? Or require some weird extensions?
Unicode is about 300,000 characters at most.
That is, do you compile and distribute the version from sqlite.org or do you depend on someone else to package it for you?
If the former, then change the compile flags.
If the latter, isn't the problem that your distro/packaging provider didn't enable the flags you wanted? Complain to them.
As a quick look, I see https://github.com/karlb/sqlite-icu claims "includes a loadable icu extension module for sqlite. This allows other python packages to use this extension without requiring dependencies outside of the python ecosystem". I've never tried it.
There are also alternate sqlite bindings for Python, like https://github.com/coleifer/cysqlite .
I'm saying the reasons given for certain architectural choices do not make much sense to me. And I'm also using this example to call out people who make claims that SQLite is on par with PostgreSQL for production use.
3336 bytes for the case tables are not comparable to the ICU mess, and easily shippable.
For one, it means extra work by the sqlite developers, to track the ever-changing Unicode spec, or your releases.
For another, the MIT license of the generated code is incompatible with the public domain dedication of sqlite.
For a third, sqlite is often part of a larger system, which will likely have its own way of doing Unicode. If there's a mismatch between the supported Unicode versions then there may be subtle errors not easily caught in testing. While if only ASCII is supported, leaving it up to the application programmer to implement the correct collation method, then that's easy to detect and resolve.
These are just the thoughts I have. I have no affiliation with the sqlite project.