MySQL Bugs: #61144: Cursor in SP once run traverses stale table columns for subsequent CALL of SP

Bug #61144	Cursor in SP once run traverses stale table columns for subsequent CALL of SP
Submitted:	12 May 2011 7:28	Modified:	6 Jul 2011 19:10
Reporter:	Jake Sully	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.5	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
A well-formed cursor in a stored procedure called once, will traverse the same table columns from that first call, even if those table columns have already been altered, i.e. removed or renamed. This occurs when (*) is used in the select statement for the cursor declaration:

DECLARE fubarCursor CURSOR FOR
SELECT * FROM `dbName`.`tableName`;

This gives the possibility of stale table info about the column names for subsequent calls of the stored procedure. This throws an error from the cursor like:

Description
Unknown column 'dbName.tableName.oldColumnName' in 'field list'

ErrorNo
1054

What is happening here is that the (*) is not being re-evaluated to the new column name when the table in question is altered in any way to have different columns after the first call of the stored procedure.

This affects temporary tables as well.

How to repeat:
1. To simplify bug replication steps create a table that has 3 columns:

CREATE DATABASE `dbName`;
CREATE TABLE  `dbName`.`tableName` (
  `oldColumnName` text,
  `column2` text,
  `column3` text
);

2. The fubarCursor stored procedure. Run this:

DELIMITER $$

DROP PROCEDURE IF EXISTS `foobarCursor` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `foobarCursor`()
BEGIN

  DECLARE cursorRead TEXT DEFAULT '';

  #Cursor-specific variables
  DECLARE colVal1, colVal2, colVal3 TEXT;
  DECLARE noMoreRows BOOLEAN DEFAULT FALSE;
  DECLARE foobarCursor CURSOR FOR
    SELECT *
    FROM `dbName`.`tableName`;
  DECLARE CONTINUE HANDLER
    FOR 1329
    SET noMoreRows = TRUE;

  OPEN foobarCursor;
  foobarCursorLoop: LOOP
  FETCH foobarCursor INTO colVal1, colVal2, colVal3;

    IF (noMoreRows)
    THEN
      CLOSE foobarCursor;
      LEAVE foobarCursorLoop;
    END IF;

    IF (cursorRead = '')
    THEN
      SET cursorRead = CONCAT_WS(', ', colVal1, colVal2, colVal3);

    ELSE
      SET cursorRead = CONCAT_WS(', ', cursorRead, colVal1, colVal2, colVal3);
    END IF;
  END LOOP foobarCursorLoop;

  SELECT cursorRead 'Values read by the cursor';

END $$

DELIMITER ;

3. Alter the table's column like:

ALTER TABLE `dbname`.`tablename` 
CHANGE COLUMN `oldColumnName` `foobarColumn` TEXT;

4. Call the foobarCursor:

CALL foobarCursor();

5. The bug is repeated:

Unknown column 'dbname.tableName.oldColumnName' in 'field list'

Suggested fix:
I suggest that when (*) is present in the SELECT statement of the cursor declaration, the behavior should be as expected without having the user change the (*) to specific columns. The cursor is not aware that the table has changed. I speculate that the cursor is traversing it's own copy of the old table for performance reasons.

I forgot to mention:

After step 3, where the SP is created, you need to call it:

CALL foobarCursor();

Then you alter the table (step 4).

EDIT (missing step):

I forgot to mention:

After step 2, where the SP is created, you need to call it:

CALL foobarCursor();

Then you alter the table (step 3).

I do not see anything in the manual about the behavior of cursors "by design", but probably the same logic as for views (see http://dev.mysql.com/doc/refman/5.5/en/create-view.html) is applied. Then this is not a bug...

Just for clarification, what is meant by "behavior" is about the SELECT statement:

 SELECT * FROM `dbName`.`tableName`

When this is run the expectation is all columns are selected. 

Hence, it was loosely termed "behavior should be as expected". When the asterisk (*) is used, all columns are expected by the average user not as per the manual. 

Just to add, hopefully this will be resolved when dynamic cursors are supported in a future version, hence it might be more of a feature request.

Thanks.

Assuming current behavior is intended, we have a documentation request here.

This is a duplicate of Bug#12257