MySQL Bugs: #110709: Chinese query conditions unable to return results correctly

Bug #110709	Chinese query conditions unable to return results correctly
Submitted:	18 Apr 2023 2:17	Modified:	18 Apr 2023 9:09
Reporter:	zy m	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	MySQL Community Server 8.0.32	OS:	Debian (11)
Assigned to:		CPU Architecture:	Any
Tags:	Chinese, query conditions

Description:
select data from view，unable to return results correctly, the query result is empty!

How to repeat:
-- Character Set:
-- character_set_client	utf8mb4
-- character_set_connection	utf8mb4
-- character_set_database	utf8mb4
-- character_set_filesystem	binary
-- character_set_results	utf8mb4
-- character_set_server	utf8mb4
-- character_set_system	utf8mb3

-- CREATE TABLE AND INSERT DATA
CREATE TABLE TEST_01 (编号 INT(2),动物 VARCHAR(20));
INSERT INTO TEST_01(编号,动物) VALUES(1,'牛');

-- CREATE TABLE AND INSERT DATA
CREATE TABLE TEST_02 (编号 INT(2),动物 VARCHAR(20));
INSERT INTO TEST_02(编号,动物) VALUES(2,'羊');

-- CREATE VIEW
CREATE VIEW TEST_V AS
SELECT 编号,动物 FROM TEST_01
UNION ALL
SELECT 编号,动物 FROM TEST_02;

-- SELECT DATA FROM VIEW
-- BUG：Unable to return results correctly, The query result is empty!
SELECT * FROM TEST_V WHERE `动物` = '牛'; 

Suggested fix:
the query result is correctly.

Hello zy m,

Thank you for the report and feedback.
Confirmed that this is duplicate of Bug #109699, which is fixed in  MySQL Server 8.0.33. Please see Bug #109699 for more details.

As a workaround, please set derived condition pushdown to off like this:
set optimizer_switch="derived_condition_pushdown=off";

regards,
Umesh

-- 8.0.32

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> -- CREATE TABLE AND INSERT DATA
mysql> CREATE TABLE TEST_01 (编号 INT(2),动物 VARCHAR(20));
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> INSERT INTO TEST_01(编号,动物) VALUES(1,'牛');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> -- CREATE TABLE AND INSERT DATA
mysql> CREATE TABLE TEST_02 (编号 INT(2),动物 VARCHAR(20));
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> INSERT INTO TEST_02(编号,动物) VALUES(2,'羊');
Query OK, 1 row affected (0.00 sec)

mysql> -- CREATE VIEW
mysql> CREATE VIEW TEST_V AS
    -> SELECT 编号,动物 FROM TEST_01
    -> UNION ALL
    -> SELECT 编号,动物 FROM TEST_02;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> -- SELECT DATA FROM VIEW
mysql> -- BUG：Unable to return results correctly, The query result is empty!
mysql>
mysql> SELECT * FROM TEST_V WHERE `动物` = '牛';
Empty set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
| Warning | 1300 | Cannot convert string '\xE7\x89\x9B' from utf8mb4 to binary |
| Warning | 1300 | Cannot convert string '\xE7\x89\x9B' from utf8mb4 to binary |
+---------+------+-------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> set optimizer_switch="derived_condition_pushdown=off";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM TEST_V WHERE `动物` = '牛';
+--------+--------+
| 编号   | 动物   |
+--------+--------+
|      1 | 牛     |
+--------+--------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM TEST_V WHERE `动物` = '牛';
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 83      | const |    1 |   100.00 | NULL  |
|  2 | DERIVED     | TEST_01    | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  |    1 |   100.00 | NULL  |
|  3 | UNION       | TEST_02    | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
3 rows in set, 1 warning (0.00 sec)