Description:
My program runs the query
select * from
(select '実施済' as CHAPTERSTATUS) A
where A.CHAPTERSTATUS = '実施済';
I expected MySQL to return the '実施済' row.
I received an empty set.
I ran show warnings and got the following message. The query is executed with explain before that.
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1300 | Cannot convert string '\xE5\xAE\x9F\xE6\x96\xBD...' from utf8mb3 to binary |
| Note | 1003 | /* select#1 */ select NULL AS `CHAPTERSTATUS` from (/* select#2 */ select '実施済' AS `CHAPTERSTATUS` from DUAL where false) `a` |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------+
The same query was executed on MySQL 8.0.31 to get the '実施済' row.
If I modify the query as follows, it returns the correct result.
select * from
(select _utf8'実施済' as CHAPTERSTATUS) A
where A.CHAPTERSTATUS = '実施済';
## Additional Information
mysql> show variables where variable_name like 'character%';
+--------------------------+----------------------------------------------------+
| Variable_name | Value|
+--------------------------+----------------------------------------------------+
| character_set_client | utf8mb3|
| character_set_connection | utf8mb3|
| character_set_database | utf8mb3|
| character_set_filesystem | binary|
| character_set_results | utf8mb3|
| character_set_server | utf8mb3|
| character_set_system | utf8mb3|
| character_sets_dir | C:\application\mysql-8.0.32-winx64\share\charsets\|
+--------------------------+----------------------------------------------------
How to repeat:
Execute the following SQL
select * from
(select '実施済' as CHAPTERSTATUS) A
where A.CHAPTERSTATUS = '実施済';
Suggested fix:
I want utf8 string literals to be treated as strings with the encoding specified in character_set_connection.
