MySQL Bugs: #99493: While-loop in Function behaves differently than in Procedure

Bug #99493	While-loop in Function behaves differently than in Procedure - does not exit
Submitted:	8 May 2020 19:46	Modified:	14 May 2020 16:33
Reporter:	Anatoliy Schmoukler	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.7, 8.0	OS:	Linux (AWS Linux deployment)
Assigned to:		CPU Architecture:	x86 (x86_64)

Description:
Certain code (see "how to repeat") in function behaves differently than in procedure when WHILE-loop and date functions are used

How to repeat:
Function Definition:

delimiter $$
CREATE  FUNCTION `DelayResponse`(pSeconds int) RETURNS varchar(1)  
BEGIN 
    DECLARE x int;
    DECLARE EndDateTime datetime ;
    Set x = 0;
    set EndDateTime = DATE_ADD(now(), INTERVAL pSeconds second); 
    
    WHILE x <= 0 DO
        set x = now() - EndDateTime;
    END WHILE;
    RETURN 'X';
END$$
delimiter ;

Call:
Select DelayResponse(5) x;

Expected behavior: function completes execution in 5 seconds.
Actual behavior: function continue to loop while and does not complete.
---------------------------------

Procedure definition:

delimiter $$
CREATE  procedure `DelayResponse`(pSeconds int) 
BEGIN 
    DECLARE x int;
    DECLARE EndDateTime datetime ;
    Set x = 0;
    set EndDateTime = DATE_ADD(now(), INTERVAL pSeconds second); 

    WHILE x <= 0 DO
        set x = now() - EndDateTime;
    END WHILE;
	
END$$
delimiter ;

Call:
call DelayResponse(5)

Expected behavior: procedure completes execution in 5 seconds.
Actual behavior: as expected.

Suggested fix:
Make WHILE behave same in functions and procedures - fix function behavior

While this is not deadly in my situation, this is has potential for destruction to others

Too fast. This could be not a bug

https://stackoverflow.com/questions/61685611/while-loop-is-not-exiting-after-set-number-of...

Hi Mr. Schmoukler,

Thank you for your bug report.

However, I do not see how is this a bug. You have programming errors in your stored routines.

Let us just look at this part of the SQL code:

-----------------------------

    Set x = 0;
    set EndDateTime = DATE_ADD(now(), INTERVAL pSeconds second); 

    WHILE x <= 0 DO
        set x = now() - EndDateTime;
    END WHILE;

----------------------------

First you set x to 0 and next time when you use that local variable you compare it to zero again.

Next, EndDateTime is at the start greater then current moment for, exempli gratia, five seconds.

Next time, when less then second away you make `x` a negative value. This is totally unclear.

Also, execution can very much depend on the load of the server.

Hence, we need you to make your report more precise.

As my last previous comment with a link shows, this is not a bug per documentation. But there is no programming error. This program works in procedure but not function. To determine if it is a true bug, is up to you.

"First you set x to 0 and next time when you use that local variable you compare it to zero again." - yes, I set 'x' to 0 and since condition is 'x <= 0', it enters the loop and resets the 'x'

"Next, EndDateTime is at the start greater then current moment for, exempli gratia, five seconds." - correct

"Next time, when less then second away you make `x` a negative value. This is totally unclear." - yes. what is wrong with negative value? if 'now()' is lesser that 'EndDateTime', subtraction makes a negative value and 'while x <= 0' will evaluate to 'true' or '1'. When 'now() - EndDateTime' will become positive, loop will finish. But it will never happen -

!!!
It all comes down to the fact that 'now()' is actually obtained at the beginning of function and only once. While in procedure, 'now()' obtained once per statement.
!!!

"Also, execution can very much depend on the load of the server." - absolutely don't care about this, this is a unit test that runs with no other load.

"Hence, we need you to make your report more precise." - I believe, I did all possible. In fact, original report states a difference in behavior in Function versus the Procedure. The code is proven to be correct and many MySql professionals agreed that code is fine and were surprised themselves in such behavior. Solution is to use 'sysdate()', and same code works fine

Hi Mr. Schmoukler,

Thank you for your feedback.

First of all, let me inform you that 5.7.12 is an ancient release and that there are so many bugs fixed interim.

Hence, please run your test case against the latest release of the version 5.7 and then with 8.0.20.

Please, let us know the results.

Thanks in advance.

Hi Mr. Schmoukler,

I have tested your procedure and function on the latest 5.7 and 8.0.

In both cases, your function is in the state of "Opening tables".

Hence, this is a verified bug report, that is valid for both, 5.7 and 8.0.

Thank you for your report.

Hi Sinisa,

I use MySql version supported by our Amazon Web Services, so version is out of my control.

Can you [in short] explain what this means -- "In both cases, your function is in the state of "Opening tables"."

So, you found it as a legit bug? Thank you for looking into this

You are welcome.