Bug #12198 Temporary Table Aliasing does not work inside stored procedure
Submitted: 27 Jul 2005 8:03 Modified: 16 Jan 2006 22:02
Reporter: conor meegan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.9, 5.0.12, 5.0.14-rc OS:Microsoft Windows (Windows XP Sp2, Linux)
Assigned to: Dmitry Lenev CPU Architecture:Any

[27 Jul 2005 8:03] conor meegan
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.
[27 Jul 2005 8:53] Valeriy Kravchuk
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)
[16 Sep 2005 16:29] Valeriy Kravchuk
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)
[21 Sep 2005 17:14] Petr Chardin
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.
[26 Nov 2005 14:01] Dmitry Lenev
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 ; |
[19 Dec 2005 11:00] Bugs System
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
[12 Jan 2006 22:52] Bugs System
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
[16 Jan 2006 16:54] Dmitry Lenev
Fixed in 5.0.19 and 5.1.6
[16 Jan 2006 22:02] Mike Hillyer
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>