Bug #7705 CONVERT_TZ() crashes with subquery/WHERE on index column

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

also crashes 5.0 (bk build from Dec 20th, using SuSE 9.0)

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?

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

Fixed in 4.1.10 and 5.0.3

Mentioned in 4.1.10 and 5.0.3 change notes.