MySQL一个关于derived table的bug描述与规避

这篇具有很好参考价值的文章主要介绍了MySQL一个关于derived table的bug描述与规避。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

关联文章:

  • MySQL对derived table的优化处理与使用限制

一、Bug描述

同事遇到一个有意思的语句,说一条SQL在MySQL8.0.25版本运行出的结果明显与给定的where条件不符,而在8.0.26版本上是正常的,语句上加了一个无关的用户变量后在8.0.25版本上结果才是正确的,想不通这是怎么回事,这么有意思的事情自然引起了我的兴趣,借此机会深入了解了一下MySQL关于derived table的优化。为了方便演示效果,让小伙伴们关注到现象的本质,我将语句进行了简化处理。

下面是模拟的表结构与数据。

create table t1(id int,c1 varchar(100));

insert into t1 values(1,'gg张三');
insert into t1 values(2,'bb李四');
insert into t1 values(3,'cc王五');
insert into t1 values(4,'dd刘麻子');
insert into t1 values(1,'gg张三');
insert into t1 values(2,'bb李四');

SQL语句:

SELECT temp.type
  FROM (SELECT  SUBSTRING(t.type, 3) type
          FROM (SELECT distinct t1.c1 type
                  FROM test.t1
                 ORDER BY t1.c1) t) temp
 WHERE temp.type='张三'
 ORDER BY temp.type DESC;

在MySQL8.0.25版本的运行结果如下:

mysql> SELECT temp.type
    ->   FROM (SELECT  SUBSTRING(t.type, 3) type
    ->           FROM (SELECT distinct t1.c1 type
    ->                   FROM test.t1
    ->                  ORDER BY t1.c1) t) temp
    ->  WHERE temp.type='张三'
    ->  ORDER BY temp.type DESC;
+--------+
| type   |
+--------+
| 李四   |
+--------+
1 rows in set (0.01 sec)

在MySQL8.0.26版本的运行结果如下:

mysql> SELECT temp.type
    ->   FROM (SELECT  SUBSTRING(t.type, 3) type
    ->           FROM (SELECT distinct t1.c1 type
    ->                   FROM test.t1
    ->                  ORDER BY t1.c1) t) temp
    ->  WHERE temp.type='张三'
    ->  ORDER BY temp.type DESC;
+--------+
| type   |
+--------+
| 张三   |
+--------+
1 rows in set (0.00 sec)

很明显,这个语句在8.0.25版本运行出的结果与我们给定where条件不符,我们要查询关于“张三”的记录,结果返回的结果是”李四“的,很明显的一个bug,但是到8.0.26版本这个问题得到了修正。

怀着对各版本对此语句执行情况的好奇,我先是往前追溯,查看了8.0.24,8.0.23,8.0.22,8.0.21,5.7.39版本上做了测试,发现在8.0.24,8.0.23,8.0.22版本结果与8.0.25相同,都是错误结果,而在8.0.21版本上运行结果是正确的,5.7版本上结果也是正确的的。往后追溯,8.0.26,8.0.32版本也都是正确的,因此判断此问题在8.0.22~8.0.25版本上存在此问题。

这个语句最大的特点就是运用了派生表(derived table),MySQL在8.0.22版本上引入了一个关于派生表的优化器开关derived_condition_pushdown, 默认设置为on。我们看一下关于这个特性在官方文档中的描述:

MySQL 8.0.22 and later supports derived condition pushdown for eligible subqueries. For a query such as SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > constant, it is possible in many cases to push the outer WHERE condition down to the derived table, in this case resulting in SELECT * FROM (SELECT i, j FROM t1 WHERE i > constant) AS dt.

在8.0.26版本中修复的bug中发现一个与此问题相关的bug。描述如下:

