Bug #11081 Using a CONVERT_TZ function in a stored function or trigger fails

Description:
Simple stored procedures that worked in 5.0.4 fail in 5.0.6.  The problem has been isolated from my procedures to be the CONVERT_TZ function.  The SQL when used outside a stored procedure works fine.  However, executing the SQL through a stored procedure gives the following error: ERROR 1146 (42S02): Table '.' doesn't exist.  I have provided a sample table and data in order to duplicate the problem.  I have implemented the timezone support from the tables supplied in the timezone-2004e.zip because of the lack of native Windows support.

TEE OUTPUT (From my sample provided in 'How to repeat':
root@sbsrp01qahq-psc_trp~> DROP PROCEDURE IF EXISTS test;
Query OK, 0 rows affected (0.05 sec)

root@sbsrp01qahq-psc_trp~> DROP TEMPORARY TABLE IF EXISTS test_date;
Query OK, 0 rows affected (0.09 sec)

root@sbsrp01qahq-psc_trp~> 
root@sbsrp01qahq-psc_trp~> CREATE TEMPORARY TABLE test_date (int_dt INT UNSIGNED NOT NULL default 0) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

root@sbsrp01qahq-psc_trp~> 
root@sbsrp01qahq-psc_trp~> INSERT INTO test_date (int_dt)
    -> VALUES (1117758889),(1117757925),(1117761679),(1117795287),(1117741186),(1117734772),(1117791602),(1117754510),(1117745928),(1117736569);
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

root@sbsrp01qahq-psc_trp~> 
root@sbsrp01qahq-psc_trp~> DELIMITER //
root@sbsrp01qahq-psc_trp~> 
root@sbsrp01qahq-psc_trp~> CREATE PROCEDURE test()
    -> SELECT CONVERT_TZ(FROM_UNIXTIME(t.int_dt),'US/Pacific','GMT') AS Time_UTC
    -> FROM test_date AS t;
    -> //
Query OK, 0 rows affected (0.05 sec)

root@sbsrp01qahq-psc_trp~> 
root@sbsrp01qahq-psc_trp~> DELIMITER ;
root@sbsrp01qahq-psc_trp~> 
root@sbsrp01qahq-psc_trp~> SELECT CONVERT_TZ(FROM_UNIXTIME(t.int_dt),'US/Pacific','GMT') AS Time_UTC
    -> FROM test_date AS t;
+---------------------+
| Time_UTC            |
+---------------------+
| 2005-06-03 00:34:49 |
| 2005-06-03 00:18:45 |
| 2005-06-03 01:21:19 |
| 2005-06-03 10:41:27 |
| 2005-06-02 19:39:46 |
| 2005-06-02 17:52:52 |
| 2005-06-03 09:40:02 |
| 2005-06-02 23:21:50 |
| 2005-06-02 20:58:48 |
| 2005-06-02 18:22:49 |
+---------------------+
10 rows in set (0.03 sec)

root@sbsrp01qahq-psc_trp~> 
root@sbsrp01qahq-psc_trp~> call test();
ERROR 1146 (42S02): Table '.' doesn't exist

How to repeat:
############## SAMPLE TEST ################

DROP PROCEDURE IF EXISTS test;
DROP TEMPORARY TABLE IF EXISTS test_date;

CREATE TEMPORARY TABLE test_date (int_dt INT UNSIGNED NOT NULL default 0) ENGINE=MyISAM;

INSERT INTO test_date (int_dt)
VALUES (1117758889),(1117757925),(1117761679),(1117795287),(1117741186),(1117734772),(1117791602),(1117754510),(1117745928),(1117736569);

DELIMITER //

CREATE PROCEDURE test()
SELECT CONVERT_TZ(FROM_UNIXTIME(t.int_dt),'US/Pacific','GMT') AS Time_UTC
FROM test_date AS t;
//

DELIMITER ;

SELECT CONVERT_TZ(FROM_UNIXTIME(t.int_dt),'US/Pacific','GMT') AS Time_UTC
FROM test_date AS t;

call test();

############### END TEST ###################

Verified on Linux and Windows with server 5.0.7 BK. Indeed the version
5.0.4 on Windows not presents this behavior.

DELIMITER //
DROP TABLE IF EXISTS tz_test;
//
CREATE TABLE tz_test (normal_time INT(11) UNSIGNED, gmt_time VARCHAR(100));
//
DROP TRIGGER BI_tztest_fer;
//
CREATE TRIGGER BI_tztest_fer
BEFORE INSERT ON tz_test
FOR EACH ROW
BEGIN
	SET NEW.gmt_time = CONVERT_TZ(FROM_UNIXTIME(NEW.normal_time), 'America/Rainy_River', 'GMT');
END;
//
INSERT INTO tz_test (normal_time) VALUES (UNIX_TIMESTAMP());
//

ERROR 1146 (42S02): Table '.' doesn't exist

This also does not work on Win2K in 5.0.15

Still broken on Win32 5.0.17

The last test case (posted at [31 Oct 2005 20:24] by Justin Plock) is still repeatable on 5.0.21-BK (ChangeSet@1.2148.1.1, 2006-04-10) on Linux.

Note that with 5.0.21 problem with stored procedures is not repeatable. It exists though for stored functions and triggers (or stored procedures which are called from them.

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/5175

Approved by email with minor comments.

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/5410

Fixed in 5.0.22 and 5.1.10

Noted in 5.0.22, 5.1.10 changelogs.

Use of <literal>CONVERT_TZ()</literal> in a stored function or
trigger (or in a stored procedure called from a stored
function or trigger) caused an error. (Bug #11081)