Bug #59140 LIKE concat('%',@a,'%') doesn't match when @a contains latin1 string
Submitted: 23 Dec 2010 18:29 Modified: 5 Jun 2012 16:32
Reporter: Andrii Nikitin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version:5.5.8 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious) / R6 (Needs Assessment)

[23 Dec 2010 18:29] Andrii Nikitin
Description:
following expression in WHERE clause doesn't always returns TRUE:

column LIKE concat('%',@a,'%');

Following preconditions were identified:

1. column has latin1 character set
2. length(@a) > 3
3. latin1 string was written into @a

Here is small log showing that expression has different result in WHERE and SELECT clauses:

set @a="root";

select
convert(user using latin1) not like concat(_latin1 "%", @a, _latin1 "%")
from mysql.user where 
convert(user using latin1) not like concat(_latin1 "%", @a, _latin1 "%");

mysql > set @a="root";
Query OK, 0 rows affected (0.00 sec)

mysql > 
mysql > select
    -> convert(user using latin1) not like concat(_latin1 "%", @a, _latin1 "%")
    -> from mysql.user where 
    -> convert(user using latin1) not like concat(_latin1 "%", @a, _latin1 "%");
+--------------------------------------------------------------------------+
| convert(user using latin1) not like concat(_latin1 "%", @a, _latin1 "%") |
+--------------------------------------------------------------------------+
|                                                                        1 |
|                                                                        0 |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

That is obviously wrong because such deterministic expression must have same result in SELECT and WHERE clause.

How to repeat:
If lucky enough, following testcase will show the problem (mysql.user should have at least one "root" entry):

select convert("root" using latin1)  into @a;

-- this will return some matching rows
select convert(user using latin1) like concat(_latin1 "%", @a, _latin1 "%") from mysql.user;

-- this will return empty set
select 1 from mysql.user where convert(user using latin1) like concat(_latin1 "%", @a, _latin1 "%");

Suggested fix:
this LIKE expression must return the same result in SELECT and FROM clauses
[23 Dec 2010 18:35] Andrii Nikitin
Note that this problem also affects local variables in stored programs.

Workaround is introduce explicit cast for @a to any character set.
[13 Sep 2011 6:59] Valeriy Kravchuk
Bug #60130 was marked as a duplicate of this one.
[5 Jun 2012 16:32] Paul Dubois
Noted in 5.5.26, 5.6.6 changelogs.

Using CONCAT() to construct a pattern for a LIKE pattern match could
result in memory corrupting and match failure.
[12 Apr 2018 5:27] Erlend Dahl
Bug#63146 LIKE with CONCAT and REPLACE produces empty resultset - connection encoding

was marked as a duplicate.