Bug #12257 SELECT * inside PROCEDURE gives "Unknown column" on second loop if tbl changed
Submitted: 29 Jul 2005 2:53 Modified: 20 Jun 2012 16:53
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0, 5.0.14-rc OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R4 (High) / E4 (High)

[29 Jul 2005 2:53] Sergey Petrunya
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'
[29 Jul 2005 3:01] Sergey Petrunya
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'
[29 Jul 2005 3:11] Godofredo Miguel Solorzano
<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>
[16 Sep 2005 16:41] Valeriy Kravchuk
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'
[18 Jan 2006 21:20] Konstantin Osipov
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.
[25 Jan 2006 14:08] Konstantin Osipov
Bug#15766 was marked as a duplicate of this bug.
[31 Dec 2007 5:45] Trent Lloyd
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();
[8 Jan 2008 16:49] Konstantin Osipov
Trent,
do not comment on a closed bug, please open a new bug report and try to get it verified.
[8 Jan 2008 16:51] Konstantin Osipov
Answering your question, your test case seems to be unrelated.
[3 Nov 2008 16:07] Harry Levinson
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.
[2 Dec 2009 10:55] Sveta Smirnova
Bug #49333 was marked as duplicate of this one.
[6 Oct 2010 12:09] Shane Bester
5.1.51 looks like it works:

mysql> create table t1 as select 1 A;
Query OK, 1 row affected (0.28 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> prepare s1 from 'select * from t1';
Query OK, 0 rows affected (0.06 sec)
Statement prepared

mysql> execute s1;
+---+
| A |
+---+
| 1 |
+---+
1 row in set (0.05 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t1 as select 2 B, 3 C;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

mysql> execute s1;
+---+---+
| B | C |
+---+---+
| 2 | 3 |
+---+---+
1 row in set (0.02 sec)

mysql> select version();
+--------------------------------+
| version()                      |
+--------------------------------+
| 5.1.51-enterprise-gpl-advanced |
+--------------------------------+
1 row in set (0.00 sec)

mysql> DROP DATABASE IF EXISTS test_f507e4;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE test_f507e4;
Query OK, 1 row affected (0.03 sec)

mysql> USE test_f507e4;
Database changed
mysql>
mysql> CREATE TABLE t1 (id INT PRIMARY KEY auto_increment);
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE t2 (id INT PRIMARY KEY auto_increment, t1_id INT);
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `test_sp`$$
Query OK, 0 rows affected, 4 warnings (0.25 sec)

mysql>
mysql> CREATE PROCEDURE `test_sp`()
    ->
    -> BEGIN
    ->         SELECT * FROM t1 RIGHT JOIN t2 ON t1.id=t2.t1_id;
    -> END$$
Query OK, 0 rows affected (0.16 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> CALL test_sp();
Empty set (0.13 sec)

Query OK, 0 rows affected (0.13 sec)

mysql> CALL test_sp();
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select version();
+--------------------------------+
| version()                      |
+--------------------------------+
| 5.1.51-enterprise-gpl-advanced |
+--------------------------------+
1 row in set (0.00 sec)

mysql>
[4 Nov 2010 13:05] Alex Green
Does not seem to be resolved in 5.1.52.

The query:

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();

still returns

Unknown column 'tv.tv_1_col' in 'field list'
[11 Sep 2011 6:11] Shane Bester
bug #62406 is a duplicate
[20 Jun 2012 16:53] Paul Dubois
Noted in 5.6.6 changelog.

"Unknown column" errors or bad data could result from changing the
set of columns in a table used within a stored program between 
executions of the program or while the table was used within a
program loop.
[15 Jul 2014 19:38] Sveta Smirnova
Bug #72838 was marked as duplicate of this one.