MySQL Bugs: #13347: empty result from query with like and cp1250 charset

Bug #13347	empty result from query with like and cp1250 charset
Submitted:	20 Sep 2005 11:13	Modified:	31 Oct 2005 19:54
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.13-log/5.0 BK source	OS:	Linux (Gentoo Linux mysql-4.1.13-r1, x8)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
query:

SELECT * FROM test WHERE str LIKE 'a%'

returns empty set, but query

SELECT * FROM test WHERE str COLLATE cp1250_czech_cs like 'a%'

works as excepted, but don't use index

How to repeat:
test data:

SET character_set_client = cp1250;
SET character_set_results = cp1250;
SET character_set_connection = cp1250;

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `str` VARCHAR(32) COLLATE cp1250_czech_cs NOT NULL default '',
        UNIQUE KEY (`str`)
);

INSERT INTO `test` VALUES (NULL, 'a');
INSERT INTO `test` VALUES (NULL, 'aa');
INSERT INTO `test` VALUES (NULL, 'aaa');
INSERT INTO `test` VALUES (NULL, 'aaaa');
INSERT INTO `test` VALUES (NULL, 'aaaaa');
INSERT INTO `test` VALUES (NULL, 'aaaaaa');
INSERT INTO `test` VALUES (NULL, 'aaaaaaa');

results from queries:
mysql> select * from test where str like 'aa%'; Empty set (0.00 sec)

mysql> explain select * from test where str like 'aa%'; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | range | str           | str  |      32 | NULL |    1 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

this query returns empty set, but rows from explain is 1. Rows in table that match 'LIKE 'a%' is 6.

mysql> select * from test where str COLLATE cp1250_czech_cs like 'aa%';
+----+---------+
| id | str     |
+----+---------+
|  2 | aa      |
|  3 | aaa     |
|  4 | aaaa    |
|  5 | aaaaa   |
|  6 | aaaaaa  |
|  7 | aaaaaaa |
+----+---------+
6 rows in set (0.00 sec)

mysql> explain select * from test where str COLLATE cp1250_czech_cs like 'aa%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL |    NULL | NULL |    7 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

This query works, but don't use an index. Rows count from EXPLAIN is 7 not 6 (as before +1 i think)

I think, this problem is related to cp1250 charset. With utf8 charset and collate works fine.

CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `str` varchar(32) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `str` (`str`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

mysql> explain select * from test where str like 'a%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | str           | NULL |    NULL | NULL |    7 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> select * from test where str like 'a%';
+----+---------+
| id | str     |
+----+---------+
|  1 | a       |
|  2 | aa      |
|  3 | aaa     |
|  4 | aaaa    |
|  5 | aaaaa   |
|  6 | aaaaaa  |
|  7 | aaaaaaa |
+----+---------+
7 rows in set (0.00 sec)

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

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

mysql> SET character_set_client = cp1250;
Query OK, 0 rows affected (0.06 sec)

mysql> SET character_set_results = cp1250;
Query OK, 0 rows affected (0.00 sec)

mysql> SET character_set_connection = cp1250;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `test` (
    ->         `id` INT AUTO_INCREMENT PRIMARY KEY,
    ->         `str` VARCHAR(32) COLLATE cp1250_czech_cs NOT NULL default '',
    ->         UNIQUE KEY (`str`)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> 
mysql> INSERT INTO `test` VALUES (NULL, 'a');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test` VALUES (NULL, 'aa');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test` VALUES (NULL, 'aaa');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test` VALUES (NULL, 'aaaa');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test` VALUES (NULL, 'aaaaa');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test` VALUES (NULL, 'aaaaaa');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `test` VALUES (NULL, 'aaaaaaa');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where str like 'aa%';
Empty set (0.01 sec)

mysql> select * from test where str COLLATE cp1250_czech_cs like 'aa%';
+----+---------+
| id | str     |
+----+---------+
|  2 | aa      |
|  3 | aaa     |
|  4 | aaaa    |
|  5 | aaaaa   |
|  6 | aaaaaa  |
|  7 | aaaaaaa |
+----+---------+
6 rows in set (0.00 sec)

mysql>

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/internals/30224

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/internals/31439

Fixed in 4.1.16.

Noted in 4.1.16 changelog.