[MDEV-11038]  Error creating temporary tables in procedures Created: 2016-10-12  Updated: 2017-04-20  Resolved: 2017-04-20

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Procedure
Affects Version/s: 10.1.14, 10.0, 10.1, 10.2
Fix Version/s: 10.0.29, 10.1.21, 10.2.5

Type: Bug Priority: Major
Reporter: Diego Hellas Assignee: Oleksandr Byelkin
Resolution: Duplicate Votes: 0
Labels: upstream-fixed
Environment:

CentOS 7
MariaDb 10.1.14

Issue Links:
Duplicate
duplicates MDEV-9084 Calling a stored function from a nes... Closed
Sprint: 10.1.23

 Description   

Very strange situation to create and use temporary tables in procedures.

In some procedures, it runs normally at first, but when it is re-executed in the same session the error happens. we found that this happens whenever we have a CREATE TABLE SELECT and SELECT is used any function. If you do not function being used, the error does not happen.

example:

DELIMITER $$
 
 
 
DROP PROCEDURE IF EXISTS `sp_fail_temp_table`$$
 
 
 
CREATE DEFINER=`xpto`@`%` PROCEDURE `sp_fail_temp_table`()
 
BEGIN
 
	/**
 
	 * Creating temporary table
 
	 */
 
	DROP TEMPORARY TABLE IF EXISTS tbl_temporary;
 
 
 
	CREATE TEMPORARY TABLE IF NOT EXISTS tbl_temporary
 
	(
 
		`a` VARCHAR(64)
 
	)
 
	;
 
 
 
	/**
 
	 * Creating second temporary table
 
	 * the call to a function makes it fail on the second time
 
	 * this stored procedure is called
 
	 */
 
	DROP TEMPORARY TABLE IF EXISTS tbl_second_temp;
 
 
 
	CREATE TEMPORARY TABLE tbl_second_temp
 
	SELECT 
		fn_return_zero() AS temp_amount
 
	FROM
 
		(
 
		SELECT
 
			a
 
		FROM
 
			tbl_temporary tt
 
		) tt
 
	ORDER BY
 
		a
 
	;
 
 
 
END$$
 
 
 
DELIMITER ;
 
 
 
 
 
DELIMITER $$
 
DROP FUNCTION IF EXISTS fn_return_zero$$
 
CREATE DEFINER=`xpto`@`%` FUNCTION fn_return_zero() RETURNS FLOAT(10,2)
 
    READS SQL DATA
 
BEGIN
 
	RETURN 0;
 
END $$
 
 
 
DELIMITER ;
 
 
 
CALL sp_fail_temp_table();

Run the procedure twice.

 Comments   
Comment by Elena Stepanova [ 2016-10-12 ]

diegohellas, thanks for the report and test case.

Reproducible on all 10.x versions.
The problem existed in 5.6/5.7 too, but is fixed in the latest releases. apparently in the scope of
https://bugs.mysql.com/bug.php?id=68972

Comment by Oleksandr Byelkin [ 2017-04-20 ]

following commit fixes the problem:

commit e4978d26b79120c58706e57fc66e4de1ec4b230c
Author: Dmitry Lenev <dmitry.lenev@oracle.com>
Date: Mon Jul 25 16:06:52 2016 +0300

MDEV-9084 Calling a stored function from a nested select from temporary table causes unpredictable behavior

Cherry-pick: f4a0af070ce49abae60040f6f32e1074309c27fb
Author: Dmitry Lenev <dmitry.lenev@oracle.com>
Date: Mon Jul 25 16:06:52 2016 +0300

Fix for bug #16672723 "CAN'T FIND TEMPORARY TABLE".

Comment by Oleksandr Byelkin [ 2017-04-20 ] 

CREATE USER `xpto`@`%`;
 
GRANT ALL on test.* to `xpto`@`%`;
 
 
 
 
 
DELIMITER $$;
 
 
 
CREATE DEFINER=`xpto`@`%` PROCEDURE `sp_fail_temp_table`()
 
BEGIN
 
	/**
 
	 * Creating temporary table
 
	 */
 
	DROP TEMPORARY TABLE IF EXISTS tbl_temporary;
 
 
	CREATE TEMPORARY TABLE IF NOT EXISTS tbl_temporary
 
	(
 
		`a` VARCHAR(64)
 
	)
 
	;
 
 
	/**
 
	 * Creating second temporary table
 
	 * the call to a function makes it fail on the second time
 
	 * this stored procedure is called
 
	 */
 
	DROP TEMPORARY TABLE IF EXISTS tbl_second_temp;
 
 
	CREATE TEMPORARY TABLE tbl_second_temp
 
	SELECT 
		fn_return_zero() AS temp_amount
 
	FROM
 
		(
 
		SELECT
 
			a
 
		FROM
 
			tbl_temporary tt
 
		) tt
 
	ORDER BY
 
		a
 
	;
 
 
END$$
 
 
CREATE DEFINER=`xpto`@`%` FUNCTION fn_return_zero() RETURNS FLOAT(10,2)
 
    READS SQL DATA
 
BEGIN
 
	RETURN 0;
 
END $$
 
 
DELIMITER ;$$
 
 
CALL sp_fail_temp_table();
 
CALL sp_fail_temp_table();
 
 
 
drop function fn_return_zero;
 
drop procedure sp_fail_temp_table;
 
drop user `xpto`@`%`;

Generated at Thu Feb 08 07:46:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.