Skip navigation links

MySQL Forums :: Stored Procedures :: Can't reopen temporary table in successive SELECTs in a FUNCTION


Advanced Search

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

Hi,

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


Subject Views Written By Posted
Can't reopen temporary table in successive SELECTs in a FUNCTION 13376 Ondra Zizka 05/10/2008 06:49PM
Re: Can't reopen temporary table in successive SELECTs in a FUNCTION 5348 Peter Brawley 05/10/2008 10:34PM
Re: Can't reopen temporary table in successive SELECTs in a FUNCTION 5303 Ondra Zizka 05/10/2008 11:11PM
Re: Can't reopen temporary table in successive SELECTs in a FUNCTION 4788 Peter Brawley 05/11/2008 10:53AM
Re: Can't reopen temporary table in successive SELECTs in a FUNCTION 3793 Ondra Zizka 05/11/2008 07:42PM
Re: Can't reopen temporary table in successive SELECTs in a FUNCTION 3394 Miljan Radovic 12/02/2008 08:03AM


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.