MySQL Bugs: #17374: select ... like 'A%' operator fails to find value on columuns with key

Bug #17374	select ... like 'A%' operator fails to find value on columuns with key
Submitted:	14 Feb 2006 12:16	Modified:	8 Apr 2006 12:34
Reporter:	Dan Ohnesorg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18-log/5.0.19BK/5.1.7 BK	OS:	Linux (debian sarge)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
After upgrade from mysql 4.0 we have noticed, that some selects return fewer values as before. We have found, that like comparsion is not working properly now. Here are 3 queries which should return the same result, but one of them is returing only one row.

If you ignore key in some way (explicit or implicit) you will get expected result.

Mysql is compiled with latin2_czech_cs collation as default.

How to repeat:
create table a (
    id  int(5) not null,    
    tt  char(255) not null
);

insert into a values (1,'Aa');
insert into a values (2,'Aas');

alter table a add primary key aaa(tt); 

mysql> select * from a where tt like 'Aa%';
+----+-----+
| id | tt  |
+----+-----+
|  2 | Aas |
+----+-----+
1 row in set (0.00 sec) <<<< WRONG

mysql> select * from a ignore index (primary) where tt like 'Aa%';
+----+-----+
| id | tt  |
+----+-----+
|  1 | Aa  |
|  2 | Aas |
+----+-----+
2 rows in set (0.00 sec) <<<< OK

mysql> select * from a where tt like '%Aa%';
+----+-----+
| id | tt  |
+----+-----+
|  1 | Aa  |
|  2 | Aas |
+----+-----+
2 rows in set (0.00 sec) <<<< OK

Suggested fix:
Don't know

Thank you for the bug report. I was able to repeat creating the table
with the collate latin2_czech_cs;

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table a (
    ->     id  int(5) not null,    
    ->     tt  char(255) not null
    -> );
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> 
mysql> alter table a add primary key aaa(tt); 
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from a where tt like 'Aa%';
+----+-----+
| id | tt  |
+----+-----+
|  1 | Aa  |
|  2 | Aas |
+----+-----+
2 rows in set (0.01 sec)

mysql> exit
Bye
miguel@hegel:~/dbs/5.0> bin/mysqladmin shutdown -uroot
miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table a;
Query OK, 0 rows affected (0.00 sec)

mysql> create table a (
    ->     id  int(5) not null,    
    ->     tt  char(255) not null
    -> )collate latin2_czech_cs;
Query OK, 0 rows affected (0.29 sec)

mysql> insert into a values (1,'Aa');
Query OK, 1 row affected (0.06 sec)

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

mysql> 
mysql> alter table a add primary key aaa(tt); 
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from a where tt like 'Aa%';
+----+-----+
| id | tt  |
+----+-----+
|  2 | Aas |
+----+-----+
1 row in set (0.17 sec)

mysql> select * from a ignore index (primary) where tt like 'Aa%';
+----+-----+
| id | tt  |
+----+-----+
|  1 | Aa  |
|  2 | Aas |
+----+-----+
2 rows in set (0.01 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3340

We applied this patch ad I can confirm, that our problem is solved and nothink other seems be broken.

Ehm... my yesterday post was made too early, after few hours we have discovered, that problem still exist, but in another version. The like is now sometimes case sensitive and sometimes case insensitive.

drop table if exists words;
CREATE TABLE `words` (
  `id_word` int(11) NOT NULL auto_increment,
  `word` varchar(128) collate latin2_czech_cs NOT NULL default '',
  PRIMARY KEY  (`id_word`),
  UNIQUE KEY `word` (`word`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;

INSERT INTO words VALUES (1,'sentinels');
INSERT INTO words VALUES (2,'sentinel');
INSERT INTO words VALUES (3,'Sentinels');
INSERT INTO words VALUES (4,'Sentinel');

insert into words values (5,'aa');
insert into words values (6,'aax');
insert into words values (7,'aay');
insert into words values (8,'Aa');
insert into words values (9,'Aax');
insert into words values (10,'Aay');

select * from words where word like 'Aa%' order by word;
+---------+------+
| id_word | word |
+---------+------+
|       8 | Aa   |
|       6 | aax  |
|       9 | Aax  |
|       7 | aay  |
|      10 | Aay  |
+---------+------+ <---  aa is missing

select * from words where word like 'aa%' order by word;
+---------+------+
| id_word | word |
+---------+------+
|       5 | aa   |
|       8 | Aa   |
|       6 | aax  |
|       9 | Aax  |
|       7 | aay  |
|      10 | Aay  |
+---------+------+ ok

select * from words where word like "Sentinel%" order by word;
+---------+-----------+
| id_word | word      |
+---------+-----------+
|       4 | Sentinel  |
|       1 | sentinels |
|       3 | Sentinels |
+---------+-----------+ sentinel is missing

select * from words where word like "sentinel%" order by word;
+---------+-----------+
| id_word | word      |
+---------+-----------+
|       2 | sentinel  |
|       4 | Sentinel  |
|       1 | sentinels |
|       3 | Sentinels |
+---------+-----------+

The problem is that LIKE is case insensitive for latin2_czech_cs, which is wrong:
mysql> select 'A' like _latin2'a' collate latin2_czech_cs;
+---------------------------------------------+
| 'A' like _latin2'a' collate latin2_czech_cs |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.00 sec)

The above quesry should return 0.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3973

ok to push

Fixed in 4.1.19, 5.0.20, 5.1.8

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 4.1.19, 5.0.20, and 5.1.8 changelogs. Closed.

turoial

Attachment: MySQL_Tutorial.pdf (application/pdf, text), 453.15 KiB.