MySQL对window函数执行sum函数可能出现的一个Bug

所属分类：数据库 / Mysql 阅读数： 2142

收藏 0赞 0分享

使用MySql的窗口函数统计数据时，发现一个小的问题，与大家一起探讨下。

环境配置：

mysql-installer-community-8.0.20.0

问题点：在sum对window函数执行时，如果有重复数据，会直接把相同的数据相加，并不是逐步相加。

问题描述

数据：在一个成绩表中，有三个个字段：学生s_id，课程c_id，成绩s_score。

查询条件查询每个课程的学生成绩排名和成绩汇总。

查询结果：发现如果同一个课程有相同成绩是，汇总成绩不是累加的，而是一次全部加上去。

创建数据表

CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
)

插入数据

-- 成绩表数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

查询数据

select c_id,s_id,s_score,
first_value(s_score) over w as first_v,
last_value(s_score) over w as last_v,
sum(s_score) over w as sum_v,
max(s_score) over w as max_v,
min(s_score) over w as min_v,
count(s_id) over w as count_v,
row_number() over w as row_id,
rank() over w as rank_id,
dense_rank() over w as dense_id
from score window w as (partition by c_id order by s_score desc);

查询结果

看课程号01的统计结果，数据第一行的sum_v列，前两个数据都是160，按照函数原理，数据应该是80，160。

看课程号02的统计结果，发现结果是正确的，sum_v的第一个为90，第二个为179。

实际显示与预期结果不一致，哪里出了问题。

c_id	s_id	s_score	first_v	last_v	sum_v	max_v	min_v	count_v	row_id	rank_id	dense_id
01	01	80	80	80	160	80	80	2	1	1	1
01	03	80	80	80	160	80	80	2	2	1	1
01	05	76	80	76	236	80	76	3	3	3	2
01	02	70	80	70	306	80	70	4	4	4	3
01	04	50	80	50	356	80	50	5	5	5	4
01	06	31	80	31	387	80	31	6	6	6	5
02	01	90	90	90	90	90	90	1	1	1	1
02	07	89	90	89	179	90	89	2	2	2	2
02	05	87	90	87	266	90	87	3	3	3	3
02	03	80	90	80	346	90	80	4	4	4	4
02	02	60	90	60	406	90	60	5	5	5	5
02	04	30	90	30	436	90	30	6	6	6	6
03	01	99	99	99	99	99	99	1	1	1	1
03	07	98	99	98	197	99	98	2	2	2	2
03	02	80	99	80	357	99	80	4	3	3	3
03	03	80	99	80	357	99	80	4	4	3	3
03	06	34	99	34	391	99	34	5	5	5	4
03	04	20	99	20	411	99	20	6	6	6	5

思考验证

课程号02的数据正确，01的不正确，01与02的区别是01课程的前两个学生成绩一样都是80。

难道是成绩一样，导致sum时出错了。

为了验证这个问题，把课程号01，学号为01的成绩修改为82，然后在执行查询，结果如下

发现sum_v列显示的为82、162，与预期结果一致。

这样可以得出结论，在sum对window函数执行时，如果有重复数据，会直接把相同的数据相加，并不是逐步相加。

c_id	s_id	s_score	first_v	last_v	sum_v	max_v	min_v	count_v	row_id	rank_id	dense_id
01	01	80	80	82	82	82	82	2	1	1	1
01	03	80	80	80	162	82	80	2	2	1	1
01	05	76	80	76	236	82	76	3	3	3	2
01	02	70	80	70	306	82	70	4	4	4	3
01	04	50	80	50	356	82	50	5	5	5	4
01	06	31	80	31	387	82	31	6	6	6	5
02	01	90	90	90	90	90	90	1	1	1	1
02	07	89	90	89	179	90	89	2	2	2	2
02	05	87	90	87	266	90	87	3	3	3	3
02	03	80	90	80	346	90	80	4	4	4	4
02	02	60	90	60	406	90	60	5	5	5	5
02	04	30	90	30	436	90	30	6	6	6	6
03	01	99	99	99	99	99	99	1	1	1	1
03	07	98	99	98	197	99	98	2	2	2	2
03	02	80	99	80	357	99	80	4	3	3	3
03	03	80	99	80	357	99	80	4	4	3	3
03	06	34	99	34	391	99	34	5	5	5	4
03	04	20	99	20	411	99	20	6	6	6	5

其他Sql验证和对比

经过上述验证，Mysql在sum时确实出现了错误，不是逐步累加的。

其他平台是否同样存在问题，在Sqlite Expert 5.3版本验证了下，发现结果一样。

这个就奇怪了，如果是Mysql在实现时出错，Sqlite出同样错误的几率小很多。

难道是sum和window函数结合使用时的特性导致的。欢迎一起讨论和研究。

总结

更多精彩内容其他人还在看

MySQL中按照多字段排序及问题解决

这篇文章主要介绍了MySQL中按照多字段排序及问题解决的方法，非常的实用，有需要的小伙伴可以参考下。

收藏 0赞 0分享

MySQL外键使用详解

