MySQL LIKE bug in 3.2.3 and 4.0.2

I seem to have stumbled across a bug in 3.2.3 and 4.0.2 with how MySQL
handles LIKE selects. If a table contains an entry of "Foo\\'Bar" a
select with LIKE "Foo\\\\%" will correctly return the entry. A select
with LIKE "Foo\\\\\'%" will not correctly return the entry. Following is
example code to reproduce the bug. It has been verified on 3.2.3 and
4.0.2 (types may need changed on 3.2.3, but bug still occurs):

How-To-Repeat:

CREATE TABLE bugtest (
entry text NOT NULL
) TYPE=MyISAM;

INSERT INTO bugtest VALUES ('Foo\\\\\'bar');


After that, the following query will correctly return the result:
SELECT `entry` FROM `bugtest` WHERE `entry` LIKE 'Foo\\\\%';

This query will NOT return the result:
SELECT `entry` FROM `bugtest` WHERE `entry` LIKE 'Foo\\\\\'%';

This query seems to be a workaround at the expense of a bit of speed:
SELECT `entry` FROM `bugtest` WHERE `entry` REGEXP 'Foo\\\\\'*';


I'd appreciate hearing anything about this bug, especially if it can be
additionally confirmed. I'd also like to thank the people in #mysql on
dalnet for assisting me in verifying this bug.

Sean Tobin
byrdhuntr@hotmail.com



____________________________________________________________ _____
Chat with friends online, try MSN Messenger: http://messenger.msn.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12391@lists.mysql.com
To unsubscribe, e-mail

MySQL LIKE bug in 3.2.3 and 4.0.2

Hi Sean,

Sean Tobin writes:
> I seem to have stumbled across a bug in 3.2.3 and 4.0.2 with how MySQL
> handles LIKE selects. If a table contains an entry of "Foo\\'Bar" a
> select with LIKE "Foo\\\\%" will correctly return the entry. A select
> with LIKE "Foo\\\\\'%" will not correctly return the entry. Following is
> example code to reproduce the bug. It has been verified on 3.2.3 and
> 4.0.2 (types may need changed on 3.2.3, but bug still occurs):
>
> How-To-Repeat:
>
> CREATE TABLE bugtest (
> entry text NOT NULL
> ) TYPE=MyISAM;
>
> INSERT INTO bugtest VALUES ('Foo\\\\\'bar');
>
>
> After that, the following query will correctly return the result:
> SELECT `entry` FROM `bugtest` WHERE `entry` LIKE 'Foo\\\\%';
>
> This query will NOT return the result:
> SELECT `entry` FROM `bugtest` WHERE `entry` LIKE 'Foo\\\\\'%';
>
> This query seems to be a workaround at the expense of a bit of speed:
> SELECT `entry` FROM `bugtest` WHERE `entry` REGEXP 'Foo\\\\\'*';
>
>
> I'd appreciate hearing anything about this bug, especially if it can be
> additionally confirmed. I'd also like to thank the people in #mysql on
> dalnet for assisting me in verifying this bug.

This is not a bug.

The value in the table is this:

Foo\\'bar

When you do a search with LIKE, the LIKE internal function in
MySQL removes one backslash for each double backslash.

So this string on the command line:

'Foo\\\\\'%';

is first parsed into this:

'Foo\\'%'

and then passed to LIKE function (at this point it goes to server
first time), which will convert it to:

'Foo\'%'

after which the comparison test is done, whether Foo\\'bar equals to
Foo\'%, which is not true.

If you want to find the above string with LIKE, you must use:

Foo\\\\\\\\\'%

which will in first round be parsed into:

Foo\\\\'%

and in the second round (inside MySQL server, in LIKE function) to:

Foo\\'%

which will then match Foo\\'bar.

Note that this doesn't affect '=' operator, you can find the
value from table with this:

SELECT `entry` FROM `bugtest` WHERE `entry` = 'Foo\\\\\'bar';

because '=' operator does not eat backslashes as LIKE does.

> Sean Tobin
> byrdhuntr@hotmail.com

Regards,

- Jani

For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Jani Tolonen
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12392@lists.mysql.com
To unsubscribe, e-mail

MySQL LIKE bug in 3.2.3 and 4.0.2

Forgot to mention the reason -

Jani Tolonen writes:
> Hi Sean,

> When you do a search with LIKE, the LIKE internal function in
> MySQL removes one backslash for each double backslash.

> because '=' operator does not eat backslashes as LIKE does.

The reason why LIKE eats backslashes while '=' doesn't, is because
LIKE is looking for possible escaping of '%' character.

'%' is a special character to LIKE function and without being
able to escape it one could not search exact match(es) for '%'
characters using LIKE.

Regards,

- Jani

For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Jani Tolonen
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12394@lists.mysql.com
To unsubscribe, e-mail