A possible bug when MySQL executes the sum function on the window function

When using MySql's window function to collect statistical data, I found a small problem and would like to discuss it with you.

Environment configuration:

• mysql-installer-community-8.0.20.0
  

Problem: When sum is executed on the window function, if there is duplicate data, the same data will be added directly instead of step by step.

Problem Description

Data: In a grade table, there are three fields: student s_id, course c_id, grade s_score.

The query conditions query the student score ranking and score summary for each course.

Query results: It was found that if the same course has the same score, the summary score is not accumulated, but added all at once.

Create a data table

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

Inserting Data

-- 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);

Query data

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 was (partition by c_id order by s_score desc);

Query results

Looking at the statistical results of course number 01, in the sum_v column of the first row of data, the first two data are both 160. According to the function principle, the data should be 80 and 160.

Looking at the statistical results of course number 02, we find that the results are correct. The first sum_v is 90 and the second is 179.

The actual display is inconsistent with the expected result, something went wrong.

Thinking Verification

The data for course number 02 is correct, but that for 01 is incorrect. The difference between 01 and 02 is that the first two students in course 01 have the same score of 80.

Could it be that the scores were the same, which led to an error in the sum?

To verify this problem, change the score of course number 01 and student number 01 to 82, and then execute the query. The result is as follows

The sum_v column shows 82 and 162, which is consistent with the expected results.

This shows that when sum is executed on the window function, if there is duplicate data, the same data will be added directly instead of step by step.

Other Sql verification and comparison

After the above verification, Mysql did have an error when summing, and it was not accumulated step by step.

I checked whether the same problem exists on other platforms in Sqlite Expert 5.3 and found the same results.

This is strange. If an error occurs in the implementation of Mysql, the probability of Sqlite making the same error is much smaller.

Could it be due to the characteristics of the sum and window functions when used together? Welcome to discuss and research together.

Summarize

This is the end of this article about a possible bug in MySQL's execution of the sum function on the window function. For more information about MySQL's execution of the sum function on the window function, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!