Hello.
The problem I think is in indexes. After dropping the unique key the query gives the correct result. I've reported a bug: http://bugs.mysql.com/bug.php?id=8976 You may add your comments there. "Artem Kuchin" <[EMAIL PROTECTED]> wrote: > MySQL: 4.1.8a > OS: FreeBSD 5.3 > > The problem is that BINARY LIKE '..' returns no rows no > matter what specified in '..' however BINARY field_name LIKE '..' > work, but I cannot use it because this does not use index (EXPLAINs are > bellow). According to documentation BINARY LIKE shoud work. But it > does not. > > Smells like a bug. > > Defaukt charset for mysql (it is specified at compile time) is cp1251 if > it matters (test uses only latin chars). > > Below is the test case: > (maybe someone else could run it on there server and tell me if it works > and also report mysql server version where it was tested). > > CREATE TABLE voc2 ( > id int(10) unsigned NOT NULL default '0', > word char(32) binary NOT NULL default '', > counter int(10) unsigned NOT NULL default '0', > PRIMARY KEY (id), > UNIQUE KEY i_vocabulary_word (word) > ) TYPE=MyISAM; > > insert into voc2 values(1,'falama',1); > insert into voc2 values(2,'lagraf',1); > insert into voc2 values(3,'folka',1); > insert into voc2 values(4,'pofik',1); > > mysql> select * from voc2 where word like 'f%'; > Empty set (0.00 sec) > > mysql> select * from voc2 where word like '%f'; > +----+--------+---------+ > | id | word | counter | > +----+--------+---------+ > | 2 | lagraf | 1 | > +----+--------+---------+ > 1 row in set (0.00 sec) > > mysql> select * from voc2 where word like '%f%'; > +----+--------+---------+ > | id | word | counter | > +----+--------+---------+ > | 1 | falama | 1 | > | 2 | lagraf | 1 | > | 3 | folka | 1 | > | 4 | pofik | 1 | > +----+--------+---------+ > 4 rows in set (0.00 sec) > > mysql> select * from voc2 where word like BINARY 'f%'; > Empty set (0.01 sec) > > mysql> select * from voc2 where word like BINARY '%f'; > +----+--------+---------+ > | id | word | counter | > +----+--------+---------+ > | 2 | lagraf | 1 | > +----+--------+---------+ > 1 row in set (0.00 sec) > > mysql> select * from voc2 where word like BINARY '%f%'; > +----+--------+---------+ > | id | word | counter | > +----+--------+---------+ > | 1 | falama | 1 | > | 2 | lagraf | 1 | > | 3 | folka | 1 | > | 4 | pofik | 1 | > +----+--------+---------+ > 4 rows in set (0.00 sec) > > > mysql> select * from voc2 where binary word like 'f%'; > +----+--------+---------+ > | id | word | counter | > +----+--------+---------+ > | 1 | falama | 1 | > | 3 | folka | 1 | > +----+--------+---------+ > 2 rows in set (0.00 sec) > > mysql> explain select * from voc2 where binary word like 'f%'; > +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ > | id | select_type | table | type | possible_keys | key | key_len | ref | > rows | Extra | > +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ > | 1 | SIMPLE | voc2 | ALL | NULL | NULL | NULL | NULL | > 4 | Using where | > +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ > 1 row in set (0.00 sec) > > > mysql> explain select * from voc2 where word like binary 'f%'; > +----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-------------+ > | id | select_type | table | type | possible_keys | key | > key_len | ref | rows | Extra | > +----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-------------+ > | 1 | SIMPLE | voc2 | range | i_vocabulary_word | i_vocabulary_word | > 32 | NULL | 1 | Using where | > +----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-------------+ > 1 row in set (0.00 sec) > > > This sucks. > > Regards, > Artem > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
