MySQL Bugs: #9759: Empty result with 'LIKE' and cp1250_czech

Bug #9759	Empty result with 'LIKE' and cp1250_czech_cs
Submitted:	8 Apr 2005 13:37	Modified:	14 Jun 2005 2:36
Reporter:	Jan Janecek	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.11	OS:	Windows (Windows 2003 CZ)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
I have two queries:
1) SELECT * FROM table WHERE login = 'name';
2) SELECT * FROM table WHERE login LIKE 'name';

First query retuns 1 rows, its correct. 
Second query return 0 rows - its wrong result.
If on column LOGIN is set collation cp1250_czech_cs second query failed. If on this column i set collation utf8_czech_ci - second query returns correct result.

How to repeat:
Create table with cp1250_czech_cs collation.
Insert any records.
Run query SELECT * FROM table WHERE login = 'name';

Suggested fix:
Change the collation from cp1250_czech_cs to UTF8_CZECH_CI.

I wasn't able to repeat with the case below:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.11-nt

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

mysql> create table ty (login varchar(35)) character set=cp1250, collate cp1250_czech_cs;
Query OK, 0 rows affected (0.19 sec)

mysql> insert into ty values ("name");
Query OK, 1 row affected (0.02 sec)

mysql> select * from ty where login = "name";
+-------+
| login |
+-------+
| name  |
+-------+
1 row in set (0.02 sec)

mysql> select * from ty where login like "name";
+-------+
| login |
+-------+
| name  |
+-------+
1 row in set (0.00 sec)

Here is definition of table:
CREATE TABLE `datumy` (
  `datum` date NOT NULL default '0000-00-00',
  `popisek` varchar(30) collate cp1250_czech_cs NOT NULL default '',
  PRIMARY KEY  (`popisek`),
  UNIQUE KEY `popisek` (`popisek`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_cs;

-- 
-- Vypisuji data pro tabulku `datumy`
-- 

INSERT INTO `datumy` VALUES ('2005-01-01', '2005-01-1');
INSERT INTO `datumy` VALUES ('2005-01-30', '2005-01-30');
---------------------------------------------------------------------------------------

And here is test queries:
GOOD R.: SELECT * FROM datumy WHERE popisek = '2005-01-1';
BAD R.: SELECT * FROM datumy WHERE popisek LIKE '2005-01-1';

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Tested on Win 2000 Sp4, MySQL server 4.1.11

mysql> SELECT * FROM datumy WHERE popisek LIKE '_005-01-1';
+------------+-----------+
| datum      | popisek   |
+------------+-----------+
| 2005-01-01 | 2005-01-1 |
+------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM datumy WHERE popisek LIKE '2005-01-1';
Empty set (0.00 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/internals/25658

Fixed in 4.1.13.

Noted in 4.1.13 changelog.