[MDEV-10135] CONVERT_TZ does not work with "out of range" inputs Created: 2016-05-27  Updated: 2016-05-28

Status: Open
Project: MariaDB Server
Component/s: Time zones
Affects Version/s: 10.1
Fix Version/s: 10.2

Type: Bug Priority: Minor
Reporter: Pythtlow Stubson Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: upstream
Environment:

Linux hostname 3.10.0-327.13.1.el7.x86_64 #1 SMP Thu Mar 31 16:04:38 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux


 Description   

When datetime argument is out of 31 bit timestamp representable domain or would be represented as a 0, CONVERT_TZ silently returns the argument unchanged. Documentation at the time of writing does not mention such behavior.

Reproduction

Input

SELECT      CONVERT_TZ('1970-01-01 00:00:00', '+00:00', @@session.time_zone) AS ``,
 
            CONVERT_TZ('1970-01-01 00:00:01', '+00:00', @@session.time_zone) AS ``,
 
            CONVERT_TZ('2038-01-19 03:14:07', '+00:00', @@session.time_zone) AS ``,
 
            CONVERT_TZ('2038-01-19 03:14:08', '+00:00', @@session.time_zone) AS ``

Result

""	""	""	""
 
1970-01-01 00:00:00	1970-01-01 03:00:01	2038-01-19 05:14:07	2038-01-19 03:14:08



 Comments   
Comment by Elena Stepanova [ 2016-05-27 ]

MySQL manual mentions this case specifically:

If the value falls out of the supported range of the TIMESTAMP type when converted from from_tz to UTC, no conversion occurs.

MariaDB documentation should be updated accordingly.

Comment by Pythtlow Stubson [ 2016-05-27 ]

Oh so it's supposed to throw a NULL? Strangely, against its own documentation, MySQL returns wrong results too.

Comment by Elena Stepanova [ 2016-05-27 ]

I guess it depends on how one interprets documentation. First, I read it as if the arguments are definitely invalid, e.g. wrong types and such, it returns NULL; but if the values just fall out of the TS range with the given timezone, they are left as is. However, I'm not quite sure. If greenman says that the results above do not match the behavior described in the MySQL manual, maybe we'll need to consider if the function itself needs fixing.

Comment by Pythtlow Stubson [ 2016-05-27 ]

Oh, sorry, it clearly says no conversion occurs. It's so counter-intuitive and unhelpful I keep overlooking that.

Comment by Ian Gilfillan [ 2016-05-27 ]

The documentation has been updated to reflect the current behaviour. The page was also missing some detail, and I've added better examples. However, surely this should give a warning, rather than just silently not perform the conversion? Will leave the issue open for reassigning to look at that issue.

Comment by Elena Stepanova [ 2016-05-27 ]

Assigned to bar to consider the warning part.

Comment by Pythtlow Stubson [ 2016-05-28 ]

One more point: For the very first epoch second UNIX_TIMESTAMP returns a 0 (not NULL) but CONVERT_TZ treats such case as out of range. Wouldn't it be better for consistency's sake to make CONVERT_TZ work with that one value too?

Generated at Thu Feb 08 07:39:56 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.