Bug #60130 LIKE is not always working as expected with CONCAT('%', XXX, '%')
Submitted: 15 Feb 2011 8:56 Modified: 13 Sep 2011 6:58
Reporter: Eimantas Jatkonis Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.5.9 OS:Linux (5.0.7 debian)
Assigned to: CPU Architecture:Any

[15 Feb 2011 8:56] Eimantas Jatkonis
Description:
LIKE is not always working as expected with CONCAT('%', XXX, '%')
its behaviour is unexpected, it returns different result set if source table has different rows - even rows do not match LIKE condition, it still interferes with result.

How to repeat:
mysql> use test;
Database changed
mysql> create table a (b int);
Query OK, 0 rows affected (0.33 sec)

mysql> insert into a values (0);
Query OK, 1 row affected (0.00 sec)

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

mysql> insert into a values (10000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a where b like CONCAT('%',10000,'%');
Empty set (0.00 sec)
(it should return one row here, but returns none)

mysql> delete from a where b < 2;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from a where b like CONCAT('%',10000,'%');
+-------+
| b     |
+-------+
| 10000 |
+-------+
1 row in set (0.00 sec)
(now it returns one)

Suggested fix:
No fix, or fix is unacceptable as solution in all cases.
[15 Feb 2011 9:33] Valeriy Kravchuk
This looks like a duplicate of bug #59140. Please, check if the following workaround:

concat('%', convert(10000 using latin1), '%')

will help in your case.
[16 Mar 2011 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[13 Sep 2011 6:49] Eimantas Jatkonis
It is not fixed in 5.5.15 either, is it going to be fixed? or is this "designed behaviour"?
[13 Sep 2011 6:58] Valeriy Kravchuk
Bug #59140 is not yet fixed even in 5.5.15. I believe this is just a duplicate of that older bug.