Re: BINARY LIKE '..' just does not work

[Artem Kuchin]

On Saturday, March 05, 2005 1:46 PM [GMT+1=CET],
Gleb Paharenko <[EMAIL PROTECTED]> wrote:
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.
I have added my comment. TOO BAD that you specifed NON-CRITICAL.
It is critical, becauase after priovider upgraded to 4.1 all our engines 
stopped
searching for products and texts because dictionary table for word and term 
search
id build on binary char. I can only guess how many sites are now broken in 
this
manner.

Artem
"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]