MySQL Bugs: #2482: query with LIKE don't fuction well

Bug #2482	query with LIKE don't fuction well
Submitted:	22 Jan 2004 12:29	Modified:	3 Feb 2004 22:00
Reporter:	Alan Space	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.1	OS:	Novel NetWare, WinXP
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
mysql> select 'A PERFECT CIRCLE' LIKE '%CIRCLE%';
+------------------------------------+
| 'A PERFECT CIRCLE' LIKE '%CIRCLE%' |
+------------------------------------+
|                                  0 |
+------------------------------------+
1 row in set (0.00 sec)

it must return 1.

the same :

SELECT 'A PERFECT CIRCLE' LIKE '%CIRCLE' => 1
SELECT 'A PERFECT CIRCLE' LIKE '%CIRCLE%' => 0
SELECT 'A PERFECT CIRCLE' LIKE '%PERFECT%' => 0
SELECT 'THE BEATLES' LIKE '%BEATLES%' => 1
SELECT 'THE BEATLES' LIKE '%BEATLES' => 1
SELECT 'CIRCLE' LIKE '%CIRCLE%' => 1
SELECT ' CIRCLE' LIKE '%CIRCLE%' => 0
SELECT ' BEATLES' LIKE '%BEATLES' => 1
SELECT ' BEATLES' LIKE '%BEATLES%' => 0

every query must return 1

How to repeat:
SELECT 'A PERFECT CIRCLE' LIKE '%CIRCLE' => 1
SELECT 'A PERFECT CIRCLE' LIKE '%CIRCLE%' => 0
SELECT 'A PERFECT CIRCLE' LIKE '%PERFECT%' => 0
SELECT 'THE BEATLES' LIKE '%BEATLES%' => 1
SELECT 'THE BEATLES' LIKE '%BEATLES' => 1
SELECT 'CIRCLE' LIKE '%CIRCLE%' => 1
SELECT ' CIRCLE' LIKE '%CIRCLE%' => 0
SELECT ' BEATLES' LIKE '%BEATLES' => 1
SELECT ' BEATLES' LIKE '%BEATLES%' => 0

What character sets are you using for this?  (SHOW VARIABLES LIKE 'char%';)

I am able to duplicate this with UCS2, but I want to know what you are using.  Thanks.

character_set_server = latin2
character_set_system = utf8
character_set_database = latin2
character_set_client = latin2
character_set_ = latin2
character_set_connection = latin2
character_set_result = latin2

or 

character_set_connection = cp852

version of mysql - 4.1.1a  ( on NetWare and WinXP)

on forge.novell.com  4.1.1a  are the newest version, but now I see 4.1.1b on mysql.com. I'm going to test it.

when I use  LIKE '%%BEATLES%%' everything is OK.  but normaly I use one character %.

This doesn't happens to me on 4.1.1alpha nor 5.0.0alpha with latin1

+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_server     | latin1                                 |
| character_set_system     | utf8                                   |
| character_set_database   | latin1                                 |
| character_set_client     | latin1                                 |
| character_set_connection | latin1                                 |
| character-sets-dir       | /usr/local/mysql/share/mysql/charsets/ |
| character_set_results    | latin1                                 |
+--------------------------+----------------------------------------+

Alright, verifying this with 4.1.2:

SET CHARACTER SET LATIN2;
SET NAMES LATIN2;
SELECT 'A PERFECT CIRCLE' LIKE '%CIRCLE%';
SELECT 'A PERFECT CIRCLE' LIKE '%%CIRCLE%';
SELECT 'A PERFECT CIRCLE' LIKE '%CIRCLE%%';

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.1a-alpha-max-nt

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

mysql> select 'A PERFECT CIRCLE' LIKE '%CIRCLE';
+-----------------------------------+
| 'A PERFECT CIRCLE' LIKE '%CIRCLE' |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.02 sec)

mysql> select 'A PERFECT CIRCLE' LIKE '%CIRCLE%';
+------------------------------------+
| 'A PERFECT CIRCLE' LIKE '%CIRCLE%' |
+------------------------------------+
|                                  0 |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+--------------------------+
| Variable_name            | Value                    |
+--------------------------+--------------------------+
| character_set_server     | latin2                   |
| character_set_system     | utf8                     |
| character_set_database   | latin2                   |
| character_set_client     | latin2                   |
| character_set_connection | latin2                   |
| character-sets-dir       | C:\mysql\share\charsets/ |
| character_set_results    | latin2                   |
+--------------------------+--------------------------+

And I have the same on the Netware Server   ( 6.0 with SP 3.0)
When I use 4.0 everything was OK.

I use in my web portal  query with double '%' to resolve this problem.

I know that this is alpha version but on Netware this is the only way to use 
translations with chars (latin2 and cp852) (using 'set character set').

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:

I fixed this problem in 4.1.2 yesterday.

I checked all the queries reported, they work well now.
Thank you for detailed report!