Description:
Until recently, it was possible to create a table and manipulate
it within a stored procedure. Now, it is no longer possible to
do this -- although one can still create a table within a stored
procedure, one can no longer do anything with that table.
Although the CREATE PROCEDURE statements returns OK,a
CALL of the resulting procedure returns an error saying the
table does not exist. This is not true however -- accessing
the table shows that it really was created, but further work
on that table from within the stored procedure is not possible.
The MySQL Reference Manual says we do this; the SQL
Standard says we should do this; other DBMSs do this.
NOTE: This is probably related to Bug#8765.
How to repeat:
mysql> delimiter //
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t1//
Query OK, 0 rows affected (0.00 sec)
mysql> create procedure sp1() begin create table t1 (col1 int); insert into t1 values
(10); end//
Query OK, 0 rows affected (0.00 sec)
mysql> call sp1()//
ERROR 1146 (42S02): Table 'tp.t1' doesn't exist
-- This is a misleading error message; the table has been created.
mysql> insert into t1 values(10)//
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1//
Empty set (0.00 sec)
-- This shows that the table really was created, but that the
INSERT statement from the stored procedure failed.
