Bug #114342 Failed to convert the character string when executing the selection statement.
Submitted: 14 Mar 2:12 Modified: 12 Apr 14:12
Reporter: BN QIU Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:8.0.35, 8.0.36 OS:Any
Assigned to: CPU Architecture:Any

[14 Mar 2:12] BN QIU
Description:
 My program runs the query "SELECT a, hex(b), c FROM t1 WHERE b LIKE concat(repeat(0xF1F2,10), '%');".
  I expected MySQL to return the result "a	hex(b)	c". But it failed to execute the query.
"mysql> SELECT a, hex(b), c FROM t1 WHERE b LIKE concat(repeat(0xF1F2,10), '%');
ERROR 3854 (HY000): Cannot convert string '\xF1\xF2\xF1\xF2\xF1\xF2...' from binary to utf8mb4".

How to repeat:
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';

CREATE TABLE t1 AS
SELECT 10 AS a, REPEAT('a',20) AS b, REPEAT('a',8) AS c, REPEAT('a',8) AS d;
ALTER TABLE t1 ADD PRIMARY KEY(a), ADD KEY(b);

INSERT INTO t1 (a, b) VALUES (1, repeat(0xF1F2,5));
INSERT INTO t1 (a, b) VALUES (2, repeat(0xF1F2,10));
INSERT INTO t1 (a, b) VALUES (3, repeat(0xF1F2,11));
INSERT INTO t1 (a, b) VALUES (4, repeat(0xF1F2,12));

# Check pattern (important for ucs2, utf16, utf32)
SELECT hex(concat(repeat(0xF1F2, 10), '%'));

SELECT a, hex(b), c FROM t1 WHERE b LIKE concat(repeat(0xF1F2,10), '%');
[14 Mar 7:35] MySQL Verification Team
Hello B QIU,

Thank you for the report and test case.
Verified as described.

regards,
Umesh
[12 Apr 14:12] Roy Lyseng
Posted by developer:
 
This is not a bug.
In MySQL 8, the default character set for the column b will be utf8mb4.
In the LIKE operation, the like string is binary but is attempted to be
converted to utf8mb4. However, the string pattern is not a valid utf8
string, thus an error is reported on the conversion.

If you really want to perform a LIKE on a byte string, substitute "b"
with "CAST(b AS BINARY)".