Bug #2547 Strange "like" behaviour in tables with default charset=cp1250
Submitted: 28 Jan 2004 14:20 Modified: 3 Feb 2004 6:16
Reporter: Andrzej Pruski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.1 OS:Linux (Linux PLD 1.0,Mandrake 9.2)
Assigned to: Alexander Barkov CPU Architecture:Any

[28 Jan 2004 14:20] Andrzej Pruski
Description:
I've found problem with "like" operator and global % in tables with default charset cp1250.

--------------
CREATE TABLE temp_k (
  nazwa varchar(250) NOT NULL default ''
) TYPE=MyISAM DEFAULT CHARSET=cp1250
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
INSERT INTO temp_k VALUES
('Techni Tapes Sp. z o.o.'),
('Pojazdy Szynowe PESA Bydgoszcz SA Holding'),
('AKAPESTER 1 P.P.H.U.'),
('Pojazdy Szynowe PESA Bydgoszcz S A Holding'),
('PPUH PESKA-I Maria Struniarska')
--------------

Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

--------------
select * from temp_k where nazwa like "%PESA%"
--------------

Empty set (0.00 sec) //WRONG!!!

--------------
select * from temp_k where nazwa like "%PESA %"
--------------

+--------------------------------------------+
| nazwa                                      |
+--------------------------------------------+
| Pojazdy Szynowe PESA Bydgoszcz SA Holding  |
| Pojazdy Szynowe PESA Bydgoszcz S A Holding |
+--------------------------------------------+
2 rows in set (0.00 sec) //GOOD!

--------------
select * from temp_k where nazwa like "%PES%"
--------------

+--------------------------------------------+
| nazwa                                      |
+--------------------------------------------+
| Techni Tapes Sp. z o.o.                    |
| Pojazdy Szynowe PESA Bydgoszcz SA Holding  |
| AKAPESTER 1 P.P.H.U.                       |
| Pojazdy Szynowe PESA Bydgoszcz S A Holding |
| PPUH PESKA-I Maria Struniarska             |
+--------------------------------------------+
5 rows in set (0.00 sec) //GOOD!

--------------
select * from temp_k where nazwa like "%PESKA%"
--------------

+--------------------------------+
| nazwa                          |
+--------------------------------+
| PPUH PESKA-I Maria Struniarska |
+--------------------------------+
1 row in set (0.00 sec) GOOD!

--------------
select * from temp_k where nazwa like "%ESKA%"
--------------

Empty set (0.00 sec) //WRONG!!!

How to repeat:
DROP TABLE IF EXISTS temp_k;
CREATE TABLE temp_k (
  nazwa varchar(250) NOT NULL default ''
) TYPE=MyISAM DEFAULT CHARSET=cp1250;

INSERT INTO temp_k VALUES
('Techni Tapes Sp. z o.o.'),
('Pojazdy Szynowe PESA Bydgoszcz SA Holding'),
('AKAPESTER 1 P.P.H.U.'),
('Pojazdy Szynowe PESA Bydgoszcz S A Holding'),
('PPUH PESKA-I Maria Struniarska');

select * from temp_k where nazwa like "%PESA%";
select * from temp_k where nazwa like "%PESA %";
select * from temp_k where nazwa like "%PES%";
select * from temp_k where nazwa like "%PESKA%";
select * from temp_k where nazwa like "%ESKA%";

Suggested fix:
Changing default charset to "binary" in above script workarounds problem.
[29 Jan 2004 3:34] Alexander Keremidarski
The problem is somehow bound to 
character_set_connection = cp1250 and stirng "%PESA%"

If character_set_connection is set to utf8 query 
select * from temp_k where nazwa like _"%PESA%";
returns expected result.

mysql> SET NAMES utf8;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from temp_k where nazwa like _cp1250"%PESA%";
+--------------------------------------------+
| nazwa                                      |
+--------------------------------------------+
| Pojazdy Szynowe PESA Bydgoszcz SA Holding  |
| Pojazdy Szynowe PESA Bydgoszcz S A Holding |
+--------------------------------------------+

mysql> SET NAMES latin1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from temp_k where nazwa like _cp1250"%PESA%";
Empty set (0.00 sec)

mysql> SET NAMES cp1250;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from temp_k where nazwa like _cp1250"%PESA%";
Empty set (0.00 sec)
[3 Feb 2004 6:16] Alexander Barkov
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