MySQL Bugs: #36653: Temp table limitation in stored routines

Bug #36653	Temp table limitation in stored routines
Submitted:	11 May 2008 16:57	Modified:	21 May 2008 20:02
Reporter:	Peter Brawley (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.x, 6.0	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
Selecting a temp table twice under different aliases in a stored function trips over the "can't reference temp table twice" limitation. Evidently it's related to http://dev.mysql.com/doc/refman/5.1/en/temporary-table-problems.html. The problem should be documented. Better still, fix it!

How to repeat:
[code]
drop table if exists a;
create table a(i int);
insert into a values(1),(2);
create temporary table atemp select * from a;
[code]
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 ;
[/code]
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".

Thank you for a problem report. This is likely just a documentation request. See my comments at Bug #36657 (which is a duplicate).

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added a note about this problem at:

http://dev.mysql.com/doc/refman/5.1/en/temporary-table-problems.html
and
http://dev.mysql.com/doc/refman/5.1/en/routine-restrictions.html