Search is case-sensitive for non-ASCII characters on pysqlite
|Reported by:||Owned by:|
|Severity:||major||Keywords:||pysqlite i18n needinfo|
Steps to reproduce:
- Set up Trac with pysqlite as database backend.
- In a blank environment, create two tickets, one named "Проблема", another "Вторая проблема" ("Problem" and "Another problem" in Russian, respectively). Note that "П" is uppercase in the first ticket, and lowercase in the second.
- Using Trac search box, search for "Проблема", then for "проблема".
- In either search, both tickets are expected to appear in the results.
- In either search, only one of the tickets appears in the results.
A quote from the SQLite docs:
A bug: SQLite only understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.
— SQL As Understood By SQLite, section The LIKE and GLOB operators
Below is the transcript of a Python session which reproduces the problem in vanilla pysqlite:
Python 2.7 (r27:82525, Jul 4 2010, 09:01:59) [MSC v.1500 32 bit (Intel)] on win32 Type "help", "copyright", "credits" or "license" for more information. >>> import sqlite3 >>> cnn = sqlite3.Connection(':memory:') >>> r1 = cnn.execute('select "IT" like "it";') >>> list(r1) [(1,)] >>> r2 = cnn.execute('select "ФУ" like "фу";') >>> list(r2) [(0,)]