Bug #104715 Several calls of stored procedure return different values
Submitted: 25 Aug 6:22 Modified: 7 Sep 13:25
Reporter: Михаил Ильин Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:8.0.26, 8.0.22+ OS:Microsoft Windows (Windows 10 Enterprise 20H2)
Assigned to: CPU Architecture:x86
Tags: regression

[25 Aug 6:22] Михаил Ильин
Description:
When called twice, the stored procedure returns an incorrect value as an out-parameter. If I recreate the stored procedure, it works correctly just once.

The code is below:

DROP TABLE IF EXISTS vs_server;
CREATE TABLE vs_server (nGroupId INT);
INSERT INTO vs_server(nGroupId) VALUES (15), (21);

DROP PROCEDURE IF EXISTS test_proc;

CREATE PROCEDURE test_proc (OUT nVal INT)
BEGIN

	DROP TEMPORARY TABLE IF EXISTS tmp_table;
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_table (
        group_id INT       
    )ENGINE=MEMORY DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`;

    INSERT INTO tmp_table (group_id) VALUES (199);
	
    SET nVal = 0 ;

    IF (EXISTS (SELECT *  FROM vs_server 
INNER JOIN  tmp_table ON vs_server.nGroupId = tmp_table.group_id )) THEN
        SET nVal = 1 ;
    END IF;

DROP TEMPORARY TABLE IF EXISTS tmp_table;
END;

How to repeat:
Do the folowing:

SET @arg = NULL;
CALL test_proc(@arg);
SELECT @arg ; -- @arg = 0

SET @arg = NULL;
CALL test_proc(@arg);
SELECT @arg ; -- @arg == 1

Expected: @arg = 0
[25 Aug 7:50] MySQL Verification Team
Hello Михаил Ильин,

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

regards,
Umesh
[25 Aug 8:31] Михаил Ильин
TRUNCATE TABLE instead of DROP TEMPORARY TABLE IF EXISTS does not solve the problem
[7 Sep 13:25] Михаил Ильин
No available workaround for this bug
[13 Sep 7:09] Roy Lyseng
The problem is related to using EXISTS within IF, and using a temporary table that is dropped and created within the procedure.
A possible workaround could be to rewrite the procedure to not use a temporary table, or rewrite EXISTS with another construct, e.g like this:

CREATE PROCEDURE test_proc (OUT nVal INT)
BEGIN
  DROP TEMPORARY TABLE IF EXISTS tmp_table;
  CREATE TEMPORARY TABLE IF NOT EXISTS tmp_table (
    group_id INT
  ) ENGINE=MEMORY DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`;

  INSERT INTO tmp_table (group_id) VALUES (199);

  SET nVal = (SELECT count(*) > 0
              FROM vs_server INNER JOIN tmp_table
                   ON vs_server.nGroupId = tmp_table.group_id);

  DROP TEMPORARY TABLE IF EXISTS tmp_table;
END;

The new procedure could be less efficient than the old one.