Bug #7705 CONVERT_TZ() crashes with subquery/WHERE on index column
Submitted: 6 Jan 2005 14:02 Modified: 5 Feb 2005 1:48
Reporter: Andrew Edem Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.8 OS:Linux (Linux/RedHat9)
Assigned to: Dmitry Lenev CPU Architecture:Any

[6 Jan 2005 14:02] Andrew Edem
Description:
This is a two part bug. The first part is related to using columns from a subquery with CONVERT_TZ, the second part is related to using CONVERT_TZ inside of SELECT ... FROM  if there is a WHERE clause that uses an index column of the table.

This query will make mysql crash reliably on 4.1.8. I suspect it is related to bug 4508

I have proplery generated time zone tables, my system time zone is set to 'PST8PDT', and in other circumstances CONVERT_TZ does work properly.

I have not tested this on 5.0.x yet.

How to repeat:
This bug has two parts, first one related to subqueries:

SELECT CONVERT_TZ(NOW(), @@time_zone, custTimeZone) FROM (SELECT 'UTC' AS custTimeZone) AS tmp;

The first time the above query is run, it will crash the server. On subsiquent attempts, it will return null, until the "to" time zone is changed from UTC to something else, in which case it will crash the server again.

The second case is related to WHERE on index columns. I have a table defined as such:

CREATE TABLE tzbug (custID int(11) NOT NULL auto_increment, custTimeZone varchar(31), PRIMARY KEY (custID)) TYPE=InnoDB;

INSERT INTO tzbug (custTimeZone) VALUES ('EST5EDT');
INSERT INTO tzbug (custTimeZone) VALUES ('CST6CDT');
INSERT INTO tzbug (custTimeZone) VALUES ('MST7MDT');
INSERT INTO tzbug (custTimeZone) VALUES ('PST8PDT');

SELECT CONVERT_TZ(NOW(), @@time_zone, custTimeZone) FROM tzbug;

It returns me the rows as I would expect without any errors.

However, if I try the following:
mysql> SELECT CONVERT_TZ(NOW(), @@time_zone, custTimeZone) FROM tzbug WHERE custID = 2;
ERROR 2013 (HY000): Lost connection to MySQL server during query

The server crashes! The only difference is the WHERE cluase using the index.

Now, lets create another column called custID2 with the same values as custID

ALTER TABLE tzbug ADD COLUMN custID2 int(11) NOT NULL;
UPDATE tzbug SET custID2 = custID;

Then, if you query:

SELECT CONVERT_TZ(NOW(), @@time_zone, custTimeZone) FROM tzbug WHERE custID2 = 3;

The server doesn't crash! This behavior is the same regardless of whether or not the table is MyISAM or InnoDB.

-Andrew
[6 Jan 2005 14:19] Hartmut Holzgraefe
also crashes 5.0 (bk build from Dec 20th, using SuSE 9.0)
[6 Jan 2005 20:54] Andrew Edem
After further investigation of the first problem, it seems as though there is an issue with the arguments passed to the function.

When args[2]->const_item() is called in Item_func_convert_tz::fix_fields(), it returns 0, but, when it is called in: Item_func_convert_tz::get_date(), args[2]->const_item() returns 1, this means that the to_tz variable is never initialized, which causes the segmentation violation.

Unfortunately I'm not familiar enough with the internals of mysql to guess as to why this is happening. Any ideas?
[7 Jan 2005 13:44] Andrew Edem
I have created a patch that works around this problem. I do not thing that this is an actual solution, because it does not address the core problem which is that in some cases object's arguments args[1] and args[2] are always marked as constant when get_date() is called, regardless of whether or not they are constant strings. My solution is to simply always look up both time zone arguments every time the function is called. Obviously this is going to lead to performance penalties if you perform a CONVERT_TZ() with one time zone as a constant string and another as a column name on a large number of rows. However, at least it won't cause mysqld to SEGV.

Patch: http://www.kaxis.cx/~andrew/item_timefunc.cc.diff
[29 Jan 2005 18:52] Dmitry Lenev
Fixed in 4.1.10 and 5.0.3
[5 Feb 2005 1:48] Paul Dubois
Mentioned in 4.1.10 and 5.0.3 change notes.