某業務系統將MySQL 8.0.26升級為GreatSQL 8.0.32-24 後,某些特定的SQL語句不能查詢到數據。經測試 MySQL 8.0.32也存在相同的問題 此BUG已在 GreatSQL 8.0.32-25 版本中解決 MySQL 8.0.26版本升級32版本查詢數據為空的跟蹤 接到客 ...
某業務系統將MySQL 8.0.26升級為GreatSQL 8.0.32-24 後,某些特定的SQL語句不能查詢到數據。經測試 MySQL 8.0.32也存在相同的問題
此BUG已在 GreatSQL 8.0.32-25 版本中解決
MySQL 8.0.26版本升級32版本查詢數據為空的跟蹤
接到客戶反饋的問題後,對問題進行了復現和分析。
版本信息
greatsql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32-24 |
+-----------+
1 row in set (0.00 sec)
建表語句
greatsql> show create table t_student;
CREATE TABLE `t_student1` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`age` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
表數據
greatsql> select * from t_student;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | abc | 10 |
| 2 | 湯姆 | 20 |
+----+--------+-----+
2 rows in set (0.08 sec)
查詢數據
greatsql> select * from (select * from t_student union select * from t_student) temp where name='湯姆';
Empty set, 2 warnings (0.00 sec)
greatsql> show warnings;
+---------+------+-------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------+
| Warning | 1300 | Cannot convert string '\xE6\xB1\xA4\xE5\xA7\x86' from utf8mb4 to binary |
| Warning | 1300 | Cannot convert string '\xE6\xB1\xA4\xE5\xA7\x86' from utf8mb4 to binary |
+---------+------+-------------------------------------------------------------------------+
2 rows in set (0.00 sec)
greatsql> select * from (select * from t_student union select * from t_student) temp;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | abc | 10 |
| 2 | 湯姆 | 20 |
+----+--------+-----+
2 rows in set (0.00 sec)
greatsql> select * from (select * from t_student union select * from t_student) temp where name='abc';
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | abc | 10 |
+----+------+-----+
1 row in set (0.00 sec)
可以看到 直接查詢temp這個view或者 在視圖上添加英文過濾條件均能查詢出數據,在view上添加中文過濾條件sql語句返回結果為空,有warnings提醒,warnings具體內容為Cannot convert string
在官方網站進行搜索,以下連接有相似內容:
https://bugs.mysql.com/bug.php?id=110228
https://bugs.mysql.com/bug.php?id=110955
MySQL 8.0.32版本中對於UNION/UNION ALL 後的結果中的字元串欄位過濾篩選不生效,客戶端中提示:
Cannot convert string '%\x...' from utf8mb4 to binary
官方已確認是8.0.32中的一個bug,已在8.0.33版本中修複,對於8.0.32版本,官方給出的解決方式如下:
set optimizer_switch="derived_condition_pushdown=off";
讓所有Session都生效,需要在配置文件中將optimizer_switch
設置為off
此BUG已在 GreatSQL 8.0.32-25 版本中解決
解決方法
方法一:
設置set optimizer_switch="derived_condition_pushdown=off";
後SQL語句能正常執行
greatsql> set optimizer_switch='derived_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
greatsql> select * from (select * from t_student union select * from t_student) temp where name='湯姆';
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 2 | 湯姆 | 20 |
+----+--------+-----+
1 row in set (0.00 sec)
方法二:
對單個SQL語句設置NO_DERIVED_CONDITION_PUSHDOWN hint
greatsql> set optimizer_switch='derived_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)
greatsql> select * from (select * from t_student union select * from t_student) temp where name='湯姆';
Empty set, 2 warnings (0.00 sec)
greatsql> select /*+ NO_DERIVED_CONDITION_PUSHDOWN(temp) */ * from (select * from t_student union select * from t_student) temp where name='湯姆';
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 2 | 湯姆 | 20 |
+----+--------+-----+
1 row in set (0.00 sec)
Enjoy GreatSQL