MySQL Bugs: #58996: SHOW CREATE TRIGGER fails when subject table is shadowed by a temporary table.

Bug #58996	SHOW CREATE TRIGGER fails when subject table is shadowed by a temporary table.
Submitted:	17 Dec 2010 11:36	Modified:	30 Mar 2011 16:51
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.54-bzr, 5.5.8	OS:	Any
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any

Description:
SHOW CREATE TRIGGER fails to show trigger definition when there is a temporary table shadowing trigger's subject table. See How-to-repeat for details.

How to repeat:
create table t1 (i int);
create trigger t1_bi before insert on t1 for each row begin end;
show create trigger t1_bi;
# Works shows:
#
# Trigger sql_mode        SQL Original Statement  character_set_client    collation_connection    Database Collation
# t1_bi           CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1 for each row begin end      latin1  latin1_swedish_ci       latin1_swedish_ci

create temporary table t1 (j int);
show create trigger t1_bi;
# Fails with
# Error: 1360: Trigger does not exist

5.5.8 is also affected:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.8 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1 (i int);
ERROR 1050 (42S01): Table 't1' already exists
mysql> create trigger t1_bi before insert on t1 for each row begin end;
Query OK, 0 rows affected (0.33 sec)

mysql> show create trigger t1_bi;
+---------+----------------------------------------------------------------+----
--------------------------------------------------------------------------------
--------+----------------------+----------------------+--------------------+
| Trigger | sql_mode                                                       | SQL
 Original Statement
        | character_set_client | collation_connection | Database Collation |
+---------+----------------------------------------------------------------+----
--------------------------------------------------------------------------------
--------+----------------------+----------------------+--------------------+
| t1_bi   | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CRE
ATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1 for each row be
gin end | utf8                 | utf8_general_ci      | utf8_general_ci    |
+---------+----------------------------------------------------------------+----
--------------------------------------------------------------------------------
--------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> create temporary table t1 (j int);
Query OK, 0 rows affected (0.16 sec)

mysql> show create trigger t1_bi;
ERROR 1360 (HY000): Trigger does not exist

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/commits/127876

Pushed into mysql-trunk 5.6.2 (revid:jon.hauglid@oracle.com-20110105144328-c9ee981z819p2zmw) (version source revid:jon.hauglid@oracle.com-20110105144328-c9ee981z819p2zmw) (merge vers: 5.6.2) (pib:24)

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/commits/127980

Noted in 5.6.2 changelog.

SHOW CREATE TRIGGER failed if there was a temporary table with the
same name as the trigger subject table. 

CHANGESET - http://lists.mysql.com/commits/127980