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

[8 Apr 2005 13:37] Jan Janecek
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.
[8 Apr 2005 16:13] MySQL Verification Team
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)
[11 Apr 2005 5:50] Jan Janecek
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';
[11 May 2005 23:00] Bugs System
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".
[22 May 2005 11:02] Vasily Kishkin
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)
[6 Jun 2005 16:25] Bugs System
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
[6 Jun 2005 16:29] Alexander Barkov
Fixed in 4.1.13.
[14 Jun 2005 2:36] Paul DuBois
Noted in 4.1.13 changelog.