两天有人问mysql中如何加外键，今天抽时间总结一下。mysql中MyISAM和InnoDB存储引擎都支持外键（foreign key），但是MyISAM只能支持语法，却不能实际使用。

收藏 0赞 0分享

Mysql中LAST_INSERT_ID()的函数使用详解

从名字可以看出，LAST_INSERT_ID即为最后插入的ID值，有了这个实用的函数，我们可以实现很多问题，下面我们就来深入探讨下。

收藏 0赞 0分享

MySql insert插入操作的3个小技巧分享

这篇文章主要介绍了MySql insert插入操作的3个小技巧分享,本文讲解了插入的数据来源自其他表、插入时排除（忽略）重复记录、插入时遇到重复记录做更新操作三个小技巧,需要的朋友可以参考下

收藏 0赞 0分享

mysql修改自增长主键int类型为char类型示例

这篇文章主要介绍了mysql修改自增长主键int类型为char类型示例,需要的朋友可以参考下

收藏 0赞 0分享

Windows下使用Cygwin编译MySQL客户端

这篇文章主要介绍了Windows下使用Cygwin编译MySQL客户端,本文详细的讲解了编译过程,需要的朋友可以参考下

收藏 0赞 0分享

MySQL数据库优化推荐的编译安装参数小结

这篇文章主要介绍了MySQL数据库优化推荐的编译安装参数小结,需要的朋友可以参考下

收藏 0赞 0分享

MySQL之Field‘***’doesn’t have a default value错误解决办法

这篇文章主要介绍了MySQL之Field‘***’doesn’t have a default value错误解决办法,需要的朋友可以参考下

收藏 0赞 0分享

php连接MySQL的两种方式对比

这篇文章主要介绍了php连接MySQL的两种方式对比，一种是原生的链接方式另外一种是PDO方式，附上示例，推荐给大家，有需要的小伙伴可以参考下

收藏 0赞 0分享

Mysql InnoDB删除数据后释放磁盘空间的方法

这篇文章主要介绍了Mysql InnoDB删除数据后释放磁盘空间的方法,Innodb数据库对于已经删除的数据只是标记为删除,并不真正释放所占用的磁盘空间,这就导致InnoDB数据库文件不断增长,本文就讲解释放磁盘空间的方法,需要的朋友可以参考下

收藏 0赞 0分享

MySQL对window函数执行sum函数可能出现的一个Bug

MySQL中按照多字段排序及问题解决

MySQL外键使用详解

Mysql中LAST_INSERT_ID()的函数使用详解

MySql insert插入操作的3个小技巧分享

mysql修改自增长主键int类型为char类型示例

Windows下使用Cygwin编译MySQL客户端

MySQL数据库优化推荐的编译安装参数小结

MySQL之Field‘***’doesn’t have a default value错误解决办法

php连接MySQL的两种方式对比

Mysql InnoDB删除数据后释放磁盘空间的方法

网络赚钱

站长故事

c_id	s_id	s_score	first_v	last_v	sum_v	max_v	min_v	count_v	row_id	rank_id	dense_id
01	01	80	80	80	160	80	80	2	1	1	1
01	03	80	80	80	160	80	80	2	2	1	1
01	05	76	80	76	236	80	76	3	3	3	2
01	02	70	80	70	306	80	70	4	4	4	3
01	04	50	80	50	356	80	50	5	5	5	4
01	06	31	80	31	387	80	31	6	6	6	5
02	01	90	90	90	90	90	90	1	1	1	1
02	07	89	90	89	179	90	89	2	2	2	2
02	05	87	90	87	266	90	87	3	3	3	3
02	03	80	90	80	346	90	80	4	4	4	4
02	02	60	90	60	406	90	60	5	5	5	5
02	04	30	90	30	436	90	30	6	6	6	6
03	01	99	99	99	99	99	99	1	1	1	1
03	07	98	99	98	197	99	98	2	2	2	2
03	02	80	99	80	357	99	80	4	3	3	3
03	03	80	99	80	357	99	80	4	4	3	3
03	06	34	99	34	391	99	34	5	5	5	4
03	04	20	99	20	411	99	20	6	6	6	5

c_id	s_id	s_score	first_v	last_v	sum_v	max_v	min_v	count_v	row_id	rank_id	dense_id
01	01	80	80	82	82	82	82	2	1	1	1
01	03	80	80	80	162	82	80	2	2	1	1
01	05	76	80	76	236	82	76	3	3	3	2
01	02	70	80	70	306	82	70	4	4	4	3
01	04	50	80	50	356	82	50	5	5	5	4
01	06	31	80	31	387	82	31	6	6	6	5
02	01	90	90	90	90	90	90	1	1	1	1
02	07	89	90	89	179	90	89	2	2	2	2
02	05	87	90	87	266	90	87	3	3	3	3
02	03	80	90	80	346	90	80	4	4	4	4
02	02	60	90	60	406	90	60	5	5	5	5
02	04	30	90	30	436	90	30	6	6	6	6
03	01	99	99	99	99	99	99	1	1	1	1
03	07	98	99	98	197	99	98	2	2	2	2
03	02	80	99	80	357	99	80	4	3	3	3
03	03	80	99	80	357	99	80	4	4	3	3
03	06	34	99	34	391	99	34	5	5	5	4
03	04	20	99	20	411	99	20	6	6	6	5

