Bug #15766 select * from table inside stored procedure uses old field names
Submitted: 15 Dec 2005 0:17 Modified: 25 Jan 2006 14:07
Reporter: Timothy Smith Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.18 OS:Any (any)
Assigned to: CPU Architecture:Any

[15 Dec 2005 0:17] Timothy Smith
A stored procedure creates a (temporary, or not) table with fields which depend on the procedure's inputs.  It then calls select * from this table.

The first time it's run in a session, it works fine.  If called with the same arguments, it continues to work fine.  If called with different arguments, the select * gets an error:

ERROR 1054 (42S22): Unknown column 'test.buggy.foo' in 'field list'

The query cache is NOT on (query_cache_size == 0).

Quitting the session, and then re-connecting to the server, clears out whatever is getting stuck.  The procedure can be called with a new parameter.

How to repeat:
I tested this with a recent 5.0.17-BK build.  I started the server as:

./bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/data
--tmpdir=$PWD/tmp --log-error=$PWD/data/log.err --socket=mysql.sock --port=33500 --server-id=33500 --log-bin=binlog  &

delimiter //
drop procedure if exists buggy//
create procedure buggy (fname varchar(100))
drop table if exists buggy;
set @query = concat("create table buggy select 1 as ", fname, " from dual");
select @query;
prepare stmt from @query;
execute stmt;
deallocate prepare stmt;

select * from buggy;

drop table buggy;

end //

-- This works
call buggy('foo')//

-- This fails with "Unknown column 'test.buggy.foo' in 'field list'"
call buggy('bar')//

-- This still works...
call buggy('foo')//

Suggested fix:
[25 Jan 2006 14:07] Konstantin Osipov
This is a duplicate of Bug#12257 "SELECT * inside PROCEDURE gives "Unknown column" on second loop if tbl changed"