MySQL Bugs: #7705: CONVERT_TZ() crashes with subquery/WHERE on index column

Bug #7705	CONVERT_TZ() crashes with subquery/WHERE on index column
Submitted:	6 Jan 2005 15:02	Modified:	5 Feb 2005 2:48
Reporter:	Andrew Edem
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	4.1.8	OS:	Linux (Linux/RedHat9)
Assigned to:	Dmitri Lenev	Target Version:

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.