MySQL Bugs: #8976: INDEX on a char column causes problems with like operator

Bug #8976	INDEX on a char column causes problems with like operator
Submitted:	5 Mar 2005 10:38	Modified:	6 Mar 2005 5:48
Reporter:	Gleb Paharenko	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
If the table has unique index on a char column which has cp1251_bin collation, like
operator doesn't work, until I point the right collation in the SQL statement with binary operator
(or collate cp1251_bin). I think  the problem  remains with other collations

How to repeat:
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 binary word like 'f%';
+----+--------+---------+
| id | word   | counter |
+----+--------+---------+
|  1 | falama |       1 |
|  3 | folka  |       1 |
+----+--------+---------+
mysql> drop index i_vocabulary_word on voc2;
Query OK, 4 rows affected (0.03 sec)
Records: 4mysql> select * from voc2 where word like 'f%';
+----+--------+---------+
| id | word   | counter |
+----+--------+---------+
|  1 | falama |       1 |
|  3 | folka  |       1 |
+----+--------+---------+
  Duplicates: 0  Warnings: 0

After dropping unique key query works perfectly

Suggested fix:
Make so, that the result of the query doesn't depend on existence of the indexes. The result
must be the same in both cases.

More information

mysql> show variables like '%char%';
+--------------------------+------------------------------------------------------------------------------+
| Variable_name            | Value                          |
+--------------------------+------------------------------------------------------------------------------+
| character_set_client     | cp1251                          |
| character_set_connection | cp1251                          |
| character_set_database   | cp1251                          |
| character_set_results    | cp1251                          |
| character_set_server     | cp1251                          |
| character_set_system     | utf8                          |
| character_sets_dir       | /home/gleb/mysqls/mysql-debug-4.1.10-pc-linux-gnu-i686/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------------------------------+

mysql> show variables like '%col%';
+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| collation_connection | cp1251_bin |
| collation_database   | cp1251_bin |
| collation_server     | cp1251_bin |

Just in case. I have tried this:

mysql> select * from voc2 where word like BINARY 'f%';

and got

Empty set (0.01 sec)

BUT

select * from voc2 where binary word like  'f%';

returns

+----+--------+---------+
| id | word   | counter |
+----+--------+---------+
|  1 | falama |       1 |
|  3 | folka  |       1 |
+----+--------+---------+
2 rows in set (0.00 sec)

However, 
select * from voc2 where binary word like  'f%';
is not practically usable because it does not use indices at all (as explain
shows) and vocabulary table has almost a million records.

I'd say THIS IS A VERY CRITICAL bug, because all searches in all our
web engines are broken now after provider upgraded to mysql 4.1.
This must be fixed ASAP.

I wasn't able to repeat the behavior reported with the latest BK source:

mysql> use o;
Database changed
mysql> 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;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 
mysql> insert into voc2 values(1,'falama',1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into voc2 values(2,'lagraf',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into voc2 values(3,'folka',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into voc2 values(4,'pofik',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from voc2 where word like 'f%';
+----+--------+---------+
| id | word   | counter |
+----+--------+---------+
|  1 | falama |       1 |
|  3 | folka  |       1 |
+----+--------+---------+
2 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> show variables like '%char%';
+--------------------------+--------------------------------------------+
| Variable_name            | Value                                      |
+--------------------------+--------------------------------------------+
| character_set_client     | cp1251                                     |
| character_set_connection | cp1251                                     |
| character_set_database   | cp1251                                     |
| character_set_results    | cp1251                                     |
| character_set_server     | cp1251                                     |
| character_set_system     | utf8                                       |
| character_sets_dir       | /home/miguel/dbs/4.1/share/mysql/charsets/ |
+--------------------------+--------------------------------------------+
7 rows in set (0.00 sec)

mysql> show variables like "%collation%";
+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| collation_connection | cp1251_bin |
| collation_database   | cp1251_bin |
| collation_server     | cp1251_bin |
+----------------------+------------+
3 rows in set (0.00 sec)

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

In my last post I got a warning in the create table statement. Below it:

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
1 row in set (0.00 sec)

so the warning not affects the issue.