MySQL Bugs: #104715: Several calls of stored procedure return different values

Bug #104715	Several calls of stored procedure return different values
Submitted:	25 Aug 2021 6:22	Modified:	7 Sep 2021 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:	Windows (Windows 10 Enterprise 20H2)
Assigned to:		CPU Architecture:	x86
Tags:	regression

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

Hello Михаил Ильин,

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

regards,
Umesh

TRUNCATE TABLE instead of DROP TEMPORARY TABLE IF EXISTS does not solve the problem

No available workaround for this bug

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.