Bug #105045 window function returns wrong result
Submitted: 27 Sep 2021 6:40 Modified: 12 Oct 2021 8:58
Reporter: casa zhang (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0, 8.0.26 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution, regression

[27 Sep 2021 6:40] casa zhang
Description:
Window function followed by order by clause, which also has complex select item will give the wrong result.

How to repeat:
mysql> create table t33(a int, b double);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t33 values(1,2.1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t33 values(2,1.4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t33 values(1,2.56);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t33 values(2,2.23);
Query OK, 1 row affected (0.00 sec)

mysql> select a, round(sum(b),2) as cnt, row_number() over(order by sum(b) desc) as rn from t33 group by a order by rn;
+------+------+----+
| a | cnt | rn |
+------+------+----+
| 1 | 3.63 | 1 |
| 2 | 3.63 | 2 |
+------+------+----+
2 rows in set (0.00 sec)

As the same, sum(b)+1 in the select lists will give the wrong results, such as 
`select a, sum(b)+1 as cnt, row_number() over(order by sum(b) desc) as rn from t33 group by a order by rn;`

mysql> select a, sum(b) as cnt, row_number() over(order by sum(b) desc) as rn from t33 group by a order by rn;
+------+------+----+
| a | cnt | rn |
+------+------+----+
| 1 | 4.66 | 1 |
| 2 | 3.63 | 2 |
+------+------+----+
2 rows in set (0.00 sec)

If we doesn't use round() function, we will get the right result.

Suggested fix:
In WindowIterator::Read() function, when reaches the end of execution, REF_SLICE_ACTIVE ref_items don't be set correctly. So `SwitchSlice(m_join, m_output_slice)` before return error.
[27 Sep 2021 6:47] MySQL Verification Team
Hello casa zhang,

Thank you for the report and feedback.

regards,
Umesh
[27 Sep 2021 6:48] MySQL Verification Team
- 5.7 - NA, with the provided test case

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(order by sum(b) desc) as rn from t33 group by a order by rn' at line 1
[27 Sep 2021 6:51] casa zhang
Switch slice before reaching execution end in WindowIterator::Read

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-Bug-105045-Switch-slice-before-reaching-execution-en.patch (application/octet-stream, text), 3.08 KiB.

[27 Sep 2021 6:53] casa zhang
OK, It seems like some sequence problem occurs in WindowIterator::Read(), wish it will be helpful.
[27 Sep 2021 6:55] MySQL Verification Team
Thank you for your Contribution.

regards,
Umesh
[28 Sep 2021 12:21] Dag Wanvik
Posted by developer:
 
This is a regression introduced in 8.0.17.
[12 Oct 2021 8:58] Jon Stephens
Documented fix as follows in the MySQL 8.0.28 changelog:

    Resetting of the active slice was not performed in
    WindowIterator::Read() at the end the end of the input result
    set for windowing. This led to reading wrong values on reaching
    the ORDER BY sort, since the number of the active slice was
    still set to 1--that is, to the items being read from the input
    table--while the ORDER BY sorting stage needs to read the values
    after computation of any window functions. For this, it needs
    the active slice to be that of the last window's output table.

    We fix this by moving the resetting of the slice to the output
    slice immediately following the read, so that it is already set
    correctly when returning at the end of the input set and moving
    on to the ordering.

    Our thanks to Casa Zhang and the Tencent team for the contribution.

Closed.