MySQL Bugs: #105045: window function returns wrong result

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

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.

Hello casa zhang,

Thank you for the report and feedback.

regards,
Umesh

- 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

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.

OK, It seems like some sequence problem occurs in WindowIterator::Read(), wish it will be helpful.

Thank you for your Contribution.

regards,
Umesh

Posted by developer:
 
This is a regression introduced in 8.0.17.

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.