MySQL Bugs: #101550: when running stored procedures twice, the variables gets nullified

Bug #101550	when running stored procedures twice, the variables gets nullified
Submitted:	10 Nov 2020 22:29	Modified:	11 Nov 2020 13:59
Reporter:	Dina Tantawy	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	8.0.22	OS:	Windows (windows server 2012)
Assigned to:		CPU Architecture:	Any
Tags:	null, stored procedures, variables

Description:
I upgraded from 8.0.18 to 8.0.22 then the following happened,

I have a stored procedure, I call it twice in a row with the same parameters,
the first time it returns result, the second time it returns null

CREATE DEFINER=`root`@`localhost` PROCEDURE `add_boiler_test`(
IN `FID` INT(11),
IN `name` VARCHAR(100))
boiler:BEGIN

set @var=null;
set @var=(select boiler.ID   from boiler where  boiler.ID = null  or (A_Name=`name` and FacilityID = FID) limit 1);
select @var, `name`,FID;

END

call add_boiler_test(28251, 'غلايه 1');

when I assign the variable using "into" it works fine.

How to repeat:
create a table called boiler with three fields ID, A_Name, FacilityID
add in it in data and try the stored procedure stated in the description.

Thank you for the bug report. I couldn't repeat the behavior reported following your description steps, then please provide the complete SQL script, run it and print it here with the the result. Thanks in advance.

I can't reproduce outside production environment, 

it happened after migration from 8.0.15 to 8.0.22 (I can't revert version to reproduce)

But I can add some more details
====================================
all stored procedures using 
    " set @anyVariable = ( select anything from tableWasUsedInMigration)" 
have the same problem 
=====================
I tried to export schema, drop it then reimport, problem still exists
====================
if I changed the code into 
     select anything into @anyVariable from ....
it works without a problem
===================
If I created a new table, and make the stored procedure run on it, it has no problem as well
=====================
I tried to run the statement without stored procedure several times, it has no problem either
=====================

Thank you for your time.

Thank you for the feedback. To verify this bug report we need a reproducible test case, if you will be able to provide it please add and comment here.

I'm also seeing this issue, I'm on 8.0.28, I also had this issue on 8.0.27

I have a set of reasonably complex stored procedures and this doesn't happen on all of them but I've found it on one particular instance (I haven't tested further).

It seems similar to the problem above, I pass a set of parameters into my sproc, inside the sproc I have code which looks like

@foo = foo

and I use @foo in the query

First time I run this query it runs fine, second time it returns nulls on one of the columns

If I run the exact same query outside of a stored procedure then it works fine every time.

I know you folks are going to want a repro but I'm not sure how to get you a simple repro, my DB is large and the queries are relatively complex. 

The data isn't private so I'm happy to somehow get a copy of the data and the sprocs to you to test if that will help

This is on MacOSX 12.2.1, Apple M1 Pro, 32GB memory if that helps?

Dropping and reopening the connection to the database allows me to re-run the query (or at least that's what seems to happen)