[MDEV-11038] Error creating temporary tables in procedures - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Duplicate
Affects Version/s: 10.1.14, 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.0.29, 10.1.21, 10.2.5
Component/s: Data Definition - Procedure
Labels:
- upstream-fixed
Environment:
CentOS 7
MariaDb 10.1.14

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

		FROM

			tbl_temporary tt

		) tt

	ORDER BY

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.

Attachments

Issue Links

duplicates

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

Closed

links to

Bug #68972 Can't find temporary table

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2016-10-12 15:25

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

Elena Stepanova added a comment - 2016-10-12 15:25 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

Oleksandr Byelkin added a comment - 2017-04-20 15:15

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".

Oleksandr Byelkin added a comment - 2017-04-20 15:15 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".

Oleksandr Byelkin added a comment - 2017-04-20 15:21

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

		FROM

			tbl_temporary tt

		) tt

	ORDER BY

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`@`%`;

Oleksandr Byelkin added a comment - 2017-04-20 15:21 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`@`%`;

People

Assignee:: Oleksandr Byelkin

Reporter:: Diego Hellas

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2016-10-12 13:51

Updated:: 2017-04-20 15:21

Resolved:: 2017-04-20 15:20

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server