MySQL Bugs: #12257: SELECT * inside PROCEDURE gives "Unknown column" on second loop if tbl changed

Bug #12257	SELECT * inside PROCEDURE gives "Unknown column" on second loop if tbl changed
Submitted:	29 Jul 2005 4:53	Modified:	8 Jan 2008 17:50
Reporter:	Sergey Petrunya
Status:	Verified
Category:	Server: SP	Severity:	S3 (Non-critical)
Version:	5.0, 5.0.14-rc	OS:	Linux (Linux)
Assigned to:	Konstantin Osipov	Target Version:
Triage:	Triaged: D2 (Serious) / R4 (High) / E4 (High)

Description:
If a statement with wildcards inside a stored procedure is executed several times 
(e.g. it is inside a loop), and the accessed table is dropped/recreated inbetween,
the statement fails with "Unknown column" error.

How to repeat:
Run this:

delimiter //

create procedure p8()
begin
  declare i int default 0;
  create temporary table tv as select 'tv1' tv_1_col;
  select 'tv is a base table' A;
  l:loop
    set i=i+1;
    if (i=3) then 
       leave l;
    end if;
    
    select * from tv;
    
    if (i=1) then
      drop table tv; 
      create temporary table tv as select 'tv2' tv_2_col;
      select 'modified table tv' A;
    end if; 
    
  end loop;
end //

call p8()//

And get this:
mysql> call p8() //
+--------------------+
| A                  |
+--------------------+
| tv is a base table |
+--------------------+
1 row in set (0.01 sec)

+----------+
| tv_1_col |
+----------+
| tv1      |
+----------+
1 row in set (0.01 sec)

+-------------------+
| A                 |
+-------------------+
| modified table tv |
+-------------------+
1 row in set (0.01 sec)

ERROR 1054 (42S22): Unknown column 'junk8.tv.tv_1_col' in 'field list'

The same effect can be observed with prepared statements: 

create table t1 as select 1 A;
prepare s1 from 'select * from t1'; 
execute s1;
drop table t1;
create table t1 as select 2 B, 3 C;
select * from t1; 
execute s1;

ERROR 1054 (42S22): Unknown column 'junk8.t1.A' in 'field list'

<cut>
mysql> select * from t1; 
+---+---+
| B | C |
+---+---+
| 2 | 3 |
+---+---+
1 row in set (0.00 sec)

mysql> execute s1;
ERROR 1054 (42S22): Unknown column 'test.t1.A' in 'field list'
mysql>

The same behaviour got on today's 5.0.14-rc build:

mysql> select version()//
+-----------+
| version() |
+-----------+
| 5.0.14-rc |
+-----------+
1 row in set (0.00 sec)

mysql> create procedure p8()
    -> bbegin
    ->   declare i int default 0;
    ->   create temporary table tv as select 'tv1' tv_1_col;
    ->   select 'tv is a base table' A;
    ->   l:loop
    ->      set i=i+1;
    ->     if (i=3) then
    ->        leave l;
    ->     end if;
    ->
    ->      select * from tv;
    ->
    ->     if (i=1) then
    ->       drop table tv;
    ->        create temporary table tv as select 'tv2' tv_2_col;
    ->       select 'modified table tv' A;
    ->     end if;
    ->
    ->    end loop;
    -> end //

cQuery OK, 0 rows affected (0.00 sec)

mysql>
mysql> call p8()//
+--------------------+
| A                  |
+--------------------+
| tv is a base table |
+--------------------+
1 row in set (0.01 sec)

+----------+
| tv_1_col |
+----------+
| tv1      |
+----------+
1 row in set (0.02 sec)

+-------------------+
| A                 |
+-------------------+
| modified table tv |
+-------------------+
1 row in set (0.02 sec)

ERROR 1054 (42S22): Unknown column 'test.tv.tv_1_col' in 'field list'

This is applicable to 4.1 as well and can be repeated using prepared statements:
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from "select * from t1";
Query OK, 0 rows affected (0.03 sec)
Statement prepared

mysql> drop table t1; create table t1 (b int);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> execute stmt;
ERROR 1054 (42S22): Unknown column 'test.t1.a' in 'field list'

MySQL expands '*' at prepare (in case of stored procedures - at first execution) and
replaces it with the actual column names. The fix for this bug is to invalidate the
prepared statement/stored procedure statement when metadata changes, and re-prepare it
automatically, but this is not implemented and is not planned until the new data
dictionary is in place.
Alerting the docs team about this issue.

Bug#15766 was marked as a duplicate of this bug.

Hi All,

I've found what seems to be a similar bug - where you get unknown column with SELECT *
but dropping a table is not involved or required - it works the first time but not the
2nd on.

Can anyone confirm if this is similar or needs to be a new bug?

Thanks,
Trent

Test Case:
DROP DATABASE IF EXISTS test_f507e4;
CREATE DATABASE test_f507e4;
USE test_f507e4;

CREATE TABLE t1 (id INT PRIMARY KEY auto_increment);
CREATE TABLE t2 (id INT PRIMARY KEY auto_increment, t1_id INT);
DELIMITER $$

DROP PROCEDURE IF EXISTS `test_sp`$$

CREATE PROCEDURE `test_sp`()

BEGIN
        SELECT * FROM t1 RIGHT JOIN t2 ON t1.id=t2.t1_id;
END$$

DELIMITER ;

CALL test_sp();
CALL test_sp();

Trent,
do not comment on a closed bug, please open a new bug report and try to get it verified.

Answering your question, your test case seems to be unrelated.

I found a workaround for this bug, which occurs when the SELECT field list changes from
one stored program call to another.

If you want to do something like this:

DECLARE cur CURSOR FOR SELECT * FROM tmp_x;
...
CREATE TEMPORARY TABLE tmp_x AS SELECT ...

You can instead do something like this:

DECLARE cur CURSOR FOR SELECT field1, field2, field3 FROM tmp_x;
...
CREATE TEMPORARY TABLE tmp_x AS SELECT something1 as field1, something2 as field2, ...

(In my case the CREATE TEMPORARY line was produced by dynamic SQL, which I have excluded
above for readability.)

The trick is to use fixed field name aliases, so even if the underlying field names have
changed, the DECLARE CURSOR line will always use the aliases.