MySQL Bugs: #60130: LIKE is not always working as expected with CONCAT('%', XXX, '%')

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

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.

This looks like a duplicate of bug #59140. Please, check if the following workaround:

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

will help in your case.

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".

It is not fixed in 5.5.15 either, is it going to be fixed? or is this "designed behaviour"?

Bug #59140 is not yet fixed even in 5.5.15. I believe this is just a duplicate of that older bug.