MySQL Bugs: #72318: Stored function throws error 1137 if temporary table was not created right then

Bug #72318	Stored function throws error 1137 if temporary table was not created right then
Submitted:	11 Apr 2014 17:36	Modified:	14 Apr 2014 17:54
Reporter:	Dillon Sadofsky	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.6.14-log	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	error 1137, stored procedure, temporary table

Description:
First some background.  The manual states this:

"- You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:

mysql> SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'

This error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function."

This implies that if aliases are not used, a temporary table can be referenced more than once in stored functions.  And in fact, that seems to be true.  However, error 1137 ("Can't reopen table") is thrown when a stored procedure attempts to open a temporary table (without aliases).  However, in my experience, it only seems to be thrown if the TEMPORARY TABLE is created before the function is invoked.  This makes me think this is an bug.

The above manual entry was cased in bug #60718, which I believe is a similar issue.  In that bug, the OP did not alias the table, but still got the error.  I believe this is because he created the temp table OUTSIDE of the function.  Similar to that bug, I do not believe this is related to the request in bug #10327 (allowing a single query to reference/open a TEMP table more than once).  I only bring this up because bug #60718 was remarked to be related to this which I believe is incorrect.

In the reproduction steps I have included, you'll see that you can have sub-functions that insert, update, and select from the SAME temporary table, but only if the table was created during execution of the function.  No matter whether MySQL considers this 'by design', this is inconsistent behavior because I CAN reopen the same temporary table twice, just not if the table pre-dates the function call.

Since there is a workaround (create temp table and drop it in function) I'm marking this as S3.  However, this is somewhat critical for me because the workaround will fail if there is an error in the function before the DROP statement (causing the TEMPORARY TABLE to persist outside the function and interfere with future calls).  I don't like any bug that can cause further calls to the function to fail.

How to repeat:
DELIMITER $$
DROP FUNCTION IF EXISTS f_insert_into_table$$
DROP FUNCTION IF EXISTS f_update_table$$
DROP FUNCTION IF EXISTS f_select_from_table$$

# function used to demonstrate a sub-function inserting into a temp table without aliases
CREATE FUNCTION `f_insert_into_table`()
    RETURNS BOOL
    LANGUAGE SQL DETERMINISTIC MODIFIES SQL DATA 
    BEGIN
	INSERT INTO TEMP_test (id) VALUES (1);
	RETURN TRUE;
    END$$
 
# A function that shows a temp table CAN be reopened in a separate function (without aliases)
CREATE FUNCTION `f_update_table`()
    RETURNS BOOL
    LANGUAGE SQL DETERMINISTIC MODIFIES SQL DATA 
    BEGIN
	IF (f_insert_into_table()) THEN
		UPDATE TEMP_test SET id = 2;
		RETURN TRUE;
	END IF;
	RETURN FALSE;
    END$$

# This function creates the temp table and calls the above 2 functions, then selects from the table (no aliases).  This works so long as the table didn't exist beforehand
CREATE FUNCTION `f_select_from_table`()
    RETURNS INT UNSIGNED
    LANGUAGE SQL DETERMINISTIC MODIFIES SQL DATA 
    BEGIN
	DECLARE val INT UNSIGNED DEFAULT 0;
	CREATE TEMPORARY TABLE IF NOT EXISTS TEMP_test (id INT UNSIGNED);
	
	IF (f_update_table()) THEN
		SELECT id INTO val FROM TEMP_test LIMIT 1;
	END IF;
	
	DROP TEMPORARY TABLE TEMP_test;
	RETURN val;
    END$$
    
DELIMITER ;

# Works
SELECT f_select_from_table();

# If the table pre-exists (possibly due to an error before the DROP), we'll have a problem
CREATE TEMPORARY TABLE TEMP_test(id INT UNSIGNED);

# Throws error 1137 ("Can't reopen table: 'TEMP_test'")
SELECT f_select_from_table();

# works again
DROP TEMPORARY TABLE IF EXISTS TEMP_test;
SELECT f_select_from_table();

Suggested fix:
* Investigate why TEMPORARY TABLEs can be referenced in multiple statements in multiple functions, but only if that table was created in said tables.
* Then, either:
** Update documentation to include the fact that MySQL has another circumstance where temp tables inexplicably work different than what is intuitive.
** or, Fix the issue

Just an additional interesting detail.  If I add DROP TEMPORARY TABLE IF EXISTS to the start of the function (so that it will be dropped and recreated within the function).  It does NOT help.

The behavior seems to be that the table gets successfully dropped, recreated, repopulated, then the SELECT fails.  If the SELECT is commented out, execution progresses correctly, without error.

This is somewhat critical for my users because, if I have an error when populating the temp table, I enter a state where the function will continue to fail until the user establishes a new connection.

Also, maybe I should submit another bug for this, but I thought I could work around the issue with:

DECLARE CONTINUE HANDLER FOR 1137 BEGIN [my code] END;
DECLARE CONTINUE HANDLER FOR SQLSTATE 'HY000' BEGIN [my code] END;

However, [my code] never seems to execute.  Is this a known issue in functions? I'm able to successfully trap error 1051, but for some reason 1137 seems to be untrappable.  This really makes this bug terrible for my situation, as I cannot create a self-contained function that is guaranteed to perform the task I need.  Please advise.  I would prefer not to move to a permanent table, as that would require serious retooling and introduce inter-connection locking since I have multi-user concurrency to worry about.

Regarding the behavior of the temporary tables within the stored routines, this is expected behavior, but totally undocumented. Hence, I verify this bug as a documentation bug that needs to be addressed.

Regarding the error 1137 not being caught by a CONTINUE HANDLER, this is a server bug. Would you be so kind to create a new bug for that this problem so that it could be handled as a bug in stored routines within the server. After you do it, please be kind to report its number in this entry. Thanks in advance.

As per your request, bug # 72347 has been created.

However, before we close this, I just wanted to make sure we're on the same page.  What we have here is inconsistent behavior.  TEMPORARY TABLEs *can* be used inside of functions and subfunctions (they can even be created in one and modified in another), but a different set of rules apply if the TABLE was created outside the function execution?

Something that seems strange about this is in my own testing (not necessarily in the sample functions I gave), calling the topmost function with a pre-existing temp table would work, so long as I commented out the SELECT statement in the topmost function (the subfunctions still ran DELETE/INSERT on the temp table without error).  I did not create test cases for this because I assumed the issue I reported would be considered inconsistent and counter-intuitive behavior (and therefore something worth changing).

I hope that something can be done about this or at least bug 72347 because it is now difficult for me to move forward.  I didn't really want to use a TEMPORARY TABLE for this purpose, but since I cannot use prepared statements to execute dynamic SQL inside a stored function, it was the only way I could build up a complex result set.  A non-temporary table would require session variables, truncating nightly EVENTs, synchronization locks, slower indices, and is generally going to hurt my use case of executing this function many thousands of times in a row, performance wise.  Any suggestions for workarounds are appreciated.

Changing the category , after deeper analysis of the problem.