MySQL Bugs: #105560: Dynamic statement inside stored procedure that returns a null triggers cursor

Bug #105560	Dynamic statement inside stored procedure that returns a null triggers cursor
Submitted:	12 Nov 2021 21:25	Modified:	17 Nov 2021 13:45
Reporter:	Justin Levene	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0.26	OS:	Linux (aarch64)
Assigned to:		CPU Architecture:	ARM (AWS 64-bit Arm Neoverse)
Tags:	cursor, dynamic select, PREMATURE, PROCEDURE

Description:
I defined a dynamic table inside a cursor loop within a stored procedure .  When the dynamic table returns no row result, the cursor loop prematurely ended.

I tested on mySQL 8.0.26 on Windows and it was fine using MySql Community Server - GPL on Win64 (x86_64).

This only happened on the Linux ARM server using 8.0.26.  Not sure if happens on other CPU architectures.

How to repeat:
CREATE PROCEDURE `test_procedure`()
BEGIN
    declare _all json default json_array();
    declare _company_id BIGINT UNSIGNED;
    declare _loopDone BIT DEFAULT 0; # Flag if loop complete

    # Get a list of dates for jobs encompassed
    declare _curs CURSOR FOR 
	select `ID` from `table1` order by `ID`;
    declare CONTINUE HANDLER FOR NOT FOUND SET _loopDone = 1;

    OPEN _curs;
    read_loop: LOOP
        fetch _curs into _company_id;
        
        set _all = json_array_append(_all, "$", _company_id);

	if (_loopDone=1) then leave read_loop; end if;
        
        set @s = concat("select `ID` from `table_2` where `COMPANY_ID`=", _company_id," into @_something");
		PREPARE stmt FROM @s;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;

    end loop read_loop;
    CLOSE _curs;

    set @get_deletions = json_object("all", _all);
END

Now call:

call test_procedure();
select @get_deletions;

When there the table_2 statement finds nothing, _loopDone is triggered.

This does not happen on Windows 11, just Linux

Suggested fix:
Do not trigger the cursor prematurely

Hi Mr. Levene,

Thank you for your bug report.

We need further info in order to process your report.

Since you do not have problems on Windows, but do on ARM Linux, that means that the problem is in the build itself. For the moment, we only support ARM Linux on Oracle and Red Hat Linux. We do not support other builds.  You did not specify which of those two builds are you using. 

If you are supporting a build made by some third party and on some other Linux distribution, we are afraid that we can not help you.

I altered the function so that it is completely stand alone (not reliant on any external tables for ease of testing), tested it on a different Linux x86 server, and still had the same issue.  It seems that it is only Linux. 

CREATE PROCEDURE `test`()
BEGIN
    declare _all json default json_array();
    declare _company_id BIGINT UNSIGNED;
    declare _loopDone BIT DEFAULT 0; # Flag if loop complete

    # Get a list of IDs from 1 to 5 inclusive
    declare _curs CURSOR FOR 
	select `ID` from json_table('[{"ID":1},{"ID":2},{"ID":3},{"ID":4},{"ID":5}]', '$[*]' columns(`ID` INT PATH '$.ID' ERROR ON ERROR)) as `table1` order by `ID`;
    declare CONTINUE HANDLER FOR NOT FOUND SET _loopDone = 1;

    OPEN _curs;
    read_loop: LOOP
        fetch _curs into _company_id;
        
        set _all = json_array_append(_all, "$", _company_id);

		if (_loopDone=1) then leave read_loop; end if;
        
        # Select the current ID from a table with rows [1, 3, 4, 5] (2 is missing, thus it will return NULL)
        set @s = concat("select `COMPANY_ID` from json_table('[{\"COMPANY_ID\":1},{\"COMPANY_ID\":3},{\"COMPANY_ID\":4},{\"COMPANY_ID\":5}]', '$[*]' columns(`COMPANY_ID` INT PATH '$.COMPANY_ID' ERROR ON ERROR)) as `table_2`
where `COMPANY_ID`=", _company_id," into @_something");
		PREPARE stmt FROM @s;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;

    end loop read_loop;
    CLOSE _curs;

    set @test_results = json_object("all", _all);
END

Now execute the following:

call test();
select @test_results; 

The response should be "[1,2,3,4,5]" and not [1,2,3]"

Removing the " into @_something" stops the bug from happening.

Please note that if I put "set _all = json_array_append(....." after "if (_loopDone=1) then leave read_loop; end if;", then the result is [1,2].

I should have put it there for simplicity.

Tested the new test procedure on Windows 11 MySQL 8.0.26 and still have the same bug.

The bug seems to be for every OS and is not limited to Linux as previously thought and incorrectly reported.

Hi Mr. Levene,

You are quite correct.

This is not an error that resulted from the bad build.

This is simply your programming error where you did not handle correctly the last part of the definition of the user variable @s, with an improper handling of another user variable.

Not a bug.

How is this improperly handled?

I agree, @s could be a declared variable "_sql_str" or similar (I wanted to keep it short for this demo), however I can't do an "into" into a declared variable as this throws an error, so the only way to do it is to put the result into a global.  Regardless, the result of that separate prepared query should not effect the cursor result handle, which it is doing.