MySQL Bugs: #110983: issue with window functions in stored procedures

Bug #110983	issue with window functions in stored procedures
Submitted:	10 May 2023 23:10	Modified:	1 Jun 2023 8:50
Reporter:	Debra Cox	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	8.0.33	OS:	Ubuntu (20.04)
Assigned to:		CPU Architecture:	x86
Tags:	regression

Description:
After updating mysql from version 8.0.32 to 8.0.33, I am having an issue with my stored procedures (which were written some time ago with some previous version of mysql).  

Window functions (eg. count over(), sum over (), avg over ()) in a stored procedure, where you do a mathematical operation on the result over the window function, give the correct result the first time you run the procedure.  Every time after that, it gives an incorrect result.

For example:  count(*) over () gives the correct result every time.  But something like (count(*) over () + 1) gives the correct result only the first time, every time after that it gives an incorrect result.    

It seems that count(*) or count(*) with a group by statement works OK.  It is just when I include the over () clause.  

And it seems to work OK if I run those queries in a console or terminal, it only happens when it's in a stored procedure.  

How to repeat:
Create a stored procedure, eg: 

CREATE PROCEDURE test2()
BEGIN
select count(*) over (rows between 5 preceding and 5 following) as A,
count(*) over (rows between 5 preceding and 5 following) + 1 as "A+1", 
count(*) over (rows between 5 preceding and 5 following) - 1 as "A-1", 
count(*) over () as B, 
count(*) over () + 1 as "B+1",
count(*) over () - 1 as "B-1"
from table1 
;
END

Run the stored procedure and get the correct results:  
A	`A+1`	`A-1`	B	`B+1`	`B-1`
6	7	5	110	111	109
7	8	6	110	111	109
8	9	7	110	111	109
9	10	8	110	111	109
10	11	9	110	111	109
11	12	10	110	111	109
11	12	10	110	111	109
11	12	10	110	111	109
11	12	10	110	111	109
11	12	10	110	111	109

Run the stored procedure again and get incorrect results: 
A	`A+1`	`A-1`	B	`B+1`	`B-1`
6	2	0	110	2	0
7	3	1	110	3	1
8	4	2	110	4	2
9	5	3	110	5	3
10	6	4	110	6	4
11	7	5	110	7	5
11	2	0	110	2	0
11	3	1	110	3	1
11	4	2	110	4	2
11	5	3	110	5	3

I get the same incorrect results every time I run the procedure after this.  

If I drop and re-create the procedure, I can again get the correct result on the first run, and then incorrect result every run after.  

if I log out and back into the database, I can get the correct result the first time I run the procedure, and then I get incorrect results every time after that, until I log out and in again.

Hello Debra Cox,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

- 8.0.32 - Not affected

 ./mtr bug110983 --nocheck-testcases
Logging: ./mtr  bug110983 --nocheck-testcases
MySQL Version 8.0.32
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
 - WARNING: Using the 'mysql-test/var' symlink
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.32/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
create table table1(id int);
insert into table1 values(1),(2),(3),(4),(5),(6);
CREATE PROCEDURE test2()
BEGIN
select count(*) over (rows between 5 preceding and 5 following) as A,
count(*) over (rows between 5 preceding and 5 following) + 1 as "A+1",
count(*) over (rows between 5 preceding and 5 following) - 1 as "A-1",
count(*) over () as B,
count(*) over () + 1 as "B+1",
count(*) over () - 1 as "B-1" from table1 ;
END $$
call test2()$$
A       A+1     A-1     B       B+1     B-1
6       7       5       6       7       5
6       7       5       6       7       5
6       7       5       6       7       5
6       7       5       6       7       5
6       7       5       6       7       5
6       7       5       6       7       5
call test2()$$
A       A+1     A-1     B       B+1     B-1
6       7       5       6       7       5
6       7       5       6       7       5
6       7       5       6       7       5
6       7       5       6       7       5
6       7       5       6       7       5
6       7       5       6       7       5
[ 50%] main.bug110983                            [ pass ]     11
[100%] shutdown_report                           [ pass ]
------------------------------------------------------------------------------

- 8.0.33 - affected, looks like regression

 ./mtr bug110983 --nocheck-testcases
Logging: ./mtr  bug110983 --nocheck-testcases
MySQL Version 8.0.33
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
 - WARNING: Using the 'mysql-test/var' symlink
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.33/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
create table table1(id int);
insert into table1 values(1),(2),(3),(4),(5),(6);
CREATE PROCEDURE test2()
BEGIN
select count(*) over (rows between 5 preceding and 5 following) as A,
count(*) over (rows between 5 preceding and 5 following) + 1 as "A+1",
count(*) over (rows between 5 preceding and 5 following) - 1 as "A-1",
count(*) over () as B,
count(*) over () + 1 as "B+1",
count(*) over () - 1 as "B-1" from table1 ;
END $$
call test2()$$
A       A+1     A-1     B       B+1     B-1
6       7       5       6       7       5
6       7       5       6       7       5
6       7       5       6       7       5
6       7       5       6       7       5
6       7       5       6       7       5
6       7       5       6       7       5
call test2()$$
A       A+1     A-1     B       B+1     B-1
6       2       0       6       2       0
6       3       1       6       3       1
6       4       2       6       4       2
6       5       3       6       5       3
6       6       4       6       6       4
6       7       5       6       7       5
[ 50%] main.bug110983                            [ pass ]     10
[100%] shutdown_report                           [ pass ]

Posted by developer:
 
Likely same root cause as Bug#35340987 (external bug Bug #110847)

Posted by developer:
 
Fixed by the patch for Bug#35340987.

Posted by developer:
 
 Bug#35340987 has been backported to 8.0 branch, so this bug is now also fixed on said branch.

Documented fix as follows in the MySQL 8.0.36 changelog:

    Performing an arithmetic operation on the result over a window
    function in a stored procedure gave the correct result the first
    time the procedure was executed, but returned an incorrect
    result on all subsequent invocations.

Closed.