Bug #8690 like appears to be case in-sensitive in some sql and case sensitive in others
Submitted: 22 Feb 2005 15:14 Modified: 22 Feb 2005 20:21
Reporter: Simon Burton Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.10 OS:Linux (Linux 2.4.20)
Assigned to: CPU Architecture:Any

[22 Feb 2005 15:14] Simon Burton
Description:
The 'like' operator on a varchar field is usually case IN-sensitive, but when combined with some other 'where' clauses it seems to become case sensitive.

How to repeat:
create table test_table
(
 block varchar(24) null
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into test_table values ('BLOCK 1');
insert into test_table values ('block 2');

-- First two selects prove that 'like' on varchar is case-INsensitive:

select count(*) from test_table where block like '%BLOCK%';
+----------+
| count(*) |
+----------+
|        2 |
+----------+

select count(*) from test_table where block like '%block%';
+----------+
| count(*) |
+----------+
|        2 |
+----------+

-- This returns what I would expect:

select count(*) from test_table where block = 'BLOCK 1' and block like '%BLOCK%';
+----------+
| count(*) |
+----------+
|        1 |
+----------+

-- But this appears to be a bug:

select count(*) from test_table where block = 'BLOCK 1' and block like '%block%';
+----------+
| count(*) |
+----------+
|        0 |
+----------+

Suggested fix:
None as yet apart from rewriting the sql.

Obviously the two final sql selects are not very sensible but they are valid sql and in our case are being generated by code hence why they may appear an odd thing to do.
[22 Feb 2005 15:44] Martin Friebe
looks like issue #7098
[22 Feb 2005 16:01] Simon Burton
Yes, I would agree that that is the same bug.

It gives a status of "Documenting" and a changeset reference - what does this mean?  Is there any help or FAQ on the bug reporting system as I could not see any?

I need to know if there a fix yet or will there be and if so when is it likely etc.

thanks.
[22 Feb 2005 16:29] MySQL Verification Team
Duplicate #7098.

This means that this bug is already fixed and changes were commited to our repository.
I wasn't able reproduce this bug with latest 4.1:

mysql> select count(*) from test_table where block = 'BLOCK 1' and block like
    -> '%block%';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.06 sec)
[22 Feb 2005 16:42] Simon Burton
What do you mean by the "latest 4.1"? 4.1.what? 

I assume you mean some sort of development version? I downloaded the latest GA release (4.1.10) and still get the problem - can I download any of the development versions or the fix for this issue? If so where from? I looked at the download pages and developer support pages but could not see any appropriate downloads.

Apologies if this is the wrong place for these queries, please feel free to point me in the right direction.
[22 Feb 2005 20:21] Sergei Golubchik
No, you are right.
When bug is fixed, there should be a comment in what version it is fixed.
Bug#7098 was fixed in 4.1.11
[22 Feb 2005 21:09] Simon Burton
o.k. so can I download 4.1.11?

If yes, where?

If no, can I get this bugfix somehow? or is there a known release date for 4.1.11?

This bug affects our production systems and I need to make a development decision (i.e. upgrage MySQL if possible or spend time working around the issue).
[22 Feb 2005 21:22] Paul DuBois
Current 4.1 version means the 4.1 BitKeeper source repository.
Please see: http://dev.mysql.com/doc/mysql/en/installing-source-tree.html