MySQL Bugs: #11960: Cannot include a temp table in a cursor declaration

Bug #11960	Cannot include a temp table in a cursor declaration
Submitted:	15 Jul 2005 8:54	Modified:	19 Aug 2005 16:17
Reporter:	David Lloyd	Email Updates:
Status:	No Feedback
Category:	Server	Severity:	S3 (Non-critical)
Version:	5	OS:	Microsoft Windows (Windows)
Assigned to:		Target Version:

Description:
If I define a temp table in function1 and subsequently call function2 from function1 then
I cannot declare a cursor in function2 which makes use of the temp table which already
exists. In other words, temp table are just not accessible from cursors. I cannot imagine
why this should be.

Since the procedural language is so feature poor (please implement PHP procedures asap!)
the use of cursors and temp tables is the only way to pass large amounts of data around
between functions.

How to repeat:
In function1 setup a temptable thus:

CREATE TEMPORARY TABLE IF NOT EXISTS list (id int, checked int) ENGINE=MEMORY;

Then in function1 call function2

CALL function2();

In function2 declate a cursor using the temp table:

DECLARE cur CURSOR FOR SELECT id FROM list WHERE list.checked = 0;

Calling function1 will cause Mysql to complain that "Table 'testdb.list'  doesn't exist"

Suggested fix:
Either eliminate the need to declare the temp table prior to the cursor declaration or
else make it possible to do so. Alternatively implement the cursors so that the checking
of table existence only occurs at the open cursor statement, not at declaration.

Finally I think that implementing PHP or some language which can handle arrays would
eliminate much of the need to resort to temp tables.

Could you please write the complete test case and mentioning which
version of 5.0.XX are you using.

Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".