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)
复制代码
评论