MySQL Bugs: #104525: EXISTS in Stored Proc returns incorrect result after first SP execution

Bug #104525	EXISTS in Stored Proc returns incorrect result after first SP execution
Submitted:	3 Aug 2021 18:18	Modified:	1 Sep 2021 19:33
Reporter:	Robert Swiston	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.26	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In a Stored Procedure, IF EXISTS may execute the THEN clause although the SELECT inside the EXISTS selects no records.  In our test case, the SP works as expected the first time, but all subsequent invocations of the SP exhibit the bad behavior.

How to repeat:
SQL code to create and execute a Stored Procedure that demonstrates the issue.  Note that, in my environment, the SP works as expected the first time, but all subsequent invocations fail.

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_existsTest` $$

CREATE PROCEDURE `sp_existsTest` (IN _id BIGINT)
BEGIN

    DROP TEMPORARY TABLE IF EXISTS tmpA;
    CREATE TEMPORARY TABLE tmpA (id BIGINT NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;

	INSERT INTO tmpA VALUES (_id);

	DROP TEMPORARY TABLE IF EXISTS tmpB;
	CREATE TEMPORARY TABLE tmpB (
		`companyId` bigint NOT NULL,
		`createBy` bigint NULL,
		PRIMARY KEY (`companyId`)
	) ENGINE=InnoDB;
	INSERT INTO tmpB VALUES
		(0, 0),
		(1, 1),
		(2, 2),
		(3, 3),
		(4, 4);

    IF EXISTS (
		SELECT 1 FROM tmpB INNER JOIN tmpA ON tmpA.id = tmpB.companyId WHERE tmpB.createBy <> _id
	) THEN
		SELECT 'BROKEN';
    END IF;

    DROP TEMPORARY TABLE IF EXISTS tmpB, tmpA;
END;
$$

DELIMITER ;
CALL sp_existsTest(3);

Thank you for the bug report, verified as described on 8.0. 5.7 version not affected for this bug.

We had a similar problem but the effect was the opposite of the bug report.

In our case, IF EXISTS may NOT execute the THEN clause even though the SELECT inside the EXISTS selects records.
Similar to the original bug report, the SP works as expected the first time, but all subsequent invocations of the SP exhibit the bad behavior.

Here is an example:

DROP PROCEDURE IF EXISTS TestIfExistsStmt;

DELIMITER $$

CREATE PROCEDURE TestIfExistsStmt (
    IN 		inputValue		INT,
    IN		expectedResult	VARCHAR(100)
)
BEGIN

	DROP TEMPORARY TABLE IF EXISTS t_input;
 	CREATE TEMPORARY TABLE t_input (
		theValue			BIGINT
  	);
    
	DROP TEMPORARY TABLE IF EXISTS t_lookup_table;
 	CREATE TEMPORARY TABLE t_lookup_table (
		theValue			BIGINT
  	);
   

	INSERT INTO t_input (theValue)
	SELECT inputValue;

	INSERT INTO t_lookup_table (theValue)
	SELECT 1
	UNION
	SELECT 2
	UNION
	SELECT 3;

	IF EXISTS (SELECT 1 FROM t_input AS i LEFT OUTER JOIN t_lookup_table AS lt ON i.theValue = lt.theValue WHERE lt.theValue IS NULL) THEN	
		SELECT 'Missing', expectedResult;
	ELSE
		SELECT 'Exists', expectedResult;
	END IF;

END $$

DELIMITER ;

CALL TestIfExistsStmt(5, 'Missing');		-- works because first call

CALL TestIfExistsStmt(1, 'Exists');		-- works because code now always runs ELSE

CALL TestIfExistsStmt(5, 'Missing');		-- fails because code should run THEN, but runs ELSE

We were able to reproduce both issues on 8.0.23.  
We were not able to reproduce either on 8.0.21, if that helps.

The problem seems to occur when a temp table is referenced, and the SELECT includes a WHERE clause.

Q: Should this be raised as a separate bug, or included with this one?

Q: We are looking to remediate by replacing the IF EXISTS statements.  Are other queries affected by this bug that we would need to also address?

Is there a timeline for fixing this? It would be helpful to have an idea when a fix will be available.

We need to verify this, but apparently this bug is a duplicate of another bug that is fixed and is scheduled for inclusion in the next MySQL release.

That would be great news - thank you, @Roy.