c_id	s_id	s_score	first_v	last_v	sum_v	max_v	min_v	count_v	row_id	rank_id	dense_id
01	01	80	80	80	160	80	80	2	1	1	1
01	03	80	80	80	160	80	80	2	2	1	1
01	05	76	80	76	236	80	76	3	3	3	2
01	02	70	80	70	306	80	70	4	4	4	3
01	04	50	80	50	356	80	50	5	5	5	4
01	06	31	80	31	387	80	31	6	6	6	5
02	01	90	90	90	90	90	90	1	1	1	1
02	07	89	90	89	179	90	89	2	2	2	2
02	05	87	90	87	266	90	87	3	3	3	3
02	03	80	90	80	346	90	80	4	4	4	4
02	02	60	90	60	406	90	60	5	5	5	5
02	04	30	90	30	436	90	30	6	6	6	6
03	01	99	99	99	99	99	99	1	1	1	1
03	07	98	99	98	197	99	98	2	2	2	2
03	02	80	99	80	357	99	80	4	3	3	3
03	03	80	99	80	357	99	80	4	4	3	3
03	06	34	99	34	391	99	34	5	5	5	4
03	04	20	99	20	411	99	20	6	6	6	5

c_id	s_id	s_score	first_v	last_v	sum_v	max_v	min_v	count_v	row_id	rank_id	dense_id
01	01	80	80	82	82	82	82	2	1	1	1
01	03	80	80	80	162	82	80	2	2	1	1
01	05	76	80	76	236	82	76	3	3	3	2
01	02	70	80	70	306	82	70	4	4	4	3
01	04	50	80	50	356	82	50	5	5	5	4
01	06	31	80	31	387	82	31	6	6	6	5
02	01	90	90	90	90	90	90	1	1	1	1
02	07	89	90	89	179	90	89	2	2	2	2
02	05	87	90	87	266	90	87	3	3	3	3
02	03	80	90	80	346	90	80	4	4	4	4
02	02	60	90	60	406	90	60	5	5	5	5
02	04	30	90	30	436	90	30	6	6	6	6
03	01	99	99	99	99	99	99	1	1	1	1
03	07	98	99	98	197	99	98	2	2	2	2
03	02	80	99	80	357	99	80	4	3	3	3
03	03	80	99	80	357	99	80	4	4	3	3
03	06	34	99	34	391	99	34	5	5	5	4
03	04	20	99	20	411	99	20	6	6	6	5

c_id	s_id	s_score	first_v	last_v	sum_v	max_v	min_v	count_v	row_id	rank_id	dense_id
01	01	80	80	80	160	80	80	2	1	1	1
01	03	80	80	80	160	80	80	2	2	1	1
01	05	76	80	76	236	80	76	3	3	3	2
01	02	70	80	70	306	80	70	4	4	4	3
01	04	50	80	50	356	80	50	5	5	5	4
01	06	31	80	31	387	80	31	6	6	6	5
02	01	90	90	90	90	90	90	1	1	1	1
02	07	89	90	89	179	90	89	2	2	2	2
02	05	87	90	87	266	90	87	3	3	3	3
02	03	80	90	80	346	90	80	4	4	4	4
02	02	60	90	60	406	90	60	5	5	5	5
02	04	30	90	30	436	90	30	6	6	6	6
03	01	99	99	99	99	99	99	1	1	1	1
03	07	98	99	98	197	99	98	2	2	2	2
03	02	80	99	80	357	99	80	4	3	3	3
03	03	80	99	80	357	99	80	4	4	3	3
03	06	34	99	34	391	99	34	5	5	5	4
03	04	20	99	20	411	99	20	6	6	6	5

c_id	s_id	s_score	first_v	last_v	sum_v	max_v	min_v	count_v	row_id	rank_id	dense_id
01	01	80	80	82	82	82	82	2	1	1	1
01	03	80	80	80	162	82	80	2	2	1	1
01	05	76	80	76	236	82	76	3	3	3	2
01	02	70	80	70	306	82	70	4	4	4	3
01	04	50	80	50	356	82	50	5	5	5	4
01	06	31	80	31	387	82	31	6	6	6	5
02	01	90	90	90	90	90	90	1	1	1	1
02	07	89	90	89	179	90	89	2	2	2	2
02	05	87	90	87	266	90	87	3	3	3	3
02	03	80	90	80	346	90	80	4	4	4	4
02	02	60	90	60	406	90	60	5	5	5	5
02	04	30	90	30	436	90	30	6	6	6	6
03	01	99	99	99	99	99	99	1	1	1	1
03	07	98	99	98	197	99	98	2	2	2	2
03	02	80	99	80	357	99	80	4	3	3	3
03	03	80	99	80	357	99	80	4	4	3	3
03	06	34	99	34	391	99	34	5	5	5	4
03	04	20	99	20	411	99	20	6	6	6	5