MySQL Bugs: #72347: Handlers for error # 1137 don't seem to execute within stored functions

Bug #72347	Handlers for error # 1137 don't seem to execute within stored functions
Submitted:	14 Apr 2014 17:44	Modified:	17 Apr 2014 15:45
Reporter:	Dillon Sadofsky	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.6.14-log	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	1137, stored function, temporary table

Description:
I originally reported this in bug 72318, but it is unrelated to that issue and is best as a separate bug.

Essentially, using the steps documented in that case, I can create functions that will cause mysql server error 1137 "Can't reopen temporary table %s" to be thrown.  However, I added both of the following to my stored function:

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

but [my code] never seemed to be called.  It doesn't matter if its a CONTINUE or an EXIT handler, neither execute.  As an aside, I was able to trap server error 1051 using the same structure.

I set this bug to S1 because I don't know of a workaround.

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;
	DECLARE CONTINUE HANDLER FOR 1137 BEGIN SET @var1 = 1; END;
	DECLARE CONTINUE HANDLER FOR SQLSTATE 'HY000' BEGIN SET @var1 = 1; END;
	
	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 ;

# Create this table to cause an undocumented error to happen
CREATE TEMPORARY TABLE TEMP_test(id INT UNSIGNED);

SET @var1 = 0;
# Throws error 1137 ("Can't reopen table: 'TEMP_test'")
SELECT f_select_from_table();

# var1 should be 1 if the HANDLER had been executed.
SELECT @var1;

Suggested fix:
Make it so that mysql server error 1137 can be trapped like the others.

Verified as described ...

After very careful analysis it was concluded that this is not a bug.

The main reason is that the opening of tables occurs before the function is executed.
Therefore the error is raised before the handler is activated. Simply put, the tables are opened outside the stored function, thus the handler logic is not activated.

However, if the calling code is put into a stored procedure, and the handling logic is moved there too, everything works.

So, here is a workaround (in a form of .test file):

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$$

CREATE PROCEDURE p1()
BEGIN
  DECLARE CONTINUE HANDLER FOR 1137 BEGIN SET @var1 = 1; END;
  DECLARE CONTINUE HANDLER FOR SQLSTATE 'HY000' BEGIN SET @var1 = 1; END;

  # Create this table to cause an undocumented error to happen
  CREATE TEMPORARY TABLE TEMP_test(id INT UNSIGNED);

  SET @var1 = 0;

  SELECT f_select_from_table();

  # var1 should be 1 if the HANDLER had been executed.
  SELECT @var1;
END$$

DELIMITER ;$$

CALL p1();

DROP PROCEDURE p1;
DROP FUNCTION f_select_from_table;
DROP FUNCTION f_update_table;
DROP FUNCTION f_insert_into_table;

I understand what you're saying, but the distinction that some errors are thrown 'before' a function is not obvious to the user.

I cannot use the suggested workaround because I need to use a stored function (I'm retrieving a single value for use in UPDATE statements).  Refer to the bug that spawned this bug for more information.

The fact that it is possible to put a set of functions into an unusable state due to the preexistance of a temporary table seems like undesired functionality to me.  Both issues have been reported as not a bug, but taken together, the represent a situation where a connection can get into a state where a (normally working) function cannot be executed and no procedural workaround can be developed.  I am disappointed that this is the case.

This all seems to be based on the fact that functions pre-explore their contents when they are invoked instead of when they are executed.  I noticed separately that when we upgraded from version 5.5 to 5.6, this made functions that contain other functions or views hundreds of times slower than they were before (even if the function or view is in unreachable code).  It would be nice if functions (like procedures) executed line by line, as is intuitive to the user.  If stored procedures could be used in more situations, I would switch to them, but our system performs some business logic at the database level, and the various restrictions on functions make this very hard.