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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
