Edgewall Software

Opened 11 years ago

Closed 10 years ago

Last modified 4 years ago

#10327 closed defect (cantfix)

Search is case-sensitive for non-ASCII characters on pysqlite

Reported by: Oleg Frantsuzov <franoleg@…> Owned by:
Priority: normal Milestone:
Component: database backend Version: 0.12.2
Severity: major Keywords: pysqlite i18n needinfo
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:


Steps to reproduce:

  1. Set up Trac with pysqlite as database backend.
  2. 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.
  3. Using Trac search box, search for "Проблема", then for "проблема".

Expected behavior:

  • In either search, both tickets are expected to appear in the results.

Actual behavior:

  • In either search, only one of the tickets appears in the results.

Additional info

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)
>>> r2 = cnn.execute('select "ФУ" like "фу";')
>>> list(r2)

Attachments (0)

Change History (3)

comment:1 by Christian Boos, 11 years ago

Keywords: needinfo added

Yes, another reference is the FAQ: http://www.sqlite.org/faq.html#q18. That one suggests that the ICU extension for SQLite could be used to achieve this goal.

You could try to compile that extension and then it should be possible to load it dynamically (#7902) via the trac.ini's [sqlite] extensions setting. If you can't get the dynamic loading to work, it should still be possible to build a SQLite version with the SQLITE_ENABLE_ICU flag and making sure PySqlite uses it.

Please let us know if this worked for you, thanks!

comment:2 by Christian Boos, 10 years ago

Resolution: cantfix
Status: newclosed

(if someone is able to check the validity of the advice given above, let us know)

comment:3 by bebugz@…, 4 years ago

Solution with extension compilation truly works. Leaving full recipe here for Ubuntu Server:

$ apt install libsqlite3-dev icu-devtools libicu-dev
$ wget https://www.sqlite.org/src/raw/ext/icu/icu.c?name=c2c7592574c08cd1270d909b8fb8797f6ea1f49e931e71dbcc25506b9b224580 -O icu.c
... change sqlite3_icu_init to sqlite3_sqliteicu_init
$ gcc -shared icu.c `icu-config --ldflags` -fPIC -o libSqliteIcu.so

In trac.ini:

extensions = /full/path/libSqliteIcu
Last edited 4 years ago by Ryan J Ollos (previous) (diff)

Modify Ticket

Change Properties
Set your email in Preferences
as closed The ticket will remain with no owner.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from (none) to the specified user.

Add Comment

E-mail address and name can be saved in the Preferences .
Note: See TracTickets for help on using tickets.