MySQL Bugs: #63376: CONVERT_TZ should fail on empty mysql.time

Bug #63376	CONVERT_TZ should fail on empty mysql.time_zone table, currently it returns NULL
Submitted:	22 Nov 2011 8:16	Modified:	23 Nov 2011 14:51
Reporter:	Alexander Petrossian	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.52, 5.5.17	OS:	Any (-enterprise-commercial-pro)
Assigned to:		CPU Architecture:	Any

Description:
Due to misconfiguration of MySQL table mysql.time_zone turned out to be empty:
mysql> select count(*) from mysql.time_zone;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

Until we fixed that, this statement returned NULL:

mysql> select CONCAT(DATE_FORMAT(CONVERT_TZ(now(), 'SYSTEM', 'UTC'), '%Y-%m-%d %H:%i:%S'), ' UTC');
+--------------------------------------------------------------------------------------+
| CONCAT(DATE_FORMAT(CONVERT_TZ(now(), 'SYSTEM', 'UTC'), '%Y-%m-%d %H:%i:%S'), ' UTC') |
+--------------------------------------------------------------------------------------+
| NULL                                                                                 |
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Which is VERY misleading, and not expected.
We feel

How to repeat:
mysql> truncate mysql.time_zone;
mysql> select CONCAT(DATE_FORMAT(CONVERT_TZ(now(), 'SYSTEM', 'UTC'), '%Y-%m-%d %H:%i:%S'), ' UTC');

Result will be
NULL

Expected result:
error message, detailing misconfiguration

Suggested fix:
Add a check to CONVERT_TZ implementation, and all other functions that rely on mysql.time_zone contents.

If there is not enough information in mysql.time_zone content, functions, dealing with time zones should FAIL, and not return obscure _successfull_ NULL value.

Our manual, http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz, clearly says:

"This function returns NULL if the arguments are invalid."

If time_zone table is empty, then all time zones are unknown (invalid) arguments, hence the results:

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
+-----------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','GMT','MET') |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+
1 row in set (0.08 sec)

So, formally this is not a bug. But I'd also prefer to get a warning at least in this case (if not error message), not just NULL silently. So, I think this is a valid feature request.

The Timezones table MUST BE loaded at installation time (by MySQL).

Other solution could be that convert_tz to print a WARNING message indicating that Timezones table is empty.

BR, Abel