当前位置:首页 > 开发 > 数据库 > 正文

关于MySQL count(distinct) 逻辑的另一个bug

发表于: 2013-11-28   作者:丁林.tb   来源:转载   浏览次数:
摘要: 背景          上一篇博文(链接)介绍了count distinct的一个bug。解决完以后发现客户的SQL语句仍然返回错误结果(0), 再查原因,发现了另外一个bug。也就是说,这个SQL语句触发了两个bug -_-   这里只说第二个,将问题简化后复现如下,影响已知的所有版本 。 &n

背景

         上一篇博文(链接)介绍了count distinct的一个bug。解决完以后发现客户的SQL语句仍然返回错误结果(0), 再查原因,发现了另外一个bug。也就是说,这个SQL语句触发了两个bug -_-

 

这里只说第二个,将问题简化后复现如下,影响已知的所有版本

 

drop table if exists tb;

set tmp_table_size=1024;

create table tb(id int auto_increment primary key, v varchar(32))engine=myisam charset=gbk;

insert into tb(v) values("aaa");

insert into tb(v) (select v from tb);

insert into tb(v) (select v from tb);

insert into tb(v) (select v from tb);

insert into tb(v) (select v from tb);

insert into tb(v) (select v from tb);

insert into tb(v) (select v from tb);

 

update tb set v=concat(v, id);

select count(distinct case when id<=64 then id end) from tb;

  返回64,正确

select count(distinct case when id<=63 then id end) from tb;

   返回0

上述中update语句的目的是将所有的v值设为各不相同。

 

与上个bug类似,5.5+的版本直接复现;5.1版本需要修改的是max_heap_table_size参数,而由于max_heap_table_size的最小值限制不能设置为1024,需要的测试数据量大些,但原理类似。

 

原因分析

         Count(distinct case when xxx then f end)的语义就是计算字段f的去重总数,计算流程细节参看前一篇。这里直接给出tmp_table_size不够大时的流程,便于说明此问题。

  

           流程:

1、  构造一个unique 集合A1, 将满足条件的结果插入A1中(计算了case when之后的值)

2、  插入item过程中若大小超过tmp_table_size,则将A1暂时写到文件中,再构造集合A2

3、  重复步骤2直到所有的item插入完成

因此若item很多则可能重复生成多个集合A1An

4、  A1An作合并操作。由于只是每个集合A保证unique,因此需要做类似归并排序的操作(实际上不需要排序,只是扫一遍)

5、  合并加和操作本来只需要去重和去掉NULL值即可,但为了复用代码,对于每个item,重新计算了一次结果的合法性,也就是,再判断一次case when是否正确。

6、  不幸的是,计算结果合法性的这些case when,其实是共同的一个:最后一行。

 

因此最后的结果是正确值还是0,就取决于最后一行的case when的结果。

案例分析

以上面这个case为例。由于使用主键,最后的一行必然是id=64的那一行。这样在合并的时候,若条件是id<=64 这些值都被认为符合条件可以合并。而最后一个语句的情况,最后一行id<=64不成立

 

作为验证可以看一下这个case

CREATE TABLE `tb2` (   `id` int(11) NOT NULL ,   `v` varchar(32) DEFAULT NULL ) ENGINE=MyISAM  DEFAULT CHARSET=gbk;

insert into tb2 (select * from tb order by id desc);

select count(distinct case when id<=63 then id end) from tb2;

返回63,正确

   可以看到,其实tb2tb1的数据内容是一样的,只是tb2没有索引且数据倒置插入,因此查询的最后一行的id1,满足id<=63, 结果记入就正确了。

 

解决方法

         调高tmp_table_size也是一种直接的方法,但是不治本,因为只要满足条件的行数足够多,就会出现这个问题。

 

         当然本质上这是一个bug

         代码上,对于已经走到合并操作的这个逻辑,其实前面在构造各个集合A1~An的时候,已经验证过条件合法,其实在合并的时候,可以直接做去重操作即可。

关于MySQL count(distinct) 逻辑的另一个bug

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
1 select column_2,count(column_2) as 'count(column_2)' ,count(column_1) as 'count(column_1)'
遇到一个问题:(面试的问题) 题目: ========================= 用户名 商品 数量 A 甲 1 B 乙 2 C 丙
刚用到count中用distinct多个字段,找到了下面这条语句,记录一下 查询出全部记录数 SELECT COUNT(*
注:这些技术是通用的,只不过我们选择使用Postgres的语法。使用独特的pgAdminIII生成解释图形。  
1:COUNT DISTINCT SELECT COUNT(DISTINCT [QS_QuestionStem].Id) AS ReqCount1, [QS_QuestionStem]
今天在群里面遇到一个人问的问题 他只问了一个问题:为何查询了主键,却没有使用主键索引,因为我对
这是今天在微博的一个人发的,有人@我了,我就做了一个,看一下吧, 两次结果相差如此直大。。。。
思维导图 前言 》 Mysql并非尽善尽美,但足够灵活,能适应高要求环境,如Web应用。 》 Mysql在众多
9 Count
http://acm.hust.edu.cn/vjudge/contest/view.action?cid=25019#problem/E Description Prof. Tigri
select count(*),count(id) from tables count(*) 会查询空行,count(id) 不会查询空行 如果payment
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号