MySQL Bugs: #27566: INSERT ... SELECT: Can't insert in the table in FROM in stored procedure

Bug #27566	INSERT ... SELECT: Can't insert in the table in FROM in stored procedure
Submitted:	31 Mar 2007 19:37	Modified:	3 Aug 2007 17:32
Reporter:	Ondra Zizka	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.37	OS:	Windows (Win32)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
An INSERT ... SELECT query gives an error:
   "Can't reopen table ..."
although, the manual states:
   "The target table of the INSERT statement may appear in the FROM clause
of the SELECT part of the query. (This was not possible in some older versions of MySQL.)
In this case, MySQL creates a temporary table to hold the rows from the SELECT
and then inserts those rows into the target table."

The statement is in the stored procedure.

    INSERT INTO tree_subnodes
    SELECT t.id, iCurLevel+1
      FROM tree_subnodes AS ts
        INNER JOIN fstree AS t ON t.pod = ts.id
      WHERE ts.level = iCurLevel ;

When I replace the statement above with a code using temporary table, it work's fine:

    INSERT INTO tree_subnodes_temp
    SELECT t.id, iCurLevel+1
      FROM tree_subnodes AS ts
        INNER JOIN fstree AS t ON t.pod = ts.id
      WHERE ts.level = iCurLevel ;

    INSERT INTO tree_subnodes SELECT * FROM tree_subnodes_temp;
    TRUNCATE tree_subnodes_temp;

According to that paragraph in the manual, the first way should work too (at least I understand it so).

How to repeat:
Table fstree:

DROP TABLE IF EXISTS `test`.`fstree`;
CREATE TABLE  `test`.`fstree` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `pod` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` enum('dir','file') NOT NULL,
  `size` bigint(20) unsigned default NULL,
  `level` smallint(5) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `level` (`level`)
) ENGINE=MyISAM AUTO_INCREMENT=557793 DEFAULT CHARSET=cp1250;

Procedure code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`GetSubNodes` $$
CREATE PROCEDURE `GetSubNodes`( iNodeId INT UNSIGNED )
COMMENT 'Returns all subnodes of a node with given ID.'
BEGIN

  DECLARE iCurLevel INT DEFAULT NULL;

  DROP TEMPORARY TABLE IF EXISTS tree_subnodes;
  CREATE TEMPORARY TABLE tree_subnodes (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    level SMALLINT UNSIGNED
  );

  DROP TEMPORARY TABLE IF EXISTS tree_subnodes_temp;
  CREATE TEMPORARY TABLE tree_subnodes_temp LIKE tree_subnodes;

  SELECT level INTO iCurLevel FROM fstree WHERE id = iNodeId;
  -- CALL Logg( CONCAT('FOUND_ROWS(): ', FOUND_ROWS()) ); ### Kontrola
  INSERT INTO tree_subnodes SET id = iNodeId, level = iCurLevel; ## Predany uzel

  ## == Cycle BEGIN ===
  loop1: LOOP

    #CALL Logg( CONCAT( '1 Level: ', iCurLevel, ';  ROW_COUNT(): ', ROW_COUNT(), ';  FOUND_ROWS(): ', FOUND_ROWS() ) );

/*    INSERT INTO tree_subnodes_temp
    SELECT t.id, iCurLevel+1
      FROM tree_subnodes AS ts
        INNER JOIN fstree AS t ON t.pod = ts.id
      WHERE ts.level = iCurLevel   -- filtrace aktualni vrstvy
        AND t.level = iCurLevel+1;

    INSERT INTO tree_subnodes SELECT * FROM tree_subnodes_temp;
    TRUNCATE tree_subnodes_temp;
*/
    INSERT INTO tree_subnodes
    SELECT t.id, iCurLevel+1
      FROM tree_subnodes AS ts
        INNER JOIN fstree AS t ON t.pod = ts.id
      WHERE ts.level = iCurLevel   -- filtrace aktualni vrstvy
        AND t.level = iCurLevel+1;
    ## Can't be done in single step - MySQL can't open a single table twice.
    ## ??? >>
    ## "The target table of the INSERT statement may appear in the FROM clause
    ##  of the SELECT part of the query. (This was not possible in some older versions of MySQL.)
    ##  In this case, MySQL creates a temporary table to hold the rows from the SELECT
    ##  and then inserts those rows into the target table.

    #CALL Logg( CONCAT('4 Level: ', iCurLevel, ';  ROW_COUNT(): ', ROW_COUNT(), ';  FOUND_ROWS(): ', FOUND_ROWS() ) );
    ## Cycle termination.
    IF (FOUND_ROWS() <= 0) OR (iCurLevel > 25) OR ( iCurLevel IS NULL ) THEN LEAVE loop1; END IF;

    -- INSERT INTO tree_subnodes SELECT id FROM tree_subnodes_temp;
    -- TRUNCATE tree_subnodes_temp;
    SET iCurLevel = iCurLevel + 1;

  END LOOP loop1;
  ## == Cycle END ===

  SELECT * FROM tree_subnodes;
  -- DROP TEMPORARY TABLE tree_subnodes;
  -- DROP TEMPORARY TABLE tree_subnodes_temp;

END $$

DELIMITER ;

Suggested fix:
...

Thank you for a bug report. Verified just as described.

This is not a bug.

MySQL manual says:
http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html:
#

You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:

mysql> SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'

When manual talks about being able to INSERT ... SELECT into the same table,
it describes base, not temporary tables. The limitation of not being able
to re-open a temporary table still applies.

Note, that the problematic code produces the same error outside a stored procedure:
mysql>   CREATE TEMPORARY TABLE tree_subnodes (
    ->     id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->     level SMALLINT UNSIGNED
    ->   );
Query OK, 0 rows affected (0.00 sec)

mysql>     INSERT INTO tree_subnodes
    ->     SELECT t.id, iCurLevel+1
    ->       FROM tree_subnodes AS ts
    ->         INNER JOIN fstree AS t ON t.pod = ts.id
    ->       WHERE ts.level = iCurLevel   -- filtrace aktualni vrstvy
    ->         AND t.level = iCurLevel+1;
ERROR 1137 (HY000): Can't reopen table: 'tree_subnodes'

Suggestion for documentation: please clarify that INSERT ... SELECT still
cannot INSERT ... SELECT into the same *temporary* table.

Thank you.

I have updated the INSERT ... SELECT section to point out that
TEMPORARY tables cannot be used:

The target table of the INSERT statement may appear in the FROM 
clause of the SELECT part of the query. (This was not possible in 
some older versions of MySQL.) In this case, MySQL creates a
temporary table to hold the rows from the SELECT and then inserts
those rows into the target table. However, it remains true that you
cannot use INSERT INTO t ... SELECT ... FROM t when t is a TEMPORARY
table, because TEMPORARY tables cannot be referred to twice in the
same statement.