Bug #16632 like statement error
Submitted: 19 Jan 2006 14:22 Modified: 21 Jan 2006 18:24
Reporter: Kennex Chan Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.18 OS:Windows (win2k)
Assigned to: CPU Architecture:Any

[19 Jan 2006 14:22] Kennex Chan
Description:
When I was implementing a autocomplete function which search database while user enter a char, I found out mysql could not return right answer to me while I used "like x%".

SQL: 1.select distinct name from aTable where name like 'a%'
       2. select distinct name from aTable where name like 'ad%'  

sql 1 return:
access_main
access_menu_main
Ads
adsfiles
AdsPages

sql 2 return:
empty

name has a KEY indexed, Index_type BTREE

How to repeat:
Tested for other tables, It happens again.
[19 Jan 2006 14:54] Aleksey Kishkin
Kennex, what collate do you use for this field?
[20 Jan 2006 1:11] Kennex Chan
Aleksey: It is utf8_general_ci .
[20 Jan 2006 14:42] Valeriy Kravchuk
Please, send the SHOW CREATE TABLE aTable results and try to repeat with newer version of MySQL, 5.0.18.
[21 Jan 2006 18:24] Valeriy Kravchuk
Sorry, but I was not able to repeat with 5.0.19-BK (ChangeSet@1.1997, 2006-01-20 17:21:39+03:00) on Linux:

mysql> CREATE TABLE `aTable` (
    ->              `IDENT` int(10) unsigned NOT NULL auto_increment,
    ->              `ROW_ID` varchar(15) NOT NULL default '',
    ->
    ->              `NAME` varchar(75) NOT NULL default '',
    ->
    ->              PRIMARY KEY  (`IDENT`),
    ->              UNIQUE KEY `ROW_ID` (`ROW_ID`),
    ->              KEY `NAME` (`NAME`)
    ->            ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.24 sec)

mysql> insert into aTable (name) values ('access_main');
Query OK, 1 row affected (0.01 sec)

mysql> insert into aTable (name, row_id) values ('access_main', 'row 1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into aTable (name, row_id) values ('access_menu_main', 'row 2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into aTable (name, row_id) values ('Ads', 'row 3');
Query OK, 1 row affected (0.00 sec)

mysql> insert into aTable (name, row_id) values ('adsfiles', 'row 4');
Query OK, 1 row affected (0.00 sec)

mysql> insert into aTable (name, row_id) values ('AdsPages', 'row 5');
Query OK, 1 row affected (0.00 sec)

mysql> select distinct name from aTable where name like 'a%';
+------------------+
| name             |
+------------------+
| access_main      |
| access_menu_main |
| Ads              |
| adsfiles         |
| AdsPages         |
+------------------+
5 rows in set (0.02 sec)

mysql> select distinct name from aTable where name like 'ad%';
+----------+
| name     |
+----------+
| Ads      |
| adsfiles |
| AdsPages |
+----------+
3 rows in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)

So, the bug, if any existed, is fixed in current code.