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 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

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.

Bug #49333 was marked as duplicate of this one.

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>

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'

bug #62406 is a duplicate

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.

Bug #72838 was marked as duplicate of this one.