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
• Status: Closed
• Category: MySQL Server: Charsets
• Severity: S2 (Serious)
• Version: 5.5.8
• OS: Any
• CPU Architecture: Any

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