MySQL Bugs: #12198: Temporary Table Aliasing does not work inside stored procedure

Bug #12198	Temporary Table Aliasing does not work inside stored procedure
Submitted:	27 Jul 2005 10:03	Modified:	16 Jan 2006 23:02
Reporter:	conor meegan
Status:	Closed
Category:	Server: SP	Severity:	S3 (Non-critical)
Version:	5.0.9, 5.0.12, 5.0.14-rc	OS:	Microsoft Windows (Windows XP Sp2, Linux)
Assigned to:	Dmitri Lenev	Target Version:

Description:
I wrote a stored procedure which involves the creation of a temporary table.  I then ran
a query which used the temporary table, however, importantly I aliased the temporary
table within the query.  I got an error message to tell me the temporary table did not
exist.

When I removed the aliasing from the query, the procedure works fine.

How to repeat:
Create procedure.  In procedure create temporary table.  Create query which aliases
temporary table.

Thank you for the bug report. I was able to reproduce this bug with the following simple
test case:

delimiter //
mysql> create procedure p1 ()
    -> begin
    ->   create temporary table tt1 (c1 integer);
    ->   select * from tt1;
    -> end
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> create procedure p2 ()
    -> begin
    ->   create temporary table tt2 (c1 integer);
    ->   select t.* from tt2 t;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call p1()//
Empty set (0.05 sec)

Query OK, 0 rows affected (0.06 sec)

mysql> call p2()//
ERROR 1146 (42S02): Table 'test.tt2' doesn't exist
mysql> desc tt1//
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> desc tt2//
ERROR 1146 (42S02): Table 'test.tt2' doesn't exist
mysql> select version();
    -> //
+---------------+
| version()     |
+---------------+
| 5.0.9-beta-nt |
+---------------+
1 row in set (0.01 sec)

Reverified on 5.0.12-beta-nt (with the same procedures):

mysql> call p1()//
Empty set (0.10 sec)

Query OK, 0 rows affected (0.10 sec)

mysql> call p2()//
Empty set (0.05 sec)

Query OK, 0 rows affected (0.05 sec)

So, looks like the problem solved, but:

mysql> desc tt1//
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| c1    | decimal(64,2) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.01 sec)

Note the difference in the data type!

mysql> desc tt2//
ERROR 1146 (42S02): Table 'test.tt2' doesn't exist
mysql> select version()//
+----------------+
| version()      |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.00 sec)

mysql> show create table tt1//
+-------+-----------------------------------------------------------------------
----------------------------+
| Table | Create Table
                            |
+-------+-----------------------------------------------------------------------
----------------------------+
| tt1   | CREATE TEMPORARY TABLE `tt1` (
  `c1` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 |
+-------+-----------------------------------------------------------------------
----------------------------+
1 row in set (0.00 sec)

mysql> show create table tt2//
+-------+-----------------------------------------------------------------------
----------------------------+
| Table | Create Table
                            |
+-------+-----------------------------------------------------------------------
----------------------------+
| tt2   | CREATE TEMPORARY TABLE `tt2` (
  `c1` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 |
+-------+-----------------------------------------------------------------------
----------------------------+
1 row in set (0.00 sec)

mysql> desc tt2//
ERROR 1146 (42S02): Table 'test.tt2' doesn't exist

The same on today's 5.0.14-rc on Linux:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.14-rc |
+-----------+
1 row in set (0.00 sec)

mysql> delimiter //
mysql> ccreate procedure p1 ()
    -> bbegin
    ->   create temporary table tt1 (c1 integer);
    ->   select * from tt1;
    -> end
    -> ///
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p2 ()
    -> begin
    ->    create temporary table tt2 (c1 integer);
    ->   select t.* from tt2 t;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call p1()//
Empty set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> call p2()//
Empty set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> desc tt1//
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| c1    | decimal(64,2) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc tt2//
ERROR 1146 (42S02): Table 'test.tt2' doesn't exist
mysql> show create table tt1//
+-------+-----------------------------------------------------------------------
--------------------------+
| Table | Create Table
                          |
+-------+-----------------------------------------------------------------------
--------------------------+
| tt1   | CREATE TEMPORARY TABLE `tt1` (
  `c1` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------+
1 row in set (0.01 sec)

mysql> show create table tt2//
+-------+-----------------------------------------------------------------------
--------------------------+
| Table | Create Table
                          |
+-------+-----------------------------------------------------------------------
--------------------------+
| tt2   | CREATE TEMPORARY TABLE `tt2` (
  `c1` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------+
1 row in set (0.00 sec)

Can't check the last comment from valeriy, as describe doesn't seem to work with temp.
tables at all now:
mysql> create temporary table tt3 (c1 integer)//
Query OK, 0 rows affected (0.01 sec)
mysql> describe tt3//
ERROR 1146 (42S02): Table 'mytest.tt3' doesn't exist

Otherwise everithing looks fine. Waiting for gluh to help with  DESCRIBE.

This bug is repeatable only for function or triggers now (or stored procedures called for
them), store procedures are no longer affected by it. Test case for function looks like:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.17-debug |
+--------------+
1 row in set (0.00 sec)
mysql> delimiter | ;
mysql> drop table if exists t1|
Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql> drop function if exists f1|
Query OK, 0 rows affected (0.01 sec)

mysql> create function f1 () returns int
    -> begin
    ->   create temporary table t1 (i int);
    ->   return (select count(*) from t1 t1_alias);
    -> end|
Query OK, 0 rows affected (0.01 sec)

mysql> select f1()|
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> delimiter ; |

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/33278

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/33344

Fixed in 5.0.19 and 5.1.6

Noted in 5.1.6 and 5.0.19 changelogs:

      <listitem><para>Temporary table aliasing did not work inside stored
        functions. (Bug #12198)</para></listitem>