When a condition is pushed down to a materialized derived table, a clone of the derived table expression replaces the column (from the outer query block) in the condition. When the cloned item included a FULLTEXT function, it was added to the outer query block instead of the derived table query block, which led to problems. To fix this, we now use the derived query block to clone such items. (Bug #32820437)

看到这里我们可以确定,就是8.0.22版本时这个新特性的引入,导致了此问题的产生,庆幸的是这个问题在8.0.26版本中已得到解决

文章开头说的问题语句跟这个bug的描述是吻合的,派生表temp外部的过滤条件 temp.type=’张三' 其实是substring(t.type,3)='张三',应该就是对应bug描述中的”the cloned item included a FULLTEXT function“不管substring函数是不是fulltext函数,总之这个问题伴随着这个bug的修复也修复了。这种语句结构下,很多函数都有这个问题,比如trim,replace等。MySQL内部如何处理得到的错误结果我们就不去深究了,但是如何规避这个bug我们需要了解一下。

二、bug规避

升级到8.0.26及以上的版本问题自然就解决了,如果不想升级也是有很多方式来规避此问题的。这个bug的产生主要是因为新特性derived_condition_pushdown的引入,关闭此特性,在这几个版本中就不会出现这个问题。

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

mysql> SELECT temp.type
    ->   FROM (SELECT  SUBSTRING(t.type, 3) type
    ->           FROM (SELECT distinct t1.c1 type
    ->                   FROM test.t1
    ->                  ORDER BY t1.c1) t) temp
    ->  WHERE temp.type='张三'
    ->  ORDER BY temp.type DESC;
+--------+
| type   |
+--------+
| 张三   |
+--------+
1 rows in set (0.00 sec)

优化器开关里还有一个派生表相关的开关,就是derived_merge,是否进行派生表合并。关闭这个derived_merge,结果也是正确的。

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

mysql> SELECT temp.type
    ->   FROM (SELECT  SUBSTRING(t.type, 3) type
    ->           FROM (SELECT distinct t1.c1 type
    ->                   FROM test.t1
    ->                  ORDER BY t1.c1) t) temp
    ->  WHERE temp.type='张三'
    ->  ORDER BY temp.type DESC;
+--------+
| type   |
+--------+
| 张三   |
+--------+
1 rows in set (0.00 sec)

也就是说当派生表条件下推撞上派生表合并时,数据库做的处理不对,导致了问题的产生。

因此只要控制不发生合并,或者不发生条件下推,就能规避此bug。除了关闭优化器开关,在语句级别我们还有很多方式来规避,下面列举几个。

1.使用NO_MERGE的hint来阻止derived table合并。

SELECT  /*+ NO_MERGE(temp) */ temp.type
  FROM (SELECT substring(t.type,3) type
          FROM (SELECT distinct t1.c1 type
                  FROM test.t1
                 ORDER BY t1.c1) t
) temp
 WHERE temp.type='张三'
 ORDER BY temp.type DESC;

2.使用NO_DERIVED_CONDITION_PUSHDOWN的hint阻止条件下推。

SELECT  temp.type
  FROM (SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN(t) */ substring(t.type,3) type
          FROM (SELECT  distinct t1.c1 type
                  FROM test.t1
                 ORDER BY t1.c1) t
) temp
 WHERE temp.type='张三'
 ORDER BY temp.type DESC;

3.使用limit子句,能同时阻止合并与条件下推。

例如:

SELECT temp.type
  FROM (SELECT substring(t.type,3) type
          FROM (SELECT distinct t1.c1 type
                  FROM test.t1
                 ORDER BY t1.c1) t limit 100000000000) temp
 WHERE temp.type='张三'
 ORDER BY temp.type DESC;

4.分配用户变量,阻止derived table 合并。

例如:

SELECT temp.type
  FROM (SELECT (@i:=0) as num, substring(t.type,3) type
          FROM (SELECT distinct t1.c1 type
                  FROM test.t1
                 ORDER BY t1.c1) t) temp
 WHERE temp.type='张三'
 ORDER BY temp.type DESC;

这种方式就是前文提到的,为什么加了一个与业务逻辑无关的用户变量,结果就正确的原因。

5.使用union all来阻止derived table 合并

SELECT temp.type
  FROM (SELECT substring(t.type,3) type
          FROM (SELECT distinct t1.c1 type
                  FROM test.t1
                 ORDER BY t1.c1) t
                 union all 
                 select '1') temp
 WHERE temp.type='张三'
 ORDER BY temp.type DESC;

这些方法主要是依据优化器使用hint灵活控制优化器的开关,以及derive_merge与derived_condition_pushdown的使用限制。

三、总结

  1. MySQL8.0.22~MySQL8.0.25 因为优化器新特性derived_condition_pushdown带来的bug,可以通过derived merge与 derived_condition_pushdown 的使用限制以及优化器开关hint来有效规避bug,当然升级到高版本更好。
  2. 如果想让新特性derived_condition_pushdown发挥作用,就要避开它的使用限制。
  3. 一个新特性的出现,不可避免会伴随着一些bug的产生,不要对此心存恐惧,只要深入了解它,就能取其长,避其短。

Enjoy GreatSQL 😃

关于 GreatSQL

GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。

相关链接: GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区:

社区博客有奖征稿详情:https://greatsql.cn/thread-100-1-1.html

MySQL一个关于derived table的bug描述与规避

技术交流群:

微信:扫码添加GreatSQL社区助手微信好友,发送验证信息加群

MySQL一个关于derived table的bug描述与规避文章来源地址https://www.toymoban.com/news/detail-493274.html

到了这里,关于MySQL一个关于derived table的bug描述与规避的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • bug的合规描述

    发现问题的版本 bug的合格描述: 开发人员需要知道出现问题的版本,才能够获取对应版本的代码来重现故障问题出现的环境 环境分为硬件环境和软件环境,详细的环境描述有利于故障的重现(如果是web项目,需要描述浏  览器版本,客户机操作系统等;如果是app项目,需要

    2024年02月15日
    浏览(22)
  • mysql中在一个表中新增字段的时候,使用alter table的时候,add和add column的区别是什么?

    在 MySQL 中使用 ALTER TABLE 语句来修改表结构,包括添加新的字段。**在 ALTER TABLE 语句中,可以使用 ADD 或 ADD COLUMN 来添加新的字段。**它们的区别主要在于语法上的不同,实际上 ADD 和 ADD COLUMN 是等价的,都可以用来添加新的字段。 在 MySQL 中,ADD 和 ADD COLUMN 两种

    2024年02月07日
    浏览(35)
  • el-table 多个表格切换多选框显示bug

    今天写了个功能,点击左侧的树做判断,一级树节点显示系统页面,二级树节点显示数据库页面,三级树节点显示表页面。 数据库页面和表页面分别有2个el-table  ,上面的没有多选框,下面的有多选框  现在出现bug,在点击树节点,代码: // 点击节点     nodeClick(data) {    

    2024年02月11日
    浏览(40)
  • el-table魔改样式出现BUG,表格内容区域出现滚动条

    代码排查后发现时我设置了 fixed:“xxx” 属性就会导致滚动条出现的问题,不设置则无。 设置 fixed:“xxx” 后elementui就会多渲染几个css。重点看bottom:-1px,这是官方设置的值。我发现重置为bottom:-0px,问题就能解决了,下面直接贴解决代码。 把样式放在全局CSS中 还有更好的方

    2024年02月02日
    浏览(42)
  • 关于软件bug

    软件错误是计算机系统编程代码中的错误或缺陷。它可能导致系统故障甚至崩溃。 软件错误可能是由人为错误、软件设计缺陷或代码本身的问题引起的。它们也可能由病毒、间谍软件和其他恶意软件引起。 软件错误可能很难找到和修复,这可能会给依赖受影响软件的企业和

    2024年02月11日
    浏览(34)
  • 【明道云】关于环境迁移的一些Bug

    部署了明道云的测试环境和生产环境两个环境,在测试环境开发的东西需要迁移到生产环境中去,这个过程中发现一些问题。 迁移分为两个部分,应用功能的迁移和记录内容的迁移。 应用功能的迁移 这个只需要利用现有功能,在测试环境中导出应用,再在生产导入应用即可

    2024年02月20日
    浏览(31)
  • Rust中的derive属性详解

    1. Rust中的derive是什么? 在Rust语言中, derive 是一个属性,它可以让编译器为一些特性提供基本的实现。这些特性仍然可以手动实现,以获得更复杂的行为。 2. derive的出现解决了什么问题? derive 属性的出现解决了手动实现一些特性时需要编写大量重复代码的问题。它可以让

    2023年04月15日
    浏览(19)
  • 日常BUG——SpringBoot关于父子工程依赖问题

    😜 作           者 :是江迪呀 ✒️ 本文 : 日常BUG 、 BUG 、 问题分析 ☀️ 每日   一言 : 存在错误说明你在进步! 在父子工程A和B中。A依赖于B,但是A中却无法引入B中的依赖,具体出现的状况如下: config工程依赖于core工程,但是在依赖都引入的情况下,con

    2024年02月12日
    浏览(45)
  • 构建第一个ArkTS之声明式UI描述

    ArkTS以声明方式组合和扩展组件来描述应用程序的UI,同时还提供了基本的属性、事件和子组件配置方法,帮助开发者实现应用交互逻辑。 根据组件构造方法的不同,创建组件包含有参数和无参数两种方式。 说明 创建组件时不需要new运算符。 无参数 如果组件的接口定义没有

    2024年04月13日
    浏览(29)
  • Bard:一个可以描述图像的人工智能

    Bard 是一个大型语言模型,可以对各种提示和问题进行交流和生成类似人类的文本。它接受了大量的文字和代码训练,可以生成文本、翻译语言、编写不同类型的创意内容,并以信息丰富的方式回答你的问题。 Bard 还可以识别图像。它可以识别图像中的对象、场景和人物。它

    2024年02月17日
    浏览(43)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包