MySQL Forums
Forum List  »  Stored Procedures

Can't reopen temporary table in successive SELECTs in a FUNCTION
Posted by: Ondra Zizka
Date: May 10, 2008 06:49PM


I've found strange behavior of temporary table in a FUNCTION and I suspect it being a bug.

I have a temporary table, created outside the function.
Inside the function, I do a SELECT without alias.
Then I do a second SELECT on the same table, but with alias.
MySQL raises an error: Can't reopen table.

SELECT MAX(iSelectedOnwardStopOffset) INTO @iMax FROM mhd_nn_FormatDesiredOutput_traces;

SELECT GROUP_CONCAT( IF(traces.id_trace = iSelectedTrace, 1.0, 0.0) ORDER BY traces.pos SEPARATOR ',' ) INTO @saValTraces
FROM mhd_nn_FormatDesiredOutput_traces AS traces

If I add " AS traces" alias into the first SELECT, it works.

Is this behavior documented somewhere?
The only thing this resembles is the need to LOCK tables for all aliases of a table when SELECTing from LOCKed aliased tables.

Ondra Zizka

Edited 1 time(s). Last edit at 05/10/2008 06:50PM by Ondra Zizka.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.