You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Nishant Neeraj <nn...@yahoo.com> on 2010/02/01 09:14:21 UTC
MySQL case sensitive search
Hi,
Is there a way to write an expression in Cayenne that performs case sensitive search over a column? Something like this:
SELECT ... FROM TEMP_USERS t0 WHERE binary t0.USER_NAME = 'CaMeL';
I am using MySQL. Is this something related to this?
http://n3.nabble.com/Re-jira-Created-CAY-1210-mysql-does-not-use-index-for-case-insensitive-searches-td14852.html
ThanksNishant
Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/
Re: MySQL case sensitive search
Posted by Andrus Adamchik <an...@objectstyle.org>.
Unfortunately this is not supported. Should probably add that MySQL-
specific feature.
Andrus
On Feb 1, 2010, at 10:14 AM, Nishant Neeraj wrote:
> Hi,
> Is there a way to write an expression in Cayenne that performs case
> sensitive search over a column? Something like this:
>
> SELECT ... FROM TEMP_USERS t0 WHERE binary t0.USER_NAME = 'CaMeL';
>
> I am using MySQL. Is this something related to this?
> http://n3.nabble.com/Re-jira-Created-CAY-1210-mysql-does-not-use-index-for-case-insensitive-searches-td14852.html
> ThanksNishant
>
>
> Your Mail works best with the New Yahoo Optimized IE8. Get it
> NOW! http://downloads.yahoo.com/in/internetexplorer/
Re: MySQL case sensitive search
Posted by Aristedes Maniatis <ar...@maniatis.org>.
On 1/02/10 7:30 PM, Aristedes Maniatis wrote:
> That task is just about optimising the use of indexes for searching case
> insensitive. Just use Cayenne's normal case insensitive flag on the
> query and it will work fine. Just not use any index.
Just ignore me. I misread your question, sorry.
Ari
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: MySQL case sensitive search
Posted by Lachlan Deck <la...@gmail.com>.
Taking to dev...
On 01/02/2010, at 7:31 PM, Andrus Adamchik wrote:
> MySQL is "special" as always:
>
> http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
Yep, also see:
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_convert
So it's easy enough to force case-sensitive comparisons using the binary keyword, but going the other way is more problematic. i.e., sure you can do a select to find the default collation for the database but this is not really good enough. This can be overridden per table and then again per column.
Thoughts Andrus?
(This is relevant discussion for CAY-1210).
with regards,
--
Lachlan Deck
Re: MySQL case sensitive search
Posted by Andrus Adamchik <an...@objectstyle.org>.
Ari, there's more to it. MySQL is "special" as always:
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
Andrus
On Feb 1, 2010, at 10:30 AM, Aristedes Maniatis wrote:
> That task is just about optimising the use of indexes for searching
> case insensitive. Just use Cayenne's normal case insensitive flag on
> the query and it will work fine. Just not use any index.
>
> Ari
>
> On 1/02/10 7:14 PM, Nishant Neeraj wrote:
>> Hi,
>> Is there a way to write an expression in Cayenne that performs case
>> sensitive search over a column? Something like this:
>>
>> SELECT ... FROM TEMP_USERS t0 WHERE binary t0.USER_NAME = 'CaMeL';
>>
>> I am using MySQL. Is this something related to this?
>> http://n3.nabble.com/Re-jira-Created-CAY-1210-mysql-does-not-use-index-for-case-insensitive-searches-td14852.html
>> ThanksNishant
>>
>>
>> Your Mail works best with the New Yahoo Optimized IE8. Get it
>> NOW! http://downloads.yahoo.com/in/internetexplorer/
>
> --
> -------------------------->
> Aristedes Maniatis
> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
>
Re: MySQL case sensitive search
Posted by Aristedes Maniatis <ar...@maniatis.org>.
That task is just about optimising the use of indexes for searching case insensitive. Just use Cayenne's normal case insensitive flag on the query and it will work fine. Just not use any index.
Ari
On 1/02/10 7:14 PM, Nishant Neeraj wrote:
> Hi,
> Is there a way to write an expression in Cayenne that performs case sensitive search over a column? Something like this:
>
> SELECT ... FROM TEMP_USERS t0 WHERE binary t0.USER_NAME = 'CaMeL';
>
> I am using MySQL. Is this something related to this?
> http://n3.nabble.com/Re-jira-Created-CAY-1210-mysql-does-not-use-index-for-case-insensitive-searches-td14852.html
> ThanksNishant
>
>
> Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: MySQL case sensitive search
Posted by Lachlan Deck <la...@gmail.com>.
On 01/02/2010, at 7:14 PM, Nishant Neeraj wrote:
> Hi,
> Is there a way to write an expression in Cayenne that performs case sensitive search over a column? Something like this:
>
> SELECT ... FROM TEMP_USERS t0 WHERE binary t0.USER_NAME = 'CaMeL';
>
> I am using MySQL. Is this something related to this?
> http://n3.nabble.com/Re-jira-Created-CAY-1210-mysql-does-not-use-index-for-case-insensitive-searches-td14852.html
> ThanksNishant
Yes, cayenne uses the UPPER function for case sensitive searches which bypasses indexes. Patching the cayenne mysql plugin is needed for this. (I subclassed EOF's MySQL plugin to utilise the binary syntax... so I could look at this tomorrow if Andrus doesn't do so overnight.)
with regards,
--
Lachlan Deck