Bug #110102 Invalid utf8mb4 character string: 'E9' on view with derived table
Submitted: 16 Feb 2023 15:36 Modified: 17 Feb 2023 7:30
Reporter: christophe offroy Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.32 OS:Ubuntu (20.04.2)
Assigned to: CPU Architecture:Any
Tags: utf8mb4 charset collation

[16 Feb 2023 15:36] christophe offroy 
Description:
after the v8.0.32 patch installed i have some issue with request.
When i do a condition on a literal with accent i receive this message.
Invalid utf8mb4 character string: 'E9'

In the v8.0.31 version it works

below the steps to reproduce the problem.

I have perhaps an configuration in my system which is the cause but i didn't find it.

 

How to repeat:
my schema :
CREATE DATABASE `btspdch1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

my table :
CREATE TABLE `cof` ( `c` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

my view :
CREATE 
    DEFINER = `toto`@`%` 
    SQL SECURITY DEFINER
VIEW btspdch1.v_cof as
select c from btspdch1.cof 
union all
select c from btspdch1.cof;

i insert one row 
INSERT INTO `cof` (`c`) VALUES ('Annulé');

SELECT *
FROM `v_cof`
where c='Annulé'

Result : 
no row return
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1300 | Invalid utf8mb4 character string: 'E9' |
+---------+------+----------------------------------------+

Suggested fix:
fix it becuase it worked befre
[17 Feb 2023 3:56] MySQL Verification Team 
Hello christophe offroy,

Thank you for the report and feedback.
Confirmed internally that this is duplicate of Bug #109699, please see Bug #109699 which is fixed in the MySQL 8.0.33. Since 8.0.33 is not yet released, we suggest you to please use the workaround for now.

Quoting Roy's note from another bug - Possible workaround: set optimizer_switch='derived_condition_pushdown=off';

regards,
Umesh
[17 Feb 2023 7:30] christophe offroy 
Hello Umesh,
Ok thanks for your feedback.
You can close this bug.

I rollback to the 8.0.31 version and for the 8.0.33.

Have a good day.
Christophe