Bug #8560 Selecting data with LIKE clause
Submitted: 16 Feb 2005 23:51 Modified: 9 Mar 2005 3:30
Reporter: Hristo Markow Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.10-win32 OS:Microsoft Windows (windows xp)
Assigned to: Sergey Petrunya

[16 Feb 2005 23:51] Hristo Markow
Description:
I've tried these queries with MySQL 4.1.10 also on Windows XP

and I've expected this QUERY to return 4 rows but it didn't
SELECT * FROM users WHERE _UserName LIKE 'we_%';

Am I wrong or it is a bug ???

the server is started with MY.INI in C:\WINDOWS directory and added key in section
[mysqld]
default-character-set=cp1251

works with this
SELECT * FROM users WHERE _UserName LIKE '%we_%';

How to repeat:
CREATE TABLE `users` (
  `_UserName` varchar(16) character set cp1251 collate cp1251_bin NOT NULL default '',
  `_Password` varchar(16) character set cp1251 collate cp1251_bin default NULL,
  `_Rights` int(10) default NULL,
  PRIMARY KEY  (`_UserName`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

INSERT INTO `users` VALUES ('air','proletina iliewa',2);
INSERT INTO `users` VALUES ('vw_kanara','1',0);
INSERT INTO `users` VALUES ('g','sunybeach05',3);
INSERT INTO `users` VALUES ('we_toshko','851203',0);
INSERT INTO `users` VALUES ('s0urce','cv3t1v0',1);
INSERT INTO `users` VALUES ('we_ivo','1',0);
INSERT INTO `users` VALUES ('we_iliyan','1',0);
INSERT INTO `users` VALUES ('we_martin','8505',0);
INSERT INTO `users` VALUES ('vw_grado','1',0);
INSERT INTO `users` VALUES ('vw_vasko','1',0);
INSERT INTO `users` VALUES ('tn_vili','1',0);
INSERT INTO `users` VALUES ('tn_kalina','kalina',0);
INSERT INTO `users` VALUES ('tn_fakira','1',0);
INSERT INTO `users` VALUES ('vw_silvia','1',0);
INSERT INTO `users` VALUES ('vw_starshi','1',0);
INSERT INTO `users` VALUES ('vw_geo','1',0);
INSERT INTO `users` VALUES ('vw_b0x1','1',0);

Suggested fix:
I don't know but LIKE clause works if the field _UserName hasn't BINARY attribute
[17 Feb 2005 1:18] Miguel Solorzano
mysql> SELECT * FROM users WHERE _UserName LIKE 'we_%';
Empty set (0.00 sec)

mysql> SELECT * FROM users WHERE _UserName LIKE '%we_%';
+-----------+-----------+---------+
| _UserName | _Password | _Rights |
+-----------+-----------+---------+
| we_toshko | 851203    |       0 |
| we_ivo    | 1         |       0 |
| we_iliyan | 1         |       0 |
| we_martin | 8505      |       0 |
+-----------+-----------+---------+
4 rows in set (0.00 sec)
[28 Feb 2005 17:50] Sergey Petrunya
Fix pushed into 4.1.11
[9 Mar 2005 3:30] Paul Dubois
Noted in 4.1.11 changelog.
[20 Apr 2005 23:33] Ignacio Avellaneda
Happened the same to me with version 4.1.10-win32 (under winxp) and with version 4.0.22-standard (under Linux).