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

背景

         上一篇博文(链接)介绍了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很多则可能重复生成多个集合A1～An。

4、  对A1～An作合并操作。由于只是每个集合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，正确

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

 

解决方法

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

 

         当然本质上这是一个bug。

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

评论

2 楼 丁林.tb 2013-11-28  

babaoqi 写道

版本5.5.29

如果在case when中加上else，则结果没有出错

测试sql:

SET @@session.tmp_table_size=1024;
DROP TABLE IF EXISTS tb;
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 'no else:64' AS ty,COUNT(DISTINCT (CASE WHEN id<=64 THEN id END)) AS cnt FROM tb
UNION ALL
SELECT 'no else:63' AS ty,COUNT(DISTINCT (CASE WHEN id<=63 THEN id END)) AS cnt FROM tb
UNION ALL
SELECT 'else:64' AS ty,COUNT(DISTINCT (CASE WHEN id<=64 THEN id ELSE 0 END)) AS cnt FROM tb
UNION ALL
SELECT 'else:63' AS ty,COUNT(DISTINCT (CASE WHEN id<=63 THEN id ELSE 0 END)) AS cnt FROM tb;

# 输出结果
ty	       cnt
no else:64	64
no else:63	0
else:64	        64
else:63	        64

是的,与分析一致

1 楼 babaoqi 2013-11-28  

版本5.5.29

如果在case when中加上else，则结果没有出错

测试sql:

SET @@session.tmp_table_size=1024;
DROP TABLE IF EXISTS tb;
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 'no else:64' AS ty,COUNT(DISTINCT (CASE WHEN id<=64 THEN id END)) AS cnt FROM tb
UNION ALL
SELECT 'no else:63' AS ty,COUNT(DISTINCT (CASE WHEN id<=63 THEN id END)) AS cnt FROM tb
UNION ALL
SELECT 'else:64' AS ty,COUNT(DISTINCT (CASE WHEN id<=64 THEN id ELSE 0 END)) AS cnt FROM tb
UNION ALL
SELECT 'else:63' AS ty,COUNT(DISTINCT (CASE WHEN id<=63 THEN id ELSE 0 END)) AS cnt FROM tb;

# 输出结果
ty	       cnt
no else:64	64
no else:63	0
else:64	        64
else:63	        64