Bug #11081 Using a CONVERT_TZ function in a stored function or trigger fails
Submitted: 3 Jun 2005 18:34 Modified: 9 May 2006 19:00
Reporter: Mark Smith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.21-BK, 5.0.6 OS:Microsoft Windows (Win 2K3 Server)
Assigned to: Dmitry Lenev

[3 Jun 2005 18:34] Mark Smith
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 ###################
[3 Jun 2005 20:03] Miguel Solorzano
Verified on Linux and Windows with server 5.0.7 BK. Indeed the version
5.0.4 on Windows not presents this behavior.
[31 Oct 2005 19:24] Justin Plock
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
[22 Dec 2005 2:38] Justin Plock
Still broken on Win32 5.0.17
[11 Apr 2006 15:39] Valerii Kravchuk
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.
[19 Apr 2006 20:00] Dmitry Lenev
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.
[19 Apr 2006 21:16] 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/commits/5175
[20 Apr 2006 18:08] Konstantin Osipov
Approved by email with minor comments.
[24 Apr 2006 14:58] 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/commits/5410
[2 May 2006 22:51] Dmitry Lenev
Fixed in 5.0.22 and 5.1.10
[9 May 2006 19:00] Paul Dubois
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)