Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11038

Error creating temporary tables in procedures

    XMLWordPrintable

    Details

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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              diegohellas Diego Hellas
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: