Bug #3426 IF x IS NULL in stored procedure fails on second call within connection

• Submitted: 9 Apr 2004 4:19
• Modified: 28 May 2004 17:36
• Reporter: Arnold Daniels
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.0
• OS: Linux (linux)
• Assigned to: Bugs System
• CPU Architecture: Any

[9 Apr 2004 4:19] Arnold Daniels

Description:
The first time the statement IF x IS NULL is preformed the code works correctly. But the second, etc. time the previous conclusion of this statement is used.

How to repeat:
--------------------------------------------
DROP PROCEDURE IF EXISTS set_stamped_time
GO

CREATE PROCEDURE set_stamped_time(in_time INT UNSIGNED, OUT x INT)
BEGIN
	IF in_time IS NULL THEN
		SET @stamped_time=UNIX_TIMESTAMP();
		SET x=1;
	ELSE
		SET @stamped_time=in_time;
		SET x=2;
	END IF;
END
GO

CALL set_stamped_time(1000, @i)
GO

SELECT @i, FROM_UNIXTIME(@stamped_time, '%d-%m-%Y %h:%i:%s') AS time
GO 

CALL set_stamped_time(NULL, @i)
GO

SELECT @i, FROM_UNIXTIME(@stamped_time, '%d-%m-%Y %h:%i:%s') AS time
GO 
------ GIVES: -------
No rows affected.
Er zijn geen resultaten meer.
(0 row(s) returned)
No rows affected.
Er zijn geen resultaten meer.
(0 row(s) returned)
@i  time                
--- ------------------- 
2   01-01-1970 01:16:40 
Er zijn geen resultaten meer.
(1 row(s) returned)
No rows affected.
Er zijn geen resultaten meer.
(0 row(s) returned)
@i  time                
--- ------------------- 
2   <NULL>              
Er zijn geen resultaten meer.
(1 row(s) returned)
--------------------------------------------

DROP PROCEDURE IF EXISTS set_stamped_time
GO

CREATE PROCEDURE set_stamped_time(in_time INT UNSIGNED, OUT x INT)
BEGIN
	IF in_time IS NULL THEN
		SET @stamped_time=UNIX_TIMESTAMP();
		SET x=1;
	ELSE
		SET @stamped_time=in_time;
		SET x=2;
	END IF;
END
GO

CALL set_stamped_time(NULL, @i)
GO

SELECT @i, FROM_UNIXTIME(@stamped_time, '%d-%m-%Y %h:%i:%s') AS time
GO 

CALL set_stamped_time(1000, @i)
GO

SELECT @i, FROM_UNIXTIME(@stamped_time, '%d-%m-%Y %h:%i:%s') AS time
GO
------ GIVES: -------
No rows affected.
Er zijn geen resultaten meer.
(0 row(s) returned)
No rows affected.
Er zijn geen resultaten meer.
(0 row(s) returned)
@i  time                
--- ------------------- 
1   09-04-2004 02:50:40 
Er zijn geen resultaten meer.
(1 row(s) returned)
No rows affected.
Er zijn geen resultaten meer.
(0 row(s) returned)
@i  time                
--- ------------------- 
1   09-04-2004 02:50:40 
Er zijn geen resultaten meer.
(1 row(s) returned)

[28 May 2004 17:36] Per-Erik Martin

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html