Bug #93811 Like result error if escape character is % of wildcard characters

• Submitted: 4 Jan 2019 6:32
• Modified: 30 Jan 2019 15:36
• Reporter: Leo Wang
• Status: Closed
• Category: MySQL Server: Charsets
• Severity: S3 (Non-critical)
• Version: 5.6/5.7/8.0
• OS: Any
• CPU Architecture: Any
• Tags: charset, like

[4 Jan 2019 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 2019 15:42] MySQL Verification Team

Thank you for the bug report.

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