| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.18 | OS: | Windows (win2k) |
| Assigned to: | CPU Architecture: | Any | |
[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.
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.