Bug #3634 LIKE with % doesn't match exact entry

• Submitted: 3 May 2004 16:36
• Modified: 4 May 2004 22:06
• Reporter: David Deutsch
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.2
• OS: Linux (Linux x86 2.6.5)
• CPU Architecture: Any

[3 May 2004 16:36] David Deutsch

Description:
In upgrading to the latest 4.1.2 CVS version two nights ago (to fix the 4.1.1 fulltext index corruption issues) I noticed that many LIKE statements were failing to return a result. It seems that the % is no longer matching zero characters.

How to repeat:
NOTE: username is a VARCHAR(30) NOT NULL default ''

mysql> SELECT username from user WHERE username = 'bruno';
+----------+
| username |
+----------+
| bruno    |
+----------+
1 row in set (0.00 sec)

however:

mysql> SELECT username from user WHERE username like 'bruno%';
Empty set (0.00 sec)

and:

mysql> SELECT username from user WHERE username like 'brun%';
+-----------+
| username  |
+-----------+
| bruno     |
+-----------+
1 rows in set (0.00 sec)

Note that only by removing the last character of the username did the like work. Also note that I have tried %% to no avail.

Here are my char variables:

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.00 sec)

and my version info:

mysql> SHOW VARIABLES LIKE 'ver%';
+-------------------------+----------------------------------+
| Variable_name           | Value                            |
+-------------------------+----------------------------------+
| version                 | 4.1.2-alpha-nightly-20040429-log |
| version_comment         | Source distribution              |
| version_compile_machine | i686                             |
| version_compile_os      | pc-linux                         |
+-------------------------+----------------------------------+
4 rows in set (0.00 sec)

Thanks! David

[4 May 2004 22:06] Michael Widenius

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

% should match end of string (empty string)

I did a test of this with the current 4.1.2 tree:

create table t1 (username VARCHAR(30) NOT NULL default '');
insert into t1 values("bruno"),("test");
SELECT username from t1 WHERE username = 'bruno';
SELECT username from t1 WHERE username like 'bruno%'
create index username on t1 (username);
SELECT username from t1 WHERE username like 'bruno%';

And all above worked as expected.

Did you check if 'make test' worked for you?
(If LIKE would have changed, this should have failed)

Regards,
Monty