Bug #93811 Like result error if escape character is % of wildcard characters
Submitted: 4 Jan 6:32 Modified: 30 Jan 15:36
Reporter: Leo Wang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.6/5.7/8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: charset, like

[4 Jan 6:32] Leo Wang
Description:
In like expression, if % is escape character, then % in pattern strings is not wildcard character. it's a common character instead of wildcard character.

How to repeat:
if % is escape character, then the query don't return any result
mysql> select 'false' from dual where 'b%a' like '%%a' escape '%';
+-------+
| false |
+-------+
| false |
+-------+
1 row in set (0.01 sec)

mysql> select version() from dual;
+--------------+
| version()    |
+--------------+
| 8.0.4-rc-log |
+--------------+
1 row in set (0.00 sec)

if escape replace any character instead of %, then it's ok
mysql> select 'false' from dual where 'b%a' like '#%a' escape '#';
Empty set (0.01 sec)
[6 Jan 15:42] Miguel Solorzano
Thank you for the bug report.
[7 Jan 10:17] Xing Zhang
Posted by developer:
 
We use '_' to present any one character, and use '%' to present zero or more characters. Although defining escape character is allowed, but letting escape character be same as wildcard characters makes the wild comparing fuzzy.

Let's raise an error for this.
[8 Jan 8:35] Leo Wang
By the way, In Oracle database,
if % or _ is escape character, it's ok.

SQL> select 'false' from dual where 'b%a' like '%%a' escape '%';

no rows selected
[30 Jan 15:36] Paul Dubois
Posted by developer:
 
Fixed in 8.0.17.

LIKE comparisons could be incorrect if either of the _ or % SQL
wildcard characters was used as the ESCAPE character.