MySQL Bugs: #18696: Why not to refer more than once to temporary table?

Bug #18696	Why not to refer more than once to temporary table?
Submitted:	31 Mar 2006 17:14	Modified:	4 Oct 2008 19:48
Reporter:	Grzegorz Laszczak	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	4.1.16	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
It's very unconveniet for me not to refer more than once to temporary table.
I want to do something like this:

create temporary table temp1 as
     select name, amount from MyTable
;
select name, amount from temp1
union select 'AVG', avg(amount) from temp1
union select 'SUM', sum(amount) from temp1
union select 'MIN', min(amount) from temp1
union select 'MAX', max(amount) from temp1
;

This example produce an error! 
Using table without temporary -  all is OK but... you must remember to drop table at the end!

G.

How to repeat:
CREATE TABLE `MyTable` (
  `name` varchar(100) NOT NULL default '',
  `amount` int(5) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
;
insert into MyTable (name, amount)
values ('john',7),('mark',20),('gregory',43)

This is NOT working:
create temporary table temp1 as
     select name, amount from MyTable
;
select name, amount from temp1
union select 'AVG', avg(amount) from temp1
union select 'SUM', sum(amount) from temp1
union select 'MIN', min(amount) from temp1
union select 'MAX', max(amount) from temp1

But this is working:
drop table if exists temp1;
create table temp1 as
     select name, amount from MyTable
;
select name, amount from temp1
union select 'AVG', avg(amount) from temp1
union select 'SUM', sum(amount) from temp1
union select 'MIN', min(amount) from temp1
union select 'MAX', max(amount) from temp1

Suggested fix:
Why not to refer more than once to temporary table?

Thank you for a reasonable feature request. Current limitation is documeted in http://dev.mysql.com/doc/refman/4.1/en/temporary-table-problems.html.

It's more than a "reasonable" feature request! Prohibition of multiple references to temp tables in queries is a serious drawback to recommending MySQL for serious projects requiring session-specific tables.

A duplicate of Bug #10327 Can't reopen temporary table - should be allowed