Bug #62438 Temporary Table in Routine

Submitted: 15 Sep 2011 2:47 Modified: 15 Sep 2011 4:01
Reporter: Yady Indrawan Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DDL Severity:S1 (Critical)
Version:5.5.15 OS:Any
Assigned to: CPU Architecture:Any
Tags: Temporary Table Behaviour

[15 Sep 2011 2:47] Yady Indrawan
Description:
I create routine to display data where total field in the result table is not fixed, depend on period that will be processed.

I know that temporary table is still exist as long as user is still connect to the server. I try to execute the routine with vstartdate= '2011-01-01' and vendate='2011-03-01'. I execute using MySqlWorkBench version 5.2.34.

There is no problem when I execute the routine. The problem are when I execute the routine for the second and so on.

Result.
1. If I just run the application without modify the date parameters. It is OK.
2. If I run the application with modify vendDate paramter, greater than than March then, it still show 3 months columns. There aren't' column for month 4 and so on.
3. If I rub the application with modify vendDate parameter become '2011-02-01' then there is an error message.
Error Code: 1054. Unknown column 'dbname.vtblreportData.Mar_2011' in 'field list'.

How to repeat:

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetReportDataByGroupId`(
   vmsrGroupId     INT,
   vkpiList        VARCHAR(100),
   vreportType     VARCHAR(20),
   vaverageType    VARCHAR(20),
   vtagList        VARCHAR(100),
   vstartDate      DATETIME,
   vendDate        DATETIME,
   vvariance       VARCHAR(20))
BEGIN
 DROP TEMPORARY TABLE IF EXISTS vtblreportData;
 CREATE TEMPORARY TABLE IF NOT EXISTS vtblreportData
 (
  GroupingId   INT,
  KpiId        INT,
  KpiName      NVARCHAR(50),
  Margin       VARCHAR(10),
  DataType     INT,
  DataType_Tx  VARCHAR(50),
  KpiFormatId  INT,
  KpiDecimal   INT,
  Primary Key (GroupingId, KpiID, DataType)
 );

      SET vcursorDate = vstartDate;
      
      WHILE Period_Diff(Date_Format(vendDate, '%Y%m'),
                        Date_Format(vcursorDate, '%Y%m')) <> -1
      DO
         CASE 
             WHEN MONTH(vcursorDate) = 1 THEN set vmonthYearField = 'Jan';
             WHEN MONTH(vcursorDate) = 2 THEN set vmonthYearField ='Feb';
             WHEN MONTH(vcursorDate) = 3 THEN set vmonthYearField ='Mar';
             WHEN MONTH(vcursorDate) = 4 THEN set vmonthYearField ='Apr';
             WHEN MONTH(vcursorDate) = 5 THEN set vmonthYearField ='May';
             WHEN MONTH(vcursorDate) = 6 THEN set vmonthYearField ='Jun';
             WHEN MONTH(vcursorDate) = 7 THEN set vmonthYearField ='Jul';
             WHEN MONTH(vcursorDate) = 8 THEN set vmonthYearField ='Aug';
             WHEN MONTH(vcursorDate) = 9 THEN set vmonthYearField ='Sep';
             WHEN MONTH(vcursorDate) = 10 THEN set vmonthYearField = 'Oct';
             WHEN MONTH(vcursorDate) = 11 THEN set vmonthYearField = 'Nov';
             WHEN MONTH(vcursorDate) = 12 THEN set vmonthYearField = 'Dec';
             ELSE  set vmonthYearField ='';
         END Case;
         
         #Testing
         SET vmonthYearField = concat(vmonthYearField, '_', cast(YEAR(vcursorDate) as char(4)) );
         SET @sql = concat('ALTER TABLE vtblreportData ADD ', vmonthYearField, ' DECIMAL(18, 6)');
         
         SET vmonthYearFieldTx = concat(vmonthYearField, '_Tx');
         SET @sql = Concat(@sql, ', ADD ', vmonthYearFieldTx, ' VARCHAR(50)');

         
         PREPARE stmt1 FROM @sql;
         EXECUTE stmt1;
         DEALLOCATE PREPARE stmt1;

 SET vcursorDate = DATE_ADD(vcursorDate, INTERVAL 1 MONTH);
      END WHILE;
      
      #select 'After adding field';

      ALTER TABLE vtblreportData ADD Total DECIMAL(18, 6), ADD Total_Tx VARCHAR(100);  

     ....
       << Input Data to the Table >>
     ....

     select * from vtblreportData
END
[15 Sep 2011 4:01] Valeriy Kravchuk
This is a well-known problem. See bug #32868, for example. I think this is the same case, but with temporary tables.

We have a worklog to fix all similar problems with metadata changes, http://forge.mysql.com/worklog/task.php?id=4179.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.