Yeah, I am aware of this issue, as I am using MySQL daily. Here is the
problem - if we force case-sensitive syntax for case-insensitive
searches on MySQL, this will break for case-sensitive (non-default)
collations. I am personally using case-sensitive LIKE searches in my
code explicitly, which makes such code MySQL-specific, which is
definitely not good, and I'd hate to recommend it to the users.
Checking and overriding collation for every column also doesn't seem
practical.
So not sure how to solve that in a reasonable way?
Andrus
On Apr 24, 2009, at 3:49 AM, Ari Maniatis (JIRA) wrote:
> mysql does not use index for case insensitive searches
> ------------------------------------------------------
>
> Key: CAY-1210
> URL: https://issues.apache.org/jira/browse/CAY-1210
> Project: Cayenne
> Issue Type: Improvement
> Reporter: Ari Maniatis
> Assignee: Ari Maniatis
> Fix For: 3.0
>
>
> When performing a case insensitive search Cayenne spits out SQL
> which looks like this
>
> SELECT .... WHERE upper(name) LIKE upper("fred")
>
> This prevents any index being used for the search. Since mysql
> already performed case insensitive searches on text fields we need
> to suppress the 'upper' functions being used in these situations.
> All searches on these fields are already case insensitive.
>
> http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
>
> --
> This message is automatically generated by JIRA.
> -
> You can reply to this email to add a comment to the issue online.
>
>
This archive was generated by hypermail 2.0.0 : Fri Apr 24 2009 - 02:03:15 EDT