MySQL Bugs: #2076: LIKE not correct with underscores

Bug #2076	LIKE not correct with underscores
Submitted:	10 Dec 2003 6:24	Modified:	10 Dec 2003 7:34
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.16	OS:	Linux (Suse Linux)
Assigned to:	Indrek Siitan	CPU Architecture:	Any

Description:
When selecting from a table that contains Data with underscores, the LIKE clause does not work as expected.

How to repeat:
Say we have a table t with a string column c, containing one row of data: "MY_HELLO". The following statement should return that row, but it returns the empty set:

SELECT * from t where c LIKE "MY%" 

On the other hand, the statement 

SELECT * from t where c LIKE "M%" 

returns "MY_HELLO" as expectd.

Suggested fix:
Please check the SQL parser.

typo in OS

Can you try to provide us with a full repeatable test case? I just tried this on
4.0.14 and pre-4.0.17, and both produce the expected results:

mysql> create table liketest ( fld varchar(255) not null );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into liketest values ('MY_HELLO');
Query OK, 1 row affected (0.00 sec)

mysql> select * from liketest where fld like 'MY%';
+----------+
| fld      |
+----------+
| MY_HELLO |
+----------+
1 row in set (0.00 sec)

mysql> alter table liketest add index ( fld );
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from liketest where fld like 'MY%';
+----------+
| fld      |
+----------+
| MY_HELLO |
+----------+
1 row in set (0.01 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.0.17-debug-log |
+------------------+
1 row in set (0.00 sec)

Indrek,
thanks for your fast reply. Your example works on our DB as well.
OK, here's the complete code of the stuff that doesn't work:

#
# Table structure for table `bildkategorie`
#

CREATE TABLE bildkategorie (
  Bk_ID int(11) unsigned NOT NULL auto_increment,
  Bk_Name varchar(255) NOT NULL default '',
  Bk_Beschreibung text NOT NULL,
  PRIMARY KEY  (Bk_ID),
  UNIQUE KEY LK_Name (Bk_Name)
) TYPE=MyISAM COMMENT='Kategorien für die Links';

#
# Dumping data for table `bildkategorie`
#

INSERT INTO bildkategorie VALUES (1, 'Bild-Wahlkampf-2002', 'zeigt Bilder von Abgeordneten im Wahlkampf 2002');
INSERT INTO bildkategorie VALUES (2, 'Bild-Parlament', 'bilder von personen im parlament');
INSERT INTO bildkategorie VALUES (3, 'Bild-Rathaus-innen', 'Bilder von bürgern im rathau');
INSERT INTO bildkategorie VALUES (4, 'Bild-Rathausmarkt', 'zeigt Bürger auf dem Rathausmarkt');
INSERT INTO bildkategorie VALUES (5, 'BA_Präsidentin', '');
INSERT INTO bildkategorie VALUES (12, 'BA_Präsidium', '');
INSERT INTO bildkategorie VALUES (6, 'Bild-Rathaus-Sitzung', 'zeigt Abgeordnete im Rathaus bei einer Sitzung');
INSERT INTO bildkategorie VALUES (7, 'PDF-specification', 'specification about technical stuff');
INSERT INTO bildkategorie VALUES (8, 'Bild-Abgeordnete', 'Abgeordneten-Bilder');
INSERT INTO bildkategorie VALUES (9, 'Bild-Rathauskicker', 'Bilder der Rathauskicker');
INSERT INTO bildkategorie VALUES (10, 'Bild-Content', 'Bilder für den Content Bereich');
INSERT INTO bildkategorie VALUES (11, 'Bild-Infomaterial', '');
INSERT INTO bildkategorie VALUES (13, 'BA_Abgeordnete', '');
INSERT INTO bildkategorie VALUES (14, 'BA_Plenarsitzung', '');
INSERT INTO bildkategorie VALUES (15, 'BA_Rathaus-außen', '');
INSERT INTO bildkategorie VALUES (16, 'BA_Rathaus-innen', '');
INSERT INTO bildkategorie VALUES (17, 'BA_Veranstaltungen', '');
INSERT INTO bildkategorie VALUES (18, 'BA_Diverses', '');
INSERT INTO bildkategorie VALUES (19, 'BA_Grafik', '');
INSERT INTO bildkategorie VALUES (20, 'BATESTTEST_TEST', 'ysijglgjlkhnrg');
INSERT INTO bildkategorie VALUES (21, 'Andere Parlamente', 'Wappen für die Seite Andere Parlamente');

When I do the following select, no rows are returned:
SELECT Bk_Name
FROM bildkategorie
WHERE Bk_Name
LIKE "BA_%"
ORDER BY Bk_Name ASC;

It's the 4.0.16 standard we're using. 

Regards,
weeny

Forgot to say that exactly this code works on the 4.0.14 DB the developer team used, the error occured in our production environment only running 4.0.16.

thx
weeny