Bug #47086 FETCH problem if the INTO-valiables have the same name as the result-set columns

• Submitted: 3 Sep 2009 8:16
• Modified: 17 Sep 2009 9:55
• Reporter: Assen Todorov
• Status: Duplicate
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.37-log, 5.0, 5.1, next bzr
• OS: Any
• CPU Architecture: Any
• Tags: Fetch statement problem

[3 Sep 2009 8:16] Assen Todorov

Description:
FETCH statement does not fetch rows from a cursor if the INTO-variables and the result-set columns have the same names.

How to repeat:
// Tables

CREATE TABLE AD_DEBUG 
(
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  VAL VARCHAR(250) NOT NULL,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8;

CREATE TABLE AD_DEBUG_COPY
(
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  VAL_STR VARCHAR(250) NOT NULL,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8;

// Stored procedure

drop procedure if exists debug_copy@

CREATE PROCEDURE debug_copy()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE val VARCHAR(250);
  DECLARE ad_cursor CURSOR FOR SELECT val from ad_debug;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET done = 1;

  OPEN ad_cursor;

  REPEAT
    FETCH ad_cursor INTO val;

      IF NOT done THEN

            INSERT INTO ad_debug_copy
                        (
                          val_copy
                        )
                        VALUES
                        (
                          val
                        );

    END IF;
  UNTIL done END REPEAT;

  CLOSE ad_cursor;
END
@

After execution the table ad_debug_copy is empty.
If I change the name of the variable "val" to something else the stored procedure works as expected.

[3 Sep 2009 8:19] Assen Todorov

Sorry, I make mistake in "How to repeat".

The table ad_debug_copy looks like this:

CREATE TABLE AD_DEBUG_COPY
(
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  VAL_COPY VARCHAR(250) NOT NULL,
  PRIMARY KEY(ID)
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8;

[3 Sep 2009 9:02] Sveta Smirnova

Thank you for the report.

Verified as described. This is feature request though.

[17 Sep 2009 9:55] Konstantin Osipov

Thank you for reporting this bug. This is a duplicate of Bug#5967.