MySQL Bugs: #33843: procedure, ERROR 1054 (42S22): Unknown column though col exists

Bug #33843	procedure, ERROR 1054 (42S22): Unknown column though col exists
Submitted:	13 Jan 2008 6:54	Modified:	20 Jun 2012 16:23
Reporter:	Joe Knall	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.54, 5.0.51, 5.1.51	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	execute, prepare, stored procedure

Description:
stored procedure creates different tables according to parameter and fills it with data;
select * gives correct result (creates table a);
called again with different param (creates table b) gives ERROR 1054 (42S22): Unknown column 'test.test.a' in 'field list';
created table and data is correct anyways;

How to repeat:
drop procedure if exists `test`.`p_test`;
delimiter $$
create procedure `test`.`p_test` (in _param int)
language sql
modifies sql data
sql security definer
begin
    drop table if exists `test`.`test`;

    if _param = 1 then
        set @sql = '
            create table `test`.`test` (
                `a` char(1) not null
            ) type = myisam
        ';
        prepare query from @sql;
        execute query;

        insert into `test`.`test` (`a`) values ('a'),('b');
    else
        set @sql = '
            create table `test`.`test` (
                `b` char(1) not null
            ) type = myisam
        ';
        prepare query from @sql;
        execute query;

        insert into `test`.`test` (`b`) values ('c'),('d');
    end if;

    select * from `test`.`test`;
end;
$$
delimiter ;

call `test`.`p_test`(1);
call `test`.`p_test`(2);

Suggested fix:
instead of

select * from `test`.`test`;

this works:

set @sql = 'select * from `test`.`test`';
prepare query from @sql;
execute query;

testcase

Attachment: procedure-bug.sql (application/octet-stream, text), 1.03 KiB.

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

Looks like "SELECT * FROM test" is replaced by "SELECT a FROM test" if table `test` with `a` as the only column exists at the moment of call (as it happens with second call).

a workaround is to flush the SP cache between calls.  the easiest way to flush SP cache is to run this:

create or replace view tmpview as select now();

Noted in 5.6.6 changelog.

Changing the set of columns in a table between executions of a stored
program that used the table could cause "Unknown column" errors.