search is case sensitive when using MySQL

[Christian Boos]

Doing some testing of source:branches/0.11-stable@8333​, I noticed that the search in MySQL is case sensitive.

Search is case insensitive for the other backends and used to be case insensitive for MySQL as well.

However, in the new guidelines given in MySqlDb (since MySqlDb@23), the default collation should now be utf8_bin and this is what makes the search case sensitive.

I suggest the following fix:

trac/db/mysql_backend.py

@@ -204 +204 @@
         return 'concat(%s)' % ', '.join(args)
 
     def like(self):
-        return "LIKE %s ESCAPE '/'"
+        return "LIKE %s COLLATE utf8_general_ci ESCAPE '/'"
 
     def like_escape(self, text):
         return _like_escape_re.sub(r'/\1', text)

[Remy Blank]

Won't that break some queries like CachedRepository._next_prev_rev() where LIKE is used to match paths?

Oh, wait, LIKE is case insensitive on the other backends anyway, so we have that issue already. Shouldn't that be fixed as well?

[Christian Boos]

You're right! However that secondary bug went unnoticed so far, so I don't think it's a big deal if we make MySQL have it as well…

Now for the second bug, almost all occurrences of like() should be changed to ilike().

We have then:

db/statement	like()	ilike()
PostgreSQL	LIKE	ILIKE
MySQL	LIKE	LIKE COLLATE utf8_general_ci
PySqlite	?	LIKE

(?) perhaps ​http://www.sqlite.org/pragma.html#pragma_case_sensitive_like
But I wonder how to make use of this in the middle of building a query (not to mention the theoretical possibility of having like() and ilike() in the same query)

[Christian Boos]

(?) perhaps ​http://www.sqlite.org/pragma.html#pragma_case_sensitive_like

Or some clever override of the like(X,Y,Z) function used in the Y LIKE X ESCAPE Z statement, with Z being (ab)used to convey the case sensitivity. See:

• ​http://www.sqlite.org/lang_corefunc.html#like
• ​http://initd.org/pub/software/pysqlite/doc/usage-guide.html#creating-user-defined-functions

[Jonas Borgström]

The mysql collation change is now committed in [8336] and [8337].

I'm leaving this ticket open but moving it to 0.11.6 until all ilike() changes are in place.

[Christian Boos]

I've created a new ticket for the ilike() changes: #8519.

Closing this ticket as the reported problem was fixed for 0.11.5.