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

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

    XMLWordPrintable

Details

    Description

      Using a Stored Procedure, creating a table and populating the table in a single statement, using a nested select as virtual table for the data, and having that nested select obtain data from a temporary table and call a stored function, causes the references to the temporary table to be lost on all runs after the first. Sequential calls fail with "1146 .Table test,tmpValues doesn't exist'

      Code to setup the test and duplicate as follows:

      USE `test`;
      DROP procedure IF EXISTS `testMariaBug`;
       
      DELIMITER $$
      USE `test`$$
      CREATE DEFINER=`root`@`localhost` PROCEDURE `testMariaBug`()
      BEGIN
             DROP  TABLE IF EXISTS tmpValues;
          CREATE  temporary TABLE tmpValues (`value` INT NOT NULL )
      	select 1 `value`;
          
      	drop table if exists tmpOutput;
      	create  table tmpOutput (
      		`COL` varchar(45)
      	) 
      	select distinct 
      		 COL
      	from 
      		(
      			select 
      				test.TestFunction() as COL
      			from
      				tmpValues i
      		) t;
      END$$
       
      DELIMITER ;

      USE `test`;
      DROP function IF EXISTS `TestFunction`;
       
      DELIMITER $$
      USE `test`$$
      CREATE DEFINER=`root`@`localhost` FUNCTION `TestFunction`(
      ) RETURNS text CHARSET latin1
      BEGIN
       
      		return 'blah';
          
      END$$
       
      DELIMITER ;

      #Executes successfully
      call test.testMariaBug();
       
      #fails with Error Code: 1146. Table 'test.tmpValues' doesn't exist
      call test.testMariaBug();

      ---------------------

      Work arounds exist as follows:

      • make the nested select table non temporary
      • place another table in the table list of the parent query, in front of the select table (t)
      • remove the function reference and replacing it with a constant or value from the temporary table

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              MarkP Mark Punak
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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