Re: Can't reopen temporary table in successive SELECTs in a FUNCTION

[Peter Brawley]

I was looking for a testcase free of the particulars of your tables. Like this:

drop table if exists a;
create table a(i int);
insert into a values(1),(2);
create temporary table atemp select * from a;
drop function if exists fa;
delimiter |
create function fa() returns int
begin
  declare x, y int default 0;
  select i into x from atemp AS aliasA where i=1;
  select i into y from atemp AS aliasB where i=1;
  return x;
end;
|
delimiter ;

If you comment out both aliases, or if you use the same or no alias in each SELECT, no runtime error occurs. If you use different aliases, you get "Can't open temp table twice".

Evidently related to http://dev.mysql.com/doc/refman/5.1/en/temporary-table-problems.html. It should be documented. Better still, it should be fixed.

PB
http://www.artfulsoftware.com



Edited 2 time(s). Last edit at 05/12/2008 01:14AM by Peter Brawley.